Weekday 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

Weekday query

Author

Topic

Sacha
Starting Member

10 Posts

Posted - 2014-12-02 : 05:22:52

I have a query with the results of one weekday. Is it possible to get the results of all weekdays?
This is the query:

SELECT DISTINCT
TOP (100) PERCENT ISNULL(LastAssignedWorkgroupID, '') AS Group, DATEPART(HOUR, StartDateTimeUTC) + 1 AS [Hour of Day],
COALESCE (COUNT(InteractionID), 0) AS [Total Calls], DATEPART(weekday, GETDATE()) - 1 AS Monday
FROM dbo.InteractionSummary
WHERE (Direction IN ('1', '3')) AND (tConnected > 0) AND (CONVERT(VARCHAR(10), StartDateTimeUTC, 102) = CONVERT(VARCHAR(10), GETDATE() - 1, 102))
GROUP BY DATEPART(HOUR, StartDateTimeUTC) + 1, LastAssignedWorkgroupID
ORDER BY [Hour of Day]

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-02 : 08:49:45

Sure it's possible. First tell me, how do you know that:


DATEPART(weekday, GETDATE()) - 1 AS Monday

is Monday?

Sacha
Starting Member

10 Posts

Posted - 2014-12-02 : 09:02:00

Hi,

First day of the week is sunday that is 1, today is tuesday, 3. So monday is today -1.
The result of the query is:
group- hour of call-total calls- weekday

What i want is the results of all weekdays in one query.Now i get the result of one day.

Thanks!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-02 : 09:34:55

"First day of the week is sunday that is 1, today is tuesday, 3. So monday is today -1."

that is true today. what about tomorrow? in other words, if you don't finish this query today, will it work tomorrow?

Sacha
Starting Member

10 Posts

Posted - 2014-12-02 : 10:03:21

The query works for one weekday, depents on this statement:
DATEPART(weekday, GETDATE()) - 1 AS Monday

This:
DATEPART(weekday, GETDATE()) gives todays results

Now i have to change the query every day to get the results.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-02 : 10:24:56

Sacha, I don't think you get it:

DATEPART(weekday, GETDATE()) - 1 AS Monday

works today, since today is Tuesday. However, it won't work tomorrow (Wednesday) since then DATEPART(weekday, GETDATE()) will be 3

Why not post some sample data and desired results? Then we can build something that will work no matter when you run it.

Sacha
Starting Member

10 Posts

Posted - 2014-12-02 : 10:45:23

These are column headers:
Group Hour of Day Total Calls weekday

Desired are the results of a whole week.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-02 : 10:58:11

so why not just:

select ... from dbo.InteractionSummary
where StartDateTimeUTC >= getdate() -7
and ... other conditions

Sacha
Starting Member

10 Posts

Posted - 2014-12-03 : 03:32:01

Ok, THX,
I get the results of the last 7 days.
But it would be nice to see it seperated for each weekday...