Tough Date Query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Tough Date Query

Author

Topic

GaryP1973
Starting Member

2 Posts

Posted - 2012-09-25 : 14:30:05

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-25 : 14:46:29

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?)

GaryP1973
Starting Member

2 Posts

Posted - 2012-09-25 : 14:58:36

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-25 : 15:08:57

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)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-25 : 15:22:45

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources