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
 Multiple Joins

Author  Topic 

amy2502
Starting Member

17 Posts

Posted - 2014-08-20 : 14:09:56
Hi All,

I need to join query a and b such that i can get results combined in to one table.Since there are many inner queries I am getting errors. But the below two queries execute perfectly when run independently.

a) Select A.SiteID,
A.ProductCatagory,
A.SUMNETSALES/nullif (SUM(B.NETSALES),0) * 100 AS PercentSales
from dbo.vw_Sv_invoicedetail B Inner join(SELECT A.PRODUCTCATAGORY,
A.SITEID,
SUM(A.NetSales)AS SUMNETSALES,
SUM( A.QtyCase)AS SUMQTYCASE FROM dbo.vw_SV_InvoiceDetail A
where A.InvoiceDate Between getdate()-90 and getdate()-1
group BY A.siteid,A.ProductCatagory)A ON A.SiteID=B.SITEID
where B.InvoiceDate Between getdate()-90 and getdate()-1
group BY A.SUMNETSALES,A.ProductCatagory,A.SiteID

b) SELECT FirstName,
ticketDueDate
FROM dbo.vw_OpenDeliveryTickets G
where ticketDueDate Between DATEADD(day,4, GETDATE()) and DATEADD(day,11, GETDATE())

Sample Result

siteid productcatagory percentsales firstname ticketduedate
1 hd 50% Route 101 08/25/2014

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-20 : 16:04:11
Whats the link between the tables vw_Sv_invoicedetail and vw_OpenDeliveryTickets?

Lets say query 1 gives 3 rows and query 2 gives 4 rows, how do you know which rows to join?
Go to Top of Page

amy2502
Starting Member

17 Posts

Posted - 2014-08-20 : 16:08:18
Hi bitsmed,

The link between two tables is SiteId. And I need to display results that match with query two. Basically I need the first query to show the percentages for all the tickets that are due next week.

-Thanks
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-20 : 16:30:52
Lets say query 1 outputs:

siteid productcatagory percentsales
1 aa 50
1 bb 45
2 aa 35

and query 2 outputs (you said table vw_OpenDeliveryTickets also contains siteid):

siteid firstname ticketduedate
1 route 101 08/25/2014
1 route 102 08/26/2014
1 route 103 08/27/2014
2 route 201 08/28/2014
2 route 202 08/29/2014

would the correct output be:

siteid productcatagory percentsales firstname ticketduedate
1 aa 50 route 101 08/25/2014
1 aa 50 route 102 08/26/2014
1 aa 50 route 103 08/27/2014
1 bb 45 route 101 08/25/2014
1 bb 45 route 102 08/26/2014
1 bb 45 route 103 08/27/2014
2 aa 35 route 201 08/28/2014
2 aa 35 route 202 08/29/2014

?

Edit: Firstname in second last output line should be "route 201"
Go to Top of Page

amy2502
Starting Member

17 Posts

Posted - 2014-08-20 : 17:10:29
Yes. You are right with the output.
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-20 : 17:12:52
My best guess for a query that might work for you, given the above information, is:

Red = optimized version of your query 1
Green = your query 2 linked to query 1
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())

Go to Top of Page

amy2502
Starting Member

17 Posts

Posted - 2014-08-20 : 18:01:51
Thanks a lot bitsmed, I get the exact results that I wanted and the optimized query looks better than mine.
Go to Top of Page

amy2502
Starting Member

17 Posts

Posted - 2014-08-21 : 13:47:27
Hi bitsmed

In the above query how can I display routeid and salespercent grouped by route id. In addition to the sales percent by siteid.

Thanks.
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-21 : 16:11:22
Is the route id a field in table vw_sv_invoicedetail or are you refering to the firstname field in table vw_OpenDeliveryTickets?

I'm hoping the first table, otherwise I don't see how it can be done.
quote:
Originally posted by amy2502

In the above query how can I display routeid and salespercent grouped by route id. In addition to the sales percent by siteid.

Go to Top of Page

amy2502
Starting Member

17 Posts

Posted - 2014-08-21 : 16:32:35
Hi,

I am referring to FirstName field in table vw_OpenDeliveryTickets .

Currently data looks like this

siteid productcatagory percentsales percentqtycase firstname ticketduedate
202794 Novelties 2.056 0 route101 8/25/2014
202794 Super Premium Ice Cream 0.3499 0 route101 8/25/2014
167154 Novelties 58.2642 59 route101 8/27/2014
167154 Super Premium Ice Cream 41.7357 40 route101 8/27/2014
101699 Novelties 79.299 80 route102 8/26/2014
101699 Super Premium Ice Cream 20.7009 19 route102 8/26/2014
65200 Novelties 86.6793 87 route102 8/26/2014
65200 Super Premium Ice Cream 13.3206 12 route102 8/26/2014
60786 Premium Ice Cream 18.0415 23 route102 8/26/2014


I need to add two columns.

a) Sum of netsales where in that is grouped by Firstname and
b) percent of netsales where in that is also grouped by Firstname

Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-21 : 16:36:29
Found a new post where you answered my question as to, where to find the route id field: [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=195840[/url]

You are probably looking for either this:
with cte1
as (select siteid
,mitrouteid
,productcategory
,sum(isnull(netsales,0)) as netsales
from dbo.vw_sv_invoicedetail
where invoicedate>=dateadd(dd,-90,getdate())
and invoicedate<getdate()
group by siteid
,mitrouteid
,productcategory
)
,cte2
as (select siteid
,sum(netsales) as netsales
from cte1
group by siteid
)
select a.siteid
,a.mitrouteid
,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())

or this:
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
,mitrouteid
,sum(isnull(netsales,0)) as netsales
from dbo.vw_sv_invoicedetail
where invoicedate>=dateadd(dd,-90,getdate())
and invoicedate<getdate()
group by siteid
,mitrouteid
)
,cte3
as (select siteid
,sum(netsales) as netsales
from cte1
group by siteid
)
select a.siteid
,a.productcategory
,case
when c.netsales=0
then 0
else a.netsales*100/c.netsales
end as percentsales_p
,a.mitrouteid
,case
when c.netsales=0
then 0
else b.netsales*100/c.netsales
end as percentsales_r
,d.firstname
,d.ticketduedate
from cte1 as a
inner join cte2 as b
on b.siteid=a.siteid
inner join cte3 as c
on c.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())

quote:
Originally posted by bitsmed

Is the route id a field in table vw_sv_invoicedetail or are you refering to the firstname field in table vw_OpenDeliveryTickets?

I'm hoping the first table, otherwise I don't see how it can be done.
quote:
Originally posted by amy2502

In the above query how can I display routeid and salespercent grouped by route id. In addition to the sales percent by siteid.



Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-21 : 16:38:49
Then disregard my last post. I'll look into it.

Edit: You are refering to the output of the Query I gave you in this thread, stating that the route id is the firstname field in table vw_OpenDeliveryTickets. In the other thread you have the field mitrouteid in table vw_Sv.invoicedetail. Which one is correct?

quote:
Originally posted by amy2502

I am referring to FirstName field in table vw_OpenDeliveryTickets.

Go to Top of Page

amy2502
Starting Member

17 Posts

Posted - 2014-08-21 : 17:16:39
So Mitrouteid is the field in dbo.vw_sv_invoicedetail

and Firstname ( has routeid's in it) which is the field from dbo.vw_OpenDeliveryTickets

so I need the firstname in my result as one of columns and I need percentsales for Firstname grouped by productcatagory and firstname.

Final Result Sample

firstname siteid productcatagory percentsales percentqtycase ticketduedate percentsalesforFirstname
route101 202794 Novelties 2.056 0 8/25/2014 60.3202
route101 202794 Super Premium Ice Cream 0.3499 0 8/25/2014 42.0856
route101 167154 Novelties 58.2642 59 8/27/2014 60.3202
route101 167154 Super Premium Ice Cream 41.7357 40 8/27/2014 42.0856
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-21 : 17:54:06
Try this:
with cte1
as (select siteid
,productcategory
,sum(isnull(netsales,0)) as netsales
,sum(isnull(qtycase,0)) as qtycase
from dbo.vw_sv_invoicedetail
where invoicedate>=dateadd(dd,-90,getdate())
and invoicedate<getdate()
group by siteid
,productcategory
)
,cte2
as (select siteid
,mitrouteid
,sum(isnull(netsales,0)) as netsales
,sum(isnull(qtycase,0)) as qtycase
from dbo.vw_sv_invoicedetail
where invoicedate>=dateadd(dd,-90,getdate())
and invoicedate<getdate()
group by siteid
,mitrouteid
)
,cte3
as (select siteid
,sum(netsales) as netsales
,sum(qtycase) as qtycase
from cte1
group by siteid
)
select d.firstname
,a.siteid
,a.productcategory
,case
when c.netsales=0
then 0
else a.netsales*100/c.netsales
end as percentsales
,case
when c.qtycase=0
then 0
else a.qtycase*100/c.qtycase
end as percentqtycase
,d.ticketduedate
,case
when c.netsales=0
then 0
else b.netsales*100/c.netsales
end as percentsalesforfirstname
,
from cte1 as a
inner join cte2 as b
on b.siteid=a.siteid
inner join cte3 as c
on c.siteid=a.siteid
inner join dbo.vw_OpenDeliveryTickets as c
on c.siteid=a.siteid
and c.firstname like concat('%',b.mitrouteid)
and c.invoicedate>=dateadd(dd,4,getdate())
and c.invoicedate<dateadd(dd,12,getdate())
Go to Top of Page

amy2502
Starting Member

17 Posts

Posted - 2014-08-21 : 18:10:48
Ok. This is what i had narrowed down to.But In the above query the second part i.e cte2 I want it like this ( I need the sum of sales and percentofsales for each routes grouped by productcategory. that is irrespective of siteid)

select g.ProductCatagory
,h.firstname
,sum(isnull(netsales,0)) as newnetsales
from dbo.vw_sv_invoicedetail g, dbo.vw_OpenDeliveryTickets h
where g.siteid=h.siteid and g.invoicedate>=dateadd(dd,-90,getdate())
and g.invoicedate<getdate()
group by g.ProductCatagory,h.firstname

So if i do this in that part then I am stuck because I dont know if i can do b.ProductCatagory=a.ProductCatagory for innerjoin, without altering the result.
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-21 : 18:46:59
If I understand correctly, this might be what you are looking for:
with cte1
as (select siteid
,productcategory
,sum(isnull(netsales,0)) as netsales
,sum(isnull(qtycase,0)) as qtycase
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
,sum(qtycase) as qtycase
from cte1
group by siteid
)
,cte3
as (select productcategory
,mitrouteid
,sum(isnull(netsales,0)) as netsales
/*,sum(isnull(qtycase,0)) as qtycase*/
from dbo.vw_sv_invoicedetail
where invoicedate>=dateadd(dd,-90,getdate())
and invoicedate<getdate()
group by productcategory
,mitrouteid
)
,cte4
as (select productcategory
,sum(netsales) as netsales
/*,sum(qtycase) as qtycase*/
from cte3
group by productcategory
)
select c.firstname
,a.siteid
,a.productcategory
,case
when b.netsales=0
then 0
else a.netsales*100/b.netsales
end as percentsales
,case
when b.qtycase=0
then 0
else a.qtycase*100/b.qtycase
end as percentqtycase
,c.ticketduedate
,case
when e.netsales=0
then 0
else d.netsales*100/e.netsales
end as percentsalesforfirstname
,
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())
inner join cte3 as d
on d.productcategory=a.productcategory
and c.firstname like concat('%',d.mitrouteid)
inner join cte4 as e
on e.productcategory=a.productcategory
Go to Top of Page

amy2502
Starting Member

17 Posts

Posted - 2014-08-22 : 17:38:00
Thanks bitsmed. So finally this is the query that i narrowed down. But I think I am going wrong with the joins somewhere because netsales and percentnetsales are not giving right values for the data.

with cte1
as (select Distinct siteid
,productcatagory
,sum(NETSALES) as netsales
,sum(QtyCase) as qtycase
from dbo.vw_sv_invoicedetail
where invoicedate>=dateadd(dd,-90,getdate())
and invoicedate<getdate()and MITRouteID='3003'
group by siteid
,productcatagory
)
,cte2
as (select Distinct productcatagory
,mitrouteid
,sum(isnull(netsales,0)) as netsales
,sum(isnull(qtycase,0)) as qtycase
from dbo.vw_sv_invoicedetail
where invoicedate>=dateadd(dd,-90,getdate())
and invoicedate<getdate()and MITRouteID='3003'
group by productcatagory
,mitrouteid
)
,cte3
as (select MITRouteID,InvoiceDate
,sum(netsales) as routenetsales
,sum(qtycase) as qtycase
from dbo.vw_sv_invoicedetail where MITRouteID='3003'
and invoicedate>=dateadd(dd,-90,getdate())
and invoicedate<getdate()and MITRouteID='3003'
group by MITRouteID,InvoiceDate
)
select Distinct d.firstname
,a.siteid
,a.productcatagory
,a.netsales
,a.qtycase
,d.ticketduedate
,c.routenetsales
,b.netsales
,case
when b.netsales=0
then 0
else b.netsales*100/c.routenetsales
end as percentsalesforfirstname
,c.InvoiceDate
from cte1 as a
inner join dbo.vw_OpenDeliveryTickets as d
on d.siteid=a.siteid
and d.TicketDueDate>=dateadd(dd,1,getdate())
and d.TicketDueDate<dateadd(dd,8,getdate())
inner join cte2 as b
on b.ProductCatagory=a.ProductCatagory
inner join cte3 as c
on c.MITRouteID=b.MITRouteID


Please let me know where I am going wrong.
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-08-22 : 21:09:41
please provide:
- relevant sample data from both tables (ex. 10 rows for both tables)
- the output you get from your current query using only your sample data
- the outout you expected from your sample data
Go to Top of Page

mana
Posting Yak Master

102 Posts

Posted - 2014-08-25 : 09:31:54

i have the below cursor code but it takes too long and i want to write it without cursor. can you help me please#?
thank you for your help

I have the following cursor. But it takes too long and can you help me how i can write it without cursor??

thank you for your help


