?
Solved

Sum total current month & same last year

Posted on 2007-03-22
2
Medium Priority
?
694 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

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.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

764 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