Finding the total number of items within weekdays

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

Finding the total number of items within weekdays

Author

Topic

rc1138
Starting Member

35 Posts

Posted - 2010-05-07 : 00:36:56

Okay I just received advice on how to count the number of items within the months of the year

SELECT COUNT (CASE WHEN MONTH(IncDate) = 1 THEN 1 END) AS JAN,
COUNT (CASE WHEN MONTH(IncDate) = 2 THEN 1 END) AS FEB
FROM irIncident AS I
INNER JOIN pdEmployee AS E ON I.EmpKey = E.EmpKey
WHERE E.Department = '1000'
AND I.HlthCare = 'Y'

Which displays

Jan Feb
5 0

Now I want to find out the number of occurences per day within the year

It works when i use this syntax

SELECT COUNT(CASE WHEN DAY(IncDate) = 1 THEN 1 END) AS Mon, COUNT(CASE WHEN DAY(IncDate) = 2 THEN 1 END) AS Tue
FROM irIncident AS I INNER JOIN
pdEmployee AS E ON I.EmpKey = E.EmpKey
WHERE (E.Department = '1000') AND (I.HlthCare = 'Y')

But I guess just because Day(IncDate) = 1 does not always mean it will be monday is there a specific syntax filter to indicate days of the week (e.g. Day(IncDate) = Monday)

Thanks again!

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-07 : 01:11:03

You should use DATEPART function with WEEKDAY as the datepart, but beware that this function is dependent on environment settings like SET LANGUAGE and SET DATEFIRST. So to get consistent results regardless of those settings, then you need to do something like this:


DECLARE @date DATETIME = '20100101';

SELECT CASE WHEN DATEPART(WEEKDAY, @date) + @@DATEFIRST > 7
                 THEN DATEPART(WEEKDAY, @date) + @@DATEFIRST - 7
            ELSE DATEPART(WEEKDAY, @date) + @@DATEFIRST
       END AS date_part

Notice that this will always return 1 for sunday, 2 for monday, ... and 7 for saturday so you can use the date_part value in your calculation safely.

rc1138
Starting Member

35 Posts

Posted - 2010-05-07 : 01:59:41

Hi and thanks for the reply

By occurrences I meant

This particular output

Sunday Monday Tuesday Wednesday Thursday Friday
2 0 0 0 0 0

From a certain date range

* Please disregard the values underneath the days those are only placeholders

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-07 : 03:05:25

If you are using SQL 2005 then you can use Pivot operator..

If you can give sample table structure and sample data then we can frame the select statement for you.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

rc1138
Starting Member

35 Posts

Posted - 2010-05-07 : 04:16:27

The 2 tables are

CREATE TABLE [dbo].[irIncident](
[IncNo] [int] NULL,
[IncDate] [datetime] NULL,
[IncType] [int] NULL,
[EmpKey] [int] NULL,
[ClaimNo] [char](15) NULL,
[LostTime] [char](1) NULL,
[HlthCare] [char](1) NULL,

IncNo IncDate IncType EmpKey HlthCare
1 1/1/1996 1 1 Y
2 1/1/1996 1 2 Y
3 1/1/1996 1 3 Y

and

CREATE TABLE [dbo].[pdEmployee](
[EmpKey] [int] NULL,
[LastName] [char](25) NULL,
[FirstName] [char](20) NULL,
[EmpId] [char](9) NULL,
[Department] [char](10) NULL,

EmpKey LastName FirstName EmpId Department
1 X Y 101 1000
2 M N 102 1000
3 G H 103 1000

) ON [PRIMARY]

Cant seem to wrap my mind around the days part

SELECT COUNT(CASE WHEN DATEPART(IncDate) = 1 THEN 1 END) AS Sun, COUNT(CASE WHEN DATEPART(IncDate) = 2 THEN 1 END) AS Mon

FROM irIncident AS I INNER JOIN
pdEmployee AS E ON I.EmpKey = E.EmpKey
WHERE (E.Department = '1000') AND (I.HlthCare = 'Y')

it outputs the data but incorrect data

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-07 : 05:15:00

Try this:

CREATE TABLE [dbo].[irIncident](
[IncNo] [int] NULL,
[IncDate] [datetime] NULL,
[IncType] [int] NULL,
[EmpKey] [int] NULL,
[ClaimNo] [char](15) NULL,
[LostTime] [char](1) NULL,
[HlthCare] [char](1) NULL)

CREATE TABLE [dbo].[pdEmployee](
[EmpKey] [int] NULL,
[LastName] [char](25) NULL,
[FirstName] [char](20) NULL,
[EmpId] [char](9) NULL,
[Department] [char](10) NULL)

Insert into [irIncident](IncNo, IncDate, IncType, EmpKey, HlthCare) Values (1,'1996-01-01',1,1,'Y')
Insert into [irIncident](IncNo, IncDate, IncType, EmpKey ,HlthCare) Values (2,'1996-01-01',1,2,'Y')
Insert into [irIncident](IncNo ,IncDate, IncType ,EmpKey ,HlthCare) Values (3,'1996-01-01',1,3,'Y')
Insert into [irIncident](IncNo ,IncDate, IncType ,EmpKey ,HlthCare) Values (4,'1996-02-01',1,2,'Y')
Insert into [irIncident](IncNo ,IncDate, IncType ,EmpKey ,HlthCare) Values (5,'1996-02-03',1,3,'Y')

Insert into [pdEmployee] (EmpKey,LastName,FirstName,EmpId,Department) Values(1,'X','Y',101,1000)
Insert into [pdEmployee] (EmpKey,LastName,FirstName,EmpId,Department) Values(2,'M','N',102,1000)
Insert into [pdEmployee] (EmpKey,LastName,FirstName,EmpId,Department) Values(3,'G','H',103,1000)

select * from
(
SELECT EMPID,EMPID as E1,DATENAME(DW,[IncDate]) AS WEEKDY
FROM irIncident AS I INNER JOIN
pdEmployee AS E ON I.EmpKey = E.EmpKey
WHERE (E.Department = '1000') AND (I.HlthCare = 'Y') ) as SourceTable
PIVOT
(
COUNT(E1)
FOR WEEKDY IN ([SUNDAY],[MONDAY],[TUESDAY],[WEDNESDAY],[THURSDAY],[FRIDAY],[SATURDAY])
) AS PivotTable

If this is not your requirement then be more clear on your expected output.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-07 : 06:38:49

quote:
SELECT EMPID,EMPID as E1,DATENAME(DW,[IncDate]) AS WEEKDY

I have mentioned that DATEPART function is dependent on environment settings like SET LANGUAGE and SET DATEFIRST, and DATENAME is not different.

quote:
By occurrences I meant

This particular output

Sunday Monday Tuesday Wednesday Thursday Friday

Your original query is good, and all you need is to replace the DAY(IncDate) with CASE expression I provided in my reply.
Anyway this is your query after modification:


SELECT COUNT(CASE WHEN date_part = 1 THEN 1 END) AS sunday_count,
       COUNT(CASE WHEN date_part = 2 THEN 1 END) AS monday_count,
       COUNT(CASE WHEN date_part = 3 THEN 1 END) AS tuesday_count,
       COUNT(CASE WHEN date_part = 4 THEN 1 END) AS wednesday_count,
       COUNT(CASE WHEN date_part = 5 THEN 1 END) AS thursday_count,
       COUNT(CASE WHEN date_part = 6 THEN 1 END) AS friday_count,
       COUNT(CASE WHEN date_part = 7 THEN 1 END) AS saturday_count
  FROM (SELECT CASE WHEN DATEPART(WEEKDAY, IncDate) + @@DATEFIRST > 7
                         THEN DATEPART(WEEKDAY, IncDate) + @@DATEFIRST - 7
                    ELSE DATEPART(WEEKDAY, IncDate) + @@DATEFIRST
               END AS date_part
          FROM irIncident AS I
               INNER JOIN
               pdEmployee AS E
               ON I.EmpKey = E.EmpKey
         WHERE E.Department = '1000'
           AND I.HlthCare = 'Y') AS T

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources