Solved

SQL Subquery Modification

Posted on 2016-07-27
4
144 Views
Last Modified: 2016-08-03
Hello Experts -

I need some help with modifying some sub-queries.

The below queries, table .sql files for the two tables we are querying, and a sample y2y comparison excel sheet we use on a day to day basis (this is not done through SQL this is done manually) found in the box link I will provide below.

The two queries I have are (Query 1) and (Query 2). Query 1 does a query to display columns A-O in the excel sheet y2ytest.xlsx. In summary, Query 1 does a comparison of CY vs PY and does math operations based on what I want. Query 2 does a total of all CY and PY and does a difference between the two. However, the difference accounts for every record in CY to subtract to PY. So lets say, as an example, we had 80 stores in PY (2015) and in the CY (2016) since that time have added 40 stores. I do not want Query 2 to do a subtraction of stores that existed in 2016 but did not exist in 2015. In the y2ytest.xlsc doc you will find the results for Query 2 how they come out when you run it on row 157 and however I want the modified query like I stated above to do the difference like row 159.

---Query 1---

DECLARE @begJan DATETIME, @endJan DATETIME, @begJanPrev DATETIME, @endJanPrev DATETIME
SET @begJan = '01/01/2016'
SET @endJan = '01/31/2016'
SET @begJanPrev = '01/01/2015'
SET @endJanPrev = '01/31/2015'

SELECT 
  st.store_Num AS [Store Num],
  st.store_UIN as [Store Code],
  st.store_Name as [Store Name],
  FORMAT((r1.Sales),'C','en-us') AS [Sales PY],
  FORMAT((r2.Sales),'C','en-us') AS [Sales CYTD],
  FORMAT(((r2.Sales - r1.Sales)/ (r1.Sales)), 'P', 'en-us') AS [y/y change in % Sales],
  FORMAT((ISNULL(r2.Sales,0) - ISNULL(r1.Sales,0)), 'C','en-us') AS [SalesTotalDifference],
  r1.CarCnt AS [Car Count PY],
  r2.CarCnt AS [Car Count CYTD],
  FORMAT(((r2.CarCnt - r1.CarCnt)/ CAST(r1.CarCnt AS NUMERIC(10))), 'P', 'en-us') AS [y/y change in % Cars],
  ISNULL(r2.CarCnt,0) - ISNULL(r1.CarCnt,0) AS [CarsTotalDifference],
  FORMAT((r1.ARO), 'C','en-us') AS [AROPY],
  FORMAT((r2.ARO),'C','en-us') AS [AROCY],
  FORMAT(((r2.ARO - r1.ARO)/ CAST(r1.ARO AS NUMERIC(10))), 'P', 'en-us') AS [y/y change in % ARO],
  FORMAT((ISNULL(r2.ARO,0) - ISNULL(r1.ARO,0)),'C','en-us') AS [AROTotalDifference]
FROM StoreInfo st
LEFT JOIN (
   SELECT
     A.store_UIN,
     SUM(total-tax) AS [Sales],
     COUNT(RO_NO) as [CarCnt],
     (((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET - DISP - DISL -DISS)) - (SUM(P_COST + T_COST))) / (SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET - DISP - DISL -DISS))) as [GPM],
     ((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET + SUPPLIES - DISP - DISL -DISS)) / (COUNT(RO_NO))) as [ARO]
  FROM HRO as A
 WHERE ( PAY_DATE >= @begJanPrev) And (PAY_DATE <= @endJanPrev) and Status IN ('C','Q')  
 GROUP BY A.store_UIN
  ) r1 ON r1.store_UIN = st.store_UIN
LEFT JOIN (
   SELECT
     A.store_UIN,
     SUM(total-tax) AS [Sales],
     COUNT(RO_NO) as [CarCnt],
     (((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET - DISP - DISL -DISS)) - (SUM(P_COST + T_COST))) / (SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET - DISP - DISL -DISS))) as [GPM],
     ((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET + SUPPLIES - DISP - DISL -DISS)) / (COUNT(RO_NO))) as [ARO]
  FROM HRO as A
 WHERE ( PAY_DATE >= @begJan) And (PAY_DATE <= @endJan) and Status IN ('C','Q')  
 GROUP BY A.store_UIN
  ) r2 ON r2.store_UIN = st.store_UIN
order by st.store_Num, st.store_UIN asc

Open in new window



---Query 2---

DECLARE @begJan DATETIME, @endJan DATETIME, @begJanPrev DATETIME, @endJanPrev DATETIME
SET @begJan = '01/01/2016'
SET @endJan = '01/31/2016'
SET @begJanPrev = '01/01/2015'
SET @endJanPrev = '01/31/2015'


SELECT FORMAT([Sales PY], 'C','en-us') as [Previous YR Sales], FORMAT([Sales CY],'C','en-us') as [Current YR Sales], FORMAT([Sales CY] - [Sales PY],'C','en-us') AS [Sales Diff], [CarCnt PY], [CarCnt CY]
FROM (
    SELECT
         SUM(CASE WHEN (PAY_DATE >= @begJanPrev) And (PAY_DATE <= @endJanPrev) THEN total - tax END) AS [Sales PY],
         SUM(CASE WHEN (PAY_DATE >= @begJan) And (PAY_DATE <= @endJan) THEN total - tax END) AS [Sales CY],
         COUNT(CASE WHEN (PAY_DATE >= @begJanPrev) And (PAY_DATE <= @endJanPrev) THEN RO_NO END) as [CarCnt PY],
         COUNT(CASE WHEN (PAY_DATE >= @begJan) And (PAY_DATE <= @endJan) THEN RO_NO END) as [CarCnt CY],
         SUM(CASE WHEN (PAY_DATE >= @begJanPrev) And (PAY_DATE <= @endJanPrev) THEN
         ((((TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET - DISP - DISL -DISS)) - ((P_COST + T_COST))) / ( (TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET - DISP - DISL -DISS))) END) as [GPM PY],
         SUM(CASE WHEN (PAY_DATE >= @begJan) And (PAY_DATE <= @endJan) THEN
         ((((TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET - DISP - DISL -DISS)) - ((P_COST + T_COST))) / ((TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET - DISP - DISL -DISS))) END) as [GPM CY],
         SUM(CASE WHEN (PAY_DATE >= @begJanPrev) And (PAY_DATE <= @endJanPrev) THEN
         (((TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET + SUPPLIES - DISP - DISL -DISS))) END) / (COUNT(RO_NO)) as [ARO PY],
         SUM(CASE WHEN (PAY_DATE >= @begJan) And (PAY_DATE <= @endJan) THEN
         (((TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET + SUPPLIES - DISP - DISL -DISS))) END) / (COUNT(RO_NO)) as [ARO CY]
    FROM HRO as A
    WHERE   Status IN ('C','Q') and
        (((PAY_DATE >= @begJanPrev) And (PAY_DATE <= @endJanPrev)) OR ((PAY_DATE >= @begJan) And (PAY_DATE <= @endJan)))
) AS derived

Open in new window


LINK TO BOX DOWNLOAD

Let me know if you need any clarification,

Isaiah
0
Comment
Question by:sj77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 41739177
Hi  sj77,

Can you explain it with a tiny example (input and requested output)
0
 

Author Comment

by:sj77
ID: 41740016
Hello I believe the examples should be in the box download in the link above.
0
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 500 total points
ID: 41740049
Try this query
 
SELECT [Sales PY], [Sales CY], [Sales CY] - [Sales PY] AS [Sales Diff]
FROM (
    SELECT 
         SUM(CASE WHEN (PAY_DATE >= @begJanPrev) And (PAY_DATE <= @endJanPrev) THEN total - tax END) AS [Sales PY],
         SUM(CASE WHEN (PAY_DATE >= @begJan) And (PAY_DATE <= @endJan) THEN total - tax END) AS [Sales CY],
         COUNT(CASE WHEN (PAY_DATE >= @begJanPrev) And (PAY_DATE <= @endJanPrev) THEN RO_NO END) as [CarCnt PY],
         COUNT(CASE WHEN (PAY_DATE >= @begJan) And (PAY_DATE <= @endJan) THEN RO_NO END) as [CarCnt CY],
         SUM(CASE WHEN (PAY_DATE >= @begJanPrev) And (PAY_DATE <= @endJanPrev) THEN
         ((((TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET - DISP - DISL -DISS)) - ((P_COST + T_COST))) / ( (TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET - DISP - DISL -DISS))) END) as [GPM PY],
         SUM(CASE WHEN (PAY_DATE >= @begJan) And (PAY_DATE <= @endJan) THEN
         ((((TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET - DISP - DISL -DISS)) - ((P_COST + T_COST))) / ((TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET - DISP - DISL -DISS))) END) as [GPM CY],
         SUM(CASE WHEN (PAY_DATE >= @begJanPrev) And (PAY_DATE <= @endJanPrev) THEN
         (((TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET + SUPPLIES - DISP - DISL -DISS))) END) / (COUNT(RO_NO)) as [ARO PY],
         SUM(CASE WHEN (PAY_DATE >= @begJan) And (PAY_DATE <= @endJan) THEN
         (((TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET + SUPPLIES - DISP - DISL -DISS))) END) / (COUNT(RO_NO)) as [ARO CY]
    FROM HRO as A
	OUTER APPLY (SELECT TOP 1 'True' AnySales FROM dbo.HRO PY WHERE PY.store_UIN = A.store_UIN AND (PY.PAY_DATE >= @begJanPrev) And (PY.PAY_DATE <= @endJanPrev)) PY
    WHERE   Status IN ('C','Q') and
        (((PAY_DATE >= @begJanPrev) And (PAY_DATE <= @endJanPrev)) OR ((PAY_DATE >= @begJan) And (PAY_DATE <= @endJan)))
    AND PY.AnySales = 'True'
) AS derived

Open in new window

0
 

Author Closing Comment

by:sj77
ID: 41740497
This solution worked for me. Thanks, Chris!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Trigger C# code inside the SQL Server 6 36
Append Query Access 2010 4 26
Need multiple Group By's 8 28
Neglected Questions 3 13
I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question