CODEWITHSUNDEEP
sqlsql-server-2008
user1724737
user1724737

Reputation: 23

dates between two particular dates

I need to display all dates between two particular dates in sql server 2008? The table contains field such as date,quantity,so if i give the from date ='01/06/2013' and todate = '05/06/2013',i need to display all the dates between those dates along with the quantity.

The output is like the following:

Date(date datatype)         Day         Produced Qty    

01-06-13          Saturday          400.00  
02-06-13          Sunday        550.00  
03-06-13          Monday        200.00  
04-06-13         Tuesday        100.00  
05-06-13          Wednsdy       250.00  

Total                       1500.00

Please help?

Upvotes: 0

Views: 144

Answers (2)

HABO
HABO

Reputation: 15816

This will give you a table of dates that you can OUTER JOIN with your data:

declare @Start as Date = '20130501';
declare @End as Date = '20130515';

with Dates as (
  select @Start as [ReportDate]
  union all
  select DateAdd( day, 1, ReportDate )
    from Dates
    where ReportDate < @End )
  select ReportDate
    from Dates option ( MaxRecursion 0 );

EDIT: Or, with sample data:

declare @Production as Table ( ActivityDate Date, ProductionQuantity Int );
insert into @Production ( ActivityDate, ProductionQuantity ) values
  ( '20130106', 400 ),
  ( '20130112', 550 ),
  ( '20130112', 50 );

declare @Start as Date = '20130101';
declare @End as Date = '20130115';

with Dates as (
  select @Start as [ReportDate]
  union all
  select DateAdd( day, 1, ReportDate )
    from Dates
    where ReportDate < @End )
  select ReportDate, Coalesce( Sum( P.ProductionQuantity ), 0 ) as Qty
    from Dates as D left outer join
      @Production as P on P.ActivityDate = D.ReportDate
      group by D.ReportDate
      option ( MaxRecursion 0 );

Upvotes: 1

MG_Bautista
MG_Bautista

Reputation: 2653

Try use this...

Select date, day, produce_qty from Table
where date >= '03/06/2013' and date < '05/06/2013'

Or

Select date, day, produce_qty from Table
    where date BETWEEN '03/06/2013' and '05/06/2013'

Upvotes: 5

Related Questions