Hi Sean,
Thanks for your time on this. please execute the below query and please take a look at column(2013-08-12) the value is null.
I need to join the table data with company on(data.name = company.name) and get the value of daysinlate
as per my sample data the value is : 15 (consider 15 as 15days)
on the column(2013-08-12) intead of null i need to dispaly (2013-08-12 + 15 days = 2013-08-27). So the output would be
2013-08-11 2013-08-12 2013-08-13 2013-08-14 2013-08-15
1020 2013-08-27 2013-08-28 2013-08-29 2013-08-30
as like the calculation will go on.
with data as (
select 'microsoft' as Name, '2013-08-01' as datareceived, 1000 as recordscount union all
select 'microsoft' as Name, '2013-08-02' as datareceived, 1001 as recordscount union all
select 'microsoft' as Name, '2013-08-03' as datareceived, 1002 as recordscount union all
select 'microsoft' as Name, '2013-08-04' as datareceived, 1003 as recordscount union all
select 'microsoft' as Name, '2013-08-05' as datareceived, 1005 as recordscount union all
select 'microsoft' as Name, '2013-08-06' as datareceived, 1005 as recordscount union all
select 'microsoft' as Name, '2013-08-07' as datareceived, 1006 as recordscount union all
select 'microsoft' as Name, '2013-08-08' as datareceived, 1007 as recordscount union all
select 'microsoft' as Name, '2013-08-09' as datareceived, 1004 as recordscount union all
select 'microsoft' as Name, '2013-08-10' as datareceived, 1033 as recordscount union all
select 'microsoft' as Name, '2013-08-11' as datareceived, 1020 as recordscount )
SELECT * from
( SELECT name,
[datareceived] ,recordscount
FROM data
where DATEPART(MM, datareceived) = (8) and DATEPART(yy, datareceived) = STR(2013)
)
as p PIVOT ( max([recordscount]) FOR [datareceived]
IN ([2013-08-01],[2013-08-02],[2013-08-03],[2013-08-04],[2013-08-05],[2013-08-06],[2013-08-07],[2013-08-08],[2013-08-09],[2013-08-10],[2013-08-11],[2013-08-12],[2013-08-13],[2013-08-14],[2013-08-15],[2013-08-16],[2013-08-17],[2013-08-18],[2013-08-19],[2013-08-20],[2013-08-21],[2013-08-22],[2013-08-23],[2013-08-24],[2013-08-25],[2013-08-26],[2013-08-27],[2013-08-28],[2013-08-29],[2013-08-30],[2013-08-31])) AS pvt ;
am i clear you now?