April 6, 2015 at 10:00 pm
I have a history table with the following values
CREATE TABLE History (SnapShotDate DATETIME, UID VARCHAR(10), DUEDATE DATETIME)
INSERT INTO History VALUES ('03-23-2015','PT-01','2015-04-22')
INSERT INTO History VALUES ('03-30-2015','PT-01','2015-04-20')
INSERT INTO History VALUES ('04-06-2015','PT-01','2015-06-30')
INSERT INTO History VALUES ('03-23-2015','PT-02','2015-04-22')
INSERT INTO History VALUES ('03-30-2015','PT-02','2015-04-22')
INSERT INTO History VALUES ('04-06-2015','PT-02','2015-04-22')
INSERT INTO History VALUES ('03-23-2015','PT-03','2015-04-18')
INSERT INTO History VALUES ('03-30-2015','PT-03','2015-04-22')
INSERT INTO History VALUES ('04-06-2015','PT-03','2015-04-22')
INSERT INTO History VALUES ('03-23-2015','PT-04','2015-04-20')
INSERT INTO History VALUES ('03-30-2015','PT-04','2015-04-22')
INSERT INTO History VALUES ('04-06-2015','PT-04','2015-04-18')
I need an output in the below format. I need the most recent changed value for any given UID. Please help me in getting the below result
OUTPUT
UID PreviousDueDate CurrentDueDate
----------------------------------------
PT-01 2015-04-20 2015-06-30
PT-02 2015-04-22 2015-04-22
PT-03 2015-04-18 2015-04-22
PT-04 2015-04-22 2015-04-18
Thanks in Advance
April 6, 2015 at 11:30 pm
Use MAX(DATE) and GROUP BY (ID)
😎
April 6, 2015 at 11:43 pm
I want Current and Previous Due Date for every UID. Previous Due Date could be greater than Current Due Date. So I cannot use Max & Min here. I need a query where it uses SnapShotDate and checks for changes for every UID and gives previous and Current Due Dates.
Hope that Explains!
April 7, 2015 at 12:25 am
v-swgar (4/6/2015)
OUTPUTUID PreviousDueDate CurrentDueDate
----------------------------------------
PT-01 2015-04-20 2015-06-30
PT-02 2015-04-22 2015-04-22
PT-03 2015-04-18 2015-04-22
PT-04 2015-04-22 2015-04-18
;with cte as
(
Select UID, dueDate,
ROW_NUMBER() over(partition by uid order by snapshotdate desc) rowNum
from History
)
select c1.uid, c2.duedate PrevDueDate, c1.duedate CurrDueDate
from cte c1
inner join cte c2 on c2.uid = c1.uid and c2.rownum = 2
where c1.rowNum = 1
This gives your expected output for 3 out of the 4 rows. Why is the logic for PreviousDueDate for PT-03 different from the others? I could not see why that would be 4/18.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 7, 2015 at 1:30 am
Quick, simple and quite efficient solution
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.History') IS NOT NULL DROP TABLE dbo.History;
CREATE TABLE dbo.History (SnapShotDate DATETIME, UID VARCHAR(10), DUEDATE DATETIME)
INSERT INTO dbo.History VALUES ('03-23-2015','PT-01','2015-04-22')
INSERT INTO dbo.History VALUES ('03-30-2015','PT-01','2015-04-20')
INSERT INTO dbo.History VALUES ('04-06-2015','PT-01','2015-06-30')
INSERT INTO dbo.History VALUES ('03-23-2015','PT-02','2015-04-22')
INSERT INTO dbo.History VALUES ('03-30-2015','PT-02','2015-04-22')
INSERT INTO dbo.History VALUES ('04-06-2015','PT-02','2015-04-22')
INSERT INTO dbo.History VALUES ('03-23-2015','PT-03','2015-04-18')
INSERT INTO dbo.History VALUES ('03-30-2015','PT-03','2015-04-22')
INSERT INTO dbo.History VALUES ('04-06-2015','PT-03','2015-04-22')
INSERT INTO dbo.History VALUES ('03-23-2015','PT-04','2015-04-20')
INSERT INTO dbo.History VALUES ('03-30-2015','PT-04','2015-04-22')
INSERT INTO dbo.History VALUES ('04-06-2015','PT-04','2015-04-18');
;WITH NUMBERED_DATES_BY_UID AS
(
SELECT
H.UID
,ROW_NUMBER() OVER
(
PARTITION BY H.UID
ORDER BY H.DUEDATE DESC
) AS UID_RID
,H.DUEDATE
FROM dbo.History H
)
SELECT
ND.UID
,MAX(CASE WHEN ND.UID_RID = 2 THEN ND.DUEDATE END) AS PREVIOUS_DUEDATE
,MAX(CASE WHEN ND.UID_RID = 1 THEN ND.DUEDATE END) AS CURRENT_DUDATE
FROM NUMBERED_DATES_BY_UID ND
GROUP BY ND.UID;
Results
UID PREVIOUS_DUEDATE CURRENT_DUDATE
---------- ----------------------- -----------------------
PT-01 2015-04-22 00:00:00.000 2015-06-30 00:00:00.000
PT-02 2015-04-22 00:00:00.000 2015-04-22 00:00:00.000
PT-03 2015-04-22 00:00:00.000 2015-04-22 00:00:00.000
PT-04 2015-04-20 00:00:00.000 2015-04-22 00:00:00.000
Bonus message
Warning: Null value is eliminated by an aggregate or other SET operation.
The purpose of the aggregation here is only to eliminate the NULL values;-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply