August 10, 2015 at 4:41 am
I have two tables, dutyrostershift and vacation.
And two querys, that both does what they are supposed to:
Query 1:
select dato from dbo.dutyrostershift where shifttype = 2
Query 2:
Select CAST(start-36163 AS datetime) as vacstart,cast(fld_end-36163 as datetime) as vacend, personalid from dbo.vacation
(CAST is done to get correct datetime out of another format for dates)
What i want to find is which dato from query one lies between START and FLD_END in QUERY TWO, where employeeid (From dutyrostershift ) equals [personalid] (From vacation)
I.e., whichs dates from the first query hits a vacation?
Best regards
Edvard Korsbæk
The two tables are:
CREATE TABLE [dbo].[dutyrostershift](
[dato] [datetime] NULL DEFAULT (NULL),
[std] [tinyint] NULL DEFAULT (NULL),
[specialvagt] [tinyint] NULL DEFAULT (NULL),
[daekbemand] [tinyint] NULL DEFAULT (NULL),
[extratimer] [int] NULL DEFAULT (NULL),
[overarbtimer] [int] NULL DEFAULT (NULL),
[manuel] [tinyint] NULL DEFAULT (NULL),
[beskyttet] [tinyint] NULL DEFAULT (NULL),
[confirmed] [tinyint] NULL DEFAULT (NULL),
[vacationtype] [varchar](50) NULL DEFAULT (NULL),
[breakswish] [tinyint] NULL DEFAULT (NULL),
[dutyrosterid] [int] NULL DEFAULT (NULL),
[employeeid] [int] NULL DEFAULT (NULL),
[employeegroupid] [int] NULL DEFAULT (NULL),
[childforcaredayid] [int] NULL DEFAULT (NULL),
[originatingstaffingrequirementid] [int] NULL DEFAULT (NULL),
[shifttype] [int] NULL DEFAULT (NULL),
[fromtime] [int] NULL DEFAULT (NULL),
[totime] [int] NULL DEFAULT (NULL),
[id] [int] IDENTITY(1,1) NOT NULL,
[leavetype_id] [int] NULL DEFAULT (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,
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 DEFAULT (NULL),
[personalid] [int] NULL DEFAULT (NULL),
[start] [int] NULL DEFAULT (NULL),
[fld_end] [int] NULL DEFAULT (NULL),
[cost] [int] NULL DEFAULT (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]
GO
August 10, 2015 at 4:54 am
Something like this?
SELECT
dato
FROM dbo.dutyrostershift d
JOIN dbo.vacation v
ON d.dato BETWEEN CAST(start-36163 AS datetime) AND CAST(fld_end-36163 AS datetime)
AND d.employeeid = v.personalid
WHERE d.shifttype = 2
John
August 10, 2015 at 5:27 am
Thanks for showing my that the ON Clause is much more powerfull than i have seen.
Best regards
Edvard Korsbæk
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply