Count total in a rolling 12 month period?

Question

Post reply

Count total in a rolling 12 month period?

kawi6rr

SSC-Addicted

Points: 403
More actions
April 13, 2016 at 12:13 pm

#313288

I’m trying to count how many times a ProvNum has an Outcome of >= 2 in a 12 month rolling period based off the QualDate.
Table
ProvNum Outcome QualDate
123123 2 1/1/2015
4564561 2/1/2015
1231232 3/1/2015
4564562 1/21/2015
1231231 4/5/2015
1231230 6/25/2015
4564563 7/8/2015
Output
ProvNum Total
1231235
4564563
Here’s what I have so far and it’s not working for me.
SELECT
Count(ProvNum) AS Total,
QualDate
FROM [dbo].[QualityData]
WHERE Outcome >= 2 AND [QualDate] >= DATEADD(yyyy,-1,getdate())
GROUP BY [QualDate];

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

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

How do you get that output from that data?

Here's what you seem to be trying to do. Please notice on how I posted the sample data so you can do it the same way.

CREATE TABLE [QualityData](

ProvNum int,

Outcome int,

QualDate datetime

);

INSERT INTO QualityData

VALUES

(123123, 2, '1/1/2015 '),

(456456, 1, '2/1/2015 '),

(123123, 2, '3/1/2015 '),

(456456, 2, '1/21/2015'),

(123123, 1, '4/5/2015 '),

(123123, 0, '6/25/2015'),

(456456, 3, '7/8/2015 ');

SELECT ProvNum,

Count(*) AS Total

FROM [dbo].[QualityData]

WHERE Outcome >= 2

AND [QualDate] >= DATEADD(yyyy,-1,getdate())

GROUP BY ProvNum;

GO

DROP TABLE QualityData;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

kawi6rr SSC-Addicted Points: 403 More actions · Answer 2

I'm sorry I tried to be as clear as I could but it looks like I could have explained better.

Table

Is a table that's already created and filled with the data this was suppose to be an example of what I'm querying. I put it into code so the formatting was better.

Output

This was suppose to indicate the results I was looking for but I put it into code because of formatting.

Thanks for the reply it looks like you got what I was looking for.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

kawi6rr (4/13/2016)
I'm sorry I tried to be as clear as I could but it looks like I could have explained better.
Table
Is a table that's already created and filled with the data this was suppose to be an example of what I'm querying. I put it into code so the formatting was better.
Output
This was suppose to indicate the results I was looking for but I put it into code because of formatting.
Thanks for the reply it looks like you got what I was looking for.

The create table statement that I included, wasn't for you. You should have included it with your post so anyone can simply copy the code and have sample data to test.

The output was nicely formatted, but it seems that it wasn't related to the data you posted. Be sure to make the output match the sample data so we can validate before posting incorrect answers or spend more time trying to figure how to get to that result.

I'm glad that you got what you were looking for and I expect that you realized what was your mistake in the code. If you have questions, feel free to ask them so you don't have to resource to a forum every time you face a problem.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2