Viewing 15 posts - 1 through 15 (of 19 total)
this gives a closer match as to what the end result should be, but still with the minus figure.
i could just remove the minus symbol.
DECLARE @startdate DATETIME;
DECLARE...
September 27, 2021 at 3:39 pm
yes it is inserting into the table correctly
also, where there are mulitple gaps and overlaps, the result is this (when using you case statement)
persref gap ...
September 27, 2021 at 3:36 pm
well no it doesn't produce the correct result.
-10 is not the correct result.
September 27, 2021 at 3:30 pm
i don't want it to show any rows.
the result i want is this
persref Gaps Overlaps
gipaq 10 10
you...
September 27, 2021 at 3:18 pm
that doesn't seem to give the total minutes of gaps and/or overlaps though?
It just shows the 2 rows of data that may have an issue.
sql2000 does support TOP, but in...
September 27, 2021 at 2:36 pm
no, i was expaining what the end result needs to be.
you said i should provide consumable data, so that's all i have done
September 27, 2021 at 2:14 pm
I can't see how your SQL will work with all those right brackets?
looks like the copy/paste into this forum has screwed something up, the script does work though
September 27, 2021 at 1:52 pm
Have you thought about upgrading your server?
not an option i'm afraid.
this is what i have to work with.
i have done what you suggest though and used a consumable data
the...
September 27, 2021 at 1:34 pm
A negative gap is an overlap and a positive gap is a gap.
also, i need those to be 2 distinct seperate values.
September 27, 2021 at 1:10 pm
i'm not sure either of those will work with SQL 2000?
i know LAG isn't supported. and i don't believe PARTITION is supported in SQL 2000 either?
September 27, 2021 at 1:08 pm
a working solution has been found thanks to @aaron-n-cutshall
has been tested and works.
declare @month int;
declare @year int;
select @month = 9, @year = 2019;
select d.Diary_Engineer_ID, sum(1-c.isWeekend)...
October 9, 2019 at 9:43 am
yes time is not an issue as we are only counting full days, so the two time fields can be ignored, only the date start and date end are the...
October 8, 2019 at 3:19 pm
guessing that uses another term that SQL2000 doesn't like, as it doesn't seem to like the word "PERSISTED"
but i'll just drop the table and re-create it with the extra column
October 8, 2019 at 3:01 pm
many thanks!
just for reference - this is thread explaining what i am trying to achieve
October 8, 2019 at 2:52 pm
yeah i know it's obsolete, but it's what i have to work with.
a legacy application uses it and there's no requirement to upgrade. the work involved would greatly outweigh the...
October 8, 2019 at 2:04 pm
Viewing 15 posts - 1 through 15 (of 19 total)