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.
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 PercentSalesfrom 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.SiteIDb) SELECT FirstName, ticketDueDateFROM dbo.vw_OpenDeliveryTickets Gwhere ticketDueDate Between DATEADD(day,4, GETDATE()) and DATEADD(day,11, GETDATE())Sample Resultsiteid productcatagory percentsales firstname ticketduedate1 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? |
|
|
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 |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-08-20 : 16:30:52
|
Lets say query 1 outputs:siteid productcatagory percentsales1 aa 501 bb 452 aa 35 and query 2 outputs (you said table vw_OpenDeliveryTickets also contains siteid):siteid firstname ticketduedate1 route 101 08/25/20141 route 102 08/26/20141 route 103 08/27/20142 route 201 08/28/20142 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" |
|
|
amy2502
Starting Member
17 Posts |
Posted - 2014-08-20 : 17:10:29
|
Yes. You are right with the output. |
|
|
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 1Green = your query 2 linked to query 1with 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()) |
|
|
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. |
|
|
amy2502
Starting Member
17 Posts |
Posted - 2014-08-21 : 13:47:27
|
Hi bitsmedIn the above query how can I display routeid and salespercent grouped by route id. In addition to the sales percent by siteid.Thanks. |
|
|
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.
|
|
|
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 ticketduedate202794 Novelties 2.056 0 route101 8/25/2014202794 Super Premium Ice Cream 0.3499 0 route101 8/25/2014167154 Novelties 58.2642 59 route101 8/27/2014167154 Super Premium Ice Cream 41.7357 40 route101 8/27/2014101699 Novelties 79.299 80 route102 8/26/2014101699 Super Premium Ice Cream 20.7009 19 route102 8/26/201465200 Novelties 86.6793 87 route102 8/26/201465200 Super Premium Ice Cream 13.3206 12 route102 8/26/201460786 Premium Ice Cream 18.0415 23 route102 8/26/2014I 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 |
|
|
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.
|
|
|
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.
|
|
|
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 Samplefirstname siteid productcatagory percentsales percentqtycase ticketduedate percentsalesforFirstnameroute101 202794 Novelties 2.056 0 8/25/2014 60.3202route101 202794 Super Premium Ice Cream 0.3499 0 8/25/2014 42.0856route101 167154 Novelties 58.2642 59 8/27/2014 60.3202route101 167154 Super Premium Ice Cream 41.7357 40 8/27/2014 42.0856 |
|
|
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()) |
|
|
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.firstnameSo 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. |
|
|
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 |
|
|
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.MITRouteIDPlease let me know where I am going wrong. |
|
|
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 |
|
|
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 helpI 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 helpALTER PROCEDURE [dbo].[usp_AMPPU_Alle_Fehlteile]ASBEGINSET NOCOUNT ON;--DECLARE @Today DATE = GETDATE();---- Update fields which got values at a later point in time!--BEGINDECLARE @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 aRIGHT OUTER JOIN AMPPU_Alle_Fehlteile bON a.Auftrag = b.AuftragAND a.Fehlercode = b.FehlercodeAND a.Zeit_Fehler_EIN = b.Zeit_Fehler_EINWHERE (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_SecondUpdateFETCH NEXT FROM cr_SecondUpdateINTO @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 = 0BEGIN--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_FehlteileSET 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_05WHERE Auftrag = @AuftragAND Fehlercode = @fehlercodeAND Zeit_Fehler_EIN = @Zeit_Fehler_Ein;END--FETCH NEXT FROM cr_SecondUpdateINTO @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_SecondUpdateDEALLOCATE cr_SecondUpdate--END--ENDquote: 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 PercentSalesfrom 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.SiteIDb) SELECT FirstName, ticketDueDateFROM dbo.vw_OpenDeliveryTickets Gwhere ticketDueDate Between DATEADD(day,4, GETDATE()) and DATEADD(day,11, GETDATE())Sample Resultsiteid productcatagory percentsales firstname ticketduedate1 hd 50% Route 101 08/25/2014
|
|
|
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. |
|
|
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.TicketDueDateThanks. |
|
|
Next Page
|
|
|
|
|