June 19, 2006 at 12:18 am
Hallo Experts,
i need your help. I have two tables.
The first is one column with Dates they will marked Days with Honor 100% of the earn Money when you work. Like 25.05.06 in Germany ( thats an Vacation Day). The second table looks like this:
CREATE TABLE [LoginLogout] (
[Site] [varchar] (55) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LoginID] [int] NOT NULL CONSTRAINT [DF__LoginLogo__Login__269AB60B] DEFAULT (null),
[NST] [decimal](10, 0) NOT NULL CONSTRAINT [DF__LoginLogout__NST__278EDA44] DEFAULT (null),
[LoginZeit] [datetime] NOT NULL ,
[LogoutZeit] [datetime] NOT NULL ,
[LogoutDate] [datetime] NOT NULL ,
[Skill1] [decimal](5, 0) NULL CONSTRAINT [DF__LoginLogo__Skill__2882FE7D] DEFAULT (null),
[Skill2] [decimal](5, 0) NULL CONSTRAINT [DF__LoginLogo__Skill__297722B6] DEFAULT (null),
[Skill3] [decimal](5, 0) NULL CONSTRAINT [DF__LoginLogo__Skill__2A6B46EF] DEFAULT (null),
[DiffInOut] [int] NULL CONSTRAINT [DF__LoginLogo__DiffI__2B5F6B28] DEFAULT (null),
[Logindate] [datetime] NULL ,
CONSTRAINT [PK_LoginLogout] PRIMARY KEY NONCLUSTERED
(
[Site],
[LoginID],
[NST],
[LoginZeit],
[LogoutZeit],
[LogoutDate]
) ON [PRIMARY]
) ON [PRIMARY]
GO
In this Table are timestamps from the Agents.
For an every Day Shift 22:00 till 06:00( without Sunday and Vacationday)
there are an Bonus of 25%.
For Sunday without Vacation there are 50% bonus and finaly
on Vacationdays 100% Bonus.
I need the Sum of Diffinout group by month. Thats Easy 🙂
For the Time 22:00:00 till 6:00:00 in the Morning.
For Sunday Workday the hole Day and for the Table Vacation also the hole Day.
If the Agent work from Saturday evennig 21:00 till sunday 7:00
I must summary Saturday from 22:00 till 24:00 and the time on sunday 00:00 till 6:00 in the other summary.
I hope i can write it for understand.
Thanks
Regards Thomas
June 19, 2006 at 1:45 am
Can you provide some sample data here, da nicht alle gutes Deutsches sprechen.
Zeit is "time"
N 56°04'39.16"
E 12°55'05.25"
June 19, 2006 at 1:58 am
Sorry for my Englisch in German is Better 🙂
Here are the sample datas.
Site,LoginID,NST,LoginZeit,LogoutZeit,LogoutDate,Skill1,Skill2,Skill3,DiffInOut,Logindate
Siteone,50001,74587,1899-12-30 20:01:09.000,1899-12-30 21:59:15.000,2006-05-24 00:00:00.000,100,,,7086,2006-05-24 00:00:00.000
Siteone,50001,74628,1899-12-30 09:29:08.000,1899-12-30 11:33:03.000,2006-05-24 00:00:00.000,100,,,7435,2006-05-24 00:00:00.000
Siteone,50002,74558,1899-12-30 13:59:18.000,1899-12-30 17:31:59.000,2006-05-24 00:00:00.000,100,,,12761,2006-05-24 00:00:00.000
Siteone,50006,74534,1899-12-30 22:50:31.000,1899-12-30 02:31:34.000,2006-05-25 00:00:00.000,100,,,13263,2006-05-24 00:00:00.000
Siteone,50007,74511,1899-12-30 13:57:32.000,1899-12-30 18:15:32.000,2006-05-24 00:00:00.000,100,,,15480,2006-05-24 00:00:00.000
Siteone,50010,74596,1899-12-30 13:59:32.000,1899-12-30 17:14:57.000,2006-05-24 00:00:00.000,100,,,11725,2006-05-24 00:00:00.000
Siteone,50011,74538,1899-12-30 14:02:00.000,1899-12-30 17:24:39.000,2006-05-24 00:00:00.000,100,,,12159,2006-05-24 00:00:00.000
Siteone,50011,74538,1899-12-30 18:59:45.000,1899-12-30 22:14:52.000,2006-05-24 00:00:00.000,100,,,11707,2006-05-24 00:00:00.000
Siteone,50014,74518,1899-12-30 13:59:40.000,1899-12-30 16:15:23.000,2006-05-24 00:00:00.000,100,,,8143,2006-05-24 00:00:00.000
Siteone,50014,74518,1899-12-30 16:59:19.000,1899-12-30 21:10:12.000,2006-05-24 00:00:00.000,100,,,15053,2006-05-24 00:00:00.000
Siteone,50016,74680,1899-12-30 18:44:19.000,1899-12-30 00:00:50.000,2006-05-25 00:00:00.000,100,,,18991,2006-05-24 00:00:00.000
Siteone,50018,74539,1899-12-30 18:59:30.000,1899-12-30 22:14:38.000,2006-05-24 00:00:00.000,100,,,11708,2006-05-24 00:00:00.000
Siteone,50019,74585,1899-12-30 08:41:12.000,1899-12-30 13:00:41.000,2006-05-24 00:00:00.000,100,,,15569,2006-05-24 00:00:00.000
Siteone,50020,74519,1899-12-30 07:58:57.000,1899-12-30 15:30:05.000,2006-05-24 00:00:00.000,100,,,27068,2006-05-24 00:00:00.000
Siteone,50025,74628,1899-12-30 13:00:37.000,1899-12-30 16:16:18.000,2006-05-24 00:00:00.000,100,,,11741,2006-05-24 00:00:00.000
Siteone,50030,74632,1899-12-30 13:32:24.000,1899-12-30 20:30:00.000,2006-05-24 00:00:00.000,100,,,25056,2006-05-24 00:00:00.000
Siteone,50033,74545,1899-12-30 00:59:47.000,1899-12-30 01:09:00.000,2006-05-24 00:00:00.000,100,,,553,2006-05-24 00:00:00.000
Siteone,50033,74690,1899-12-30 19:59:41.000,1899-12-30 00:15:51.000,2006-05-25 00:00:00.000,100,,,15370,2006-05-24 00:00:00.000
Siteone,50034,74599,1899-12-30 20:00:11.000,1899-12-30 01:15:11.000,2006-05-25 00:00:00.000,100,,,18900,2006-05-24 00:00:00.000
Siteone,50037,74618,1899-12-30 14:01:01.000,1899-12-30 20:30:04.000,2006-05-24 00:00:00.000,100,,,23343,2006-05-24 00:00:00.000
Siteone,50041,74681,1899-12-30 10:01:02.000,1899-12-30 16:30:04.000,2006-05-24 00:00:00.000,100,,,23342,2006-05-24 00:00:00.000
Siteone,50045,74597,1899-12-30 19:59:20.000,1899-12-30 01:13:21.000,2006-05-25 00:00:00.000,100,,,18841,2006-05-24 00:00:00.000
Siteone,50046,74561,1899-12-30 13:56:27.000,1899-12-30 20:30:08.000,2006-05-24 00:00:00.000,100,,,23621,2006-05-24 00:00:00.000
Siteone,50048,74687,1899-12-30 17:54:12.000,1899-12-30 22:16:54.000,2006-05-24 00:00:00.000,100,,,15762,2006-05-24 00:00:00.000
Siteone,50053,74520,1899-12-30 14:00:41.000,1899-12-30 17:31:20.000,2006-05-24 00:00:00.000,100,,,12639,2006-05-24 00:00:00.000
I hope you can insert this.
greetings Thomas
June 19, 2006 at 2:07 am
Here is just the sample data modified to be inserted into a table variable for further development by everyone.
See Last Post!
N 56°04'39.16"
E 12°55'05.25"
June 19, 2006 at 2:12 am
Danke schön !! Wie bekommst du das so hin ??
Gibt es da einen Tip, damit ich es alleine schaffe ??
Gruß
Thomas
June 19, 2006 at 2:20 am
N 56°04'39.16"
E 12°55'05.25"
June 19, 2006 at 2:33 am
The first problem I see is that you must look for both Honors table and LoginLogout table for which kind of day to use.
You should consider having ALL rules in one table, such as an
Everyday table
Date PaymentTotal InTime OutTime
---------- ------------ ------ -------
...
2006-05-24 125% 00:00 05:59 <- Mittwoch, Wednesday
2006-05-24 100% 06:00 21:59 <- Mittwoch, Wednesday
2006-05-24 125% 22:00 23:59 <- Mittwoch, Wednesday
2006-05-25 200% 00:00 23:59 <- Donnerstag, Thursday HOLIDAY day!
2006-05-26 125% 00:00 05:59 <- Freitag, Friday
2006-05-26 100% 06:00 21:59 <- Freitag, Friday
2006-05-26 125% 22:00 23:59 <- Freitag, Friday
2006-05-27 125% 00:00 05:59 <- Samstag, Saturday
2006-05-27 100% 06:00 21:59 <- Samstag, Saturday
2006-05-27 125% 22:00 23:59 <- Samstag, Saturday
2006-05-28 150% 00:00 23:59 <- Sonntag , Sunday
2006-05-29 125% 00:00 05:59 <- Montag, Monday
2006-05-29 100% 06:00 21:59 <- Montag, Monday
2006-05-29 125% 22:00 23:59 <- Montag, Monday
...
N 56°04'39.16"
E 12°55'05.25"
June 19, 2006 at 2:40 am
Hey it looks like Great, but not so much work.
I need only the sum of the Time. Not the 25% or else i write this only fot understand. Here is the Statement i buil for count the shifts. I hope this help a little?
-- between 22 Uhr and 6 Uhr Loginzeit no Sunday and no Vacationday --
select datepart(Year,Logindate)Jahr,
datepart(month,Logindate)Monat,
count(1)Anzahl
from loginlogout lg
where skill1 = 100
and skill2 is null
and lg.logindate not in (select fh.feiertage from fhessen fh)
and datepart(dw,logindate) 1
and site ='site one'
and lg.loginzeit not between '1899-12-30 06:00:00.000' and '1899-12-30 22:00:00.000'
and lg.loginzeit not between '1900-01-01 06:00:00.000' and '1900-01-01 22:00:00.000'
group by datepart(month,Logindate),datepart(Year,Logindate)
order by datepart(Year,Logindate),datepart(month,Logindate)
-- Loginzeit Sunday and no vacationday--
select datepart(Year,Logindate) Jahr,datepart(month,Logindate)Monat,count(1)Anzahl from loginlogout lg
where skill1 = 100
and skill2 is null
and datepart(dw,logindate)= 1
and lg.logindate not in (select fh.feiertage from fhessen fh)
and site ='site one'
and diffinout >= 1700
group by datepart(month,Logindate),datepart(Year,Logindate)
order by datepart(Year,Logindate),datepart(month,Logindate)
--Vacationdays--
select datepart(Year,Logindate) Jahr,datepart(month,Logindate)Monat,count(1)Anzahl from loginlogout lg
where skill1 = 100
and skill2 is null
and lg.logindate in (select fh.feiertage from fhessen fh)
and site =site one'
and diffinout >= 1700
group by datepart(month,Logindate),datepart(Year,Logindate)
order by datepart(Year,Logindate),datepart(month,Logindate)
This Part and diffinout >= 1700 i dont Need.
Thanks Thomas
June 19, 2006 at 2:59 am
Something like this?
select *,
case
when logindate = logoutdate then datediff(ss, loginzeit, logoutzeit)
else datediff(ss, 2 + logindate + loginzeit, logoutdate)
end 'From Date Work Seconds',
case
when logindate = logoutdate then 0
else datediff(ss, -2, logoutzeit)
end 'To Date Work Seconds'
from @LoginLogout
N 56°04'39.16"
E 12°55'05.25"
June 19, 2006 at 3:02 am
If you use an "everyday" table, you will not have to worry about a massive checks for "special days" as vacation and week days.
Extend "Everyday" table with two columns. One with "Weekday" (tinyint) field and one "Vaction" (bit) field.
And all calculations are much easier do to!
N 56°04'39.16"
E 12°55'05.25"
June 19, 2006 at 3:31 am
How do you count a work-day when logging in 21:30 saturday and logging out 06:30 sunday, where saturday is the last day of september 2006 and sunday is first day of october 2006?
Wie zählen Sie einen Arbeiten, bei der Protokollierung in 21:30 Samstag und der Protokollierung aus 06:30 Sonntag, wo Samstag der letzte Tag von September 2006 ist und Sonntag erster Tag von Oktober 2006 ist?
N 56°04'39.16"
E 12°55'05.25"
June 19, 2006 at 4:06 am
Thanks for you answere. Sorry but i don't understand what this Statement do.
case
when logindate = logoutdate then datediff(ss, loginzeit, logoutzeit)
else datediff(ss, 2 + logindate + loginzeit, logoutdate)
end 'From Date Work Seconds',
case
when logindate = logoutdate then 0
else datediff(ss, -2, logoutzeit)
end 'To Date Work Seconds'
the first case ask for :
when logindate = logoutdate
and the second case too. ??
For your last Question :
I group by logoutdate this is ok for the analyse.
You think if i had two collumns more in the table Loginlogout its easiery to work. So i can changed this, its no Problem.
Thomas
June 19, 2006 at 6:11 am
Here is a solution that works for your environment.
This is not an environment I recommend.
See Last Post!
N 56°04'39.16"
E 12°55'05.25"
June 19, 2006 at 6:23 am
2 + logindate + loginzeit
is for that you have altered the default date for smalldatetime/datetime values from 1 jan 1900 to 30 dec, 1899. This is two days earlier.
I suppose this was an application built in MS Access earlier?
N 56°04'39.16"
E 12°55'05.25"
June 19, 2006 at 6:28 am
Thanks for all. It Looks like Great !!!
I test it as soon i can. In next two hours i have an Confcall. But it stoped i go for looking.
Thanks and best regards
Thomas
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply