Utkarsh sahu Utkarsh sahu - 1 year ago 78
SQL Question

Trying to get Month to date sales value for each date

I have Shipdate and Totaldue column in my salesOrderheader table.

My current query is like this

[ShipDate], SUM([TotalDue]) Total
[ShipDate] between '2005-08-1' and '2005-08-31'
group by

This returns the result for total sales for each day, I want a result where each date column corresponds to a value which shows month to date sale (i.e. totaldue here). Like if total due for 2005-08-1 is $1000 ,total due for 2005-08-2 is $3000 and total due for 2005-08-3 is $6000 then result should be,

ShipDate MTD Value
2005-08-1 1000
2005-08-2 4000
2005-08-3 11000

And so on till 2008-08-31. I am kind of new to database and can't seem to figure this out. If anybody has idea how do to it, please help me out. Thanks

Answer Source

You could do it like this:

select soh1.ShipDate, SUM(soh2.TotalDue) from 
   (select distinct ShipDate from sales.SalesOrderHeader
        where ShipDate < '2005-09-01' and ShipDate >= '2005-08-01') as soh1
   join sales.SalesOrderHeader soh2 
        on soh2.ShipDate <= soh1.ShipDate and soh2.ShipDate >= '2005-08-01' 
   group by soh1.ShipDate order by soh1.ShipDate

The join will create the following resultset:

  • ShipDate MTD Value
  • 2005-08-1 1000
  • 2005-08-2 1000
  • 2005-08-2 4000
  • 2005-08-3 1000
  • 2005-08-3 4000
  • 2005-08-3 11000

and the Sum and Group By joins the dates togheter.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download