? on keeping a continues count grouped by month ???

Question

Post reply

? on keeping a continues count grouped by month ???

jbalbo

SSCertifiable

Points: 7583
More actions
October 16, 2024 at 6:21 pm

#4469292

Hi All,
Not sure why I'm hung up on this, but I am.....
I have a table called "ClientCases" This table has an admit and discharge date for clients
I would like to query a year of data via a start and end date pull all the clients active in that range, but *(this is where i get lost)
I'd like to group the data by MMyyyy, and keep a continuous count based on if the client was discharged
Lets say I do 1/1/23 thru 12/31/23
I'd like to have something like
Jan2023 - 212
Feb2023 - 223 - where the 223 are the clients from January(212) that are not discharged and any new clients entered in Febuary
March2023 - 233, etc
I know I left something out.....

Thanks
Joe

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76985 More actions · Answer 1

Not sure why you failed to post sample data and expected results, but you did...

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

Here's an introduction to what you need to do...

https://www.sqlservercentral.com/articles/finding-%e2%80%9cactive%e2%80%9d-rows-for-the-previous-month-sql-spackle-1

If you need more detailed help, we'll need some "Readily Consumable Data". Please see the first article in my signature line below for why and one way for how. Thanks for helping us help you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jbalbo SSCertifiable Points: 7583 More actions · Answer 3

Thank You Jeff,

Let me read thru it, mess around with some data, I will get back with some data if I get hung up.

I really appreciate the info, Thank you !!

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

jbalbo wrote:

Thank You Jeff,
Let me read thru it, mess around with some data, I will get back with some data if I get hung up.
I really appreciate the info, Thank you !!

It's an introduction to your issue and it's for only one month. C'mon back if you can't figure out how to "extend it" to many months and we'll see what we can hammer out together.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)