ALTER PROCEDURE [dbo].[usp_AMPPU_Alle_Fehlteile]
AS
BEGIN
SET NOCOUNT ON;
--
DECLARE @Today DATE = GETDATE();
--
-- Update fields which got values at a later point in time!
--
BEGIN
DECLARE @Auftrag VARCHAR(50), @fehlercode VARCHAR(50), @Zeit_Fehler_Ein DATETIME, @Zeit_Fehler_Aus DATETIME,
@NA_durch VARCHAR(50), @Bemerkung_Austrag VARCHAR(100), @NA_d_Kost VARCHAR(50), @4M_Analyse VARCHAR(50),
@OKTOSHIP DATETIME, @BV_05 DATETIME, @Reparatur_hr DECIMAL(5,2), @On_Line_Repair NVARCHAR(3);
--
DECLARE cr_SecondUpdate CURSOR FOR
--SELECT a.Auftrag, a.Fehlercode, a.Zeit_Fehler_EIN, a.Zeit_Fehler_AUS, a.NA_durch,
-- a.Bemerkung_Austrag, a.NA_d_KoSt, a.[4M_Analyse], a.OKTOSHIP, LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10),
-- ROUND(a.Reparatur_min/60,2) AS Reparatur_hr
--FROM AQIs.dbo.Alle_Fehlerdaten a
-- RIGHT OUTER JOIN AMPPU_Alle_Fehlteile b
-- ON a.Auftrag = b.Auftrag
-- AND a.Fehlercode = b.Fehlercode
-- AND a.Zeit_Fehler_EIN = b.Zeit_Fehler_EIN
--WHERE (a.Zeit_Fehler_AUS <> b.Zeit_Fehler_AUS OR (b.Zeit_Fehler_AUS IS NULL AND a.Zeit_Fehler_AUS IS NOT NULL))
-- OR (a.NA_durch <> b.NA_durch OR (b.NA_durch IS NULL AND a.NA_durch IS NOT NULL))
-- OR (a.Bemerkung_Austrag <> b.Bemerkung_Austrag OR (b.Bemerkung_Austrag IS NULL AND a.Bemerkung_Austrag IS NOT NULL))
-- OR (a.NA_d_KoSt <> b.NA_d_KoSt OR (b.NA_d_KoSt IS NULL AND a.NA_d_KoSt IS NOT NULL))
-- OR (a.[4M_Analyse] <> b.[4M_Analyse] OR (b.[4M_Analyse] IS NULL AND a.[4M_Analyse] IS NOT NULL))
-- OR (ROUND(a.Reparatur_min/60,2) <> CONVERT(FLOAT,b.Reparatur_hr) OR (CONVERT(FLOAT,b.Reparatur_hr) IS NULL AND ROUND(a.Reparatur_min/60,2) IS NOT NULL))
-- OR (a.OKTOSHIP <> b.OKTOSHIP OR (b.OKTOSHIP IS NULL AND a.OKTOSHIP IS NOT NULL))
-- OR (a.[BV-05] <> b.[BV-05] OR (b.[BV-05] IS NULL AND a.[BV-05] IS NOT NULL))
-- OR (LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10) <> b.Offline_date OR (b.Offline_date IS NULL AND LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10) IS NOT NULL));
--
--
SELECT a.Auftrag, a.Fehlercode, a.Zeit_Fehler_EIN, a.Zeit_Fehler_AUS, a.NA_durch,
a.Bemerkung_Austrag, a.NA_d_KoSt, a.[4M_Analyse], a.OKTOSHIP, LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10),
ROUND((a.Reparatur_min + ISNULL(a.ReparaturFolge_min, 0))/60,2) AS Reparatur_hr
FROM AQIs.dbo.Alle_Fehlerdaten a
RIGHT OUTER JOIN AMPPU_Alle_Fehlteile b
ON a.Auftrag = b.Auftrag
AND a.Fehlercode = b.Fehlercode
AND a.Zeit_Fehler_EIN = b.Zeit_Fehler_EIN
WHERE (a.Zeit_Fehler_AUS <> b.Zeit_Fehler_AUS
OR (b.Zeit_Fehler_AUS IS NULL AND a.Zeit_Fehler_AUS IS NOT NULL))
OR (a.NA_durch <> b.NA_durch OR (b.NA_durch IS NULL AND a.NA_durch IS NOT NULL))
OR (a.Bemerkung_Austrag <> b.Bemerkung_Austrag OR (b.Bemerkung_Austrag IS NULL AND a.Bemerkung_Austrag IS NOT NULL))
OR (a.NA_d_KoSt <> b.NA_d_KoSt OR (b.NA_d_KoSt IS NULL AND a.NA_d_KoSt IS NOT NULL))
OR (a.[4M_Analyse] <> b.[4M_Analyse] OR (b.[4M_Analyse] IS NULL AND a.[4M_Analyse] IS NOT NULL))
OR (ROUND((a.Reparatur_min + ISNULL(a.ReparaturFolge_min, 0))/60,2) <> CONVERT(FLOAT,b.Reparatur_hr) OR (CONVERT(FLOAT,b.Reparatur_hr) IS NULL AND ROUND((a.Reparatur_min + ISNULL(a.ReparaturFolge_min, 0))/60,2) IS NOT NULL))
OR (a.OKTOSHIP <> b.OKTOSHIP OR (b.OKTOSHIP IS NULL AND a.OKTOSHIP IS NOT NULL))
OR (a.[BV-05] <> b.[BV-05] OR (b.[BV-05] IS NULL AND a.[BV-05] IS NOT NULL))
OR (LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10) <> b.Offline_date OR (b.Offline_date IS NULL AND LEFT(CONVERT(NVARCHAR, a.[BV-05], 120), 10) IS NOT NULL));
--
OPEN cr_SecondUpdate
FETCH NEXT FROM cr_SecondUpdate
INTO @Auftrag, @Fehlercode, @Zeit_Fehler_Ein, @Zeit_Fehler_Aus, @NA_durch,
@Bemerkung_Austrag, @NA_d_Kost, @4M_Analyse, @OKTOSHIP, @BV_05, @Reparatur_hr
--
WHILE @@FETCH_STATUS = 0
BEGIN
--
BEGIN
--
SET @On_Line_Repair = CASE WHEN @Zeit_Fehler_Aus IS NULL THEN 'No'
WHEN @BV_05 >= @Zeit_Fehler_Aus THEN 'Yes'
ELSE 'No' END;
--
UPDATE AMPPU_Alle_Fehlteile
SET Zeit_Fehler_AUS = @Zeit_Fehler_Aus, NA_durch = @NA_durch, Bemerkung_Austrag = @Bemerkung_Austrag,
NA_d_KoSt = @NA_d_Kost, [4M_Analyse] = @4M_Analyse, Reparatur_hr = @Reparatur_hr,
OKTOSHIP = @OKTOSHIP, On_Line_Repair = @On_Line_Repair, Offline_date = @BV_05
WHERE Auftrag = @Auftrag
AND Fehlercode = @fehlercode
AND Zeit_Fehler_EIN = @Zeit_Fehler_Ein;
END
--
FETCH NEXT FROM cr_SecondUpdate
INTO @Auftrag, @Fehlercode, @Zeit_Fehler_Ein, @Zeit_Fehler_Aus, @NA_durch,
@Bemerkung_Austrag, @NA_d_Kost, @4M_Analyse, @OKTOSHIP, @BV_05, @Reparatur_hr
--
END
--
CLOSE cr_SecondUpdate
DEALLOCATE cr_SecondUpdate
--
END
--
END





quote:
Originally posted by amy2502

Hi All,

I need to join query a and b such that i can get results combined in to one table.Since there are many inner queries I am getting errors. But the below two queries execute perfectly when run independently.

a) Select A.SiteID,
A.ProductCatagory,
A.SUMNETSALES/nullif (SUM(B.NETSALES),0) * 100 AS PercentSales
from dbo.vw_Sv_invoicedetail B Inner join(SELECT A.PRODUCTCATAGORY,
A.SITEID,
SUM(A.NetSales)AS SUMNETSALES,
SUM( A.QtyCase)AS SUMQTYCASE FROM dbo.vw_SV_InvoiceDetail A
where A.InvoiceDate Between getdate()-90 and getdate()-1
group BY A.siteid,A.ProductCatagory)A ON A.SiteID=B.SITEID
where B.InvoiceDate Between getdate()-90 and getdate()-1
group BY A.SUMNETSALES,A.ProductCatagory,A.SiteID

b) SELECT FirstName,
ticketDueDate
FROM dbo.vw_OpenDeliveryTickets G
where ticketDueDate Between DATEADD(day,4, GETDATE()) and DATEADD(day,11, GETDATE())

Sample Result

siteid productcatagory percentsales firstname ticketduedate
1 hd 50% Route 101 08/25/2014

Go to Top of Page

