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
Solved

SQL Subquery Modification

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query and VBA 5 45
Where on a calculated field 1 20
SQL Syntax Grouping Sum question 7 24
how to restore or keep sql2000  backups useful... 2 12
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

789 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