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
2
Medium Priority
?
695 Views
Last Modified: 2011-09-20
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
Comment
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
  • Learn & ask questions
2 Comments
 
LVL 18

Expert Comment

by:chrismc
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

by:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

609 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