amy2502
Starting Member

17 Posts

Posted - 2014-08-26 : 19:08:59
Hi All,

The below is my query

with cte1
as (select Distinct a.siteid
,a.productcatagory
,a.MITRouteID
,sum(a.NetSales) as SiteNetSales
,sum(a.QtyCase) as SiteQtyCase
from dbo.vw_sv_invoicedetail a
where a.invoicedate>=dateadd(dd,-90,getdate())
and a.invoicedate<getdate()
and a.MITRouteID='1101'
group by a.siteid
,a.productcatagory
,a.MITRouteID


)
,cte2
as (select Distinct b.productcatagory
,b.mitrouteid
,sum(isnull(b.netsales,0)) as SumNetSales
,sum(isnull(b.qtycase,0)) as QtyCase
from dbo.vw_sv_invoicedetail b
where invoicedate>=dateadd(dd,-90,getdate())
and invoicedate<getdate()
and MITRouteID='1101'
group by b.productcatagory
,b.mitrouteid

)
,cte3
as ( select Distinct c.MITRouteID
,sum(c.netsales) as RouteNetSales
,sum(c.qtycase) as QtyCase
from dbo.vw_sv_invoicedetail c
where invoicedate>=dateadd(dd,-90,getdate())
and invoicedate<getdate()
and MITRouteID='1101'
group by c.MITRouteID

)
,cte4
as ( Select Distinct d.ticketduedate
from dbo.vw_OpenDeliveryTickets d
Where d.TicketDueDate>=dateadd(dd,0,getdate())
and d.TicketDueDate<dateadd(dd,8,getdate())
and d.FirstName='route1101'
group by d.Ticketduedate
)

select Distinct d.firstname
,a.siteid
,a.productcatagory as ProductCategory
,b.SumNetSales
,a.SiteNetSales
,a.SiteQtyCase
,d.TicketDueDate
,c.RouteNetSales
,case
when SumNetSales=0
then 0
else SumNetSales*100/c.routenetsales
end as RouteSalesPercent
from cte1 as a
inner join dbo.vw_OpenDeliveryTickets as d
on d.SiteID=a.siteid
inner join cte2 as b
on b.ProductCatagory=a.ProductCatagory
inner join cte3 as c
on c.MITRouteID=a.MITRouteID
order by d.TicketDueDate

Sample Result

firstname siteid ProductCategory SumNetSales SiteNetSales SiteQtyCase TicketDueDate RouteNetSales RouteSalesPercent
route1101 147845 Super Premium Ice Cream 21989.37 72 3 8/27/2014 169450.84 12.9768
route1101 148117 Novelties 97672.77 567.48 27 8/27/2014 169450.84 57.6407
route1101 148117 Premium Ice Cream 24078.34 59.52 4 8/27/2014 169450.84 14.2096
route1101 148117 Super Premium Ice Cream 21989.37 84 4 8/27/2014 169450.84 12.9768
route1101 159120 Novelties 97672.77 411.36 18 8/27/2014 169450.84 57.6407
route1101 159120 Premium Ice Cream 24078.34 240.24 13 8/27/2014 169450.84 14.2096
route1101 203178 Novelties 97672.77 514.81 24 8/27/2014 169450.84 57.6407
route1101 203178 Super Premium Ice Cream 21989.37 45.6 2 8/27/2014 169450.84 12.9768
route1101 128921 Frozen Food 11754.96 571.18 17 8/28/2014 169450.84 6.937
route1101 128921 Novelties 97672.77 225.63 10 8/28/2014 169450.84 57.6407
route1101 128921 Pizza 11623.65 257.04 5 8/28/2014 169450.84 6.8596
route1101 128921 Premium Ice Cream 24078.34 489.24 18 8/28/2014 169450.84 14.2096
route1101 128921 Super Premium Ice Cream 21989.37 226.5 9 8/28/2014 169450.84 12.9768
route1101 135120 Frozen Food 11754.96 578.24 17 8/28/2014 169450.84 6.937
route1101 135120 Novelties 97672.77 253.55 10 8/28/2014 169450.84 57.6407
route1101 135120 Pizza 11623.65 307.08 6 8/28/2014 169450.84 6.8596


So in the above result, I need to display RouteNetsales and Routesalespercent by ticketduedate. So the mistake in the above result is that routesales and Routepercentsales are constant for all days. If I group by ticketduedate in cte2 and cte3 the result gets thrown off badly. Please suggest me how to go about it.

Go to Top of Page

amy2502
Starting Member

17 Posts

Posted - 2014-08-27 : 13:13:34
Hi, I came up with below query, Still not able to get the exact results. Any suggestions will be appreciated.

with cte1
as (select Distinct a.siteid
,a.productcatagory
,a.MITRouteID
,g.TicketDueDate
,sum(a.NetSales) as SiteNetSales
,sum(a.QtyCase) as SiteQtyCase
from dbo.vw_sv_invoicedetail a,dbo.vw_OpenDeliveryTickets g
where a.invoicedate>=dateadd(dd,-90,getdate())
and a.invoicedate<getdate()and g.TicketDueDate>=dateadd(dd,0,getdate())
and g.TicketDueDate<dateadd(dd,8,getdate())
and a.MITRouteID='1101' and a.SiteID=g.SiteID
group by a.siteid
,a.productcatagory
,a.MITRouteID
,g.TicketDueDate

)
,cte2
as (select Distinct b.productcatagory
,b.mitrouteid
,e.TicketDueDate
,sum(isnull(b.netsales,0)) as SumNetSales
,sum(isnull(b.qtycase,0)) as QtyCase
from dbo.vw_sv_invoicedetail b,dbo.vw_OpenDeliveryTickets e
where invoicedate>=dateadd(dd,-90,getdate())
and invoicedate<getdate()and e.TicketDueDate>=dateadd(dd,0,getdate())
and e.TicketDueDate<dateadd(dd,8,getdate())
and MITRouteID='1101' and b.SiteID=e.SiteID
group by b.productcatagory
,b.mitrouteid
,e.TicketDueDate

)
,cte3
as ( select c.MITRouteID
,f.TicketDueDate
,sum(c.netsales) as RouteNetSales
,sum(c.qtycase) as QtyCase
from dbo.vw_sv_invoicedetail c,dbo.vw_OpenDeliveryTickets f
where c.invoicedate>=dateadd(dd,-90,getdate())
and c.invoicedate<getdate()and f.TicketDueDate>=dateadd(dd,0,getdate())
and f.TicketDueDate<dateadd(dd,8,getdate())
and c.MITRouteID='1101'and c.SiteID=f.SiteID
group by c.MITRouteID
,f.TicketDueDate

)
,cte4

as ( select c.routenetsales
,j.ticketduedate

from cte3 as c
inner join dbo.vw_OpenDeliveryTickets as j
on j.TicketDueDate=c.TicketDueDate
inner join cte2 as b
on c.TicketDueDate=b.TicketDueDate
where j.FirstName='route1101'
group by c.RouteNetSales,j.TicketDueDate

)

select Distinct h.firstname
,a.SiteID
,h.TicketDueDate
,j.RouteNetSales
,b.SumNetSales
,case
when b.SumNetSales=0
then 0
else b.SumNetSales*100/c.routenetsales
end as RouteSalesPercent
from cte1 as a
inner join dbo.vw_OpenDeliveryTickets as h
on h.SiteID=a.siteid
inner join cte4 as j
on j.TicketDueDate=a.TicketDueDate
inner join cte2 as b
on b.TicketDueDate=a.TicketDueDate
inner join cte3 as c
on c.TicketDueDate=a.TicketDueDate
where h.FirstName='route1101'
order by h.TicketDueDate


Thanks.
Go to Top of Page
    Next Page

- Advertisement -