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.

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.

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.