CODEWITHSUNDEEP
sql
sameen
sameen

Reputation: 3

display list of dates by giving start and end date and get data against those dates

I have a sql table having three columns

id, balance, datetime i want to get data from the table by giving time duration. suppose i want to get data between 1/1/2013 to 1/15/2013. data is given table is shown as:

#id   Datetime  Balance #
1     1/1/2013   1500
2     1/2/2013   2000
3     1/4/2013   1500
4     1/5/2013   2500

now I want the output as

#id  Datetime  Balance #
1    1/1/2013   1500
2    1/2/2013   2000
3    1/3/2013    0
4    1/4/2013   1500
5    1/5/2013   2500

i want to display all the dates and if there is no balance against the date. it shows O or null value

Upvotes: 0

Views: 374

Answers (3)

Julius B
Julius B

Reputation: 56

I would get rid of ID column as it is useless when you are adding additional rows and do something like this:

set dateformat mdy 

declare @tmpTable table (dates date)

declare @startDate date = '1/1/2013'
declare @endDate date = '1/15/2013'

while @startDate <= @endDate
begin
    insert into @tmpTable (dates) values (@startDate)
    set @startDate = DATEADD(DAY, 1, @startDate)
end

select tmp.dates, yourtable.balance 
from @tmpTable as tmp
left outer join yourTable on yourTable.[Datetime] = tmp.dates
where yourtable.[Datetime] between @startDate and @endDate

Upvotes: 1

DB_learner
DB_learner

Reputation: 1026

You can do it by creating a cte with the dates between your daterange and outer joining it with your table.

with cte as
 (
 select start_date dt
 union all
 select dateadd(dd,1,dt) from cte where dt < end_date
)
select id, cte.dt, balance from cte left outer join yourtable b on cte.dt = b.date;

Upvotes: 0

user2144429
user2144429

Reputation: 91

I'm not sure what flavor of SQL you're using but you can a table of all dates and to do an outer join to it. For example, if you have a table of all dates called 'Dates', then you could do:

select id, dateTime, balance from table1 t RIGHT JOIN Dates d on t.dateTime = d.dateTime where t.dateTime BETWEEN '1/1/2013' AND '1/5/2013'

Upvotes: 0

Related Questions