Avatar of Lapchien
Lapchien
Flag 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
Microsoft SQL Server

Avatar of undefined
Last Comment
Deepak Chauhan

8/22/2022 - Mon
Deepak Chauhan

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
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
smilitaru

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Deepak Chauhan

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Lapchien

ASKER
Thanks, can you re-check the syntax?
Lapchien

ASKER
That's great thanks, I just selected the top 4 and ordered the date desc
smilitaru

what is the problem?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Deepak Chauhan

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