schedule 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

SQL Server 2005 Forums

Transact-SQL (2005)

schedule query.

Author

Topic

sphericalx
Starting Member

10 Posts

Posted - 2008-12-14 : 23:45:13

StaffSchedule
StaffID, StaffName, WorkingDate, StartHrs, EndHrs, Hrswork
100, James, 01/01/2008, 8:00 AM, 8:00 PM, 12
100, James, 01/02/2008, 10:00 AM, 8:00 PM, 10
102, Tommy, 01/01/2008, 10:00 AM, 8:00 PM, 10
105, Sandy, 01/02/2008, 8:00 AM, 8:00 PM, 12
......
......

===============================================
Output Design
___________James_________| Tommy_________| Sandy
01/01/2008| 8:00AM-8:00PM | 10:00AM-8:00PM |
01/02/2008| 10:00AM-8:00PM|_______________| 8:00AM-8:00PM
01/03/2008|.......
.......
.......
01/31/2008|......

i have a table called staffSchedule. as the name implies, its meant for the staff working schedule and i would need to show it in the layout of Output Design.

for example if jan 2008, there will be 31 rows return with all the required information
feb there will be 28 rows.

as i'm using vb.net, i can get the number of days in a month. anyone know of how it can be done in the query?

thanx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 00:18:26

[code]
DECLARE @Month int,@StartDate datetime
SET @Month=1 --January (as an example)

SET @StartDate=DATEADD(mm,@Month-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))

;With Date_CTE (Date) AS
(SELECT @StartDate
UNION ALL
SELECT DATEADD(dd,1,Date)
FROM Date_CTE
WHERE DATEADD(dd,1,Date)<DATEADD(mm,1,@StartDate)
)

SELECT d.Date,
COALESCE(data.James,0) AS James,
COALESCE(data. Tommy,0) AS Tommy,
COALESCE(data. Sandy,0) AS Sandy
FROM Date_CTE d
LEFT JOIN
(
SELECT WorkingDate,
MAX(CASE WHEN StaffName='James' THEN StartHrs ELSE NULL END) + '-'+MAX(CASE WHEN StaffName='James' THEN EndHrs ELSE NULL END) AS James,
MAX(CASE WHEN StaffName='Tommy' THEN StartHrs ELSE NULL END) + '-'+MAX(CASE WHEN StaffName='Tommy' THEN EndHrs ELSE NULL END) AS Tommy,
MAX(CASE WHEN StaffName='Sandy' THEN StartHrs ELSE NULL END) + '-'+MAX(CASE WHEN StaffName='Sandy' THEN EndHrs ELSE NULL END) AS Sandy
FROM Table
GROUP BY WorkingDate
)data
ON d.Date=data.WorkingDate
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 00:21:09

and just in case the staffnames are dynamic, you need to apply the below logic

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

sphericalx
Starting Member

10 Posts

Posted - 2008-12-17 : 04:55:53

err.. are there another way to do it? cuz i gonna be using it in my vb application. are there such query which dun need to do any declare??

xpandre
Posting Yak Master

212 Posts

Posted - 2008-12-17 : 08:03:30

I am not sure if this is what you want..



CREATE TABLE STAFFSCHEDULE(
STAFFID INT, STAFFNAME VARCHAR(MAX), WORKINGDATE DATETIME, STARTHRS VARCHAR(MAX), ENDHRS VARCHAR(MAX), HRSWORK	INT)

INSERT INTO STAFFSCHEDULE VALUES(100,'JAMES','01/01/2008','8:00 AM','8:00 PM',12)
INSERT INTO STAFFSCHEDULE VALUES(100,'JAMES','01/02/2008','8:00 AM','8:00 PM',12)
INSERT INTO STAFFSCHEDULE VALUES(110,'TOMMY','01/01/2008','10:00 AM','8:00 PM',12)
INSERT INTO STAFFSCHEDULE VALUES(120,'SANDY','01/02/2008','8:00 AM','8:00 PM',12)
INSERT INTO STAFFSCHEDULE VALUES(120,'SANDY','01/03/2008','8:00 AM','8:00 PM',12)


SELECT A.WORKINGDATE,JAMES.STARTHRS+'-'+JAMES.ENDHRS 'JAMES',
TOMMY.STARTHRS+'-'+TOMMY.ENDHRS 'TOMMY',
SANDY.STARTHRS+'-'+SANDY.ENDHRS 'SANDY' 
FROM  (SELECT DISTINCT WORKINGDATE FROM STAFFSCHEDULE) A LEFT JOIN
(SELECT *
FROM STAFFSCHEDULE B WHERE STAFFID = 100) JAMES ON A.WORKINGDATE = JAMES.WORKINGDATE 
LEFT JOIN
(SELECT *
FROM STAFFSCHEDULE C WHERE STAFFID = 110) TOMMY ON A.WORKINGDATE = TOMMY.WORKINGDATE 
LEFT JOIN
(SELECT *
FROM STAFFSCHEDULE D WHERE STAFFID = 120) SANDY ON A.WORKINGDATE = SANDY.WORKINGDATE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 08:22:37

quote:
Originally posted by sphericalx

err.. are there another way to do it? cuz i gonna be using it in my vb application. are there such query which dun need to do any declare??

the declare was just to show you passed value. in actual practice what you need is to create a procedure with parameter for startDate and month like below


CREATE PROCEDURE GetDetails
@Month int,
@StartDate datetime=NULL
AS

SET @StartDate=COALESCE(@StartDate,DATEADD(mm,@Month-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)))

;With Date_CTE (Date) AS
(SELECT @StartDate
 UNION ALL
SELECT DATEADD(dd,1,Date)
FROM Date_CTE
WHERE DATEADD(dd,1,Date)<DATEADD(mm,1,@StartDate)
)


SELECT d.Date,
COALESCE(data.James,0) AS James,
COALESCE(data. Tommy,0) AS Tommy,
COALESCE(data. Sandy,0) AS Sandy
FROM Date_CTE d
LEFT JOIN
(
SELECT WorkingDate,
MAX(CASE WHEN StaffName='James' THEN StartHrs ELSE NULL END) + '-'+MAX(CASE WHEN StaffName='James' THEN EndHrs ELSE NULL END) AS James,
MAX(CASE WHEN StaffName='Tommy' THEN StartHrs ELSE NULL END) + '-'+MAX(CASE WHEN StaffName='Tommy' THEN EndHrs ELSE NULL END) AS Tommy,
MAX(CASE WHEN StaffName='Sandy' THEN StartHrs ELSE NULL END) + '-'+MAX(CASE WHEN StaffName='Sandy' THEN EndHrs ELSE NULL END) AS Sandy
FROM Table
GROUP BY WorkingDate
)data
ON d.Date=data.WorkingDate
GO

and call it from vb passing values for month & startdate

sphericalx
Starting Member

10 Posts

Posted - 2008-12-17 : 21:00:31

err.. not realli sure if which database system i using will affect or not but i'm using sql server 2000.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-17 : 23:15:08

quote:
Originally posted by sphericalx

err.. not realli sure if which database system i using will affect or not but i'm using sql server 2000.

You've posted your question in a SQL Server 2005 forum.

You should start a new thread in the appropriate forum. I could move this one to the right forum, however you will get more attention if you started a new thread since this one has a bunch of replies.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sphericalx
Starting Member

10 Posts

Posted - 2008-12-18 : 02:00:51

opps.. now tat u say then i realise i at a 2005 forum.. =x

hmm.. mind if u help mi move it? thnx alot..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources