Group By using Days and Week and Month

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

SQL Server 2005 Forums

Transact-SQL (2005)

Group By using Days and Week and Month

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2010-10-27 : 07:45:27

SELECT COUNT(UserID) AS NoOfEmployees,
JoinedOn,
ActInactDate,
DATEDIFF(DAY,JoinedOn,ActInactDate) AS NoOfDays
FROM Employee WHERE CompanyID=11
AND JoinedOn BETWEEN '09/01/2010' AND '09/30/2010'
AND Status=0 AND ActInactDate IS NOT NULL
GROUP BY DATEDIFF(DAY,JoinedOn,ActInactDate),JoinedOn,ActInactDate

NoOfEmployees Joined Date Termination Date NoOfDays
1 2010-09-27 00:00:00 2010-10-01 00:00:00 4
1 2010-09-06 00:00:00 2010-09-11 00:00:00 5
1 2010-09-10 00:00:00 2010-09-15 00:00:00 5
1 2010-09-09 00:00:00 2010-09-15 00:00:00 6
1 2010-09-20 00:00:00 2010-10-02 00:00:00 12
1 2010-09-06 00:00:00 2010-09-22 00:00:00 16
1 2010-09-08 00:00:00 2010-09-24 00:00:00 16
1 2010-09-07 00:00:00 2010-09-24 00:00:00 17
2 2010-09-04 00:00:00 2010-09-28 00:00:00 24
1 2010-09-07 00:00:00 2010-10-10 00:00:00 33
1 2010-09-16 19:11:00 2010-10-21 00:00:00 35
1 2010-09-07 00:00:00 2010-10-15 00:00:00 38
1 2010-09-06 00:00:00 2010-10-15 00:00:00 39
2 2010-09-04 00:00:00 2010-10-14 00:00:00 40
1 2010-09-07 00:00:00 2010-10-18 00:00:00 41
1 2010-09-15 00:00:00 2010-10-27 00:00:00 42
1 2010-09-15 00:00:00 2010-10-28 00:00:00 43
1 2010-09-07 00:00:00 2010-10-26 00:00:00 49
1 2010-09-07 00:00:00 2010-10-27 00:00:00 50

I need this output has below. Need to Group NoOfDays as 1 day, 3 day, 1 week and 1 month. Please help me in this..

NoOfEmployees NoOfDays
5 1
10 3
8 1Week
12 1Month

Regards,
Kalaiselvan R
Love Yourself First....

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-27 : 07:51:51

Is this just for presentation in front end then i suggest you do the formatting in front end only.

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2010-10-27 : 08:55:17

No I need it in a paka format.. Just the Group by of those days to 1day, 3 Days ,1 week and 1 Month

Regards,
Kalaiselvan R
Love Yourself First....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-27 : 14:04:09

you need to add an expression using CASE like


....
CASE WHEN DATEDIFF(DAY,JoinedOn,ActInactDate) > 3 AND DATEDIFF(DAY,JoinedOn,ActInactDate)<= 7 THEN '1 Week'
     WHEN DATEDIFF(DAY,JoinedOn,ActInactDate) > 7 AND DATEDIFF(DAY,JoinedOn,ActInactDate)<= 30 THEN '1 Month'
     ELSE DATEDIFF(DAY,JoinedOn,ActInactDate)
END AS NoOfDays 
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kalaiselvan.rajendran
Starting Member

2 Posts

Posted - 2010-11-09 : 02:46:06

Thank you, Its working fine now...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-10 : 10:48:45

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2011-01-27 : 08:31:04

SELECT COUNT(USerID),JoinedOn FROM Employee WHERE Status=1 GROUP BY Joined ON

It will give the Output has:

1 1985-01-01 00:00:00
1 1989-04-01 00:00:00
1 1990-02-01 00:00:00
1 1992-01-01 00:00:00
1 1994-11-03 00:00:00
1 1995-04-17 00:00:00
2 1995-05-11 00:00:00
1 2004-02-23 00:00:00
1 2004-05-23 00:00:00
1 2004-12-01 00:00:00
1 2005-03-23 00:00:00
1 2005-08-08 00:00:00
1 2005-08-18 00:00:00
1 2005-09-02 00:00:00
1 2005-09-28 00:00:00
1 2006-01-01 00:00:00
1 2006-05-12 00:00:00
1 2006-07-16 00:00:00
1 2007-01-01 00:00:00
1 2007-03-12 00:00:00
1 2007-04-01 00:00:00
1 2007-04-04 00:00:00
1 2007-05-03 00:00:00
1 2007-06-01 00:00:00
1 2007-07-01 00:00:00
1 2007-07-09 00:00:00
1 2007-11-21 00:00:00
1 2007-12-05 00:00:00
1 2007-12-26 00:00:00
1 2008-01-10 00:00:00
1 2008-02-04 00:00:00
1 2008-03-01 00:00:00
1 2008-03-05 00:00:00
1 2008-04-11 00:00:00
1 2008-04-21 00:00:00
1 2008-04-23 00:00:00
1 2008-05-07 00:00:00
1 2008-06-23 00:00:00
1 2008-07-23 00:00:00
1 2008-07-29 00:00:00
1 2008-08-01 00:00:00
1 2008-09-01 00:00:00
1 2008-11-11 00:00:00
1 2008-11-27 00:00:00
1 2009-02-06 00:00:00
1 2009-05-02 00:00:00
1 2009-06-10 00:00:00
1 2009-06-11 00:00:00
1 2009-07-01 00:00:00
1 2009-07-07 00:00:00
1 2009-08-26 00:00:00
1 2009-08-28 00:00:00
2 2009-10-01 00:00:00
2 2009-10-15 00:00:00
1 2009-11-11 00:00:00
1 2009-11-25 00:00:00
1 2009-12-08 00:00:00
2 2009-12-14 00:00:00
2 2009-12-29 00:00:00
8 2010-01-01 00:00:00
1 2010-01-06 00:00:00
2 2010-01-08 00:00:00
1 2010-01-12 00:00:00
1 2010-01-18 00:00:00
1 2010-02-01 00:00:00
1 2010-02-03 00:00:00
1 2010-02-05 00:00:00
1 2010-02-08 00:00:00
1 2010-02-12 00:00:00
3 2010-02-15 00:00:00
1 2010-02-24 00:00:00
1 2010-03-08 00:00:00
1 2010-03-15 00:00:00
1 2010-03-22 00:00:00
1 2010-04-01 00:00:00
1 2010-04-05 00:00:00
1 2010-04-10 00:00:00
2 2010-04-12 00:00:00
2 2010-04-15 00:00:00
2 2010-04-26 00:00:00
1 2010-04-28 00:00:00
2 2010-05-10 00:00:00
2 2010-05-25 00:00:00
2 2010-06-01 00:00:00
1 2010-06-04 00:00:00
2 2010-06-14 00:00:00
1 2010-06-16 00:00:00
1 2010-06-28 00:00:00
1 2010-07-01 00:00:00
4 2010-07-02 00:00:00
3 2010-07-12 00:00:00
1 2010-07-19 00:00:00
1 2010-07-20 00:00:00
2 2010-07-26 00:00:00
1 2010-08-03 00:00:00
1 2010-08-04 00:00:00
1 2010-08-09 00:00:00
2 2010-08-17 00:00:00
1 2010-08-20 00:00:00
7 2010-08-30 00:00:00
3 2010-09-01 00:00:00
1 2010-09-02 00:00:00
1 2010-09-03 00:00:00
2 2010-09-06 00:00:00
3 2010-09-07 00:00:00
1 2010-09-22 00:00:00
1 2010-09-23 00:00:00
1 2010-09-29 00:00:00
1 2010-10-04 00:00:00
1 2010-10-05 00:00:00
1 2010-10-06 00:00:00
1 2010-10-07 00:00:00
4 2010-10-08 00:00:00
1 2010-10-16 00:00:00
2 2010-10-18 00:00:00
1 2010-10-19 00:00:00
1 2010-10-22 00:00:00
2 2010-10-26 00:00:00
1 2010-10-28 00:00:00
2 2010-11-01 00:00:00
2 2010-11-02 00:00:00
1 2010-11-03 00:00:00
1 2010-11-15 00:00:00
3 2010-11-23 00:00:00
1 2010-11-25 00:00:00
2 2010-11-26 00:00:00
1 2010-11-30 00:00:00
1 2010-12-02 00:00:00
1 2010-12-03 00:00:00
8 2010-12-06 00:00:00
1 2010-12-10 00:00:00
1 2010-12-14 00:00:00
1 2010-12-15 00:00:00
1 2010-12-16 00:00:00
2 2010-12-22 00:00:00
1 2010-12-23 00:00:00
3 2010-12-27 00:00:00
1 2010-12-28 00:00:00
1 2010-12-29 00:00:00
1 2011-01-03 00:00:00
1 2011-01-06 00:00:00
2 2011-01-07 00:00:00
1 2011-01-07 10:57:00
6 2011-01-10 00:00:00
3 2011-01-18 00:00:00
1 2011-01-21 00:00:00
3 2011-01-24 00:00:00
1 2011-01-24 14:18:00

I need the output has Bucket system. With Interval of joining date for Count of employees.

Days EmployeeCount
<30 25
30-60 21
60-90 44
90-120 35
>120 15

Please help me in this to built a query..

Regards,
Kalaiselvan R
Love Yourself First....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources