Get the Values with null for all dates

akhil,

Check if this helps you:

--CREATE TABLE #T1
--( Item varchar(50),
--  SalesDate Datetime,
--  Quantity int
--)
--
--INSERT INTO #T1 VALUES ('Item1', '2013/02/01', 3) 
--INSERT INTO #T1 VALUES ('Item2', '2013/02/01', 5)
--INSERT INTO #T1 VALUES ('Item1', '2013/02/02', 2)
--INSERT INTO #T1 VALUES ('Item1', '2013/02/03', 6)
--INSERT INTO #T1 VALUES ('Item2', '2013/02/03', 4)

SELECT * FROM #T1

declare @tab TABLE(date_col datetime)
insert @tab select '2013/02/01'
insert @tab select '2013/02/02'
insert @tab select '2013/02/03'

;with cte
as
(
        select * from 
        (select distinct Item from #t1) a
        cross join 
        (select date_col from @tab) b
)
select c.item,c.date_col,t.Quantity from cte c 
left join #T1 t on c.Item=t.item and c.date_col=t.SalesDate

You have the following options for the population of @tab (that decodes the dates for which values are to be obtained):

• either explicitly as i have done


• using a select statement on #T1 like, insert @tab select distinct SalesDate from #T1


• or for past three days means, something like - getdate(),getdate()-1 and getdate()-2

---

Thanks,

Jay

<If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>