Isaiah Melendez
asked on
SQL Subquery Modification
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---
---Query 2---
LINK TO BOX DOWNLOAD
Let me know if you need any clarification,
Isaiah
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
---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
LINK TO BOX DOWNLOAD
Let me know if you need any clarification,
Isaiah
ASKER
Hello I believe the examples should be in the box download in the link above.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This solution worked for me. Thanks, Chris!
Can you explain it with a tiny example (input and requested output)