May 1, 2012 at 4:19 am
Hi,
I get duplicate data when I join 2 tables to calculate Standard hours(using SUM) for an employee. Table- Staff has the static data for an employee which lists the standard hours for each day of the week.
Table- Staffday has the actual data for the employee- no. of hours worked each day etc. I need to calculate the Standard hours for the employee by using the following query:
-----------------------------------------------------------
Select
max(st.Cardholder_name) as [Employee Name],
max(case
When datepart(mm,s.[Date])='01' Then 'January'
When datepart(mm,s.[Date])='02' Then 'February'
When datepart(mm,s.[Date])='03' Then 'March'
When datepart(mm,s.[Date])='04' Then 'April'
When datepart(mm,s.[Date])='05' Then 'May'
When datepart(mm,s.[Date])='06' Then 'June'
When datepart(mm,s.[Date])='07' Then 'July'
When datepart(mm,s.[Date])='08' Then 'August'
When datepart(mm,s.[Date])='09' Then 'September'
When datepart(mm,s.[Date])='10' Then 'October'
When datepart(mm,s.[Date])='11' Then 'November'
When datepart(mm,s.[Date])='12' Then 'December'
End) as [Month],
max(datepart(yyyy,s.[Date])) as [Year],
CONVERT(int, (sum(datepart(hour,Monhours) + datepart(hour,Tuehours) + datepart(hour,Wedhours) + datepart(hour,Thuhours) + datepart(hour,Frihours) ))) as [Standard Hours]
from StaffDay s
inner join Staff st
on s.Staff_id = st.Staff_id
where s.Dayname NOT IN ('Sun','Sat')
and datepart(mm,s.[Date]) IN ('01','02', '03','04','05','06','07','08','09','10','11','12')
and case
When datepart(mm,s.[Date])='01' Then 'January'
When datepart(mm,s.[Date])='02' Then 'February'
When datepart(mm,s.[Date])='03' Then 'March'
When datepart(mm,s.[Date])='04' Then 'April'
When datepart(mm,s.[Date])='05' Then 'May'
When datepart(mm,s.[Date])='06' Then 'June'
When datepart(mm,s.[Date])='07' Then 'July'
When datepart(mm,s.[Date])='08' Then 'August'
When datepart(mm,s.[Date])='09' Then 'September'
When datepart(mm,s.[Date])='10' Then 'October'
When datepart(mm,s.[Date])='11' Then 'November'
When datepart(mm,s.[Date])='12' Then 'December'
End in ('April')
group by st.Cardholder_name, datepart(mm,s.[Date])
order by st.Cardholder_name, datepart(mm,s.[Date]) asc
-----------------------------------------------------------
however, as per the sample data (attached), I get the result as 252 which is wrong, the correct value should be 42(7d hrs * 6 days).
Could someone please suggest a solution which can be applied weithin the same query or if not possible, any other way to achive this. Please find the tables structures and sample data attached.
Thanks,
Paul
May 1, 2012 at 4:31 am
Would you be able to post DDL and data setup not as attachments? Many workplaces do prohibit downloading from web...
May 1, 2012 at 4:42 am
Eugene Elutin (5/1/2012)
Would you be able to post DDL and data setup not as attachments? Many workplaces do prohibit downloading from web...
yes,please find the DDL and sample data below:
Staff- DDL
--------------------------
CREATE TABLE [dbo].[Staff](
[Staff_id] [int] IDENTITY(1,1) NOT NULL,
[Cardholder_name] [varchar](50) NULL,
[MonHours] [time](0) NULL,
[TueHours] [time](0) NULL,
[WedHours] [time](0) NULL,
[ThuHours] [time](0) NULL,
[FriHours] [time](0) NULL,
[WeekHours] [varchar](7) NULL,
CONSTRAINT [PK_Staff] PRIMARY KEY CLUSTERED
(
[Staff_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
---------------------------------------------
Staff_day DDL
---------------------------------------------
CREATE TABLE [dbo].[StaffDay](
[Staff_id] [int] NOT NULL,
[Cardholder_name] [varchar](50) NULL,
[Date] [date] NOT NULL,
[Dayname] [char](3) NULL,
[TimeNetOut] [time](7) NULL,
CONSTRAINT [PK_StaffDay] PRIMARY KEY CLUSTERED
(
[Staff_id] ASC,
[Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
---------------------------------------------
Sample data
---------------------------------------------
INSERT INTO [Staff]
VALUES('30', 'Graham','07:00:00','07:00:00','07:00:00','07:00:00','00:00:00','28:00')
INSERT INTO [StaffDay]
VALUES('30', 'Graham','2012-04-10', 'Mon', '08:52:20.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham','2012-04-11', 'Tue', '07:30:45.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham','2012-04-12', 'Wed', '09:41:32.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham','2012-04-13', 'Thu', '08:52:27.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham','2012-04-14', 'Fri', '08:11:18.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham','2012-04-15', 'Mon', '07:54:37.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham','2012-04-16', 'Tue', '07:19:02.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham','2012-04-17', 'Wed', '08:55:46.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham','2012-04-18', 'Thu', '07:29:52.0000000')
---------------------------------------------
Regards,
Paul
May 1, 2012 at 5:03 am
Paul, let start from making your query a bit more readable.
1. You can use DATENAME function instead of converting month number to string.
2. You don't need check if "datepart(mm,s.[Date]) IN ('01','02', '03','04','05','06','07','08','09','10','11','12') " as s[Date] is of date datatype, therefore having month from 1 to 12 is only a possible option.
3. it always much clear to use full date part name instead of short form: month vs mm
Let see where it will take us:
Select
max(st.Cardholder_name) as [Employee Name],
max(datename(month,s.[Date])) as [Month],
max(datepart(yyyy,s.[Date])) as [Year],
CONVERT(int, (sum(datepart(hour,Monhours) + datepart(hour,Tuehours) + datepart(hour,Wedhours) + datepart(hour,Thuhours) + datepart(hour,Frihours) ))) as [Standard Hours]
from StaffDay s
inner join Staff st
on s.Staff_id = st.Staff_id
where s.Dayname NOT IN ('Sun','Sat')
and datename(month,s.[Date]) in ('April')
group by st.Cardholder_name, datepart(month,s.[Date])
order by st.Cardholder_name, datepart(month,s.[Date]) asc
Yep, it does still return 315 hours.
But that is exactly what you calculating. there are no duplicate records in your StaffDay sample data.
Could you clarify how you thing the query should calculate total hours based on given Date values.
May 1, 2012 at 5:11 am
I can see the problem. Please wait for few minutes.
May 1, 2012 at 5:13 am
Eugene Elutin (5/1/2012)
Paul, let start from making your query a bit more readable.1. You can use DATENAME function instead of converting month number to string.
2. You don't need check if "datepart(mm,s.[Date]) IN ('01','02', '03','04','05','06','07','08','09','10','11','12') " as s[Date] is of date datatype, therefore having month from 1 to 12 is only a possible option.
3. it always much clear to use full date part name instead of short form: month vs mm
Let see where it will take us:
Select
max(st.Cardholder_name) as [Employee Name],
max(datename(month,s.[Date])) as [Month],
max(datepart(yyyy,s.[Date])) as [Year],
CONVERT(int, (sum(datepart(hour,Monhours) + datepart(hour,Tuehours) + datepart(hour,Wedhours) + datepart(hour,Thuhours) + datepart(hour,Frihours) ))) as [Standard Hours]
from StaffDay s
inner join Staff st
on s.Staff_id = st.Staff_id
where s.Dayname NOT IN ('Sun','Sat')
and datename(month,s.[Date]) in ('April')
group by st.Cardholder_name, datepart(month,s.[Date])
order by st.Cardholder_name, datepart(month,s.[Date]) asc
Yep, it does still return 315 hours.
But that is exactly what you calculating. there are no duplicate records in your StaffDay sample data.
Could you clarify how you thing the query should calculate total hours based on given Date values.
The standard hours for an employee are based on the hours mentioned for each day in the Staff table. In this case, for the employee- Graham, it should be 7 hrs for each day except friday, which is 28 standard hours per week.
In the Staffday table, it should not calculate the hours for friday, so it should be 56 hours based on the revised sample data below:
------------------------------
INSERT INTO [Staff]
VALUES('30', 'Graham','07:00:00','07:00:00','07:00:00','07:00:00','00:00:00','28:00')
INSERT INTO [StaffDay]
VALUES('30', 'Graham','2012-04-10', 'Mon', '08:52:20.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham','2012-04-11', 'Tue', '07:30:45.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham','2012-04-12', 'Wed', '09:41:32.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham','2012-04-13', 'Thu', '08:52:27.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham','2012-04-14', 'Fri', '00:00:00.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham','2012-04-15', 'Mon', '07:54:37.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham','2012-04-16', 'Tue', '07:19:02.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham','2012-04-17', 'Wed', '08:55:46.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham','2012-04-18', 'Thu', '07:29:52.0000000')
------------------------------
I hope this explains the situation clearly.
May 1, 2012 at 5:26 am
You would greatly benefit if you could normalise your Staff table.
I would suggest to get MonHours, TueHours etc. out of this table and place it into dedicated StaffWorkHours table which can be defined as:
StaffId,
DayName (as Monday, Tuesday etc.)
Hours.
Otherwise, you need to do it on fly:
;with BetterToBeATable
as
(
SELECT Staff_Id, LEFT(WeekDayName,3) WeekDayName, WorkHours
FROM
(SELECT Staff_Id, MonHours, TueHours, WedHours, ThuHours, FriHours
FROM Staff) p
UNPIVOT
(WorkHours FOR WeekDayName IN
(MonHours, TueHours, WedHours, ThuHours, FriHours)
)AS unpvt
)
Select
max(st.Cardholder_name) as [Employee Name],
max(datename(month,s.[Date])) as [Month],
max(datepart(yyyy,s.[Date])) as [Year],
CONVERT(int,sum(datepart(hour,b.WorkHours))) as [Standard Hours]
from StaffDay s
join BetterToBeATable b
on b.staff_id = s.staff_id
and b.WeekDayName = s.[Dayname]
join Staff st
on st.staff_id = s.staff_id
where s.Dayname NOT IN ('Sun','Sat')
and datename(month,s.[Date]) in ('April')
group by st.Cardholder_name, datepart(month,s.[Date])
order by st.Cardholder_name, datepart(month,s.[Date]) asc
May 1, 2012 at 5:36 am
Eugene Elutin (5/1/2012)
You would greatly benefit if you could normalise your Staff table.I would suggest to get MonHours, TueHours etc. out of this table and place it into dedicated StaffWorkHours table which can be defined as:
StaffId,
DayName (as Monday, Tuesday etc.)
Hours.
Otherwise, you need to do it on fly:
;with BetterToBeATable
as
(
SELECT Staff_Id, LEFT(WeekDayName,3) WeekDayName, WorkHours
FROM
(SELECT Staff_Id, MonHours, TueHours, WedHours, ThuHours, FriHours
FROM Staff) p
UNPIVOT
(WorkHours FOR WeekDayName IN
(MonHours, TueHours, WedHours, ThuHours, FriHours)
)AS unpvt
)
Select
max(st.Cardholder_name) as [Employee Name],
max(datename(month,s.[Date])) as [Month],
max(datepart(yyyy,s.[Date])) as [Year],
CONVERT(int,sum(datepart(hour,b.WorkHours))) as [Standard Hours]
from StaffDay s
join BetterToBeATable b
on b.staff_id = s.staff_id
and b.WeekDayName = s.[Dayname]
join Staff st
on st.staff_id = s.staff_id
where s.Dayname NOT IN ('Sun','Sat')
and datename(month,s.[Date]) in ('April')
group by st.Cardholder_name, datepart(month,s.[Date])
order by st.Cardholder_name, datepart(month,s.[Date]) asc
I suggested the same thing you mentioned to the person who created these tables. However, I got the reply that this was out of question and I have to proceed with it regardless.
Thanks for your suggestion but I had another question for you. Using the method you suggested would it be possible to deduct the Standard hours from Actual Hours query (not included in the query above) ? That is very important for me. This is the query I used for Actual hours:
CONVERT(varchar(6), (Sum((datepart(hour,Timenetin) * 3600)) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(second,Timenetin)) + ) / 3600)
+ ':' + RIGHT('0' + CONVERT(varchar(2), (sum((datepart(hour,Timenetin) * 3600)) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(second,Timenetin))) % 3600 / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), (sum((datepart(hour,Timenetin) * 3600)) * 3600) +
sum(datepart(minute,Timenetin) * 60) + sum(datepart(second,Timenetin))) % 60), 2) as [Actual Hours]
May 1, 2012 at 5:45 am
....
I suggested the same thing you mentioned to the person who created these tables. However, I got the reply that this was out of question and I have to proceed with it regardless.
Thanks for your suggestion but I had another question for you. Using the method you suggested would it be possible to deduct the Standard hours from Actual Hours query (not included in the query above) ? That is very important for me. This is the query I used for Actual hours:
...
1. The person who design the tables supposed to know a bit more about basic database design... Actually, What is the reason to have such configuration cross-tab? It gives only pain, I can't see any benefits here.
Actually, you may find that unpivot is quite slow operation when dealing with large data sets.
2. You can deduct whatever you want from whatever you like.
You didn't post the query you have, only one single calculation line...
May 1, 2012 at 6:07 am
Eugene Elutin (5/1/2012)
....
I suggested the same thing you mentioned to the person who created these tables. However, I got the reply that this was out of question and I have to proceed with it regardless.
Thanks for your suggestion but I had another question for you. Using the method you suggested would it be possible to deduct the Standard hours from Actual Hours query (not included in the query above) ? That is very important for me. This is the query I used for Actual hours:
...
1. The person who design the tables supposed to know a bit more about basic database design... Actually, What is the reason to have such configuration cross-tab? It gives only pain, I can't see any benefits here.
Actually, you may find that unpivot is quite slow operation when dealing with large data sets.
2. You can deduct whatever you want from whatever you like.
You didn't post the query you have, only one single calculation line...
Thanks a ton, Eugene !! Your query worked wonderfully. I tested it and it is quite quick to run as well.
Would it be okay if I work on the 2nd part of the query and then com back to you if I face any issues ?
Thanks again !
Paul
May 1, 2012 at 6:10 am
no probs at all, if I'll have a time I'll help.
May 1, 2012 at 6:18 am
You can deduct whatever you want from whatever you like.
This could come in very useful. I'd like to deduct all the French people from the city I live in.
:w00t: Just kidding
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 1, 2012 at 7:01 am
Phil Parkin (5/1/2012)
You can deduct whatever you want from whatever you like.
This could come in very useful. I'd like to deduct all the French people from the city I live in.
:w00t: Just kidding
No probs at all! Take a look into modern "nuke" technology. It helps with deduction of large numbers where performance is important. Just remember, that French are aware of this technique as well and they have more capabilities in this regard... :w00t: :hehe: :w00t:
May 1, 2012 at 10:10 am
Eugene Elutin (5/1/2012)
no probs at all, if I'll have a time I'll help.
Thanks a ton once again for saving my job !!! I have completed the report which I has really stuck on for a long time and which you resolved in minutes !!! God Bless you !!!
May 3, 2012 at 6:07 am
Eugene Elutin (5/1/2012)
no probs at all, if I'll have a time I'll help.
Hi Eugene,
I am trying to resolve this last requirement with the query. The problem I am facing now is while calculating the sum of cat2_hours for each individual category2_id(Grouped by each category2_id).
When I try to group it just groups the cat2_hours as a whole for each employee (commented out code below). I want it to be grouped as per the category2_id and can be created as 4 different columns like
1. S_cat2hours
2. M_cat2hours
3. B_cat2hours
4. R_cat2hours
Would that be possible ?
This is the query that I had created with your help before:
-------------------------------------------------------------
;with BetterToBeATable
as
(
SELECT Staff_Id, LEFT(WeekDayName,3) WeekDayName, WorkHours
FROM
(SELECT Staff_Id, MonHours, TueHours, WedHours, ThuHours, FriHours
FROM Staff) p
UNPIVOT
(WorkHours FOR WeekDayName IN
(MonHours, TueHours, WedHours, ThuHours, FriHours)
)AS unpvt
)
Select
max(st.Cardholder_name) as [Employee Name],
max(datename(month,s.[Date])) as [Month],
max(datepart(yyyy,s.[Date])) as [Year],
/*
case when max(category2_id) = 'S' then CONVERT(varchar(6), (sum((datepart(hour,ISNULL(cat2_hours,'00:00:00')) * 3600)) + sum(datepart(minute,ISNULL(cat2_hours,'00:00:00')) * 60) + sum(datepart(second,ISNULL(cat2_hours,'00:00:00')))) / 3600)
+ ':' + RIGHT('0' + CONVERT(varchar(2), (sum((datepart(hour,ISNULL(cat2_hours,'00:00:00')) * 3600)) + sum(datepart(minute,ISNULL(cat2_hours,'00:00:00')) * 60)+
sum(datepart(second,ISNULL(cat2_hours,'00:00:00')))) % 3600 / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), (sum((datepart(hour,ISNULL(cat2_hours,'00:00:00')) * 3600)) +
sum(datepart(minute,ISNULL(cat2_hours,'00:00:00')) * 60) + sum(datepart(second,ISNULL(cat2_hours,'00:00:00')))) % 60), 2) end as [Cat2_Hours],
*/
CONVERT(varchar(10), (Sum((datepart(hour,b.WorkHours) * 3600)) + sum(datepart(minute,b.WorkHours) * 60) + sum(datepart(second,b.WorkHours))) / 3600)
+ ':' + RIGHT('0' + CONVERT(varchar(2), (sum((datepart(hour,b.WorkHours) * 3600)) + sum(datepart(minute,b.WorkHours) * 60) +
sum(datepart(second,b.WorkHours))) % 3600 / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), (sum((datepart(hour,b.WorkHours) * 3600)) +
sum(datepart(minute,b.WorkHours) * 60) + sum(datepart(second,b.WorkHours))) % 60), 2) as [Standard Hours],
CONVERT(varchar(6), (Sum((datepart(hour,Timenetin) * 3600)) + sum((datepart(hour,ISNULL(cat2_hours,'00:00:00')) * 3600)) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(minute,ISNULL(cat2_hours,'00:00:00')) * 60) + sum(datepart(second,Timenetin)) + sum(datepart(second,ISNULL(cat2_hours,'00:00:00')))) / 3600)
+ ':' + RIGHT('0' + CONVERT(varchar(2), (sum((datepart(hour,Timenetin) * 3600)) + sum((datepart(hour,ISNULL(cat2_hours,'00:00:00')) * 3600)) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(minute,ISNULL(cat2_hours,'00:00:00')) * 60)+
sum(datepart(second,Timenetin)) + sum(datepart(second,ISNULL(cat2_hours,'00:00:00')))) % 3600 / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), (sum((datepart(hour,Timenetin) * 3600)) + sum((datepart(hour,ISNULL(cat2_hours,'00:00:00')) * 3600)) +
sum(datepart(minute,Timenetin) * 60) + sum(datepart(minute,ISNULL(cat2_hours,'00:00:00')) * 60) + sum(datepart(second,Timenetin)) + sum(datepart(second,ISNULL(cat2_hours,'00:00:00')))) % 60), 2) as [Actual Hours],
convert(int,((sum(datepart(hour,Timenetin) * 3600) + sum(datepart(hour,ISNULL(cat2_hours,'00:00:00')) * 3600) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(minute,ISNULL(cat2_hours,'00:00:00')) * 60) + sum(datepart(second,Timenetin) * 1) + sum(datepart(second,ISNULL(cat2_hours,'00:00:00')) * 1))))/3600 -
((Sum(datepart(hour,b.WorkHours) * 3600) + sum(datepart(minute,b.WorkHours) * 60) + sum(datepart(second,b.WorkHours) * 1)))/3600 As Hrs_Diff,
convert(int,((sum(datepart(hour,Timenetin) * 3600) + sum(datepart(hour,ISNULL(cat2_hours,'00:00:00')) * 3600) + sum(datepart(minute,Timenetin) * 60) + sum(datepart(minute,ISNULL(cat2_hours,'00:00:00')) * 60) + sum(datepart(second,Timenetin) * 1) + sum(datepart(second,ISNULL(cat2_hours,'00:00:00')) * 1)) -
((Sum(datepart(hour,b.WorkHours) * 3600) + sum(datepart(minute,b.WorkHours) * 60) + sum(datepart(second,b.WorkHours) * 1)))))% 3600/60 As Min_Diff
from StaffDay s
join BetterToBeATable b
on b.staff_id = s.staff_id
and b.WeekDayName = s.[Dayname]
join Staff st
on st.staff_id = s.staff_id
where s.Dayname NOT IN ('Sun','Sat')
and datepart(yyyy,s.[Date]) IN ('2012')
and datename(month,s.[Date]) in ('April')
group by s.Cardholder_name, datepart(month,s.[Date])
order by s.Cardholder_name, datepart(month,s.[Date]) asc
-----------------------------------------------------------
Please find the table structures for the Staff and Staffday tables below:
-------------------------------------------------
CREATE TABLE [dbo].[Staff](
[Staff_id] [int] NOT NULL,
[Cardholder_name] [varchar](50) NULL,
[MonHours] [time](0) NULL,
[TueHours] [time](0) NULL,
[WedHours] [time](0) NULL,
[ThuHours] [time](0) NULL,
[FriHours] [time](0) NULL,
CONSTRAINT [PK_Staff] PRIMARY KEY CLUSTERED
(
[Staff_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-----------------------------------------------------
CREATE TABLE [dbo].[StaffDay](
[Staff_id] [int] NOT NULL,
[Cardholder_name] [varchar](50) NULL,
[Date] [date] NOT NULL,
[Dayname] [char](3) NULL,
[TimeNetIn] [time](7) NULL,
[category2_id] [char](2) NOT NULL,
[cat2_hours] [time](7) NULL,
CONSTRAINT [PK_StaffDay] PRIMARY KEY CLUSTERED
(
[Staff_id] ASC,
[Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
---------------------------------------------
Please find the sample data below:
INSERT INTO [Staff]
VALUES('30', 'Graham', '07:00:00', '07:00:00', '07:00:00', '07:00:00', '07:00:00')
INSERT INTO [Staff]
VALUES('35', 'Paul', '08:00:00', '08:00:00', '08:00:00', '08:00:00', '08:00:00')
INSERT INTO [StaffDay]
VALUES('30', 'Graham', '2012-04-10', 'Mon', '08:52:20.0000000','S','07:00:00.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham', '2012-04-11', 'Tue', '07:30:45.0000000','S','08:30:00.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham', '2012-04-12', 'Wed', '09:41:32.0000000','M','09:30:00.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham', '2012-04-13', 'Thu', '08:52:27.0000000','M','08:30:00.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham', '2012-04-14', 'Fri', '08:11:18.0000000','B','07:00:00.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham', '2012-04-15', 'Mon', '07:54:37.0000000','B','06:00:00.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham', '2012-04-16', 'Tue', '07:19:02.0000000','B','05:30:00.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham', '2012-04-17', 'Wed', '08:55:46.0000000','R','10:30:00.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham', '2012-04-18', 'Thu', '07:29:52.0000000','R','09:00:00.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham', '2012-04-18', 'Fri', '07:29:52.0000000','R','07:30:00.0000000')
INSERT INTO [StaffDay]
VALUES('30', 'Graham', '2012-04-18', 'Mon', '07:29:52.0000000','R','08:00:00.0000000')
INSERT INTO [StaffDay]
VALUES('35', 'Paul', '2012-04-10', 'Mon', '07:59:20.0000000','S','09:00:00.0000000')
INSERT INTO [StaffDay]
VALUES('35', 'Paul', '2012-04-11', 'Tue', '09:38:45.0000000','S','07:35:00.0000000')
INSERT INTO [StaffDay]
VALUES('35', 'Paul', '2012-04-12', 'Wed', '07:41:32.0000000','M','08:00:00.0000000')
INSERT INTO [StaffDay]
VALUES('35', 'Paul', '2012-04-13', 'Thu', '08:52:27.0000000','M','08:35:00.0000000')
INSERT INTO [StaffDay]
VALUES('35', 'Paul', '2012-04-14', 'Fri', '08:11:18.0000000','B','07:00:00.0000000')
INSERT INTO [StaffDay]
VALUES('35', 'Paul', '2012-04-15', 'Mon', '06:29:37.0000000','B','10:35:00.0000000')
INSERT INTO [StaffDay]
VALUES('35', 'Paul', '2012-04-16', 'Tue', '07:19:02.0000000','B','06:00:00.0000000')
INSERT INTO [StaffDay]
VALUES('35', 'Paul', '2012-04-17', 'Wed', '08:45:46.0000000','R','10:35:00.0000000')
INSERT INTO [StaffDay]
VALUES('35', 'Paul', '2012-04-18', 'Thu', '07:29:52.0000000','R','09:35:00.0000000')
INSERT INTO [StaffDay]
VALUES('35', 'Paul', '2012-04-18', 'Fri', '07:29:52.0000000','R','06:00:00.0000000')
INSERT INTO [StaffDay]
VALUES('35', 'Paul', '2012-04-18', 'Mon', '07:29:52.0000000','R','07:35:00.0000000')
-----------------------------------------------------------
Thanks for your help.
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply