April 17, 2017 at 11:46 pm
Hi!
Shortly descipted - the tables are at the end of this post - I have two tables:
Dutyrostershift, which is duties on a day for an employee
Vacation, which is vacation periods for an emplyee
All dutyrostershift, which is in a vacation period should have the shifttype 1 (vacation) instead of shifttype 0 (Work)
This script counts the post which is ok, i.e. theese dutyrostershift has a vacation record
SELECT COUNT(*) FROM dbo.dutyrostershift A
JOIN dbo.vacation B ON B.personalid = A.employeeid AND A.dato BETWEEN DATEADD(day, start , '18001228') AND DATEADD(day, fld_end , '18001228')
WHERE shifttype = 1
But what i want is dutyrostershift records, which is vacation (shifttype 1) which is not in a vacation post.
How to?
The DATEADD(day, fld_end , '18001228') comes from, that the vacation table uses CLARION dates, which is based upon days since 18001228, and dutyrostershift uses datetime.
Best regards
Edvard Korsbæk
Table definations with comments on the fields, whic is interesting in this context
CREATE TABLE [dbo].[dutyrostershift](
[dato] [datetime] NULL, -- date for duty
[std] [tinyint] NULL,
[specialvagt] [tinyint] NULL,
[daekbemand] [tinyint] NULL,
[extratimer] [int] NULL,
[overarbtimer] [int] NULL,
[manuel] [tinyint] NULL,
[beskyttet] [tinyint] NULL,
[confirmed] [tinyint] NULL,
[vacationtype] [varchar](50) NULL,
[breakswish] [tinyint] NULL,
[dutyrosterid] [int] NULL,
[employeeid] [int] NULL, -- employee id
[employeegroupid] [int] NULL,
[childforcaredayid] [int] NULL,
[originatingstaffingrequirementid] [int] NULL,
[shifttype] [int] NULL, -- type of duty in range from 0 to 54, 0 is work, 1 is vacation
[fromtime] [int] NULL,
[totime] [int] NULL,
[id] [int] IDENTITY(1,1) NOT NULL,
[leavetype_id] [int] NULL,
[LoginID] [int] NULL,
[StatusNo] [int] NULL,
[Time_Stamp] [datetime] NULL,
[Comment] [char](120) NULL,
[Is_Free_sat] [tinyint] NULL,
[Is_Center_Opening] [tinyint] NULL,
[is_fo_day] [tinyint] NULL,
[SavedDuty_Id] [int] NULL,
[mTid_Id] [int] NULL,
[duty_released] [tinyint] NULL,
[recordstatus] [tinyint] NULL,
[FROM_FLEXJOB] [tinyint] NULL,
[Work_minutes_travel_days] [int] NULL,
[from_work] [int] NULL,
[to_work] [int] NULL,
[originating_Shift_Type] [int] NULL,
[vacation_id] [int] NULL,
[EmploymentID] [int] NULL,
CONSTRAINT [pk_dbo_pk_dutyrostershift] PRIMARY KEY CLUSTERED
(
[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]
and
CREATE TABLE [dbo].[vacation](
[typecode] [TINYINT] NULL,
[personalid] [INT] NULL, -- employee
[start] [INT] NULL, -- startdate in Clarion format
[fld_end] [INT] NULL, -- enddate in clarion format
[cost] [INT] NULL,
[id] [INT] IDENTITY(1,1) NOT NULL,
[Wish_Vacation] [TINYINT] NULL,
[vacation_model] [TINYINT] NULL,
CONSTRAINT [pk_dbo_pk_vacation] PRIMARY KEY CLUSTERED
(
[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]
April 18, 2017 at 2:48 am
Edvard
Not easy to visualise without sample data, but do you just need to do a LEFT JOIN and add B.personalid IS NULL to your WHERE clause?
John
April 18, 2017 at 3:36 am
John Mitchell-245523 - Tuesday, April 18, 2017 2:47 AMEdvardNot easy to visualise without sample data, but do you just need to do a LEFT JOIN and add B.personalid IS NULL to your WHERE clause?
John
That was it - Thanks!
And I understand, that its difficult to visualise, so a very clever answer!
Best regards
Edvard Korsbæk
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply