August 29, 2014 at 10:16 am
Hi,
I would like to have a while loop in a CTE but I wonder if that will work out the way I planned it.
I am still learning in T-SQL so apologies if this may sound a bit foolish.
My CTE has nested queries (joining with the previous query) but only the first query (Schedule1) is shown in the query below.
I would like to have a WHILE loop in Schedule1 or in the SELECT-statement at the end.
I'll explain at the end.
A simplified extract of my query:
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
SET @startdate = '2013-12-31'
SET @enddate = '2014-08-31'
;WITH Schedule1 AS
(
SELECTh.res_id,
'John Doe' AS fullname,
wsl.DayStart,
wsl.StartDate,
wsl.ShiftID,
t.datum AS DateField,
(SELECT MAX(WeekDay) FROM WorkSchedules WHERE ShiftID = wsl.ShiftID) AS MaxDaysSchedule
FROMhumres h
LEFT OUTER JOIN workschedulelinks wsl ON h.res_id = wsl.Resource AND wsl.EndDate IS NULL
LEFT OUTER JOIN workschedulegroups wsg ON wsl.GroupCode = wsg.Code AND wsg.ShiftID IS NOT NULL
CROSS APPLY (SELECT * FROM [dbo].[AllDays] (wsl.StartDate, @enddate)) t
WHEREh.res_id IN (79, 1131901, 1894)
GROUP BY
h.res_id,
h.fullname,
wsl.DayStart,
wsl.StartDate,
wsl.ShiftID,
t.datum
)
SELECTs1.*
FROMSchedule1 s1
This query produces this result set (only first 10 rows):
res_idfullnameDayStartStartDateShiftIDDateFieldMaxDaysSchedule
79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-14 00:00:00.000 14
79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-15 00:00:00.000 14
79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-16 00:00:00.000 14
79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-17 00:00:00.000 14
79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-18 00:00:00.000 14
79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-19 00:00:00.000 14
79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-20 00:00:00.000 14
79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-21 00:00:00.000 14
79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-22 00:00:00.000 14
79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-23 00:00:00.000 14
The AllDays function generates all separate rows per datefield.
What I would like to have is the following resultset:
res_idfullnameDayStartStartDateShiftIDDateFieldMaxDaysSchedule Counter
79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-14 00:00:00.000 14 8
79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-15 00:00:00.000 14 9
79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-16 00:00:00.000 14 10
79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-17 00:00:00.000 14 11
79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-18 00:00:00.000 14 12
79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-19 00:00:00.000 14 13
79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-20 00:00:00.000 14 14
79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-21 00:00:00.000 14 1
79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-22 00:00:00.000 14 2
79John Doe82010-06-14 00:00:00.00075A18201-BC97-4E10-8EE6-F25E05ADBEE3 2010-06-23 00:00:00.000 14 3
For John Doe the "8" in column DayStart is the starting value. It should then add up 1 for every next row until the value of 14 (column "MaxDaysSchedule") has been reached. This value should be shown in a new column "Counter". The next row should then start with 1 until 14 have been reached again. Then again starting with 1 until 14 etc. etc. until the last DateField has been reached.
The next resource-id (not shown in example) may have a DayStart of i.e. 3 and a MaxDaySchedule of 7. The first row should then show 3 in the counter column; next row 4 in the counter column; next 5; next 6; next 7; next 1; next 2 etc. etc.
Perhaps all can be solved avoiding a CTE but I would like to keep the CTE if possible because of its speed and readability.
I hope someone is willing to give my a helping hand.
Thanks!
August 29, 2014 at 12:02 pm
This could be accomplished without a loop using the modulo operator.
To get a coded answer, please provide DDL and sample data in a consumable format.
August 29, 2014 at 12:45 pm
I agree with Luis, a while loop is not needed in this query.
😎
August 31, 2014 at 2:05 pm
I have added CREATE and INSERT queries!
Could you explain what you had in mind with the MOD function?
Thx!
August 31, 2014 at 2:31 pm
michielbijnen (8/31/2014)
I have added CREATE and INSERT queries!Could you explain what you had in mind with the MOD function?
Thx!
Good job with the DDL and the sample data, will have a look at it tomorrow.
😎
September 1, 2014 at 4:15 am
Hi ,Please try below if it's match with your desire result..
May be it can be done with more simply way.
I have alterd function alldays to include startdate for join.
ALTER FUNCTION [dbo].[AllDays] (@startdate DATETIME, @enddate DATETIME)
RETURNS @alldays TABLE
(
startdate DATETIME,
datum DATETIME
)
AS
BEGIN
DECLARE @i DATETIME
SELECT @i = @startdate
WHILE @i <= @enddate
BEGIN
INSERT INTO @alldays
(
startdate,
datum
)
VALUES
(
@startdate,
@i
)
SELECT @i = @i + 1
END
RETURN
END
GO
DECLARE @enddate DATETIME
SET @enddate = '2014-08-31'
SELECT H.res_id,
fullname,
WSL.DayStart,
WSL.StartDate,
WSL.ShiftID,
T.datum AS DATEFIELD,
(
SELECT MAX(WeekDay)
FROM WorkSchedules
WHERE ShiftID = WSL.ShiftID
)
AS MAXDAYSSCHEDULE,
CASE
WHEN ABS(DATEDIFF(DAY, WSL.StartDate, T.datum)) + WSL.DayStart <=
(
SELECT MAX(WeekDay)
FROM WorkSchedules
WHERE ShiftID = WSL.ShiftID
)
THEN ROW_NUMBER() OVER (PARTITION BY (DATEDIFF(DAY, WSL.StartDate, T.datum) + WSL.DayStart - 1) /
(
SELECT MAX(WeekDay)
FROM WorkSchedules
WHERE ShiftID = WSL.ShiftID
), Res_id ORDER BY DATEDIFF(DAY, WSL.StartDate, T.datum) ,Res_id)+wsl.DayStart-1
ELSE ROW_NUMBER() OVER (PARTITION BY (DATEDIFF(DAY, WSL.StartDate, T.datum) + WSL.DayStart - 1) /
(
SELECT MAX(WeekDay)
FROM WorkSchedules
WHERE ShiftID = WSL.ShiftID
), Res_id ORDER BY DATEDIFF(DAY, WSL.StartDate, T.datum) , Res_id)
END [Counter]
FROM humres H
LEFT JOIN workschedulelinks WSL ON H.res_id = WSL.Resource
AND WSL.EndDate IS NULL
CROSS APPLY [dbo].[AllDays](WSL.StartDate, @enddate) T
WHERE T.startdate = WSL.StartDate
--and h.res_id IN (79, 233, 321) --= 1131901
ORDER BY Res_ID,
DATEFIELD
September 1, 2014 at 9:04 am
I haven't read all of Megha's code. I stopped at the function because that will be extremely unefficient. Check this alternative that uses an inline table valued function that will run a lot faster. It uses an in-memory tally table (at least the principle) to generate all dates. To learn more about tally tables and how they replace loops, check the following article: http://www.sqlservercentral.com/articles/T-SQL/62867/
ALTER FUNCTION [dbo].[AllDays]
(
@startdate DATETIME,
@enddate DATETIME
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
WITH E1(n) AS(
SELECT n FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(n) --10 rows
),
E2(n) AS(
SELECT a.n FROM E1 a, E1 b --10 x 10 = 100 rows
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b --100 x 100 = 10,000 rows (enough for over 27 years)
)
SELECT TOP (DATEDIFF( dd, @startdate, @enddate) + 1)
DATEADD( dd, ROW_NUMBER() OVER( ORDER BY ( SELECT NULL)) - 1, @startdate) datum
FROM E4;
September 1, 2014 at 9:28 am
Here's my version of the code. I just added a column to your select. Be sure to understand how does it work and ask any questions that you have.
The startdate column in the function is not needed at all. It makes no sense to have a constant returned.
;WITH Schedule1 AS
(
SELECTh.res_id,
'John Doe' AS fullname,
wsl.DayStart,
wsl.StartDate,
wsl.ShiftID,
t.datum AS DateField,
(SELECT MAX(WeekDay) FROM WorkSchedules WHERE ShiftID = wsl.ShiftID) AS MaxDaysSchedule
FROMhumres h
LEFT OUTER JOIN workschedulelinks wsl ON h.res_id = wsl.Resource AND wsl.EndDate IS NULL
LEFT OUTER JOIN workschedulegroups wsg ON wsl.GroupCode = wsg.Code AND wsg.ShiftID IS NOT NULL
CROSS APPLY (SELECT * FROM [dbo].[AllDays] (wsl.StartDate, @enddate)) t
WHEREh.res_id IN (79, 1131901, 1894)
GROUP BY
h.res_id,
h.fullname,
wsl.DayStart,
wsl.StartDate,
wsl.ShiftID,
t.datum
)
SELECTs1.res_id,
s1.fullname,
s1.DayStart,
s1.StartDate,
s1.ShiftID,
s1.DateField,
s1.MaxDaysSchedule,
ISNULL( NULLIF((DATEDIFF( DD, s1.StartDate, s1.DateField) + DayStart) % s1.MaxDaysSchedule, 0), s1.MaxDaysSchedule)
FROMSchedule1 s1
September 1, 2014 at 1:30 pm
.... What can I say? I have read it 10 times now and it sounds like THE MASTER is severely correcting one of his students.
Thanks for your reply anyhow! I try to learn from it.
September 1, 2014 at 1:32 pm
Thanks a lot Luis!
I understand your code and it works perfectly!
September 1, 2014 at 1:50 pm
You're welcome.
I hope that you've learned something from this. The main lesson would be that you don't need to count yourself because SQL Server will do it for you. The main difference between SQL and procedural languages is that it's designed to work on sets and you should be doing set-based programming instead of procedural-programming. It's not easy to change your mind, the first step is to think on what you want to do with a column and not with a row (sounds simple but it might be simple for everyone).
Some statements from Celko are correct such as promoting set-based code, but some others are just plain BS such as not using variables or cross apply or adding a new column.
September 1, 2014 at 5:15 pm
michielbijnen (9/1/2014)
.... What can I say? I have read it 10 times now and it sounds like THE MASTER is severely correcting one of his students.Thanks for your reply anyhow! I try to learn from it.
I suggest you pay no attention to Joe C in these forums - he is completely incapable of dealing in a civilised manner with learners, and is so busy telling them they are wrong (by his standards, which are arbitrary and sometimes unreal) that he forgets to tell them anything useful. In direct conversation he is much more sensible, and his books are not bad, but in forums....
Look instead at what Luis posted, put the code in his two posts together and see if it does what you need. If not, tell us how the result differs from what you want.
Tom
October 16, 2014 at 2:31 pm
Just to note, you dont really need the GROUP BY clause in this example. The "SELECT MAX(WeekDay)" part is not really an aggregation on the previous columns listed. But it does not affect your results either.
----------------------------------------------------
October 18, 2014 at 11:02 am
CELKO (10/16/2014)
.. BS such as not using variables or cross apply or adding a new column.
Avoiding variables is something in any books on functional programming today. Have you ever has a LISP or APL class? Probably not; so look at F# in the Microsoft world. One of their demos is math problem that they write as a procedural program in F#, improve it a bit then write it as a single recursive statement. The final version is several orders of magnitude faster and smaller. Start with FUNCTIONAL PROGRAMMING THROUGH LAMBDA CALCULUS by Greg Michaelson (978-0-486-47883-8) and get a feel for the mindset.
Remember what a pain it was to learn Structured Programming (SP) in the 1970's? No more GOTO's and labels, no more jumping in and out of loops. There was lots of bitching about this new bulshit. Your first efforts with the new tools of software engineering were clumsy and a mix of SP and the old mindset. SQL programmers are in the same situation today. Local variables are a big part of the old mindset you need to get rid of.
CROSS APPLY is dialect; you need a damn good reason for heavy dialect in your code. This is a basic principle of Software Engineering.
If a new column is an attribute that was left out of the data model, then add it, of course. But if it is computed or redundant in some way, then use a virtual column. A modern CPU can do the math faster than it can read a disk.
Damn it, Joe, why don't you always adopt that as your style in these forums? I'm sure that it would actually get your points over much mre effectively than your usual intemperate diatribes. The advice you've given in the quoted message is excellent, is phrased in a way to encourage people to follow it instead of treating it as an insult and therefor ignoring it, and doesn't include anything that is just a personal point of view. The only thing I might disagree with is the choice of book to recommend. Obviously I couldn't recommend the old Darlington, Henderson, and Turner (eds) book although I liked it 30+ years ago and Know all three editors because it may be too difficult for beginners and anyway it's out of print, but I've heard much good of Neal Ford's "Thinking Functionally" and although I haven't looked at it myself what I hear of it suggests that it might be easier for modern SQL people - who may have learnt awful languages like C++ or VB and have probably learnt all about cursors and control flow and variables in whatever dialect of SQL they have used - than Greg's book.
Tom
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply