• There is a 5 pm timestamp for example below and when I run for all data it doesn't follow logic for  a few

    CREATE TABLE #TimeIncrements

    (

    FieldSort1 VARCHAR(100),

    FieldSort2 VARCHAR(4000),

    FieldSort3 VARCHAR(4000),

    StartDate DATETIME,

    Duration INT,

    TimeIncrements VARCHAR(20)

    )

    INSERT INTO #TimeIncrements ( FieldSort1,FieldSort2,FieldSort3,StartDate,Duration )

    SELECT 'Scenario 1','00 MFG 00 - Scenario Kickoff',' Test Scenario Kick-off pre-requisite Test case- Mandatory Step','9/5/2017', 3 UNION

    SELECT 'Scenario 1','01 MFG 01 - Upload and Update Forecast','MFG Maintain PIR (Forecast) manually','9/5/2017', 3 UNION

    SELECT 'Scenario 1','01 MFG 01 - Upload and Update Forecast','MFG_ECC_Production Planning Processor_BR153','9/5/2017', 3 UNION

    SELECT 'Scenario 1','01.1 MFG 01 - Extend and Split Value a Material','Extend Material and Split Value','9/5/2017', 3 UNION

    SELECT 'Scenario 1','02 LEG/STP 01 - _ - Run MRP for Reservation','DIS_ECC_MRP Demand Planning Processor_BR159','9/5/2017', 3 UNION

    SELECT 'Scenario 1','02 LEG/STP 01 - _ - Run MRP for Reservation','Run MRP by Tcode MD03 to create Planned Order','9/5/2017', 3 UNION

    SELECT 'Scenario 1','02 LEG/STP 01 - _ - Run MRP for Reservation','Shopping cart creation from OneCat','9/5/2017', 3 UNION

    SELECT 'Scenario 1','02A DIS 01 - Convert Planned order to Purchase Requisition','Convert planned order to Purchase req','9/5/2017', 3 UNION

    SELECT 'Scenario 1','02A DIS 01 - Convert Planned order to Purchase Requisition','DIS_ECC_MRP Supply Planning Processor_BR188','9/5/2017', 3 UNION

    SELECT 'Scenario 1','02A DIS 01 - Convert Planned order to Purchase Requisition','Validate Creation of Purchase requisition from Planned Order by Tcode ME53N','9/5/2017', 3 UNION

    SELECT 'Scenario 1','02B STP 01 - Convert Purchase Requisition to Purchase order','Display Purchase order by Tcode ME23N','9/5/2017', 3 UNION

    SELECT 'Scenario 1','02B STP 01 - Convert Purchase Requisition to Purchase order','EDD0006 - Purchase Requisition Sourcing Determination','9/5/2017', 3 UNION

    SELECT 'Scenario 1','02B STP 01 - Convert Purchase Requisition to Purchase order','STP_ECC_Procurement and Sourcing Confidential Viewer_BR171','9/5/2017', 3 UNION

    SELECT 'Scenario 1','02B STP 01 - Convert Purchase Requisition to Purchase order','SUP Convert Purchase Reqs of procured part to Purchase Orders in the opening period','9/5/2017', 3 UNION

    SELECT 'Scenario 1','02C STP 01 - Manage Purchase orders','STP Approve Purchase Order_Fiori R2.2','9/5/2017', 3 UNION

    SELECT 'Scenario 1','02C STP 01 - Manage Purchase orders','STP Validate PO approver(s) R2.2','9/5/2017', 3 UNION

    SELECT 'Scenario 1','02D CPF 01 - Validate PO Pricing Conditions','CPF_ECC_Finance Viewer_BR060','9/5/2017', 3 UNION

    SELECT 'Scenario 1','02D CPF 01 - Validate PO Pricing Conditions','DIS_Validate Delivery Cost Conditions after PO Creation_ME23N','9/5/2017', 3 UNION

    SELECT 'Scenario 1','03 MFG 02 - Forecast Consumption and MRP Run','MFG Review STO demand and evaluate planning results and forecast consumption','9/5/2017', 3 UNION

    SELECT 'Scenario 1','03 MFG 02 - Forecast Consumption and MRP Run','MFG Run MRP for the product at the Supplying Plant using Tcode MD02','9/5/2017', 3 UNION

    SELECT 'Scenario 1','03 MFG 02 - Forecast Consumption and MRP Run','MFG_ECC_Production Planning Processor_BR153','9/5/2017', 3 UNION

    SELECT 'Scenario 1','04 MFG 03 - Base Manufacturing Process','MFG Confirm that a serial or batch number (as applicable) are created on release of the production order using Tcode CO03. MFG-C102','9/5/2017', 3 UNION

    SELECT 'Scenario 1','04 MFG 03 - Base Manufacturing Process','MFG Confirm that an inspection Lot is created.','9/5/2017', 3 UNION

    SELECT 'Scenario 1','04 MFG 03 - Base Manufacturing Process','MFG Confirm that Equipment Record is created (as applicable)','9/5/2017', 3 UNION

    SELECT 'Scenario 1','04 MFG 03 - Base Manufacturing Process','MFG Convert planned order to YP00 Production order using Tcodes MD19, CO03-Verify planned order BOM is re-read at conversion','9/5/2017', 3 UNION

    SELECT 'Scenario 1','04 MFG 03 - Base Manufacturing Process','MFG Print Shop FloorPaper (RICEFW LDD0001)','9/5/2017', 3 UNION

    SELECT 'Scenario 1','04 MFG 03 - Base Manufacturing Process','MFG Release Production Order CO02','9/5/2017', 3 UNION

    SELECT 'Scenario 1','04 MFG 03 - Base Manufacturing Process','MFG Verify Material availability checks are performed using TCode CO02 Change Production Order','9/5/2017', 3 UNION

    SELECT 'Scenario 1','04 MFG 03 - Base Manufacturing Process','MFG Verify missing parts using the following tcodes CO03 Display Production Order MFG-C114 & MFG-C113','9/5/2017', 3 UNION

    SELECT 'Scenario 1','04 MFG 03 - Base Manufacturing Process','MFG_ECC_Production Planning Processor_BR153','9/5/2017', 3 UNION

    SELECT 'Scenario 1','05 DIS 02 - Issue Material to Production Order','DIS_ECC_Internal Materials Processor Warehouse Management_BR294','9/5/2017', 3 UNION

    SELECT 'Scenario 1','05 DIS 02 - Issue Material to Production Order','Manual Component Issue to Production Order manual MIGO','9/5/2017', 3 UNION

    SELECT 'Scenario 1','05 DIS 02 - Issue Material to Production Order','RF Pick','9/5/2017', 3 UNION

    SELECT 'Scenario 1','05 DIS 02 - Issue Material to Production Order','WHSE Report Dashboard for TR-TO management','9/5/2017', 3 UNION

    SELECT 'Scenario 1','05.1 CPF - Validate Accounting Documents','CPF_ECC_Finance Viewer_BR060','9/5/2017', 3 UNION

    SELECT 'Scenario 1','05.1 CPF - Validate Accounting Documents','CPF_Validate Accounting Entries for PGI - EMS Plants','9/5/2017', 3 UNION

    SELECT 'Scenario 1','06 QMR 01 - Perform inprocess Quality using MIC','QMR RR with Attachments and Accepted for Inspections ','9/5/2017', 3 UNION

    SELECT 'Scenario 1','06 QMR 01 - Perform inprocess Quality using MIC','QMR_ECC_Materials Inspector_BR135','9/5/2017', 3 UNION

    SELECT 'Scenario 1','06.1 QMR 02 - InProcess Adhoc Failure','02-Process ZM Quality Notification','9/5/2017', 3 UNION

    SELECT 'Scenario 1','06.1 QMR 02 - InProcess Adhoc Failure','04-Results Recording with Mandatory ','9/5/2017', 3 UNION

    SELECT 'Scenario 1','06.1 QMR 02 - InProcess Adhoc Failure','QMR_ECC_Materials Inspector_BR135','9/5/2017', 3 UNION

    SELECT 'Scenario 1','06.2 QMR 03 Quality Notifcation Rework Disposition','05-Close Quality Notification(s)','9/5/2017', 3 UNION

    SELECT 'Scenario 1','06.2 QMR 03 Quality Notifcation Rework Disposition','09-Close Task(s) in Quality Notification','9/5/2017', 3 UNION

    SELECT 'Scenario 1','06.2 QMR 03 Quality Notifcation Rework Disposition','QMR_ECC_Quality Notification Processor_BR137','9/5/2017', 3 UNION

    SELECT 'Scenario 1','06.3 QMR 04 Reinspect Adhoc Inspection (PASS)','QMR Addition of characteristics to Lot and Results Recording with in spec','9/5/2017', 3 UNION

    SELECT 'Scenario 1','06.3 QMR 04 Reinspect Adhoc Inspection (PASS)','QMR Results Recording with in spec values_Tcode QE51n','9/5/2017', 3 UNION

    SELECT 'Scenario 1','06.3 QMR 04 Reinspect Adhoc Inspection (PASS)','QMR_ECC_Materials Inspector_BR135','9/5/2017', 3 UNION

    SELECT 'Scenario 1','06.4 QMR 05 - Add IMTE to Inspection Characteristic','12-QMR RR with Attachments and Accepted for Inspections using IMTE ','9/5/2017', 3 UNION

    SELECT 'Scenario 1','06.4 QMR 05 - Add IMTE to Inspection Characteristic','QMR_ECC_Materials Inspector_BR135','9/5/2017', 3 UNION

    SELECT 'Scenario 1','06.5 QMR 06 - Print NCR','03-Print Notification (Type ZM) from Transaction QM03','9/5/2017', 3 UNION

    SELECT 'Scenario 1','06.5 QMR 06 - Print NCR','QMR_ECC_Product Quality Document Maintainer_BR142','9/5/2017', 3 UNION

    SELECT 'Scenario 1','07 MFG 04 - Order Confirmation at Header Level','MFG Perform production order confirmation at header level ','9/5/2017', 3 UNION

    SELECT 'Scenario 1','07 MFG 04 - Order Confirmation at Header Level','MFG_ECC_Manufacturing Production Processor_BR151','9/5/2017', 3 UNION

    SELECT 'Scenario 1','08 DIS 03 - Receive Production Order','Confirm TO using RF Put Away','9/5/2017', 3 UNION

    SELECT 'Scenario 1','08 DIS 03 - Receive Production Order','DIS ECC Perform Goods Receipt Against a Production Order in WM using MIGO','9/5/2017', 3 UNION

    SELECT 'Scenario 1','08 DIS 03 - Receive Production Order','DIS_ECC_Internal Materials Processor Warehouse Management_BR294','9/5/2017', 3 UNION

    SELECT 'Scenario 1','08.1 MFG 12 - Manually Generate As-Built','MFG As Built Traceability Report','9/5/2017', 3 UNION

    SELECT 'Scenario 1','08.1 MFG 12 - Manually Generate As-Built','MFG Manually create as built COIB','9/5/2017', 3 UNION

    SELECT 'Scenario 1','08.1 MFG 12 - Manually Generate As-Built','MFG_ECC_Manufacturing Process Maintainer_BR150','9/5/2017', 3 UNION

    SELECT 'Scenario 1','08.1 MFG 12 - Manually Generate As-Built','MFG_ECC_Manufacturing Production Processor_BR151','9/5/2017', 3 UNION

    SELECT 'Scenario 1','09 QMR 07 - Usage Decision','QMR Perform Usage Decision - Accepted UD with manual stock posting_Tcode QA32','9/5/2017', 3 UNION

    SELECT 'Scenario 1','09 QMR 07 - Usage Decision','QMR_ECC_Materials Inspector_BR135','9/5/2017', 3 UNION

    SELECT 'Scenario 1','09.1 QMR 08 - Print Inspection Lot','04-Print Inspection Lot with Usage Decision','9/5/2017', 3 UNION

    SELECT 'Scenario 1','09.1 QMR 08 - Print Inspection Lot','QMR_ECC_Product Quality Document Maintainer_BR142','9/5/2017', 3 UNION

    SELECT 'Scenario 1','09.2 QMR 09 - Data Book Creation and TPI QM Lot (based on last routing operation)','01- OCC Validation','9/5/2017', 3 UNION

    SELECT 'Scenario 1','09.2 QMR 09 - Data Book Creation and TPI QM Lot (based on last routing operation)','1.1- Purchase Order Workspace Auto Generation','9/5/2017', 3 UNION

    SELECT 'Scenario 1','09.2 QMR 09 - Data Book Creation and TPI QM Lot (based on last routing operation)','1.1-PQF Pre-Report','9/5/2017', 3 UNION

    SELECT 'Scenario 1','09.2 QMR 09 - Data Book Creation and TPI QM Lot (based on last routing operation)','1.2- Production Order Workspace Auto Generation','9/5/2017', 3 UNION

    SELECT 'Scenario 1','09.2 QMR 09 - Data Book Creation and TPI QM Lot (based on last routing operation)','1.2-PQF Generation','9/5/2017', 3 UNION

    SELECT 'Scenario 1','09.2 QMR 09 - Data Book Creation and TPI QM Lot (based on last routing operation)','1.3- Sales Order Workspace Auto Generation','9/5/2017', 3 UNION

    SELECT 'Scenario 1','09.2 QMR 09 - Data Book Creation and TPI QM Lot (based on last routing operation)','1.7- Maintenance Order Workspace Auto Generation','9/5/2017', 3 UNION

    SELECT 'Scenario 1','09.2 QMR 09 - Data Book Creation and TPI QM Lot (based on last routing operation)','2.1- Auto Watermarking','9/5/2017', 3 UNION

    SELECT 'Scenario 1','09.2 QMR 09 - Data Book Creation and TPI QM Lot (based on last routing operation)','2.2- Manual Automarking','9/5/2017', 3 UNION

    SELECT 'Scenario 1','09.2 QMR 09 - Data Book Creation and TPI QM Lot (based on last routing operation)','3.1 Inspection Lot Query','9/5/2017', 3 UNION

    SELECT 'Scenario 1','09.2 QMR 09 - Data Book Creation and TPI QM Lot (based on last routing operation)','3.2 Legacy Query','9/5/2017', 3 UNION

    SELECT 'Scenario 1','09.2 QMR 09 - Data Book Creation and TPI QM Lot (based on last routing operation)','3.3 Maintenance Order Query','9/5/2017', 3 UNION

    SELECT 'Scenario 1','09.2 QMR 09 - Data Book Creation and TPI QM Lot (based on last routing operation)','3.4 Part Number Query','9/5/2017', 3 UNION

    SELECT 'Scenario 1','09.2 QMR 09 - Data Book Creation and TPI QM Lot (based on last routing operation)','3.5 Plant Maintenance Order Query','9/5/2017', 3 UNION

    SELECT 'Scenario 1','09.2 QMR 09 - Data Book Creation and TPI QM Lot (based on last routing operation)','3.6 Production Query','9/5/2017', 3 UNION

    SELECT 'Scenario 1','09.2 QMR 09 - Data Book Creation and TPI QM Lot (based on last routing operation)','3.7 Purchase Order Line Item Query','9/5/2017', 3 UNION

    SELECT 'Scenario 1','09.2 QMR 09 - Data Book Creation and TPI QM Lot (based on last routing operation)','3.8 Quality Notification Query','9/5/2017', 3 UNION

    SELECT 'Scenario 1','09.2 QMR 09 - Data Book Creation and TPI QM Lot (based on last routing operation)','3.9 Sales Order Line Item Query','9/5/2017', 3 UNION

    SELECT 'Scenario 1','09.2 QMR 09 - Data Book Creation and TPI QM Lot (based on last routing operation)','QMR_ECC_Materials Inspector_BR135','9/5/2017', 3 UNION

    SELECT 'Scenario 1','10 MFG 05 - Variance Analysis','MFG Evaluate the production order Actual Costs, Actual dates, Actual Goods Movement, Confirmation data, variance ','9/5/2017', 3 UNION

    SELECT 'Scenario 1','10 MFG 05 - Variance Analysis','MFG Prior to TECO Verify production order completion based on production order status.using tcodes COOIS','9/5/2017', 3 UNION

    SELECT 'Scenario 1','10 MFG 05 - Variance Analysis','MFG_ECC_Production Planning Processor_BR153','9/5/2017', 3 UNION

    SELECT 'Scenario 1','12 MFG 06 - TECO Order','MFG Manually TECO order using Tcode CO02','9/5/2017', 3 UNION

    SELECT 'Scenario 1','12 MFG 06 - TECO Order','MFG_ECC_Production Planning Processor_BR153','9/5/2017', 3 UNION

    SELECT 'Scenario 1','13 CPF 02 - Order Settlement','CPF Calculate Variances_ Single Order Level_KKS2','9/5/2017', 3 UNION

    SELECT 'Scenario 1','13 CPF 02 - Order Settlement','CPF Post processing of Confirmations with Errors_COFC','9/5/2017', 3 UNION

    SELECT 'Scenario 1','13 CPF 02 - Order Settlement','CPF Settle Order Individual Processing_KO88 CPF-C227','9/5/2017', 3 UNION

    SELECT 'Scenario 1','13 CPF 02 - Order Settlement','CPF_ECC_Product Costing Processor_BR082','9/5/2017', 3 UNION

    SELECT 'Scenario 1','14 CPF 03 - CPF Validation','CPF_ECC_Finance Viewer_BR060','9/5/2017', 3 UNION

    SELECT 'Scenario 1','14 CPF 03 - CPF Validation','CPF_Validate FI Postings for Production Order Execution','9/5/2017', 3 UNION

    SELECT 'Scenario 1','14 CPF 03 - CPF Validation','CPF_Validate Production Order Settlement Entries and Postings','9/5/2017', 3 UNION

    SELECT 'Scenario 1','15 MFG 07 - Order Closure','MFG Close production orders using tcode CO02','9/5/2017', 3 UNION

    SELECT 'Scenario 1','15 MFG 07 - Order Closure','MFG Run order information system report COOIS MFG-C083','9/5/2017', 3 UNION

    SELECT 'Scenario 1','15 MFG 07 - Order Closure','MFG_ECC_Production Planning Processor_BR153','9/5/2017', 3 UNION

    SELECT 'Scenario 1','16 DIS 04 - Ship Finished Goods','Create Outbound delivery by Tcode VL10G','9/5/2017', 3 UNION

    SELECT 'Scenario 1','16 DIS 04 - Ship Finished Goods','Create transfer order T.Code LT03','9/5/2017', 3 UNION

    SELECT 'Scenario 1','16 DIS 04 - Ship Finished Goods','DIS_ECC_Outbound Materials Processor Warehouse Management_BR296','9/5/2017', 3 UNION

    SELECT 'Scenario 1','16 DIS 04 - Ship Finished Goods','Manage Pick and Pack','9/5/2017', 3 UNION

    SELECT 'Scenario 1','16 DIS 04 - Ship Finished Goods','Post Goods Issue VL02N','9/5/2017', 3 UNION

    SELECT 'Scenario 1','16 DIS 04 - Ship Finished Goods','RF Outbound Picking for TO Confirmation','9/5/2017', 3 UNION

    SELECT 'Scenario 1','16.1 CPF - Validate Goods Movement Accounting Document','CPF_ECC_Finance Viewer_BR060','9/5/2017', 3 UNION

    SELECT 'Scenario 1','16.1 CPF - Validate Goods Movement Accounting Document','CPF_Validate Accounting Entries for PGI - EMS Plants','9/5/2017', 3 UNION

    SELECT 'Scenario 1','99 MFG 99 - Scenario Sign-off',' Test Scenario Final sign-off Test case- Mandatory Step','9/5/2017', 3

    SELECT * FROM #TimeIncrements

    ;WITH RNs AS(

    SELECT *, ROW_NUMBER() OVER (PARTITION BY FieldSort1, StartDate ORDER BY FieldSort1, FieldSort2, FieldSort3) AS RN

    FROM #TimeIncrements) --You will need to ensure you use the correct table name here.

    SELECT FieldSort1, FieldSort2, FieldSort3,

    DATEADD(MINUTE, (20 * (RN - 1)) + ((15 * 60) * (CASE WHEN RN <= 28 THEN RN ELSE RN + 1 END / 28)), DATEADD(HOUR, 8, StartDate)) AS StartTime,

    StartDate, Duration

    FROM RNs;

    DROP TABLE #TimeIncrements