November 10, 2018 at 1:48 pm
Hi Guys,
Can anybody help?
I would like to create a stored procedure based on anexisting table, where the stored procedure will automatically create a newtable with additional columns of data in the right format with a loop and arange of if statements.
To try and make my requirements easier to understand I haveadded a Create table, the values to represent the data i start with, the required"stored procedure steps" and an "end table output withdata" of how I am trying to get it to look like.
I have the following table in SQL [CIP],
CREATE TABLE [dbo].[CIP](
[TB_Code][nvarchar](max) NULL,
[TB_Start_production] [datetime] NULL,
[TB_Resource_code] [nvarchar](max) NULL,
[TB_Version][nvarchar](max) NULL,
[TB_Start_changeover] [datetime] NULL,
[tb_Duration_changeover] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
--[tb_Duration_changeover] contains duration of a CIP inminutes
The values to the CIP table are populated by a 3rd partysoftware, below is the update of the values.
INSERT INTO CIP (TB_Code, TB_Start_production,TB_Resource_code, TB_Version, TB_Start_changeover, TB_Duration_changeover)
VALUES ('T129048', '2018-11-08 23:00:00.000', 'P1', 'NG4', '2018-11-09 05:15:00.000','120'),
('T129049', '2018-11-09 01:00:00.000', 'P2', 'NG4', '2018-11-09 07:15:00.000','150'),
('T129050', '2018-11-09 03:00:00.000', 'P3', 'NG4', '2018-11-09 09:15:00.000','120'),
('T129051', '2018-11-09 05:00:00.000', 'P4', 'NG4', '2018-11-09 11:15:00.000','120'),
('T129052', '2018-11-09 11:00:00.000', 'P1', 'NG4', '2018-11-09 17:15:00.000','120'),
('T129053', '2018-11-09 13:00:00.000', 'P2', 'NG4', '2018-11-09 19:15:00.000','150'),
('T129054', '2018-11-09 15:00:00.000', 'P3', 'NG4', '2018-11-09 21:15:00.000','120'),
('T129055', '2018-11-0917:00:00.000', 'P4', 'NG4', '2018-11-09 23:15:00.000', '140');
Requirements:
The stored procedure I want to create should follow thesestep ;
Step 1) create a new table called “CIP_updated_dateTOBE” based on the table [CIP]
Step 2) Sort the data into ascending order by“TB_Resource_code”, “TB_start_production”
Step 3) add a new column “[Index]” incremental column ,starting at 1, adding 1 for each record. (each time the stored proc is executedthe index number must restart back at 1)
Step 4) add a new column “[END_CIP]”, data is populated byadding the value of each row record
“[TB_Start_changeover]”+“[tb_Duration_changeover]” format tobe set as a datetime “YYYY-MM-DD HH:MM”
Step 5) Add a new column “[CIP_LatestEndtime] Datetime
Step 6) Add a new column”[MoreThan8hours]” Y/N
Step 7) Populating [Morethan8hours], loop/if statement
If [Index] 1 [TB_Resource_Code] = [Index] 2 [TB_Resource_code] and [Index] 1 [CIP_END] – [Index] 2[TB_Start_production] < 8 hours then Morethan8hours = N
Else if
[Index] 1 [TB_Resource_Code] = [Index] 2 [TB_Resource_code]and [Index] 1 [CIP_END] – [Index] 2 [TB_Start_production] > 8 hours thenMorethan8hours = Y
Else if
[Index] 1 [ TB_Resource_code] is not = [Index] 2[tb_Resource_code] then Morethan8hours =y
Step 8) Populating the [CIP_LatestEndTime] values
If {Index] row 1 [Morethan8hours] = N, Then[Index] 1[CIP_LatestEndTime] = [CIP_END] + 480 minutes yyyy-mm-dd hh:mm
Keep looping through all index numbers until the last indexrecord.
End
If you could narrate the stored procedure then this would begreat, as I can learn how the procedure works, JFI, I assumed that i would needto add the Index number as i wasn’t sure if I could use the row number in thetable after the sort, if you could let me know if this is correct or not thatwould be great!
The finished result should look like:
I would like to create a table that looks similar to thisone below:
CREATE TABLE [dbo].[CIP_updated_dateTOBE](
[Index]INT NULL,
[TB_Code][nvarchar](max) NULL,
[TB_Start_production][datetime] NULL,
[TB_Resource_Code][nvarchar](max) NULL,
[TB_Version][nvarchar](max) NULL,
[TB_Start_Changeover][datetime] NULL,
[tb_duration_changeover][int] NULL,
[CIP_End][datetime] NULL,
[CIP_LatestEnd][datetime] NULL,
[MoreThan8hours][nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT INTO CIP_updated_dateTOBE ([Index], [TB_Code],[TB_Start_production], [TB_Resource_Code], [TB_Version], [TB_Start_Changeover],[tB_Duration_changeover],[CIP_End],[CIP_LatestEnd],[MoreThan8hours])
VALUES
('1', 'T129048', '2018-11-08 23:00:00.000', 'P1', 'NG4','2018-11-09 05:15:00.000', '120', '2018-11-09 07:15:00.000', '2018-11-0911:00:00.000', 'no'),
('2', 'T129052', '2018-11-09 11:00:00.000', 'P1', 'NG4','2018-11-09 17:15:00.000', '120', '2018-11-09 19:15:00.000', '2018-11-0919:15:00.000', 'yes'),
('3', 'T129049', '2018-11-09 01:00:00.000', 'P2', 'NG4','2018-11-09 07:15:00.000', '120', '2018-11-09 09:15:00.000', '2018-11-0913:00:00.000', 'no'),
('4', 'T129053', '2018-11-09 13:00:00.000', 'P2', 'NG4','2018-11-09 19:15:00.000', '120', '2018-11-09 21:15:00.000', '2018-11-1005:15:00.000', 'yes'),
('5', 'T129050', '2018-11-09 03:00:00.000', 'P3', 'NG4','2018-11-09 09:15:00.000', '120', '2018-11-09 11:15:00.000', '2018-11-0915:00:00.000', 'no'),
('6', 'T129054', '2018-11-09 15:00:00.000', 'P3', 'NG4','2018-11-09 21:15:00.000', '120', '2018-11-09 23:15:00.000', '2018-11-1007:15:00.000', 'yes'),
('7', 'T124958', '2018-11-10 10:00:00.000', 'P3', 'NG9','2018-11-10 16:00:00.000', '120', '2018-11-10 18:00:00.000', '2018-11-1102:00:00.000', 'yes'),
('8', 'T129051', '2018-11-09 05:00:00.000', 'P4', 'NG4','2018-11-09 11:15:00.000', '120', '2018-11-09 13:15:00.000', '2018-11-0917:00:00.000', 'No'),
('9', 'T129055', '2018-11-09 17:00:00.000', 'P4', 'NG4','2018-11-09 23:15:00.000', '120', '2018-11-10 01:15:00.000', '2018-11-1009:15:00.000', 'Yes'),
('10', 'T124960', '2018-11-10 14:00:00.000', 'P4', 'NG9','2018-11-10 20:00:00.000', '120', '2018-11-10 22:00:00.000', '2018-11-1106:00:00.000', 'yes');
Many thanks in advance,
Roy
November 10, 2018 at 6:22 pm
How far did you get writing this? Pretty good bet that nobody is going to write this for you.
And what's the deal with all the NVARCHAR(MAX) columns? What are you trying to put into that column, a video file?
November 11, 2018 at 7:46 am
it seems you want your homework done...
could you please share what you have done so far, and what issues have you encounter?
MCSE, MCSA SQL Server Database Developer/Administrator
November 11, 2018 at 1:08 pm
Ok, i am very new to SQL, I am at present only able to run thecode, by each step,
--Step 1
DROP TABLE [dbo].[CIP_updated_dateTOBE1]
--Step 2
CREATE TABLE [dbo].[CIP_updated_dateTOBE1](
[TB_Code] [nvarchar](max) NULL,
[TB_Start_production] [datetime] NULL,
[TB_Resource_Code] [nvarchar](max) NULL,
[TB_Version] [nvarchar](max) NULL,
[TB_Start_Changeover] [datetime] NULL,
[tb_duration_changeover] [INT])
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
--step 3 - Copy data from CIP into new table
INSERT INTO [dbo].[CIP_updated_dateTOBE1]
SELECT * FROM dbo.CIP
ALTER TABLE [dbo].[CIP_updated_dateTOBE1]
ADD [id] INT NULL, [END_CIP] [datetime] NULL, [CIP_LatestEndtime][datetime] NULL, [MoreThan8hours] INT;
--Populate the END_CIP based on the tb_duration_changeover +SB_Start_Changeover
Select TB_Resource_Code,TB_Start_productionTB_Start_Changeover,tb_duration_changeover,CIP_LatestEndtime, CONVERT(char(5), DATEADD(MINUTE, tb_duration_changeover, ''), 114) ASEND_CIP
From [dbo].[CIP_updated_dateTOBE1];
--step 4 Add the END_CIP time to the TB_Start_changeover works replacing thevalue with a correct data time format
UPDATE [dbo].[CIP_updated_dateTOBE1]
SET END_CIP = CONVERT(char(5), DATEADD(MINUTE, tb_duration_changeover,''), 114) + TB_Start_Changeover
Select * from [dbo].[CIP_updated_dateTOBE1];
-- Add the ID number based on the sort order
WITH a AS(
SELECT ROW_NUMBER() OVER(ORDER BY TB_Resource_code,TB_Start_production) as rn,id
FROM [dbo].[CIP_updated_dateTOBE1]
)
UPDATE a SET id=rn
OPTION (MAXDOP 1)
Select * from [dbo].[CIP_updated_dateTOBE1] order by ID
I am guessing that i have over complicated the code, but this is my firstshot at it, I figured once i have the code completed i would copy it to aSP and run it each time the CIP table is populated.
So... I am now at the stage where i want to create a combined if statement =true do something else do something else!
I was thinking that i could use the id column record 1 as a startingpoint, check if the record id1, TB_Resource_code = ID 2 Resource code andif END_CIP - TB_Start_production < 8 hours, then update ID 1CIP_LatestEndtime with the contents of ID2 TB_Start_production,else CIP_LatestEndTime+08:00.
Then loop to the next record, ie ID2 checking against ID3 record
(I added the column Morethan8hours,to check that the correct data has beenadded correctly)
I have so far only got:
DECLARE @id INT;
Declare @IDMax INT =(Select max(ID) from [dbo].[CIP_updated_dateTOBE1];
While (@ID <= @IDMax) BEGIN
Now i am unsure of how write ID code 1 record column data checkagainst record 2 column data else!!
any pointers at this stage would be a help, am i on the right track or is therea easier way to write this?
Cheers
November 11, 2018 at 1:12 pm
Ok, i am very new to SQL, I am at present only able to run thecode, by each step,
--Step 1
DROP TABLE [dbo].[CIP_updated_dateTOBE1]
--Step 2
CREATE TABLE [dbo].[CIP_updated_dateTOBE1](
[TB_Code] [nvarchar](max) NULL,
[TB_Start_production] [datetime] NULL,
[TB_Resource_Code] [nvarchar](max) NULL,
[TB_Version] [nvarchar](max) NULL,
[TB_Start_Changeover] [datetime] NULL,
[tb_duration_changeover] [INT])
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
--step 3 - Copy data from CIP into new table
INSERT INTO [dbo].[CIP_updated_dateTOBE1]
SELECT * FROM dbo.CIP
ALTER TABLE [dbo].[CIP_updated_dateTOBE1]
ADD [id] INT NULL, [END_CIP] [datetime] NULL, [CIP_LatestEndtime][datetime] NULL, [MoreThan8hours] INT;
--Populate the END_CIP based on the tb_duration_changeover +SB_Start_Changeover
Select TB_Resource_Code,TB_Start_productionTB_Start_Changeover,tb_duration_changeover,CIP_LatestEndtime, CONVERT(char(5), DATEADD(MINUTE, tb_duration_changeover, ''), 114) ASEND_CIP
From [dbo].[CIP_updated_dateTOBE1];
--step 4 Add the END_CIP time to the TB_Start_changeover works replacing thevalue with a correct data time format
UPDATE [dbo].[CIP_updated_dateTOBE1]
SET END_CIP = CONVERT(char(5), DATEADD(MINUTE, tb_duration_changeover,''), 114) + TB_Start_Changeover
Select * from [dbo].[CIP_updated_dateTOBE1];
-- Add the ID number based on the sort order
WITH a AS(
SELECT ROW_NUMBER() OVER(ORDER BY TB_Resource_code,TB_Start_production) as rn,id
FROM [dbo].[CIP_updated_dateTOBE1]
)
UPDATE a SET id=rn
OPTION (MAXDOP 1)
Select * from [dbo].[CIP_updated_dateTOBE1] order by ID
I am guessing that i have over complicated the code, but this is my firstshot at it, I figured once i have the code completed i would copy it to aSP and run it each time the CIP table is populated.
So... I am now at the stage where i want to create a combined if statement =true do something else do something else!
I was thinking that i could use the id column record 1 as a startingpoint, check if the record id1, TB_Resource_code = ID 2 Resource code andif END_CIP - TB_Start_production < 8 hours, then update ID 1CIP_LatestEndtime with the contents of ID2 TB_Start_production,else CIP_LatestEndTime+08:00.
Then loop to the next record, ie ID2 checking against ID3 record
(I added the column Morethan8hours,to check that the correct data has beenadded correctly)
I have so far only got:
DECLARE @id INT;
Declare @IDMax INT =(Select max(ID) from [dbo].[CIP_updated_dateTOBE1];
While (@ID <= @IDMax) BEGIN
Now i am unsure of how write ID code 1 record column data checkagainst record 2 column data else!!
any pointers at this stage would be a help, am i on the right track or is therea easier way to write this?
Cheers
November 11, 2018 at 2:01 pm
I would like to create a stored procedure based on anexisting table, where the stored procedure will automatically create a newtable with additional columns of data in the right format with a loop and arange of if statements.
I'd say you should take a giant step back and explain the business case you're trying to solve. This solution sounds way harder than it should.
November 12, 2018 at 5:17 am
Hi Pietinden
Thank you for your reply, maybe i should have explain the case first,
In short, i have a table called CIP, which contains some basic data about a production schedule, I want to enrich the data, into a new table, and import the contents back into scheduling tool.
I can delete and repopulate the contents of the CIP table, and execute a SP from the scheduling tool.
Once the SP has enriched the data, i can read this new data back into the scheduling tool as a produciton schedule of CIP (cleaning times)
The data poulated into the Table CIP_updated_dateTOBE1, is nearly complete, however i still need to build a statement which will loop through each index number, returning data on to each row if conditions on a IF, AND statement and <8 hrs are correct. poulating the last remaining columns of data fore each record.
Once i have used the data reading this into the scheduling tool, the table CIP_updated_dateTOBE1 becomes redundent, hense why i delete the table and recreate it each time i need it.
Does this kinda make sense what i am trying to achieve?
November 12, 2018 at 5:43 am
roy.hoult - Monday, November 12, 2018 5:17 AMHi Pietinden
Thank you for your reply, maybe i should have explain the case first,In short, i have a table called CIP, which contains some basic data about a production schedule, I want to enrich the data, into a new table, and import the contents back into scheduling tool.
I can delete and repopulate the contents of the CIP table, and execute a SP from the scheduling tool.
Once the SP has enriched the data, i can read this new data back into the scheduling tool as a produciton schedule of CIP (cleaning times)The data poulated into the Table CIP_updated_dateTOBE1, is nearly complete, however i still need to build a statement which will loop through each index number, returning data on to each row if conditions on a IF, AND statement and <8 hrs are correct. poulating the last remaining columns of data fore each record.
Once i have used the data reading this into the scheduling tool, the table CIP_updated_dateTOBE1 becomes redundent, hense why i delete the table and recreate it each time i need it.
Does this kinda make sense what i am trying to achieve?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 12, 2018 at 7:03 am
Hi Chris
[CIP_LatestEndtime] = '', -- EXPRESSION REQUIRED
If the [TB_Resource_Code], on ID 1 = TB_Resource_Code on ID 2 & If the [END_CIP] on ID1 - [TB_Start_production] on ID2 < 8 hours, then ,[CIP_LatestEndtime] on ID1 = TB_Start_production] on ID2, else [CIP_LatestEndtime] on ID1 = [END_CIP] on ID1 + 8 hours.
[MoreThan8hours] = 0 -- EXPRESSION REQUIRED
[MoreThan8Hours] If [END_CIP] on ID1 - [TB_Start_production] on ID2 < 8 hours then [[MoreThan8Hours]] on ID 1 = Y
The order of the CIP table will always need to be [TB_Resource_Code],[TB_Start_production] to ensure that the data is in the correct order prior to doing the last two columns.
Does that make sense?
November 12, 2018 at 9:33 am
roy.hoult - Monday, November 12, 2018 7:03 AMHi Chris[CIP_LatestEndtime] = '', -- EXPRESSION REQUIRED
If the [TB_Resource_Code], on ID 1 = TB_Resource_Code on ID 2 & If the [END_CIP] on ID1 - [TB_Start_production] on ID2 < 8 hours, then ,[CIP_LatestEndtime] on ID1 = TB_Start_production] on ID2, else [CIP_LatestEndtime] on ID1 = [END_CIP] on ID1 + 8 hours.
[MoreThan8hours] = 0 -- EXPRESSION REQUIRED
[MoreThan8Hours] If [END_CIP] on ID1 - [TB_Start_production] on ID2 < 8 hours then [[MoreThan8Hours]] on ID 1 = YThe order of the CIP table will always need to be [TB_Resource_Code],[TB_Start_production] to ensure that the data is in the correct order prior to doing the last two columns.
Does that make sense?
Hi Roy, busier than I expected today so it's taken a while to get back to you. Have a look at this little query. Once you've got your row numbers, picking up data from the 'next row' becomes trivial. I started off by using LEAD but it was much more complicated than this method is likely to be.
-- NOTE 'tr' = this row, 'nr' = next row
SELECT
tr.*,
[MoreThan8hours] = DATEDIFF(hour, tr.[CIP_End], nr.[TB_Start_production]) -- nearly there
FROM #CIP_updated_dateTOBE tr
OUTER APPLY (
SELECT *
FROM #CIP_updated_dateTOBE i
WHERE i.[Index] = tr.[Index]+1) nr
ORDER BY tr.[TB_Resource_Code], tr.[TB_Start_production]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 12, 2018 at 9:52 am
Something like this:
;WITH FirstSelection AS (
SELECT
[TB_Code],
[TB_Start_production],
[TB_Resource_Code],
[TB_Version],
[TB_Start_Changeover],
[tb_duration_changeover],
[id] = ROW_NUMBER() OVER(ORDER BY TB_Resource_code ,TB_Start_production),
CIP_END,
[END_CIP] = CONVERT(char(5), DATEADD(MINUTE, tb_duration_changeover, ''))
FROM #CIP_updated_dateTOBE
)
SELECT
tr.*,
[CIP_LatestEndtime] = CASE
WHEN tr.[TB_Resource_Code] = nr.TB_Resource_Code AND DATEDIFF(hour, tr.CIP_END, nr.[TB_Start_production]) < 8 THEN nr.[TB_Start_production]
ELSE DATEADD(hour,8,tr.CIP_END) END,
[MoreThan8hours] = CASE WHEN DATEDIFF(hour, tr.CIP_END, nr.[TB_Start_production]) < 8 THEN 'Y' ELSE 'N' END
FROM FirstSelection tr
OUTER APPLY (
SELECT *
FROM FirstSelection i
WHERE i.[id] = tr.[id]+1
) nr
ORDER BY tr.[TB_Resource_Code], tr.[TB_Start_production]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 12, 2018 at 12:17 pm
Hi Chris
Thank you ever so much for the code, I have added some extra bits: see below:
--Run 1st
DROP TABLE [dbo].[CIP_updated_dateTOBE]
-- Run 2nd
CREATE TABLE [dbo].[CIP_updated_dateTOBE](
[TB_Code] [nvarchar](max) NULL,
[TB_Start_production] [datetime] NULL,
[TB_Resource_Code] [nvarchar](max) NULL,
[TB_Version] [nvarchar](max) NULL,
[TB_Start_Changeover] [datetime] NULL,
[tb_duration_changeover] [INT])
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
--run 3rd
INSERT INTO [dbo].[CIP_updated_dateTOBE]
SELECT * FROM dbo.CIP
--Run 4th
ALTER
TABLE [dbo].[CIP_updated_dateTOBE]
ADD
[id] INT NULL, [CIP_END] [datetime] NULL, [CIP_LatestEndtime] [datetime] NULL, [MoreThan8hours] INT;
--Run 5th
UPDATE [dbo].[CIP_updated_dateTOBE]
SET CIP_END = CONVERT(char(5), DATEADD(MINUTE, tb_duration_changeover, ''), 114) + TB_Start_Changeover
Select * from [dbo].[CIP_updated_dateTOBE];
--Run 6th
;WITH FirstSelection AS (
SELECT
[TB_Code],
[TB_Start_production],
[TB_Resource_Code],
[TB_Version],
[TB_Start_Changeover],
[tb_duration_changeover],
[id] = ROW_NUMBER() OVER(ORDER BY TB_Resource_code ,TB_Start_production),
[CIP_END]
--[END_CIP] = CONVERT(char(5), DATEADD(MINUTE, tb_duration_changeover, ''))
FROM CIP_updated_dateTOBE
)
SELECT
tr.*,
[CIP_LatestEndtime] = CASE
WHEN tr.[TB_Resource_Code] = nr.TB_Resource_Code AND DATEDIFF(hour, tr.[CIP_END], nr.[TB_Start_production]) < 8 THEN nr.[TB_Start_production]
ELSE DATEADD(hour,8,tr.[CIP_END]) END,
[MoreThan8hours] = CASE WHEN DATEDIFF(hour, tr.[CIP_END], nr.[TB_Start_production]) > 8 THEN 'Y' ELSE 'N' END
FROM FirstSelection tr
OUTER APPLY (
SELECT *
FROM FirstSelection i
WHERE i.[id] = tr.[id]+1
) nr
ORDER BY tr.[TB_Resource_Code], tr.[TB_Start_production]
but i am very happy to say it works as per the execection run order above,
I did take out a line:
--[END_CIP] = CONVERT(char(5), DATEADD(MINUTE, tb_duration_changeover, '')) as this caused a error coverting the date string from a char, and should have been mapped as CIP_END, (i did try and run with the correct column name, but it still failed_!! not sure why;
But when i execute the --Run 5th step, ( your code) it correctly populated the right data;
Now i do have two remianing question, is it possible to run the whole code in one hit, or should i create a SP for each run step? When i finished running the code you sent, it seems to load the data virtually, ie. when i run: Select * from CIP_updated_dateTOBE the data is missing from the table, it is only there when i run the code 5? Am i missing something...
many thanks
Roy
November 13, 2018 at 1:57 am
If you really need the table CIP_updated_dateTOBE then use this:
;WITH FirstSelection AS (
SELECT
TB_Code,
TB_Start_production,
TB_Resource_Code,
TB_Version,
TB_Start_Changeover,
tb_duration_changeover,
[id] = ROW_NUMBER() OVER(ORDER BY TB_Resource_code, TB_Start_production),
[CIP_END] = DATEADD(MINUTE, tb_duration_changeover, TB_Start_Changeover)
FROM dbo.CIP
)
SELECT
tr.*,
[CIP_LatestEndtime] = CASE
WHEN tr.[TB_Resource_Code] = nr.TB_Resource_Code AND x.DownTimeHours < 8 THEN nr.TB_Start_production
ELSE DATEADD(hour, 8, tr.CIP_END) END,
[MoreThan8hours] = CASE WHEN x.DownTimeHours > 8 THEN 'Y' ELSE 'N' END
INTO dbo.CIP_updated_dateTOBE
FROM FirstSelection tr
OUTER APPLY (
SELECT *
FROM FirstSelection i
WHERE i.[id] = tr.[id]+1
) nr
CROSS APPLY (
SELECT [DownTimeHours] = DATEDIFF(hour, tr.CIP_END, nr.TB_Start_production)
) x
ORDER BY tr.TB_Resource_Code, tr.TB_Start_production
But you probably don't need it, in which case use this:
;WITH FirstSelection AS (
SELECT
TB_Code,
TB_Start_production,
TB_Resource_Code,
TB_Version,
TB_Start_Changeover,
tb_duration_changeover,
[id] = ROW_NUMBER() OVER(ORDER BY TB_Resource_code, TB_Start_production),
[CIP_END] = DATEADD(MINUTE, tb_duration_changeover, TB_Start_Changeover)
FROM dbo.CIP
)
SELECT
tr.*,
[CIP_LatestEndtime] = CASE
WHEN tr.[TB_Resource_Code] = nr.TB_Resource_Code AND x.DownTimeHours < 8 THEN nr.TB_Start_production
ELSE DATEADD(hour, 8, tr.CIP_END) END,
[MoreThan8hours] = CASE WHEN x.DownTimeHours > 8 THEN 'Y' ELSE 'N' END
FROM FirstSelection tr
OUTER APPLY (
SELECT *
FROM FirstSelection i
WHERE i.[id] = tr.[id]+1
) nr
CROSS APPLY (
SELECT [DownTimeHours] = DATEDIFF(hour, tr.CIP_END, nr.TB_Start_production)
) x
ORDER BY tr.TB_Resource_Code, tr.TB_Start_production
I've used an alternative means of calculating CIP_END, here's a simple test harness to check if it works:
-- alternative calculation of [CIP_END]
SELECT
TB_Start_Changeover,
tb_duration_changeover,
DATEADD(MINUTE, tb_duration_changeover, ''),
CONVERT(char(5), DATEADD(MINUTE, tb_duration_changeover, ''), 114),
CONVERT(char(5), DATEADD(MINUTE, tb_duration_changeover, ''), 114) + TB_Start_Changeover,
[oldCIP_END] = CAST(CONVERT(char(5), DATEADD(MINUTE, tb_duration_changeover, ''), 114) + TB_Start_Changeover AS DATETIME),
[newCIP_END] = DATEADD(MINUTE, tb_duration_changeover, TB_Start_Changeover)
FROM dbo.CIP
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 13, 2018 at 12:10 pm
Hi Chris,
Just managed to get onto the DB and test the code, extremely happy to report the first piece of code works and does the job beautifully and more importantly you’re right, I don’t need to execute the second bit of code! ??
I must admit, I am totally blown away; the code you most elegantly wrote is certainly a lot more efficient than my original efforts of Google-sourced code, cobbled together at random to try and attempt the required output… what was I thinking!! JFI, I did make a request today for a SQL course, as a starting point, but it was unfortunately rejected. Costs! Maybe the answer is to hit the books, with that in mind, can you recommend suitable ones?
Roy
November 14, 2018 at 9:55 am
roy.hoult - Tuesday, November 13, 2018 12:10 PMHi Chris,
Just managed to get onto the DB and test the code, extremely happy to report the first piece of code works and does the job beautifully and more importantly you’re right, I don’t need to execute the second bit of code! 😊
I must admit, I am totally blown away; the code you most elegantly wrote is certainly a lot more efficient than my original efforts of Google-sourced code, cobbled together at random to try and attempt the required output… what was I thinking!! JFI, I did make a request today for a SQL course, as a starting point, but it was unfortunately rejected. Costs! Maybe the answer is to hit the books, with that in mind, can you recommend suitable ones?
Roy
Thank you for your kind words Roy!
As to sources and books, I wouldn't know any more, however a thread has just started here which will certainly get you started. Good luck!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply