Link to home
Start Free TrialLog in
Avatar of Lapchien
LapchienFlag for United Kingdom of Great Britain and Northern Ireland

asked on

SQL Query Day's of Week

I need a query that does a count of calls by day, for today, and on this day lasy week, and for the 2 weeks before that, so I end up with:

Calls Day Date
25 Monday 21/05/12
42 Monday 14/05/12
62 Monday 07/05/12
35 Monday 30/04/12

etc

Thanks

Lapchien
Avatar of Deepak Chauhan
Deepak Chauhan
Flag of India image

use this script  it is tested provide result set as u need


select count(calls) as calls , Days,date from (
SELECT  calls, datename(weekday, date) AS Days, date
from table name ) T where days='monday'
group by days, date
Avatar of Lapchien

ASKER

thanks, but it's needed day by day.  I do have an insert query using a temp table, but it isn't very elegant.
ASKER CERTIFIED SOLUTION
Avatar of smilitaru
smilitaru
Flag of Romania image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok you just join this as below

select count(t.calls) as calls , t.Days, t.date from (
SELECT  calls, datename(weekday, date) AS Days, date
from table name ) T join  
join  (select datename(weekday,  getdate()) AS days) dy
on dy.days=m.days
group by days, date
Thanks, can you re-check the syntax?
That's great thanks, I just selected the top 4 and ordered the date desc
what is the problem?
Sorry for above  Please remove one join caluse from two  and change
replace table aliase "m" to "T"

corrected qry is


select count(t.calls) as calls , t.Days, t.date from (
SELECT  calls, datename(weekday, date) AS Days, date
from tablename ) T  
join (select datename(weekday,  getdate()) AS days) dy
on dy.days=t.days
group by days, date