SQL Query Day's of Week

Lapchien
Lapchien used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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
LapchienDirector

Author

Commented:
thanks, but it's needed day by day.  I do have an insert query using a temp table, but it isn't very elegant.
How about this:

SELECT Count(*), (DATEADD(day, DATEDIFF(day, 0, DateAdd(d,- (Datediff(D, DateField, GetDate())), getdate())), 0))
FROM Table_1
WHERE (Datediff(D, DateField, GetDate()) % 7)=0
GROUP BY (DATEADD(day, DATEDIFF(day, 0, DateAdd(d,- (Datediff(D, DateField, GetDate())), getdate())), 0))

Maybe looks to complicated but there is need to extract only date and no time from some dates
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
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
LapchienDirector

Author

Commented:
Thanks, can you re-check the syntax?
LapchienDirector

Author

Commented:
That's great thanks, I just selected the top 4 and ordered the date desc
what is the problem?

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial