Help writing SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help writing SQL Query

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-11-25 : 09:14:35

I want to sum up the colums by Region and weekend date (for this table it would be Friday Oct 2nd) So if someone selects 10/02/2009 from the drop down box I want the query to add up the columns for those five days in the week. I hope that makes sense.

Here's some data.

OFC	Area	Loc	Reg	Dist	CO	CR	PR	Ans	PerAns	Date
030	02	1099	Bos	030	13	11	84%	10	90%	9/28/2009
030	02	1099	Bos	030	11	10	90%	10	100%	9/29/2009
030	02	1099	Bos	030	13	10	76%	10	100%	9/29/2009
030	02	1099	Bos	030	9	9	100%	9	100%	10/01/2009
030	02	1099	Bos	030	13	13	100%	9	69%	10/02/2009

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-25 : 09:55:46

What is the expected result?

Madhivanan

Failing to plan is Planning to fail

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-11-25 : 09:56:26

You mean add up rows??

Can you show us the expected output for your sample data?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-11-25 : 09:57:31

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-11-25 : 10:07:32

Yes I want the add up the columns. This is what I want to get:


W/E	     CO	   CR	PR	Ans   PerAns	
10/02/2009   59	   53	84%	47    90%

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-11-25 : 10:08:40

I didn't do anything with the Percent columns don't know how I would get those or how to add them all up to get the total for the week.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-25 : 10:13:11

select sum(co) as co, sum(cr) as cr,... from your_table
where date_col between dateadd(day,-4,@date) and @date

Madhivanan

Failing to plan is Planning to fail

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-11-25 : 10:22:10

Almost there but the DBA set up the columns with spaces in it.


SELECT     SUM(calls_offered) AS co, SUM(calls_received) AS cr
FROM         test
WHERE     (Date BETWEEN DATEADD(day, - 4, @date) AND @date)

I tried this but it doesn't like it.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-11-25 : 10:29:53

Would I need the between word since you have -4 @date (what's selected)?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-11-25 : 10:48:07

What is the error you received when you ran Madhivanan's code?

quote:
Date BETWEEN DATEADD(day, - 4, @date) AND @date

This will give you cases for that whole week. The "Date" column , is it a datetime field?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-11-25 : 11:20:38

Okay that code worked since there are spaces in my column names in the DB I needed to put the brackets around the column name. Unfortunately I explained it wrong as the numbers are too big.

This is what


select sum(isnull([calls offered],0)) as co, sum(isnull([calls received],0)) as cr
from test
where (dateadd(dd,-4,'10/02/2009'))

I want to add up Calls Offered and Calls Received for the week of 10/02/2009.

So in the webpage I will have Friday's listed so when a user selects the Friday I want that day - 4 days to give me the total for the week.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-11-25 : 11:45:47

The date column in the DB is a varchar (30) if that helps.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-11-25 : 11:49:12

You would need to change the WHERE clause to something like

WHERE Date BETWEEN DATEADD(day, - 4, '10/02/2009') AND '10/02/2009'

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-11-25 : 12:09:48

Thanks that's it! I just had to add more columns to tweak it more for me:

select sum(isnull([calls offered],0)) as co, sum(isnull([calls received],0)) as cr
from test
WHERE office = '074' and regionname = 'boston' and Date BETWEEN DATEADD(day, - 4, '10/02/2009') AND '10/02/2009'

Thanks I'm sure I'll be back!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-11-25 : 12:17:02

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources