New column in multiple joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

New column in multiple joins

Author

Topic

amy2502
Starting Member

17 Posts

Posted - 2014-08-21 : 13:27:40

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
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-21 : 15:06:56

Please post the tables and sample data as per this guideline:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

amy2502
Starting Member

17 Posts

Posted - 2014-08-21 : 15:15:12

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%

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-21 : 15:23:01

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.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-21 : 16:14:42

This is a duplicate of [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=195805[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources