July 7, 2014 at 12:00 pm
Im trying to count the number of rows with a PSIOutcome of 0 starting from the last month by Division Region,PSI month, PSIKey....so if I have
DivisionRegion/PSIYear/PSIMonth/PSIKey/PSIOutcome
A/2014/01/2/1
A/2014/02/2/0
A/2014/03/2/0
A/2014/04/2/0
A/2014/05/2/0
A/2014/06/2/0
The count of PSIOutcome would be 5. If July comes around and PSIOutcome is 0, it would change the count to 6. If PSIOutcome for July is > 0 then the count would be 0.
Any help is appreciated.
SELECT f.[DivisionRegion]
,f.[PSIYear]
,f.[psimonth]
,f.[psikey]
,f.[PSIOutcome]
FROM [EDS].[dbo].[PSITableFinal] f
order by f.psiyear,f.psimonth
July 7, 2014 at 12:18 pm
I'm not sure if you're expecting something like this.
CREATE TABLE PSITableFinal
(DivisionRegion char(1),
PSIYear char(4),
PSIMonth char(2),
PSIKey int,
PSIOutcome int)
INSERT INTO PSITableFinal VALUES
('A','2014','01',2,1),
('A','2014','02',2,0),
('A','2014','03',2,0),
('A','2014','04',2,0),
('A','2014','05',2,0),
('A','2014','06',2,0),
('A','2014','07',2,0)
DECLARE @Month char(2) = '07',
@Year char(4) = '2014'
SELECT COUNT(*)
FROM [PSITableFinal] f
WHERE PSIYear = @Year
AND PSIMonth <= @Month
AND PSIOutcome = 0
AND PSIMonth >= ( SELECT MAX(x.PSIMonth)
FROM [PSITableFinal] x
WHERE x.PSIYear = @Year
AND x.PSIMonth <= @Month
AND x.PSIOutcome > 0)
--Change the value for July to test both cases.
UPDATE [PSITableFinal] SET
PSIOutcome = 1
WHERE PSIYear = @Year
AND PSIMonth = @Month
SELECT COUNT(*)
FROM [PSITableFinal] f
WHERE PSIYear = @Year
AND PSIMonth <= @Month
AND PSIOutcome = 0
AND PSIMonth >= ( SELECT MAX(x.PSIMonth)
FROM [PSITableFinal] x
WHERE x.PSIYear = @Year
AND x.PSIMonth <= @Month
AND x.PSIOutcome > 0)
GO
DROP TABLE [PSITableFinal]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply