November 27, 2009 at 9:31 am
Dear friends,
I have a table like below:
CREATE TABLE #MyTempTable (qty INT, startdate datetime, enddate datetime);
INSERT INTO #MyTempTable VALUES (1,'2009-01-01 00:00:00','2009-01-03 00:00:00');
INSERT INTO #MyTempTable VALUES (2,'2009-01-04 00:00:00','2009-01-06 00:00:00');
INSERT INTO #MyTempTable VALUES (3,'2009-01-07 00:00:00','2009-01-10 00:00:00');
i want the result as:
qty day
1 1
1 2
1 3
2 4
2 5
2 6
3 7
3 8
3 9
3 10
For easch day i want to display the Qty values.
Please help. Thanks in advance.
November 27, 2009 at 9:36 am
I don't know whether you have a calendar table or not - I'm guessing there's none...
So, please have a look at the Tally Table article I linked in my signature or search for "calendar table" on this site.
Some of the posts will describe exactly what you're asking for.
November 27, 2009 at 9:38 am
I haven't got a calendar table. Is it possible to do this without creating the tally table.
November 27, 2009 at 9:48 am
sarvesh singh-457805 (11/27/2009)
I haven't got a calendar table. Is it possible to do this without creating the tally table.
You can make non-permanent number tables using CTE's. Here's one example (courtesy of Itzik Ben-Gan).
WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
SELECT n
FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
FROM Nbrs ) D ( n )
WHERE n <= 500 ;
November 27, 2009 at 4:42 pm
i tried this:
; WITH
t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N FROM t3 x, t3 y)
SELECT
n. N,t1.qty
FROM Tally n right outer join #MyTempTable t1 on
n.N <= DATEDIFF(DAY, t1.startdate, DATEADD(day, 1,t1.enddate))
but i am getting:
Nqty
11
21
31
12
22
32
13
23
33
43
I am not getting the result i mentioned in my question. Where am i going wrong?
November 27, 2009 at 10:20 pm
Very nice, and very close. A slight alteration will give you what you need.
; WITH
t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N FROM t3 x, t3 y)
SELECT
t1.qty, DAY(startdate) + (N-1)
FROM Tally n right outer join #MyTempTable t1 on
n.N <= DATEDIFF(DAY, t1.startdate, DATEADD(day, 1,t1.enddate))
November 28, 2009 at 4:25 am
Thank you so much. It works fine.
Just got another question: Will this logic still work if i wanted to do it for each week or each month instead of each day, or do i need calendar table for that.
November 29, 2009 at 10:56 am
You should be able to use the same method, but the logic will get slightly more complex. Post back any specific questions if you have difficulty adapting it.
November 30, 2009 at 1:38 am
This is what i wanted to ask:
I have a table as below:
CREATE TABLE #MyTempTable2 (qty INT, startdate datetime, enddate datetime);
INSERT INTO #MyTempTable2 VALUES (1,'2009-01-01 00:00:00','2009-01-25 00:00:00');
INSERT INTO #MyTempTable2 VALUES (2,'2009-02-04 00:00:00','2009-02-20 00:00:00');
INSERT INTO #MyTempTable2 VALUES (3,'2009-03-07 00:00:00','2009-03-31 00:00:00');
How can i have qty values each week. I want the result as
Qty WK
1 1
1 2
1 3
... and so on.
November 30, 2009 at 8:36 am
Unfortunately, I'll have to guess at what 'and so on...' means, but this should point you in the right direction:
; WITH
t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N FROM t3 x, t3 y)
SELECT
t1.qty, n
FROM Tally n right outer join #MyTempTable2 t1
on n.N >= DATEPART(wk,startdate) AND
n.N <= DATEPART(wk,enddate)
December 1, 2009 at 1:25 am
It's Brilliant. It works. Thank you very much.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply