December 23, 2013 at 9:32 am
Hi All!
I know the subject seems a bit cryptic, but:
I have one table duties, which among many other columns has a dutydate coloumn
I have a tabble 'Vacation' which among many other coloums has two, start_Vacation and End_Vacation
all is datetime
What i want to find is the duty.dutydate which is not between any set of vacation.start_vacation and vacation.End_Vacation.
I have not been able to figure out how to.
Any Christmas Gifts out there?
Best regards
Edvard Korsbæk
December 23, 2013 at 10:12 am
Are you aquatinted with BETWEEN operator available in SQL ?
BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.
Here is a link to some examples and an excellent discussion of how to properly utilize the BETWEEN operator:
December 23, 2013 at 10:14 am
Could you provide some DDL and also some sample data with expected outcome? Please look at the first article in my signature for additional help on posting questions to the forums. Thanks!
December 23, 2013 at 11:02 am
Dear Keith!
I have read your post once again.
Problem with my tables are, that they have a lot of columns.and there are named in danish shorthand.
Don't say much...
So, a sample tabe could be:
CREATE TABLE [dbo].[duties](
[id] [int] NOT NULL,
[dutydate] [datetime] NULL,
[duty] [nchar](10) NULL,
CONSTRAINT [PK_duties] 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]
With theese data:
12012-04-09 00:00:00.000job
22012-04-10 00:00:00.000job 2
NULLNULLNULL
The vacation table:
CREATE TABLE [dbo].[vacation](
[id] [int] NOT NULL,
[vacation_start] [datetime] NULL,
[vacation_end] [datetime] NULL,
CONSTRAINT [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]
With a data set, which could be something like:
12012-04-01 00:00:00.0002012-04-09 00:00:00.000
22012-05-09 00:00:00.0002012-05-12 00:00:00.000
NULLNULLNULL
What i want to know now, is which of the jobs is in one of the two data ranges in table vacation.
The answer is here, that job one is in, and job two is not in (2013-04-10 is not between neither 2012-04-01 and 2012-04-09 or 201205-09 and 2012-05-12.
But what i want is a general solution, telling the which records in duties does not belong to the data ranges in vacations.
In my real tables, the dutydata is a datetime, and the datarange is in clarion dates, but thats my job to solve...
Best regards
Edvard Korsbæk
December 23, 2013 at 11:14 am
Would this work for you?
CREATE TABLE [dbo].[duties](
[id] [int] NOT NULL,
[dutydate] [datetime] NULL,
[duty] [nchar](10) NULL,
CONSTRAINT [PK_duties] 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]
CREATE TABLE [dbo].[vacation](
[id] [int] NOT NULL,
[vacation_start] [datetime] NULL,
[vacation_end] [datetime] NULL,
CONSTRAINT [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]
insert dbo.duties
values (1,'2012-04-09 00:00:00.000','job')
,(2,'2012-04-10 00:00:00.000','job 2')
insert dbo.vacation
values (1,'2012-04-01 00:00:00.000','2012-04-09 00:00:00.000')
,(2,'2012-05-09 00:00:00.000','2012-05-12 00:00:00.000')
select *
from dbo.duties d
cross apply dbo.vacation v
where d.dutydate >= v.vacation_start
and d.dutydate <= v.vacation_end
December 23, 2013 at 11:31 am
I think it's the solution.
I did not know 'Cross Apply', which makes the difference as far as i see.
Thanks
best regards
Edvard Korsbæk
December 23, 2013 at 12:25 pm
I would make 1 change to the proposed solution.
You need to take the time portion into consideration when doing ranges.
So this:
where d.dutydate >= v.vacation_start
and d.dutydate <= v.vacation_end
Becomes:
where d.dutydate >= v.vacation_start
and d.dutydate < dateadd(dd, 1, v.vacation_end)
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 23, 2013 at 1:58 pm
thanks!
That is not the case here, as the time part as 0.
But, I have too learned dateadd, which is good.
Best regards
Edvard Korsbæk
December 23, 2013 at 2:11 pm
Thanks all!
I really learned quite a bit, and can do something without asking next time.
If you are curious, the final script became:
Delete from dbo.dutyrostershift where shifttype = 1 and id not in (
select d.id
from dbo.dutyrostershift d
cross apply dbo.vacation v
where (Convert(int, d.dato) + 36163) >= v.start
and (Convert(int, d.dato) + 36163) <= v.fld_end
AND d.employeeid = v.personalid
and D.shifttype = 1
)
It found 9 out of 210862 records, which was wrongly inserted.
And, even better, i know that all the rest is ok.
Best regards
Edvard Korsbæk
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply