Query Edit - Modification to get additional data

Hello Experts -

A previous expert was able to help me generate the below query that will look for all the stores in our company and display data that adds two separate years together.

I understand the logic, yet, I am having an issue trying to manipulate the query in such a way where I get the query to give me totals of items I need.

For instance, I want the query to be modified to pull a sum of all sales from 2015 and also 2016. Along with that I would like to do a average of sales.

I have included a picture of the data results along with the query itself below. I know that in the outer select query I am supposed to remove the store_Num, store_UIN, and store_Name so that it does not group by or display that data. I just want a sum of all sales, averages and % change, I can figure out the rest.

results1.jpg
---QUERY---
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

sj77Asked:
Who is Participating?
 
Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
Try this, it is not cleaned up as well as it could be, I left some of the extra parentheses that could be removed but do not change the results.
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
    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


I removed the internal SUM functions because you are SUMming with the outer one.  I also moved your / COUNT() out side the SUM.  I do not have you exact table and data to fully test it, but it ran with my limited recreation of your table.
1
 
Scott PletcherSenior DBACommented:
You can get PY and CY in one pass of the data, as below.  In the outer query below, specify whatever calcs/computations you need to do on the data from the inner/derived 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
         (((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))) END) as [GPM PY],
         SUM(CASE WHEN (PAY_DATE >= @begJan) And (PAY_DATE <= @endJan) THEN
         (((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))) END) as [GPM CY],
         SUM(CASE WHEN (PAY_DATE >= @begJanPrev) And (PAY_DATE <= @endJanPrev) THEN
         ((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET + SUPPLIES - DISP - DISL -DISS)) / (COUNT(RO_NO))) END) as [ARO PY],
         SUM(CASE WHEN (PAY_DATE >= @begJan) And (PAY_DATE <= @endJan) THEN
         ((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET + SUPPLIES - DISP - DISL -DISS)) / (COUNT(RO_NO))) END) 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
1
 
sj77Author Commented:
Hi Scott -

Thanks for your answer. I input it into my query and got this error:

Msg 130, Level 15, State 1.
Cannot perform an aggregate function on an expression containing an aggregate or a subquery. (Line 16)


Please view the code below:

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 [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
         (((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))) END) as [GPM PY],
         SUM(CASE WHEN (PAY_DATE >= @begJan) And (PAY_DATE <= @endJan) THEN
         (((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))) END) as [GPM CY],
         SUM(CASE WHEN (PAY_DATE >= @begJanPrev) And (PAY_DATE <= @endJanPrev) THEN
         ((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET + SUPPLIES - DISP - DISL -DISS)) / (COUNT(RO_NO))) END) as [ARO PY],
         SUM(CASE WHEN (PAY_DATE >= @begJan) And (PAY_DATE <= @endJan) THEN
         ((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET + SUPPLIES - DISP - DISL -DISS)) / (COUNT(RO_NO))) END) 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


Let me know where the logic went wrong?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Chris LuttrellSenior Database ArchitectCommented:
In short, your lines like this are nesting SUM inside SUM and it does not like those.
         SUM(CASE WHEN (PAY_DATE >= @begJanPrev) And (PAY_DATE <= @endJanPrev) THEN
         (((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))) END) as [GPM PY],
0
 
sj77Author Commented:
Chris I will test tomorrow and get back with you.
0
 
funwithdotnetCommented:
Don't know if it'll work for you, but I usually start with something like:

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
-- comment or rearrange columns as desired.
--x.[Store Num],
--x.[Store Code],
x.[Store Name],
x.[Sales PY],
x.[Sales CYTD],
x.[y/y change in % Sales],
x.[SalesTotalDifference],
x.[Car Count PY],
x.[Car Count CYTD],
x.[y/y change in % Cars],
x.[CarsTotalDifference],
x.[AROPY],
x.[AROCY],
x.[y/y change in % ARO],
x.[AROTotalDifference],
FORMAT((x.[CombinedSalesRaw]),'C','en-us') AS [Sales CMB] -- combined sales.
FROM (
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],
  r1.Sales + r2.Sales AS [CombinedSalesRaw], -- included new raw calculated column
  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
   TOP (100) PERCENT -- May or may not process faster, especially if there is a sort or grouping.
     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
   TOP (100) PERCENT -- May or may not process faster, especially if there is a sort or grouping.
     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
) x
order by x.store_Num, x.store_UIN --asc --is implied --Moved sort to new outer table (x).
1
 
sj77Author Commented:
Hi Chris -

That worked!!!!!

Now, to clarify on my request, in the code below I asked the outer select query to subtract the data from the inner query from CY-PY as [Sales Diff]. My question to you is, there were some stores that were not open in 2015 but were open in 2016 (see the NULL data in the screenshot below), how do I do the subtracting of CY-PY but in the case where there is a NULL for 2015 do not do a CY-PY?

Let me know if that makes sense?

---QUERY---

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 Result set excel file:---


https://christianbrothersautomotive.box.com/s/feiarj885l3olca79bvax850v1mp2rl7
0
 
Chris LuttrellSenior Database ArchitectCommented:
What do you want the result to be when there is no PY sales?  The NULL is one way to represent the fact that you did not have previous data, otherwise you could COALESCE the PY sales with 0 so your Sales Diff would be the 100% of CY sales.  It all depends on what you want the final result to reflect.  If that does not answer it, can you provide a sample of what you want the output to look like in that situation?
Thanks, Chris
1
 
sj77Author Commented:
Hey Chris -

So if the PY contains a NULL value than we do not want CY-PY because it will skew the overall total we are trying to achieve. Hence row 159 on y2ytest.xlsx document I provided. That row accounts for only values that were present in CY and PY. If there were NULL values in PY than we did not do the math for those stores.

I have provided a link below to our y2y comparison sheet that we use now in excel (observe JAN (1) sales: columns Y-AC. Column Z is hitting cause it is a comparative from 2016-2015 if it finds a zero in 2015 it does no subtracting).

https://christianbrothersautomotive.box.com/s/8cfo42528rlc8fcs0rvrs4z7nozxvuxs
0
 
Chris LuttrellSenior Database ArchitectCommented:
I am not sure how the results of the query end up in your spread sheet?  Do you need some column to return a 0 for column Z in your sheet so your formula works?  Otherwise I am not sure what the difference between the query returning NULL and you formatting the cell with a "-", that is all formatted output, but we can't mix real numbers with strings in SQL output.
1
 
sj77Author Commented:
Well that sheet I sent was just an example.

My goal is to basically compare CY to PY. If PY had 80 stores generating revenue (hence the SQL result query is NOT NULL) and CY had 120 stores generating revenue I want to make sure I do not compare stores in CY that did not exist in PY - CY-PY (exclude the subtraction if a store was in CY but not in PY.) Let me know if that makes sense?
0
 
sj77Author Commented:
Chris was great! He even helped me on the side to make the query better.
0
 
sj77Author Commented:
This was the correct person who answered it right
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.