SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Tough Date Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GaryP1973
Starting Member

USA
2 Posts

Posted - 09/25/2012 :  14:30:05  Show Profile  Reply with Quote
I am new to sqlserver 2008R2. I am creating stored proc for an SSRS report.

I am trying to get a count of red items for each month end for the last 12 months. I have a date range. Below is what the data would look like for one part number:

Availability BeginDate EndDate
RED 2012-04-03 00:00:00.000 2012-04-06 00:00:00.000
WHITE 2012-04-07 00:00:00.000 2012-04-12 00:00:00.000
RED 2012-04-13 00:00:00.000 2012-08-02 00:00:00.000
RED 2012-08-03 00:00:00.000 2012-08-18 00:00:00.000
RED 2012-08-19 00:00:00.000 2012-08-19 00:00:00.000
RED 2012-08-20 00:00:00.000 2012-09-06 00:00:00.000

So the result should look like Oct-31-2011 Count:0
until the end of April-30-2012 which would be Count:1. And 1 for May,June, July and the rest of the year it is still 1.

I think I need some kind of loop. Can you help? Thank You!

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/25/2012 :  14:46:29  Show Profile  Reply with Quote
Can you explain the logic in a bit more detail? Specifically:

a) Is the count for April 1 because there was a break in RED, or a because RED ended, or because of something else?

b) Based on your answer to (a), what is the logic to determine that the count is 1 for the rest of the year (for example how did you calculate that it is 1 in December, 2012?)
Go to Top of Page

GaryP1973
Starting Member

USA
2 Posts

Posted - 09/25/2012 :  14:58:36  Show Profile  Reply with Quote
Thanks for the reply.

I have to go back a year counting the number of records at the end of each month. So the first entry for this record is:
RED 2012-04-03 00:00:00.000 2012-04-06 00:00:00.000
But I really don't care about this. I only care about what is on April 30.

Then it is red from 04-14-12 --> 08-02-12 so all of those months would be 1. (May,June,July). Then the record is loaded a few more times because of other data changing but it is still 'RED' which is what I care about.

Months previous to this record being entered on 04-13-12 should be zero.

When I refereed to the rest of the year I did not mean Dec, 2012... Just Year to Date.

Thanks
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/25/2012 :  15:08:57  Show Profile  Reply with Quote
It would be easiest to do this using a Calendar or numbers table. If you don't have one in your database, you can create one like this:
CREATE TABLE #N(n INT NOT NULL IDENTITY(1,1));
GO
INSERT INTO #N DEFAULT VALUES
GO 100
Once you have that table, then use it like this:
DECLARE @startDate DATETIME;
SET @startDate = DATEADD(mm,DATEDIFF(mm,0,GETDATE())-11,0);

SELECT
	DATEADD(dd,-1,DATEADD(mm,n,@startDate)) AS [End of Month],
	COUNT(b.Availability) AS [Count]
FROM
	#N a
	LEFT JOIN YourTable b ON
		b.BeginDate <= DATEADD(dd,-1,DATEADD(mm,n,@startDate))
		AND b.EndDate >= DATEADD(dd,-1,DATEADD(mm,n,@startDate))
		AND b.Availability	= 'RED'
WHERE
	DATEADD(dd,-1,DATEADD(mm,n,@startDate)) < GETDATE()
GROUP BY
	DATEADD(dd,-1,DATEADD(mm,n,@startDate));
Edits: See in read (now that I have the benefit of Lamprey's test data)

Edited by - sunitabeck on 09/25/2012 15:46:42
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 09/25/2012 :  15:22:45  Show Profile  Reply with Quote
I only had a couple of minutes to mess around, so jsut posting incase it might help. Although, I suspect there is an edge case (end of month date) that will mess things up, so this probably isn't perfect:
-- Setup Sample Data
DECLARE @Foo TABLE (Availability VARCHAR(20), BeginDate DATETIME, EndDate DATETIME)
INSERT @Foo VALUES

('RED', '2012-04-03 00:00:00.000', '2012-04-06 00:00:00.000'),
('WHITE', '2012-04-07 00:00:00.000', '2012-04-12 00:00:00.000'),
('RED', '2012-04-13 00:00:00.000', '2012-08-02 00:00:00.000'),
('RED', '2012-08-03 00:00:00.000', '2012-08-18 00:00:00.000'),
('RED', '2012-08-19 00:00:00.000', '2012-08-19 00:00:00.000'),
('RED', '2012-08-20 00:00:00.000', '2012-09-06 00:00:00.000')

;WITH CteDateRange
AS
(
    SELECT 
        DATEADD(MONTH, DATEDIFF(MONTH, 0, SYSDATETIME()) - 12, 0) AS MonthStart,
        DATEADD(MONTH, DATEDIFF(MONTH, 0, SYSDATETIME()) - 11, 0) AS MonthEnd

  UNION ALL
  
    SELECT 
        DATEADD(MONTH, 1, MonthStart),
        DATEADD(MONTH, 1, MonthEnd)
    FROM 
        CteDateRange
    WHERE 
        DATEADD(MONTH, 1, MonthStart) <= DATEADD(MONTH, DATEDIFF(MONTH, 0, SYSDATETIME()), 0)
)
SELECT
    DATEADD(DAY, -1, Cte.MonthEnd),
    CASE WHEN T.Availability IS NOT NULL THEN 1 ELSE 0 END
FROM 
    CteDateRange AS Cte
LEFT OUTER JOIN 
    @Foo AS T
    ON T.Availability = 'RED'
    AND Cte.MonthEnd >= T.BeginDate 
    AND Cte.MonthEnd <= T.EndDate
GROUP BY
    DATEADD(DAY, -1, Cte.MonthEnd),
    CASE WHEN T.Availability IS NOT NULL THEN 1 ELSE 0 END
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000