Count and Group by Date Range

RMS123

Board Regular
Joined
Jun 1, 2016
Messages
62
Hi there,

I am trying to do a count based on Account, Vendor, Amount and date range of 3 days, while being able to select the date as well... If that makes sense.

This is my SQL so far:
Rich (BB code):
SELECT DATA.ACCOUNT, DATA.VENDOR, DATA.AMOUNT, Count(DATA.AMOUNT) AS CountOfAMOUNT, DATA.DUEDATE
FROM DATA
WHERE (((DATA.DUEDATE) Between [DUEDATE]-1 And [DUEDATE]+1))
GROUP BY DATA.ACCOUNT, DATA.VENDOR, DATA.AMOUNT, DATA.DUEDATE;

Without Selecting date:

Book1
ABCD
1ACCOUNTVENDORAMOUNTCountOfAMOUNT
21AAA1003
32CCC5002
43DDD101
54EEE80001
Sheet1


With Date Selected:

Book1
FGHIJ
1ACCOUNTVENDORAMOUNTCountOfAMOUNTDUEDATE
21AAA100107-Sep-16
31AAA100108-Sep-16
41AAA100109-Sep-16
52CCC500106-Sep-16
62CCC500107-Sep-16
73DDD10106-Sep-16
84EEE8000105-Sep-16
Sheet1


Desired Result:

Book1
FGHIJ
1ACCOUNTVENDORAMOUNTCountOfAMOUNTDUEDATE
21AAA100307-Sep-16
31AAA100308-Sep-16
41AAA100309-Sep-16
52CCC500206-Sep-16
62CCC500207-Sep-16
73DDD10106-Sep-16
84EEE8000105-Sep-16
Sheet1


Thanks for any help on this :)
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
You are getting each date value is it's own group, likely because the field contains time. If so, try the format function to format your query field based on the day, month and year. If you are sure there is no time component in the date (you have to check the table field properties to be sure; just because you don't see time doesn't mean it isn't there) then try adding a calculated field that concatenates the entire date using the DatePart function 3x - one for day, one for month and one for year - and grouping on that.
 
Last edited:
Upvote 0
I tried that and still got these results, maybe I am doing something wrong?


Book1
LMNOP
1ACCOUNTVENDORAMOUNTCountOfAMOUNTDUEDY
21AAA1001792016
31AAA1001892016
41AAA1001992016
52CCC5001692016
62CCC5001792016
73DDD101692016
84EEE80001592016
Sheet1
 
Upvote 0
When someone makes two suggestions and you say "I tried that" it doesn't shed much light on whatever that attempt was. An example of the query design grid or sql would be helpful. So I'm not convinced either of those suggestions wouldn't work, but that's not to say I have the solution.

Anyway, to be sure you understand the issue, and therefore have a better chance at discovering a solution, here's what's going on. Each date is a group. On each date you have associated Account, Vendor and Amount data but the count is 1. If you had 4 transactions on 07-Sep-16, the count for that date group would be 4 and might more resemble what you're after. However, that would be a count over that grouping (07-Sep-16), not an overall count that I think you're after. If you think of it that way, you should be able to grasp why you get what you want without a date, - the date introduces a new grouping level.

Another possible solution is to create a cross tab query that uses your totals query and the table that contains the date. I can't suggest how to get this date (based on some ID or Min or Max function) since you've only posted results and no sample of the underlying data. With this, you might be able to coerce a single date value into a column beside the first query results.
 
Last edited:
Upvote 0
this is a cartesian product, so can be slow with large amounts of data

[btw, it shouldn't matter, but I did this in Excel.] so there is one query with all except the due dates and then a cross join with the query containing due dates

this seems a little awkward, likely a more efficient approach exists.

SELECT B.ACCOUNT, B.VENDOR, B.AMOUNT, B.[Count of Amount], D.DUEDATE
FROM (SELECT A.ACCOUNT, A.VENDOR, A.AMOUNT, COUNT(A.AMOUNT) AS [Count of Amount]
FROM tblA A
GROUP BY A.ACCOUNT, A.VENDOR, A.AMOUNT) B,
(SELECT C.ACCOUNT, C.VENDOR, C.AMOUNT, C.DUEDATE
FROM tblA C) D
WHERE B.ACCOUNT = D.ACCOUNT AND B.VENDOR = D.VENDOR AND B.AMOUNT = D.AMOUNT

PS I notice afterwards I left out the 3 day date limitation, so just add that. cheers
 
Last edited:
Upvote 0
I've come to this late but you could try leaving DATA.DUEDATE out of the display fields. That way you don't need to group by it.

So, change...

Code:
SELECT DATA.ACCOUNT, DATA.VENDOR, DATA.AMOUNT, Count(DATA.AMOUNT) AS CountOfAMOUNT, [COLOR=#ff0000]DATA.DUEDATE[/COLOR]
FROM DATA
WHERE (((DATA.DUEDATE) Between [DUEDATE]-1 And [DUEDATE]+1))
GROUP BY DATA.ACCOUNT, DATA.VENDOR, DATA.AMOUNT, DATA.DUEDATE;

To...

Code:
SELECT DATA.ACCOUNT, DATA.VENDOR, DATA.AMOUNT, Count(DATA.AMOUNT) AS CountOfAMOUNT
FROM DATA
WHERE (((DATA.DUEDATE) Between [DUEDATE]-1 And [DUEDATE]+1))
GROUP BY DATA.ACCOUNT, DATA.VENDOR;

Also, because DATA.AMOUNT is aggregated as a count, leave it out of the grouping
Denis
 
Last edited:
Upvote 0
Thanks for all the suggestion guys, it really helped me to figure this best possible solution for this.

I think the simplest route is to to take out selecting the date and then do an inner join to acquire the date after.


Code:
SELECT DATA.ACCOUNT, DATA.VENDOR, DATA.AMOUNT, Count(DATA.AMOUNT) AS CountOfAMOUNT
FROM DATA
WHERE (((DATA.DUEDATE) Between [DUEDATE]-1 And [DUEDATE]+1))
GROUP BY DATA.ACCOUNT, DATA.VENDOR, DATA.AMOUNT;

Code:
SELECT COUNT.*, DATA.DUEDATE
FROM [COUNT] INNER JOIN DATA ON (COUNT.AMOUNT = DATA.AMOUNT) AND (COUNT.VENDOR = DATA.VENDOR) AND (COUNT.ACCOUNT = DATA.ACCOUNT);


Result:

Book1
ABCDE
1ACCOUNTVENDORAMOUNTCountOfAMOUNTDUEDATE
21AAA100307-Sep-16
31AAA100309-Sep-16
41AAA100308-Sep-16
52CCC500206-Sep-16
62CCC500207-Sep-16
73DDD10106-Sep-16
84EEE8000105-Sep-16
Sheet1
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top