Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Sum total current month & same last year

Posted on 2007-03-22
Medium Priority
695 Views
Hello,

Here is my sql:

SELECT
StoreID, YEAR(DatePickedUp) AS ROYear,
SUM(BilledHrsNonWarranty + BilledHrsWarranty + BilledHrsStock) as TotalHours,
SUM(PartsAmtNonWarranty + PartsAmtWarranty + PartsAmtStock) as TotalParts,
SUM(SubletAmtNonWarranty + SubletAmtWarranty + SubletAmtStock) as TotalSublet,
SUM(LaborAmtNonWarranty +  LaborAmtWarranty +  LaborAmtStock) as TotalLabor,
COUNT(RONumber) AS ROCount, SUM(Deposit1 + Deposit2 + Deposit3 + Deposit4 + PaymentAmt1 + PaymentAmt2 + PaymentAmt3) AS TotalROAmount

FROM
ROSummary

WHERE Year(DatePickedUp) BETWEEN 2006 And 2007
GROUP BY
StoreID, YEAR(DatePickedUP)

I'm trying to get the total labor for the current month and current month 1 year ago.
This is being used in SQL 2005 reporting services.

Thanks

Dan
0
Question by:DanPerlman
[X]
###### 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

LVL 18

Expert Comment

ID: 18773751
Group by month and in the where, just choose this month and the month 1 year ago.

Where (Year(GetDate()) = Year(DatePickedUp) Or Year(GetDate())-1 = Year(DatePickedUp)) And Month(GetDate()) = Month(DatePickedUp))

Cheers
Chris
0

LVL 2

Accepted Solution

JimyLee earned 2000 total points
ID: 19956678
If you are trying to get the previous year's total in the same result row as this year's data, something like this may do the trick for you.

SELECT
StoreID, YEAR(DatePickedUp) AS ROYear,
SUM(BilledHrsNonWarranty + BilledHrsWarranty + BilledHrsStock) as TotalHours,
SUM(PartsAmtNonWarranty + PartsAmtWarranty + PartsAmtStock) as TotalParts,
SUM(SubletAmtNonWarranty + SubletAmtWarranty + SubletAmtStock) as TotalSublet,
SUM(LaborAmtNonWarranty +  LaborAmtWarranty +  LaborAmtStock) as TotalLabor,
(select SUM(LaborAmtNonWarranty +  LaborAmtWarranty +  LaborAmtStock) from ROSummary where year(DatePickedUp) = 2006 and StoreID = ROS.StoreID) PrevYearTotalLabor
COUNT(RONumber) AS ROCount, SUM(Deposit1 + Deposit2 + Deposit3 + Deposit4 + PaymentAmt1 + PaymentAmt2 + PaymentAmt3) AS TotalROAmount
FROM ROSummary ROS
WHERE Year(DatePickedUp) = 2007
GROUP BY StoreID, YEAR(DatePickedUP)

Hope I didn't leave out anything in there.  Not at my normal computer today.  Hope it helps.

JimyLee
0

## Featured Post

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
###### Suggested Courses
Course of the Month9 days, 17 hours left to enroll