Count Consecutive Months Back For Value From Date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Count Consecutive Months Back For Value From Date

Author

Topic

Hugobarb
Starting Member

4 Posts

Posted - 2012-01-10 : 11:08:53

I am an intermediate with SQL and can usually figure things out but mainly use it for reporting. I have an issue where I need to count the number of consecutive months back (historically) where hours = 0 from a user selected month-end date. Essentially count back the number of months from the current month that hours = 0 based on my data set below. I can't even begin to write the psuedo code or logical steps for how to do this. I have read that I could use a cursor but that is bad programming and I have never done that either. - Thank you

Based on this data set I would expect my if the user selected month end date was 11/30/2011. The result set would be:

222 41106[Engine] 1235 3(months)
555 41109[Engine] 1237 2(months)

666 41114[Engine] 1238 - would not show in result set or only show 0 bc. only current month end has 0 hours no prior months.

If the user selected month end 10/31 then Plane 222 would show 2 months for 41106[Engine]

Plane Part Contract Num month end Hours
111 1621 [APU] 1234 7/31/2011 0
222 41106 [Engine] 1235 11/30/2011 0
222 41106 [Engine] 1235 10/31/2011 0
222 41106 [Engine] 1235 9/30/2011 0
444 41109 [Engine] 1236 9/30/2011 0
555 41109 [Engine] 1237 11/30/2011 0
555 41109 [Engine] 1237 10/31/2011 0
555 41109 [Engine] 1237 8/31/2011 0
666 41114 [Engine] 1238 11/30/2011 0
666 41114 [Engine] 1238 8/31/2011 0
666 41114 [Engine] 1238 7/31/2011 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 11:21:52

[code]
DECLARE @MonthEndDate datetime
SET @MonthEndDate='20111130'

SELECT Plane, Part, [Contract], [Num], [month end], Hours
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY Plane,Part ORDER BY [month end] DESC) AS Rn
FROM YourTable
WHERE DATEPART(dd,[month end])=DATEPART(dd,@MonthEndDate)
AND Hours=0
)t
WHERE Rn=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hugobarb
Starting Member

4 Posts

Posted - 2012-01-10 : 11:45:02

quote:
Originally posted by visakh16

DECLARE @MonthEndDate datetime
SET @MonthEndDate='20111130'

SELECT Plane, Part, [Contract], [Num], [month end], Hours
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY Plane,Part ORDER BY [month end] DESC) AS Rn
FROM YourTable
WHERE DATEPART(dd,[month end])=DATEPART(dd,@MonthEndDate)
AND Hours=0
)t
WHERE Rn=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thank you. How would I be able to count though the number of months back from the month end date. So for example right now I want to show the # of consecutive months back basically a count from the month selected until it is not zero. The result set from you query is:
222 41106 [Engine] 1235 2011-11-30 00:00:00 0
444 41109 [Engine] 1236 2011-09-30 00:00:00 0
555 41109 [Engine] 1237 2011-11-30 00:00:00 0
666 41114 [Engine] 1238 2011-11-30 00:00:00 0

I would only want to see

555 | 41109 [Engine] | 1237 | 2011-11-30 | 2(months)
222 | 41106 [Engine] | 1235 | 2011-11-30 | 3(months)

666 should either show 0 months and 444 should not appear since it does not have 0 for 11/30/2011

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 11:49:48

[code]
DECLARE @MonthEndDate datetime
SET @MonthEndDate='20111130'

SELECT Plane, Part, [Contract], [Num], [month end], Hours,DATEDIFF(mm,[month end],@MonthEndDate) +1 AS MonthsBefore
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY Plane,Part ORDER BY [month end] DESC) AS Rn
FROM YourTable
WHERE DATEPART(dd,[month end])=DATEPART(dd,@MonthEndDate)
AND Hours=0
)t
WHERE Rn=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hugobarb
Starting Member

4 Posts

Posted - 2012-01-10 : 12:00:42

quote:
Originally posted by visakh16

