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
 General SQL Server Forums
 New to SQL Server Programming
 New column in multiple joins
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

amy2502
Starting Member

17 Posts

Posted - 08/21/2014 :  13:27:40  Show Profile  Reply with Quote
Hi All,

in the below query

with cte1
as (select siteid
,productcategory
,sum(isnull(netsales,0)) as netsales
from dbo.vw_sv_invoicedetail
where invoicedate>=dateadd(dd,-90,getdate())
and invoicedate<getdate()
group by siteid
,productcategory
)
,cte2
as (select siteid
,sum(netsales) as netsales
from cte1
group by siteid
)
select a.siteid
,a.productcategory
,case
when b.netsales=0
then 0
else a.netsales*100/b.netsales
end as percentsales
,c.firstname
,c.ticketduedate
from cte1 as a
inner join cte2 as b
on b.siteid=a.siteid
inner join dbo.vw_OpenDeliveryTickets as c
on c.siteid=a.siteid
and c.invoicedate>=dateadd(dd,4,getdate())
and c.invoicedate<dateadd(dd,12,getdate())

I need to add routeid column so that i can finally display sum of sales grouped by routeid in addition to the siteid that i am already displaying.

gbritton
Flowing Fount of Yak Knowledge

1443 Posts

Posted - 08/21/2014 :  15:06:56  Show Profile  Reply with Quote
Please post the tables and sample data as per this guideline:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Go to Top of Page

amy2502
Starting Member

17 Posts

Posted - 08/21/2014 :  15:15:12  Show Profile  Reply with Quote
Table a)

dbo.vw_Sv.invoicedetail

sitedid
mitrouteid
productcatagory
netsales
qtycases

Table b)

dbo.vw_OpenDeliveryTickets

Firstname ( Has routeid)
Ticketduedate

Sample Data:

Mitrouteid Sitedid Productcatagory percentnetsales:bysiteid percentnetsales:bymitrouteid percent qty case
1001 324234 hd 45% 80% 12%


Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1443 Posts

Posted - 08/21/2014 :  15:23:01  Show Profile  Reply with Quote
That's not what the guidelines say. You need to post:

1. CREATE TABLE statements to create the tables
2. INSERT INTO statements to populate the tables with test data
3. The output of your query when executed against the test tables
4. The desired output of your query when modified to meet the new requirements

The point is, no one on this forum wants to type in the table defs or inserts.
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

421 Posts

Posted - 08/21/2014 :  16:14:42  Show Profile  Reply with Quote
This is a duplicate of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=195805
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