August 11, 2017 at 10:43 am
PSB - Friday, August 11, 2017 9:58 AMComputed column based on the sort the conditions 1 and 2
PSB - Friday, August 11, 2017 10:36 AMI will be using a stored procedure and it has a temp table . I would like to add the TimeIncrement field at the final select query of the reporting procedure.
This is two different things. Are you doing both, neither, or just one of them?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 11, 2017 at 11:20 am
Doing both 1) and 2)
Condition 1)
If FieldSort1 is a large, and the TimeIncrement runs past 4:40 and there are still more values for FieldSort1 , just move on tothe next day (8am) (StartDate + 1 ) in the TimeIncrement field
SELECT 'Scenario 2','27 Set 29',' IC9' ,'2017-08-12 08:00:00.000' ,'2017-08-11',5 ---- New date as we ecxceeded 14:40
2)
the clock resets back to 8am when you move on to a new FieldSort1
August 14, 2017 at 4:31 pm
Any updates will be greatly appreciated.
August 15, 2017 at 1:42 am
PSB - Monday, August 14, 2017 4:31 PMAny updates will be greatly appreciated.
You haven't answered the questions previously asked. For example, Phil asked is this an SP or not? You implied earlier you want to add this value as a Computed Column in the table, but also then state you want to return the results in an SP. Why add a computed column (which will perform badly) if you're returning the results in an SP and you can calculate the field there instead?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 15, 2017 at 5:35 am
Yes, sure . I will calculate the field in the SP for better performance .
Thanks,
PSB
August 15, 2017 at 5:46 am
PSB - Tuesday, August 15, 2017 5:35 AMYes, sure . I will calculate the field in the SP for better performance .Thanks,
PSB
Ok, well this would be one way:CREATE PROC TimeIncrements_SP AS
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;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 16, 2017 at 10:45 am
Thanks. Works good except for a few . For some cases , where it passes 4:40 timestamp it shows the same day 5:00 Pm instead of next day 8:00 am increment . Is it because of the RN = 28 that is in the code ?
August 17, 2017 at 1:53 am
PSB - Wednesday, August 16, 2017 10:45 AMThanks. Works good except for a few . For some cases , where it passes 4:40 timestamp it shows the same day 5:00 Pm instead of next day 8:00 am increment . Is it because of the RN = 28 that is in the code ?
Do you have any data to show this? When testing I actually created more rows for Scenario 2 20170811 than you provided, so that it would progress to 20170813; both day cross overs worked as you wanted (last time of the day 16:40, and next row 08:00 on the next day).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 17, 2017 at 7:49 am
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
August 17, 2017 at 8:00 am
Oh a 4 day Scenario. Give this a go instead:WITH RNs AS(
SELECT *, ROW_NUMBER() OVER (PARTITION BY FieldSort1, StartDate ORDER BY FieldSort1, FieldSort2, FieldSort3) AS RN
FROM #TimeIncrements)
SELECT FieldSort1, FieldSort2, FieldSort3,
DATEADD(MINUTE, (20 * (RN - 1)) + ((15 * 60) * ((RN + (RN / 28)) / 28)), DATEADD(HOUR, 8, StartDate)) AS StartTime,
--RN,
StartDate, Duration
FROM RNs;
Tested this up to 5 days.
P.S. WITH statements don't begin with a ;, the previous statement should END with one. You should get used to ending your statements with a ;, as the functionality of not doing so is deprecated. It's always good to future proof
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 25, 2017 at 6:49 am
How can I exclude weekends from here ?
August 25, 2017 at 6:55 am
Create a calendar table in your database, with weekends and other non-working days marked off. You then only need to add another predicate to your WHERE clause:WHERE Calendar.IsWorking = 0
John
August 25, 2017 at 8:41 am
I have a Dates table where Weekday 1 and 7 are weekends .
Create table #Dates
(
DateID INT IDENTITY(1,1),
[Date] date,
Year int,
Month int,
Day int,
QuarterNumber int,
WeekDay int
)
INSERT INTO #Dates ( [Date] ,
Year ,
Month ,
Day ,
QuarterNumber ,
WeekDay
)
SELECT '2017-08-01',2017,8,1,3,3 UNION
SELECT '2017-08-02',2017,8,2,3,4 UNION
SELECT '2017-08-03',2017,8,3,3,5 UNION
SELECT '2017-08-04',2017,8,4,3,6 UNION
SELECT '2017-08-05',2017,8,5,3,7 UNION
SELECT '2017-08-06',2017,8,6,3,1
--etc...
WITH RNs AS(
SELECT *, ROW_NUMBER() OVER (PARTITION BY FieldSort1, StartDate ORDER BY FieldSort1, FieldSort2, FieldSort3) AS RN
FROM #TimeIncrements JOIN #Dates d on TimeIncrements.StartDate = d.Date WHERE d.Weekday NOT IN (1,7) )
SELECT FieldSort1, FieldSort2, FieldSort3,
DATEADD(MINUTE, (20 * (RN - 1)) + ((15 * 60) * ((RN + (RN / 28)) / 28)), DATEADD(HOUR, 8, StartDate)) AS StartTime,
--RN,
StartDate, Duration
FROM RNs;
But this will not remove weekends
August 25, 2017 at 9:22 am
Yes, you probably do need something a bit more sophisticated than just a calendar table. The snippet below will convert your Dates table into a table that just shows the 20-minute periods during working times on working days. You could join to that and count up.
WITH N6 AS (
SELECT n FROM (VALUES (1), (2), (3), (4), (5), (6)) v(n)
)
, N36 AS (
SELECT n1.n
FROM N6 n1 CROSS JOIN N6 n2
)
, Numbers(n) AS (
SELECT ROW_NUMBER() OVER (ORDER BY n)
FROM N36
WHERE n <= 27 -- no of 20-min intervals in a 9-hour day
)
SELECT
ROW_NUMBER() OVER (ORDER BY DATEADD(MINUTE,480+20*(Nbr.n-1),CAST([Date] AS datetime))) AS RowNo
,DATEADD(MINUTE,480+20*(Nbr.n-1),CAST([Date] AS datetime)) AS PeriodStart
FROM #Dates d
CROSS JOIN Numbers Nbr
WHERE WeekDay BETWEEN 2 AND 6
John
August 25, 2017 at 10:40 am
The calendar table implementation would have to be somewhat different. Just adding a table and thinking that such would solve the problem was just not realistic. You would have to create a calendar table that actually has the individual 20 minute time slots in it, and then have a numerical key in that table that a row number could be mapped to, and row 1 would have to potentially map to something other than 1 in your calendar table, so that the table can always be used, no matter when it might be needed. That's not something that's going to go together in 2 minutes, and will require a bit more thought. Also, what will you do about holidays? Weekends alone are not the only potential issue. Also, how about just good old office closings or shutdown weeks? Those would also have to be handled in the calendar table, and probably manually.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply