Results by date - 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

Results by date

Author

Topic

confuzed04
Starting Member

39 Posts

Posted - 2007-05-25 : 09:19:40

I am trying to get my query to give me the results I want by date in a list. I want in one column to get the date and the next column to show me the minutes of use (as reported in my database). I tried a Group by statement and asked it to group by the CallDate column. It gives me the date, but the minute totals in the next column are not right. Please help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-25 : 09:21:28

Can you post your table structure, some sample data and the result that you want.

confuzed04
Starting Member

39 Posts

Posted - 2007-05-25 : 09:44:30

This is what my query looks like. This just gives me one lump sum. I am wanting to get it to break down the results so that it shows me the calldate and how many total minutes where used for that day.

SELECT sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM VoiceCallDetailRecord
WHERE CallDate Between '02/19/2007' and '03/19/2007' and COS <> 3
AND (((CONVERT(varchar, CallDate, 108) Between '21:00:00' AND '23:59:59') OR (CONVERT(varchar, CallDate, 108) Between '00:00:00' AND '07:00:00'))
OR DATEPART(weekday, CallDate) in (1,7))

so that I get something like this for the results:
02/19/2007 65,000
02/20/2007 64,980
02/21/2007 65,050 etc...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-25 : 09:50:43

try this


SELECT dateadd(day, datediff(day, 0, CallDate), 0) as CallDate,
       sum(PaidBalCost), 
       sum(BonusBalCost), 
       sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM  VoiceCallDetailRecord
WHERE CallDate Between '02/19/2007' and '03/19/2007' and COS <> 3
AND (((CONVERT(varchar, CallDate, 108) Between '21:00:00' AND '23:59:59') OR (CONVERT(varchar, CallDate, 108) Between '00:00:00' AND '07:00:00'))
OR DATEPART(weekday, CallDate) in (1,7))
GROUP BY dateadd(day, datediff(day, 0, CallDate), 0)

confuzed04
Starting Member

39 Posts

Posted - 2007-05-25 : 10:11:49

Thanks so much!!!! That worked!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-26 : 01:41:19

More on querying dates
http://www.sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources