August 21, 2015 at 3:52 pm
CREATE TABLE dbo.SCHEDULES(SCHEDULEID VARCHAR(10), EFFECTIVE_DATE DATE, AMOUNT DECIMAL(4,3))
INSERT INTO dbo.SCHEDULES(SCHEDULEID, EFFECTIVE_DATE, AMOUNT) VALUES('CMS_MI_8202', '2015/01/01', 6.200)
INSERT INTO dbo.SCHEDULES(SCHEDULEID, EFFECTIVE_DATE, AMOUNT) VALUES('CMS_MI_8202', '2015/02/01', 6.225)
INSERT INTO dbo.SCHEDULES(SCHEDULEID, EFFECTIVE_DATE, AMOUNT) VALUES('CMS_MI_8205', '2015/01/01', 90.025)
INSERT INTO dbo.SCHEDULES(SCHEDULEID, EFFECTIVE_DATE, AMOUNT) VALUES('CMS_MI_8208', '2015/01/01', 810.025)
INSERT INTO dbo.SCHEDULES(SCHEDULEID, EFFECTIVE_DATE, AMOUNT) VALUES('CMS_MI_8208', '2015/02/01', 811.025)
There is no TERMINATION_DATE in the record. I need a way to artificially create a TERMINATION_DATE for each record, and the TERMINATION_DATE should be 1 day before the
EFFECTIVE_DATE of any new record OR '2099/12/31' if there is only 1 record or '2099/12/31' for the
latest record.
RESULTS I need would look like this:
SCHEDULEID EFFECTIVE_DATE TERMINATION_DATE AMOUNT
CMS_MI_8202 2015/01/01 2015/01/31 6.200
CMS_MI_8202 2015/02/01 2099/12/31 6.225
CMS_MI_8205 2015/01/01 2099/12/31 90.025
CMS_MI_8208 2015/01/01 2015/01/31 810.025
CMS_MI_8208 2015/02/01 2099/12/31 811.025
Is there a way to use LAG() ?
August 22, 2015 at 2:39 am
Nilssond (8/21/2015)
CREATE TABLE dbo.SCHEDULES(SCHEDULEID VARCHAR(10), EFFECTIVE_DATE DATE, AMOUNT DECIMAL(4,3))INSERT INTO dbo.SCHEDULES(SCHEDULEID, EFFECTIVE_DATE, AMOUNT) VALUES('CMS_MI_8202', '2015/01/01', 6.200)
INSERT INTO dbo.SCHEDULES(SCHEDULEID, EFFECTIVE_DATE, AMOUNT) VALUES('CMS_MI_8202', '2015/02/01', 6.225)
INSERT INTO dbo.SCHEDULES(SCHEDULEID, EFFECTIVE_DATE, AMOUNT) VALUES('CMS_MI_8205', '2015/01/01', 90.025)
INSERT INTO dbo.SCHEDULES(SCHEDULEID, EFFECTIVE_DATE, AMOUNT) VALUES('CMS_MI_8208', '2015/01/01', 810.025)
INSERT INTO dbo.SCHEDULES(SCHEDULEID, EFFECTIVE_DATE, AMOUNT) VALUES('CMS_MI_8208', '2015/02/01', 811.025)
There is no TERMINATION_DATE in the record. I need a way to artificially create a TERMINATION_DATE for each record, and the TERMINATION_DATE should be 1 day before the
EFFECTIVE_DATE of any new record OR '2099/12/31' if there is only 1 record or '2099/12/31' for the
latest record.
RESULTS I need would look like this:
SCHEDULEID EFFECTIVE_DATE TERMINATION_DATE AMOUNT
CMS_MI_8202 2015/01/01 2015/01/31 6.200
CMS_MI_8202 2015/02/01 2099/12/31 6.225
CMS_MI_8205 2015/01/01 2099/12/31 90.025
CMS_MI_8208 2015/01/01 2015/01/31 810.025
CMS_MI_8208 2015/02/01 2099/12/31 811.025
Is there a way to use LAG() ?
Lead() is better than lag() in this instance:
select * ,
TerminationDate = dateadd(day, -1,
lead(EFFECTIVE_DATE, 1, '21000101') over ( partition by SCHEDULEID order by SCHEDULEID,EFFECTIVE_DATE ))
from dbo.SCHEDULES
order by SCHEDULEID ,
EFFECTIVE_DATE;
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 22, 2015 at 4:11 am
Further on Phil's fine answer, recommend adding a POC (Partition, Order, Covering) index on the table, makes a huge difference if the set is large, here is a quick example.
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.SCHEDULES') IS NOT NULL DROP TABLE dbo.SCHEDULES;
CREATE TABLE dbo.SCHEDULES
(
SCHEDULEID VARCHAR(12) NOT NULL
,EFFECTIVE_DATE DATE NOT NULL
,AMOUNT DECIMAL(12,3) NOT NULL
)
/*
Test set of 10^6 rows
*/
DECLARE @SAMPLE_SIZE INT = 1000000;
DECLARE @SESSION_COUNT INT = 10000;
DECLARE @DAY_RANGE INT = 365;
DECLARE @FIRST_DAY DATE = '2015-01-01'
DECLARE @MAX_AMOUNT INT = 1000;
INSERT INTO dbo.SCHEDULES
(SCHEDULEID, EFFECTIVE_DATE, AMOUNT)
SELECT TOP(@SAMPLE_SIZE)
CONCAT('CMS_MI_',CONVERT(VARCHAR(12),ABS(CHECKSUM(NEWID())) % @SESSION_COUNT,0))
,DATEADD(DAY,ABS(CHECKSUM(NEWID())) % @DAY_RANGE,@FIRST_DAY)
,CONVERT(DECIMAL(12,3),(ABS(CHECKSUM(NEWID())) % (@MAX_AMOUNT * 100)),0) / 100
FROM sys.all_columns SC1
CROSS JOIN sys.all_columns SC2
CROSS JOIN sys.all_columns SC3
CROSS JOIN sys.all_columns SC4
GO
RAISERROR('--------------------------------------------------------
WITHOUT POC INDEX
--------------------------------------------------------',0,0) WITH NOWAIT;
SET STATISTICS IO,TIME ON;
SELECT
S.SCHEDULEID
,S.EFFECTIVE_DATE
,DATEADD(DAY,-1,LEAD(S.EFFECTIVE_DATE,1,CONVERT(DATE,'2100/01/01',111)) OVER
(
PARTITION BY S.SCHEDULEID
ORDER BY S.SCHEDULEID ASC
,S.EFFECTIVE_DATE ASC
))
,S.AMOUNT
FROM dbo.SCHEDULES S;
SET STATISTICS TIME,IO OFF;
GO
CREATE NONCLUSTERED INDEX NCLIDX_DBO_SCHEDULES_SCHEDULEID_EFFECTIVE_DATE_INCL_AMOUNT ON
dbo.SCHEDULES (SCHEDULEID ASC,EFFECTIVE_DATE ASC) INCLUDE (AMOUNT);
GO
RAISERROR('--------------------------------------------------------
WITH POC INDEX
--------------------------------------------------------',0,0) WITH NOWAIT;
SET STATISTICS IO,TIME ON;
SELECT
S.SCHEDULEID
,S.EFFECTIVE_DATE
,DATEADD(DAY,-1,LEAD(S.EFFECTIVE_DATE,1,CONVERT(DATE,'2100/01/01',111)) OVER
(
PARTITION BY S.SCHEDULEID
ORDER BY S.SCHEDULEID ASC
,S.EFFECTIVE_DATE ASC
)) AS TerminationDate
,S.AMOUNT
FROM dbo.SCHEDULES S;
SET STATISTICS TIME,IO OFF;
GO
Statistics
--------------------------------------------------------
WITHOUT POC INDEX
--------------------------------------------------------
SQL Server parse and compile time:
CPU time = 1357 ms, elapsed time = 1528 ms.
Table 'SCHEDULES'. Scan count 5, logical reads 4445, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 12182 ms, elapsed time = 10736 ms.
--------------------------------------------------------
WITH POC INDEX
--------------------------------------------------------
SQL Server parse and compile time:
CPU time = 2 ms, elapsed time = 2 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SCHEDULES'. Scan count 1, logical reads 5091, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 4103 ms, elapsed time = 8498 ms.
August 22, 2015 at 6:34 am
Many thanks for the responses/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply