troubleshooting Question

(SQL 2005) SUM values across multiple date ranges

Avatar of hutch-hcss
hutch-hcss asked on
Microsoft SQL Server 2005
2 Comments1 Solution311 ViewsLast Modified:
I'm trying to sum up some values across multiple date ranges using SQL 2005.
First, the data isn't normalized (sorry - we're converting over a program and for some reason we're going to normalize it well after the fact).
db structure is essentially as follows:
jobcode   date       foreman    item#  code   qty   lab_hours   lab_cost   eqp_hours  eqp_cost
(sample data)
A        1/1/2006    JONES       0        100     5      25             450          7               275
A        1/1/2006    JONES       1        100     7      34             560          6               250
A        1/1/2006    JONES       1        200     18    45              700         25             1100
A        1/7/2006    JONES       0        100     8      20             375          15             800
B        1/1/2006    SMITH        0        100     10    15              300         5               200
...

What I want to do is sum up the qty, lab_hours, lab_cost, eqp_hours, and eqp_cost values over 4 separate date ranges (inception to current date, beginning of month to current date, beginning of week to current date, and current date) with the conditions being similar to job="A", person_id="JONES" and code="100"

individually the SELECT statements look something like this

SELECT SUM(PR_QUAN), SUM(LAB_HOURS), SUM(LAB_AMT), SUM(EQP_HOURS), SUM(EQP_AMT) FROM TABLE_A
  WHERE [JOBCODE] = 'A' AND [CODE] = '100' AND [FOREMAN] = 'JONES" AND [DATE] BETWEEN '1/1/2006'' AND '12/22/2006'

SELECT SUM(PR_QUAN), SUM(LAB_HOURS), SUM(LAB_AMT), SUM(EQP_HOURS), SUM(EQP_AMT) FROM TABLE_A
  WHERE [JOBCODE] = 'A' AND [CODE] = '100' AND [FOREMAN] = 'JONES' AND [DATE] BETWEEN '12/1/2006' AND '12/22/2006'

SELECT SUM(PR_QUAN), SUM(LAB_HOURS), SUM(LAB_AMT), SUM(EQP_HOURS), SUM(EQP_AMT) FROM TABLE_A
  WHERE [JOBCODE] = 'A' AND [CODE] = '100' AND [FOREMAN] = 'JONES' AND [DATE] BETWEEN '12/18/2006'' AND '12/22/2006'

SELECT SUM(PR_QUAN), SUM(LAB_HOURS), SUM(LAB_AMT), SUM(EQP_HOURS), SUM(EQP_AMT) FROM TABLE_A
  WHERE [JOBCODE] = 'A' AND [CODE] = '100' AND [FOREMAN] = 'JONES' AND [DATE] = '12/22/2006'

I want to know if there is a way to do this more efficiently via a single SQL statement (instead of 4 different statements).
Currently with the 4 SELECT STATEMENTS I am hitting some records up to 4 different times.  I would like to be able to do it in one pass through of the table.
I'm hoping I could possibly end up with a result set something along the lines of this:
range                 qty            lab_hours               lab_cost           eqp_hours              eqp_cost
full                     18000        2560                       75900              1260                      108050
month                 4000         275                        2567                 85                          1900
week                  1100          95                          1067                40                          950
today                  200            10                         356                   3                            175

the result set doesn't have to look like that however.  I'll take what I can get, so long as I can get a more efficient selection method.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros