June 19, 2013 at 5:45 am
Hello all,
I wonder if somebody could point me in the right direction...
I have following table:
SET DATEFORMAT DMY;
SET NOCOUNT ON;
DECLARE @t TABLE (ITEM varchar(10), QTY INT, CREATED DATETIME, ENDS DATETIME,USERID INT)
INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 1',1,'27-05-2013 09:30:00',NULL,1
INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 3',1,'28-05-2013 14:50:00',NULL,1
INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 2',1,'27-05-2013 09:45:00',NULL,2
INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 4',1,'28-05-2013 11:45:00',NULL,2
INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 5',1,'28-05-2013 13:17:00',NULL,2
INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 3',1,'28-05-2013 10:10:00',NULL,3
INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 4',1,'28-05-2013 11:45:00', '30-06-2013 11:45:00',3
INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 1',1,'28-05-2013 13:17:00',NULL,4
INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 3',1,'28-05-2013 10:10:00','30-06-2013 11:45:00',4
INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 5',1,'28-05-2013 10:10:00','30-06-2013 11:45:00',4
SELECT * FROM @t
It contains a list of items (ITEM1 - ITEM5) that is assigned to a user (USERID) once every day if that day < than ENDS of item (= this is discontinuation date for item and user) or if ENDS is NULL.
For a given date range filter I need to group the quantities of items by date.
e.g. for filter 01-06-2013 - 03-06-2013 the desired outcome would be
SET DATEFORMAT DMY;
SET NOCOUNT ON;
DECLARE @t TABLE (ASSIGNED DATETIME, ITEM varchar(10), QTY int)
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-06-2013','ITEM1',2
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-06-2013','ITEM2',1
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-06-2013','ITEM3',3
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-06-2013','ITEM4',2
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-06-2013','ITEM5',1
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-06-2013','ITEM1',2
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-06-2013','ITEM2',1
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-06-2013','ITEM3',3
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-06-2013','ITEM4',2
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-06-2013','ITEM5',1
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-06-2013','ITEM1',2
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-06-2013','ITEM2',1
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-06-2013','ITEM3',3
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-06-2013','ITEM4',2
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-06-2013','ITEM5',1
SELECT * FROM @t
while if date range would be 01-07-2013 - 03-07-2013, desired outcome would be like this (ignoring the items discontinued on 30-06-2013):
SET DATEFORMAT DMY;
SET NOCOUNT ON;
DECLARE @t TABLE (ASSIGNED DATETIME, ITEM varchar(10), QTY int)
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-07-2013','ITEM1',2
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-07-2013','ITEM2',1
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-07-2013','ITEM3',2 -- one item discontinued on 30-06-2013
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '01-07-2013','ITEM4',1 -- one item discontinued on 30-06-2013
-- ITEM5 missing completely for 01-07-2013 as discontinued on 30-06-2013
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-07-2013','ITEM1',2
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-07-2013','ITEM2',1
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-07-2013','ITEM3',2 -- one item discontinued on 30-06-2013
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '02-07-2013','ITEM4',1 -- one item discontinued on 30-06-2013
-- ITEM5 missing completely for 02-07-2013 as discontinued on 30-06-2013
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-07-2013','ITEM1',2
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-07-2013','ITEM2',1
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-07-2013','ITEM3',2 -- one item discontinued on 30-06-2013
INSERT INTO @t (ASSIGNED, ITEM, QTY) SELECT '03-07-2013','ITEM4',1 -- one item discontinued on 30-06-2013
-- ITEM5 missing completely for 03-07-2013 as discontinued on 30-06-2013
SELECT * FROM @t
How would I achieve these results? My main problem is how to group by date in selected range.
Many thanks for any hints.
June 19, 2013 at 7:35 am
If I understood you correctly, your problem isn't really how to group by a date: GROUP BY [ASSIGNED] takes care of that. I'm sure you also have no problem creating a WHERE clause to select only rows with [ASSIGNED] dates in the specified range.
Your problem is you do not want to count quantities of assigned items where they are beyond the end date. For that you need a CASE expression that only returns the value of quantities for dates prior to [ENDS].
select Assigned, Item, SUM(case when [Assigned] <= [Ends] then Qty else 0 end) as QTY
from @t
join blah blah blah
where blah blah blah
group by Item, [assigned]
order by Item, [assigned]
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 19, 2013 at 8:00 am
Something like this
DECLARE@startdate DATE
DECLARE@enddate DATE
SET@startdate = '20130701'
SET@enddate = '20130703'
SELECTT.ITEM, DATEADD(DAY, D.number, @startdate) AS Dt, SUM(T.QTY) AS QTY
FROM@t AS T
CROSS APPLY(
SELECT*
FROMmaster.dbo.spt_values AS sv -- You can use a Tally table instead of this
WHEREsv.type = 'P' AND sv.number <= DATEDIFF(DAY, @startdate, @enddate)
AND(
( DATEADD(DAY, sv.number, @startdate) >= T.CREATED AND T.ENDS IS NULL ) OR
( DATEADD(DAY, sv.number, @startdate) >= T.CREATED AND DATEADD(DAY, sv.number, @startdate) < T.ENDS )
)
) AS D
GROUP BY T.ITEM, DATEADD(DAY, D.number, @startdate)
ORDER BY Dt, ITEM
You can use Tally tables instead of the spt_values tables that I have used
Please check the link below for more information on Tally Tables
http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/
I think the results that you have given are incorrect
There are 2 rows for ITEM5 in your sample data, one of them gets discontinued on 20130630
INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 5',1,'28-05-2013 13:17:00',NULL,2
INSERT INTO @t (ITEM, QTY, CREATED, ENDS, USERID) SELECT 'ITEM 5',1,'28-05-2013 10:10:00','30-06-2013 11:45:00',4
So, its incorrect when you said that it has been discontinued completely.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 19, 2013 at 8:07 am
Removed as it was a duplicate post
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 19, 2013 at 8:59 am
Hello Dixie Flatline and Kingston,
many thanks for your quick responses, much appreciated.
Kingston's solution is exactly what I am looking for - I did not know how to group by date within specified date range, but where records do not have a timestamp to group by. Apologies to Dixie Flatline if my request might not have been clear enough, really appreciate your suggestion with CASE for discontinued items.
Kingston's note about non-discontinuation for one instance of item5 is absolutely correct - I overlooked this one when posting the expected results for 01-07-2013 - 03-07-2013.
Many thanks again,
Marin
June 19, 2013 at 9:16 am
marin-231997 (6/19/2013)
Hello Dixie Flatline and Kingston,many thanks for your quick responses, much appreciated.
Kingston's solution is exactly what I am looking for - I did not know how to group by date within specified date range, but where records do not have a timestamp to group by. Apologies to Dixie Flatline if my request might not have been clear enough, really appreciate your suggestion with CASE for discontinued items.
Kingston's note about non-discontinuation for one instance of item5 is absolutely correct - I overlooked this one when posting the expected results for 01-07-2013 - 03-07-2013.
Many thanks again,
Marin
Glad that I could assist you
Please check the link that I provided about the Tally Tables and if possible read more articles on SSC about Tally tables
I would also like to appreciate the way you posted sample data, DDL and the expected results, great job 🙂
It really helped me in creating the code and testing the solution before posting it here
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 20, 2013 at 7:54 am
Sorry if I misunderstood. I would like to add my thanks for your taking time to post the problem with sample data.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply