Solved

SQL Subquery Modification

Posted on 2016-07-27
4
123 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now