We help IT Professionals succeed at work.
Get Started

Math Operators with SQL Statements

157 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

Comment
Watch Question
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 11 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE