SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Query for Sale
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jdeforums
Starting Member

India
8 Posts

Posted - 02/13/2014 :  06:04:59  Show Profile  Reply with Quote
Hi ,

I have query to download sale summary value in monthwise, but now i want to include previous days sale and total sales as mentioned below format .

Sales Type Month_sales
3rd Domes XXX
3rd Export XXX
Inter Domes XXX

But i want

Sales Type Yesterda_Sales Month_sales
3rd Domes XXX XXX
3rd Export XXX XXX
Inter Domes XXX XXX

Current query is

select
ABAC29_DESC as Sales,ABAC30_DESC as Type , round(sum(order_qty*UNIT)/1000000,2) as MTD_Sales_Million from sales
where
datediff(month,invoice_date,getdate())<=0

request your help to include the yesterday sales also.

Thanks


Suresh.K.P

khtan
In (Som, Ni, Yak)

Singapore
17636 Posts

Posted - 02/13/2014 :  07:47:58  Show Profile  Reply with Quote
select 	ABAC29_DESC as Sales,
	ABAC30_DESC as Type,
	round(sum(case when invoice_date = dateadd(day, datediff(day, 0, getdate()), -1)
		       then order_qty*UNIT else 0 end)/1000000, 2) as YESTERDAY_Sales_Million 
	round(sum(order_qty*UNIT)/1000000,2) as MTD_Sales_Million 
from 	sales
where	invoice_date	>= dateadd(month, datediff(month, 0, getdate()), 0)
and	invoice_date	<  dateadd(month, datediff(month, 0, getdate()) + 1, 0)
group by ABAC29_DESC, ABAC30_DESC



KH
Time is always against us

Go to Top of Page

jdeforums
Starting Member

India
8 Posts

Posted - 02/15/2014 :  02:02:07  Show Profile  Reply with Quote
quote:
Originally posted by khtan

select 	ABAC29_DESC as Sales,
	ABAC30_DESC as Type,
	round(sum(case when invoice_date = dateadd(day, datediff(day, 0, getdate()), -1)
		       then order_qty*UNIT else 0 end)/1000000, 2) as YESTERDAY_Sales_Million 
	round(sum(order_qty*UNIT)/1000000,2) as MTD_Sales_Million 
from 	sales
where	invoice_date	>= dateadd(month, datediff(month, 0, getdate()), 0)
and	invoice_date	<  dateadd(month, datediff(month, 0, getdate()) + 1, 0)
group by ABAC29_DESC, ABAC30_DESC



KH
Time is always against us





Many Thanks KH.

Suresh.K.P
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17636 Posts

Posted - 02/15/2014 :  02:09:08  Show Profile  Reply with Quote
you are welcome


KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000