Solved

Math Operators with SQL Statements

Posted on 2016-07-22
11
77 Views
Last Modified: 2016-07-24
Hello Pros -

Hope you are doing well.

I am running into an issue with writing a query that will do current year comparison to previous year. In my code below it stores current year month for beginning to end of month in separate variables. Than it also stores the same but for previous year. As you proceed, you will see the t-sql query that will display all stores, sales data, volume of transactions in cars, GPM, and ARO.


However, my conumdrum is how do I display an additional column that will do the operation of previous year for the month of January and than subtract, add, multiply or divide if need be and display only the sales data, car count, GPM and ARO? Is this possible?

I have attached a picture of the data results when I run the 2016 query below. Let know if you have any questions or you need me to verify.

Sales Data


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 

B.store_Num as [Store Name],
A.store_UIN as [Store Code],
B.store_Name as [Store Name],
FORMAT(SUM(total-tax), 'C', 'en-us') AS [Sales],
COUNT(RO_NO) as [Car Count],
FORMAT((((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))), 'P', 'EN-US') as [GPM],
FORMAT(((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET + SUPPLIES - DISP - DISL -DISS)) / (COUNT(RO_NO))), 'C', 'EN-US') as [ARO],


from HRO as A
left JOIN StoreInfo as B
  ON A.store_UIN = B.store_UIN

where
( PAY_DATE >= @begJan) And (PAY_DATE <= @endJan) and Status IN ('C','Q')  


group by B.store_Num, A.store_UIN, B.store_Name, B.store_OpeningDate, B.store_UIN

order by B.store_Num, A.store_UIN asc

Open in new window

0
Comment
Question by:sj77
  • 6
  • 5
11 Comments
 
LVL 41

Expert Comment

by:pcelba
ID: 41725051
You should start from the list of stores and then join annual results twice, e.g.:
SELECT 
  st.store_Num AS [Store Num],
  st.store_UIN as [Store Code],
  st.store_Name as [Store Name],
  r1.Sales AS [Sales1], 
  r1.CarCnt AS [Car Count1],
  r1.GPM AS GPM1,
  r1.ARO AS ARO1,
  r2.Sales AS Sales2, 
  r2.CarCnt AS [Car Count2],
  r2.GPM AS GPM2,
  r2.ARO AS ARO2
FROM StoreInfo st
LEFT JOIN (
   SELECT
     A.store_UIN,
     FORMAT(SUM(total-tax), 'C', 'en-us') AS [Sales],
     COUNT(RO_NO) as [CarCnt],
     FORMAT((((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))), 'P', 'EN-US') as [GPM],
     FORMAT(((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET + SUPPLIES - DISP - DISL -DISS)) / (COUNT(RO_NO))), 'C', 'EN-US') 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 OR r1.store_UIN = st.store_UIN
LEFT JOIN (
   SELECT
     A.store_UIN,
     FORMAT(SUM(total-tax), 'C', 'en-us') AS [Sales],
     COUNT(RO_NO) as [CarCnt],
     FORMAT((((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))), 'P', 'EN-US') as [GPM],
     FORMAT(((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET + SUPPLIES - DISP - DISL -DISS)) / (COUNT(RO_NO))), 'C', 'EN-US') 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 OR r2.store_UIN = st.store_UIN

order by st.store_Num, st.store_UIN asc

Open in new window

Remember the fact NULL values can appear on the output so you have to possibly convert them before further calculations.
You may filter out inactive stores by appropriate WHERE clause.
Optional way of inner subselects is to have just one groupped by YEAR + MONTH.
1
 

Author Comment

by:sj77
ID: 41725116
Pcelba -

Thank you for the information. Never would have thought that logic. I added in the declare variables and ran the query and I did get an error. Any idea why the OR or maybe the r2 would throw it off?

ERROR
Msg 156, Level 15, State 1.
Incorrect syntax near the keyword 'OR'.
Msg 102, Level 15, State 1.
Incorrect syntax near 'r2'. (Line 41)
---------------------------------------------------------

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],
  r1.Sales AS [Sales1], 
  r1.CarCnt AS [Car Count1],
  r1.GPM AS GPM1,
  r1.ARO AS ARO1,
  r2.Sales AS Sales2, 
  r2.CarCnt AS [Car Count2],
  r2.GPM AS GPM2,
  r2.ARO AS ARO2
FROM StoreInfo st
LEFT JOIN (
   SELECT
     A.store_UIN,
     FORMAT(SUM(total-tax), 'C', 'en-us') AS [Sales],
     COUNT(RO_NO) as [CarCnt],
     FORMAT((((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))), 'P', 'EN-US') as [GPM],
     FORMAT(((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET + SUPPLIES - DISP - DISL -DISS)) / (COUNT(RO_NO))), 'C', 'EN-US') 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 OR r1.store_UIN = st.store_UIN
LEFT JOIN (
   SELECT
     A.store_UIN,
     FORMAT(SUM(total-tax), 'C', 'en-us') AS [Sales],
     COUNT(RO_NO) as [CarCnt],
     FORMAT((((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))), 'P', 'EN-US') as [GPM],
     FORMAT(((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET + SUPPLIES - DISP - DISL -DISS)) / (COUNT(RO_NO))), 'C', 'EN-US') 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 OR r2.store_UIN = st.store_UIN

order by st.store_Num, st.store_UIN asc

Open in new window

0
 
LVL 41

Accepted Solution

by:
pcelba earned 500 total points
ID: 41725169
Sorry, it should have been ON, I didn't test the command so some tuning is expected...
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],
  r1.Sales AS [Sales1], 
  r1.CarCnt AS [Car Count1],
  r1.GPM AS GPM1,
  r1.ARO AS ARO1,
  r2.Sales AS Sales2, 
  r2.CarCnt AS [Car Count2],
  r2.GPM AS GPM2,
  r2.ARO AS ARO2
FROM StoreInfo st
LEFT JOIN (
   SELECT
     A.store_UIN,
     FORMAT(SUM(total-tax), 'C', 'en-us') AS [Sales],
     COUNT(RO_NO) as [CarCnt],
     FORMAT((((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))), 'P', 'EN-US') as [GPM],
     FORMAT(((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET + SUPPLIES - DISP - DISL -DISS)) / (COUNT(RO_NO))), 'C', 'EN-US') 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,
     FORMAT(SUM(total-tax), 'C', 'en-us') AS [Sales],
     COUNT(RO_NO) as [CarCnt],
     FORMAT((((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))), 'P', 'EN-US') as [GPM],
     FORMAT(((SUM(TAX_PARTS + NTX_PARTS + TAX_LABOR + NTX_LABOR + TAX_SUBLET + NTX_SUBLET + SUPPLIES - DISP - DISL -DISS)) / (COUNT(RO_NO))), 'C', 'EN-US') 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

0
 
LVL 41

Expert Comment

by:pcelba
ID: 41725178
BTW, what data type is the PAY_DATE column?  If it is DateTime then you should rather test if it is less than the first day of the next month:

@endJan 01/31/2016  converted to DateTime gives 01/31/2016 00:00:00 and if the PAY_DATE contains some non-zero time part then the results could be incorrect.
0
 

Author Comment

by:sj77
ID: 41725256
Pcelba!

It worked! Thank you so much. Now a question I had. Assume I display that data like I just did but how would I do an operation on it such as adding, subtracting, etc in my select statement?

like this?

r1.Sales + r2.Sales
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Closing Comment

by:sj77
ID: 41725257
AWESOME JOB! Was able to assist me and took the time to explain the solution.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 41725429
Note to calculations:
Inner queries contain FORMAT() function which returns (formatted) character string. You cannot use such values in arithmetic operations. So to e.g. sum two years results just remove the FORMAT() and then you may calculate whatever you need:
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],
  r1.Sales AS [Sales1], 
  r1.CarCnt AS [Car Count1],
  r1.GPM AS GPM1,
  r1.ARO AS ARO1,
  r2.Sales AS Sales2, 
  r2.CarCnt AS [Car Count2],
  r2.GPM AS GPM2,
  r2.ARO AS ARO2,
  ISNULL(r1.CarCnt,0) + ISNULL(r2.CarCnt,0) AS CarsTotal,
  (r2.CarCnt/r1.CarCnt-1)*100 AS [y/y change in %]
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

1
 

Author Comment

by:sj77
ID: 41725436
Thank you so much!
0
 

Author Comment

by:sj77
ID: 41726042
Hello Pcelba -

Thank you for updating the code and assisting. I wanted to ask why this is happening. I cannot seem to figure out what causes it.

I have played around with what you told me to do to get math operations to work and specifically get percentages. For instance y/y change for sales. However, when I do y/y change it gives me a zero for all results. To test I subtracted r2.CarCnt - r1.CarCnt as [Increase] and it displayed correctly, but, when I did the y/y formula which is (r2.CarCnt - r1.CarCnt) / (r1.CarCnt) I get all zeros. Any ideas why? I have pasted 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 
  st.store_Num AS [Store Num],
  st.store_UIN as [Store Code],
  st.store_Name as [Store Name],
  r1.Sales AS [Sales PY], 
  r1.CarCnt AS [Car Count PY],
  r1.GPM AS GPM1,
  r1.ARO AS ARO1,
  r2.Sales AS [Sales CYTD], 
  r2.CarCnt AS [Car Count CYTD],
  r2.GPM AS GPM2,
  r2.ARO AS ARO2,
  ISNULL(r2.Sales,0) - ISNULL(r1.Sales,0) AS [SalesTotal],
  FORMAT(((r2.Sales - r1.Sales)/ (r1.Sales)), 'P', 'en-us') AS [y/y change in % Sales],
  ISNULL(r2.CarCnt,0) - ISNULL(r1.CarCnt,0) AS [CarsTotal],
  FORMAT(((r2.CarCnt - r1.CarCnt)/ (r1.CarCnt)), 'P', 'en-us') AS [y/y change in % Cars]
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

0
 
LVL 41

Expert Comment

by:pcelba
ID: 41726434
Sorry, mi mistake. I should tell something about data types.
When you divide two integer numbers (and the car count is integer) the result is also integer and fraction parts are stripped out.

To get what you need you have to convert integer numbers into some numeric data type which can contain decimal places, e.g. NUMERIC.

So instead of:
(r2.CarCnt - r1.CarCnt) / (r1.CarCnt)
convert one of the operands to NUMERIC:
(r2.CarCnt - r1.CarCnt) / CAST(r1.CarCnt AS NUMERIC(10))
and it should provide decimal places in the result.

Note the NUMERIC does not need to be defined with decimal places but you may specify NUMERIC(15,5) etc.

You may also test various expressions in SQL Server Management studio. Simply execute

SELECT 3/2, 2/3, 2/CAST(3 as NUMERIC(5))

in the query window
1
 

Author Comment

by:sj77
ID: 41726603
You my friend are a GENIUS!

Thank you so much for the teaching point. Once again, I greatly appreciate it!
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Read about achieving the basic levels of HRIS security in the workplace.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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…

896 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

11 Experts available now in Live!

Get 1:1 Help Now