group cases by months between two dates

[TyeReece]

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]

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]

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]

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]

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]

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]

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.