group cases by months between two dates

TyeReece

Board Regular
Joined
Aug 3, 2007
Messages
132
I need to get a count of cases associated with vendors, grouped by month over a time between two dates. The month part is where I have trouble. I don't know how to make the query "count" each case number by vendor for each month. Ideally what I would get is something like

Vendor Month Count
1 1/1/2018 10
2 1/1/2018 20
1 2/1/2018 9
2 2/1/2018 21
etc.

The code I have is below. Any help is appreciated


SELECT DISTINCT PCC_Vendor_Number.PCC_VNum, Placements.CRPCaseNum
FROM (PCC_Vendor_Number INNER JOIN Placements ON PCC_Vendor_Number.PCC_VNum = Placements.PCC_VNum) INNER JOIN Client ON Placements.CRPCaseNum = Client.CRPCaseNum
WHERE (((Placements.datDischarge)>=DateAdd("m",-15,DateSerial(Year(Date()),Month(Date()),1)) Or (Placements.datDischarge) Is Null) AND ((Placements.datAdmit)<DateAdd("m",-15,DateSerial(Year(Date()),Month(Date()),1))))
GROUP BY PCC_Vendor_Number.PCC_VNum, Placements.CRPCaseNum
ORDER BY PCC_Vendor_Number.PCC_VNum;
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,186
It looks like there is more than one date: Placements.datDischarge and Placements.datAdmit
And I don't know the one to use: I'll just pick one.

what if in each of the SELECT clause & GROUP BY clause you add another field: Placements.datDischarge - Day(Placements.datDischarge) + 1

and in the SELECT clause you can name it like this : Placements.datDischarge - Day(Placements.datDischarge) + 1 AS [Month]

I didn't test this at all & assume the SQL you posted works.
How is that?
 

TyeReece

Board Regular
Joined
Aug 3, 2007
Messages
132
Thanks, that allows me to group by the month of the admit date but I also need each case/vendor combo to appear for every month thereafter until discharge date or current month if discharge is null. any ideas on how to show that?
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,186
Maybe. Sounds like you'd need another table - a table of months.

Then include that new table into the query, modified if needed with the rules/logic.
 

TyeReece

Board Regular
Joined
Aug 3, 2007
Messages
132
Sorry I don't get to work on this with any regularity but I am back at it. So to recap, I am trying to get a count of people associated with a vendor for each month between two dates. (an admission and discharge date). I can pretty easily pull the person and the vendor and the first day of the month of their admission date and the last day of the month of their discharge date. What I can't figure out is how to include them in the count for the months between the first day of the admit month and the last day of the discharge month. So if a person has a first day as 6/1/2017 and last day as 3/31/2018, how to I get them to count for 7/2017 through 2/2018?
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,186
If you forget about databases for a while, how would you work it out with pencil & paper? What rules/logic do you use to create a count in a month?

When working this out on a sheet of paper,
- what tables do you have?
- what fields do you have?
- post back to explain the tables & fields & sample data, and all the rules/logic.

Please provide this sort of knowledge & the solution should be easy (in the database).
Without this information, there is for me insufficient information - I'd only be guessing or asking too many questions.
 

TyeReece

Board Regular
Joined
Aug 3, 2007
Messages
132
I hope this is what you are asking for. Sorry I'm not practiced at doing this.

For each child I have 6 pieces of information in one query:

Child Key (required)
Vendor Key (required)
Admit Date (required)
Discharge Date (may be null)
First Month (calculated)
Last Month (calculated)

So data will look like
Child Key Vendor Key Admit Date DC Date First Month Last Month
Billy Program A 2/12/2018 8/1/2018 2/1/2018 8/1/2018
Susie Program B 6/5/2018 Null 6/1/2018 10/1/2018

In my query I return the first 4 pieces of info and calculate a First Month and Last Month. When the DC date is Null, I return the month prior to the current month. (e.g., run in November, then Last Month = 10/1/2018). Children can be placed in more than one program and can be in the same program more than once. I want to count them only once per program per month.
So now for vendor 999201 I want to count this child as in the program for each month from February thru August.
And vendor 107228 I want to count the child for June thru August.
Of course, there are many vendors and children. What I want to end up with is something like the following
Vendor Month Count of Children
Program A 2/1/2018 12
Program A 3/1/2018 13
Program A 4/1/2018 11
Program A 5/1/2018 12
Program A 6/1/2018 13
Program A 7/1/2018 11
Program A 8/1/2018 14
Program B 6/1/2018 24
Program B 7/1/2018 26
Program B 8/1/2018 21
Program B 9/1/2018 22
Program B 10/1/2018 27

I can count the children per program for the First and Last Months but don’t know how to get the counts for the months in between.
 

Forum statistics

Threads
1,078,437
Messages
5,340,278
Members
399,362
Latest member
iayb

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top