DECLARE @MonthEndDate datetime
SET @MonthEndDate='20111130'

SELECT Plane, Part, [Contract], [Num], [month end], Hours,DATEDIFF(mm,[month end],@MonthEndDate) +1 AS MonthsBefore
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY Plane,Part ORDER BY [month end] DESC) AS Rn
FROM YourTable
WHERE DATEPART(dd,[month end])=DATEPART(dd,@MonthEndDate)
AND Hours=0
)t
WHERE Rn=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thank you. I think I am close. The query is doing what it should but the result set is wrong in part bc. it is taking a date part on the day which is 30 so it is only showing where date ends in 30 (ie nov and dec). 444 is showing 3 months but 222 should show 3 and 555 should show 2. I would expect that 444 would not even appear since it does not have any data for 11/30/2011 and the query should only show data when the month end date has 0. So if the month end date is 11.30 then say how many prior consecutive months was it 0. If it was not in the case of 666 where only the 11/30 period is 0 then it should just say 0 or not be in result set. 444 should not be in result set at all. If the monthend date was 10/31/2011 the n 222 would be 2 and 555 would be 1.

Thanks again I feel like I am starting to understand the approach but m still a bit lost.

The result set is:
222 41106 [Engine] 1235 2011-11-30 0 1
444 41109 [Engine] 1236 2011-09-30 0 3
555 41109 [Engine] 1237 2011-11-30 0 1
666 41114 [Engine] 1238 2011-11-30 0 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 12:31:11

sorry i didnt understand few things
the query should only show data when the month end date has 0
in which case you've monthenddate as 0? i cant see single row like that in above posted data (initial post)
If it was not in the case of 666 where only the 11/30 period is 0 then it should just say 0 or not be in result set.
do you mean row shouldnt come at all or you need row with 0 as months value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hugobarb
Starting Member

4 Posts

Posted - 2012-01-10 : 12:49:34

quote:
Originally posted by visakh16

sorry i didnt understand few things
the query should only show data when the month end date has 0
in which case you've monthenddate as 0? i cant see single row like that in above posted data (initial post)
If it was not in the case of 666 where only the 11/30 period is 0 then it should just say 0 or not be in result set.
do you mean row shouldnt come at all or you need row with 0 as months value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sorry for the confusion 0 refers to the hours. The data set is going to show each plane and part with hours of 0 for the past year. The report is going to show the plane and part for a specific monthend date. What I am trying to do is count back how many consecutive months that the part was at zero from the user selected date (if any). So in my dataset if the selected date is 11/30/2011 then the report will show plane 111, 222, 555, and 666. These all have 0 hours for the part on the 11/30/2011 monthend. Now I would like a field that counts back how many prior consecutive months it was at zero. So for 222 it would be 2 months 10/31 and 9/30. For 555 it would only be 1 bc. the 9/30 period is not in the dataset and 8/31 is not consecutive..

Regarding 666 I would prefer to have a row that says zero since it had no prior months at zero or it can not show at all and I will code around it in the report.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 13:03:44

[code]

DECLARE @MonthEndDate datetime
SET @MonthEndDate='20111130'

;With Counter_Table
AS
(
SELECT Plane, Part, Contract, Num, [month end], Hours,CAST(0 AS int) AS Cnt
FROM Table
WHERE Hours=0
AND [month end]=@MonthEndDate

UNION ALL

SELECT t.Plane, t.Part, t.Contract, t.Num, t.[month end], t.Hours ,ct.Cnt + 1
FROM table t
INNER JOIN Counter_Table ct
ON ct.[month end] = DATEADD(mm,1,t.[month end])
AND ct.Plane = t.Plane
AND ct.Part = t.Part
AND t.Hours=0
)

SELECT Plane, Part, Contract, Num, [month end], Hours,MAX(Cnt) OVER (PARTITION BY Plane,Part) AS Cnt
FROM Counter_Table
WHERE Cnt=0

OPTION (MAXRECURSION 0)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources