| Author |
 Topic  |
|
|
GaryP1973
Starting Member
USA
2 Posts |
 Posted - 09/25/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 09/25/2012 : 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
USA
2 Posts |
Posted - 09/25/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 09/25/2012 : 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) |
Edited by - sunitabeck on 09/25/2012 15:46:42 |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3821 Posts |
Posted - 09/25/2012 : 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
|
|
|
| |
Topic |
|
|
|
Tough Date Query
