August 26, 2013 at 4:06 pm
Hi,
I have a table like below, and I need to break it by period of time and split amt by respective period, so I'll get let say 3 row out from one input.
I also want to keep rest of fields as is , how better achieve this?
I can use just Max(col1) or include all Col1,2,3,4 on Group BY or ... something else
what guru do in this case
Tx
Mario
id timeIN timeOut Duration col1 col2 col3 col4
---------------------------------------------------------
1 6/2/13 8/15/13 64 days info1 info2 info3 info4
||
\/
1 NULL 6/30/13 28 days info1 info2 info3 info4
1 NULL 7/31/13 31 days info1 info2 info3 info4
1 NULL 8/15/13 15 days info1 info2 info3 info4
August 27, 2013 at 6:49 am
Have a play with the code below. The other columns aren't really a problem...you can just include them.
I'm sure this could be done a lot more concisely, but this should get things started.
I use a Tally table below which is a table called Tally with 1 field [N] which goes from 1 to lots. (So you'll need one of those!)
e.g.
N
---
1
2
3
4
5....
;
WITH topline
AS ( SELECT '2 june 2013' AS datein ,
'15 aug 2013' AS dateout
UNION
SELECT '19 may 1971' ,
'25 july 1971'
),
months
AS ( SELECT DATEADD(d, -1,
DATEADD(m, N - 1 + DATEDIFF(m, 0, datein), 0)) AS bom ,
DATEADD(d, -1,
DATEADD(m, N + DATEDIFF(m, 0, datein), 0)) AS eom ,
topline.*
FROM Tally AS st ,
topline
WHERE N <= DATEDIFF(m, topline.datein, topline.dateout) + 1
),
r3
AS ( SELECT * ,
CASE WHEN bom > datein THEN bom
ELSE months.datein
END AS beginRef ,
CASE WHEN eom < dateout THEN eom
ELSE months.dateout
END AS endRef
FROM months
)
SELECT DATEDIFF(d, beginRef, endRef) ,
*
FROM r3
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply