Days of a month upto sys date - 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

Days of a month upto sys date

Author

Topic

ramyasre
Starting Member

16 Posts

Posted - 2011-06-30 : 05:09:19

Hi,

i am stored in a table as follows

Meric date value
rr 6/6/2011(dd-mm-yy) 3
rt 6/6/2011 34
rk 6/6/2011 1
rr 7/6/2011 5
rt 7/6/2011 5
rk 7/6/2011 65

Assume todays date is 8/6/2011
I want output like as

Metric 1/6/2011 2/6/2011 3/6/2011 4/6/2011 5/6/2011 6/6/2011 7/6/2011
rr 0 0 0 0 0 3 5
rt 0 0 0 0 0 34 5 rk 0 0 0 0 0 1 65

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-30 : 05:21:57

Is that the last 7 days, from the begining of the month, something else?

It would be a cte to get the days, left join to get the values and a pivot to reformat the result

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-30 : 06:02:39

Here's a possible result - you would probably want to create a temp table from cte4 then do the pivot in dynamic sql so that the dates could change.

declare @tbl table (Metric varchar(10), date datetime, value int)
insert @tbl values
('rr','20110606',3)
,('rt','20110606',34)
,('rk','20110606',1)
,('rr','20110607',5)
,('rt','20110607',5)
,('rk','20110607',65)

;with cte1 as
(
select mindte = dateadd(mm,datediff(mm,0,min(date)),0), maxdte = max(date) from @tbl
),
cte2 as
(
select dte = mindte from cte1
union all
select dte + 1 from cte2,cte1 where dte < maxdte
),
cte3 as
(select distinct metric from @tbl) ,
cte4 as
(
select cte3.Metric, cte2.dte, value = coalesce(t.value,0)
from cte2
cross join cte3
left join @tbl t
on t.Metric = cte3.Metric
and t.date = cte2.dte
)
select Metric, [20110601],[20110602],[20110603],[20110604],[20110605],[20110606],[20110607]
from
(select [dte], Metric, Value from cte4) p
pivot
(
max(value)
for dte in ([20110601],[20110602],[20110603],[20110604],[20110605],[20110606],[20110607])
) pvt

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources