December 25, 2014 at 12:38 pm
Hi All,
Need a urgent help here. Thanks in advance fr your time and effort.
I need to capture changes in my table and store it in another table. Here is how my table would look like.
CREATE TABLE [dbo].[ABC](
[Hno] [nchar](10) NULL,
[Status] [smallint] NULL,
[Date] [datetime] NULL
) ON [PRIMARY]
Insert into [dbo].[ABC] values (100,0,getdate()-6)
Insert into [dbo].[ABC] values (100,0,getdate()-5)
Insert into [dbo].[ABC] values (100,1,getdate()-4)
Insert into [dbo].[ABC] values (100,1,getdate()-3)
Insert into [dbo].[ABC] values (100,0,getdate()-2)
Insert into [dbo].[ABC] values (100,0,getdate()-1)
Insert into [dbo].[ABC] values (100,1,getdate())
In return I need to check column Status for whenever the value has changed and need to store that in my table. If there are two records for which value in column Status is same, I need to pick only one of the records and that being the earliest of them and therefore the Date field is mentioned in my table . My output should be something like below.
100 02014-12-20 00:55:44.667
100 12014-12-22 00:55:44.723
100 02014-12-24 00:55:44.723
100 12014-12-26 00:55:44.723
December 25, 2014 at 2:54 pm
First of all, good job with the data and the DDL, made this easy;-)
Here is a quick window function solution, self explanatory and should get you passed this hurdle
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.ABC') IS NOT NULL DROP TABLE dbo.ABC;
CREATE TABLE [dbo].[ABC](
[Hno] [nchar](10) NULL,
[Status] [smallint] NULL,
[Date] [datetime] NULL
) ON [PRIMARY];
Insert into [dbo].[ABC] values (100,0,getdate()-6)
Insert into [dbo].[ABC] values (100,0,getdate()-5)
Insert into [dbo].[ABC] values (100,1,getdate()-4)
Insert into [dbo].[ABC] values (100,1,getdate()-3)
Insert into [dbo].[ABC] values (100,0,getdate()-2)
Insert into [dbo].[ABC] values (100,0,getdate()-1)
Insert into [dbo].[ABC] values (100,1,getdate())
;
;WITH STATUS_MARKED AS
(
SELECT
A.Hno
,A.[Status]
,CASE
WHEN LAG(A.[Status],1,A.[Status]) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY A.[Date]
) = A.[Status] THEN 0
ELSE 1
END AS ST_FLG
,A.[Date]
FROM dbo.ABC A
)
,STATUS_SET AS
(
SELECT
SM.Hno
,SM.[Status]
,SM.[Date]
,SUM(SM.ST_FLG) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY SM.[Date]
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS ST_FLG
FROM STATUS_MARKED SM
)
,FINAL_SET AS
(
SELECT
ST.Hno
,ST.[Status]
,ST.[Date]
,ROW_NUMBER() OVER
(
PARTITION BY ST.ST_FLG
ORDER BY ST.[Date]
) AS ST_RID
FROM STATUS_SET ST
)
SELECT
FS.Hno
,FS.[Status]
,FS.[Date]
FROM FINAL_SET FS
WHERE FS.ST_RID = 1
;
Results
Hno Status Date
---------- ------ -----------------------
100 0 2014-12-19 21:55:44.333
100 1 2014-12-21 21:55:44.333
100 0 2014-12-23 21:55:44.333
100 1 2014-12-25 21:55:44.337
December 26, 2014 at 11:18 am
I don't understand the point of:
"PARTITION BY (SELECT NULL)"
in the LAG. Why not just leave out "PARTITION BY"?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 26, 2014 at 12:07 pm
ScottPletcher (12/26/2014)
I don't understand the point of:"PARTITION BY (SELECT NULL)"
in the LAG. Why not just leave out "PARTITION BY"?
It's a habit which comes from the strange over clause defaults, better to explicitly state it than implicitly apply it, makes the code even more self explanatory.
😎
January 5, 2015 at 6:27 pm
Perhaps this might be a little simpler, assuming I got the requirements correct.
CREATE TABLE #ABC(
[Hno] [nchar](10) NULL,
[Status] [smallint] NULL,
[Date] [datetime] NULL
) ON [PRIMARY]
Insert into #ABC values (100,0,getdate()-6)
Insert into #ABC values (100,0,getdate()-5)
Insert into #ABC values (100,1,getdate()-4)
Insert into #ABC values (100,1,getdate()-3)
Insert into #ABC values (100,0,getdate()-2)
Insert into #ABC values (100,0,getdate()-1)
--Insert into #ABC values (100,1,getdate())
Insert into #ABC values (101,0,getdate()-6)
Insert into #ABC values (101,0,getdate()-5)
Insert into #ABC values (101,1,getdate()-4)
Insert into #ABC values (101,1,getdate()-3)
Insert into #ABC values (101,0,getdate()-2)
Insert into #ABC values (101,0,getdate()-1)
Insert into #ABC values (101,1,getdate())
SELECT Hno, [Status], [Date]
FROM
(
SELECT Hno, [Status], [Date]
,x=LAG([Status],1,-1) OVER (PARTITION BY Hno ORDER BY [Date])
FROM #ABC
) a
WHERE [Status] <> x;
GO
DROP TABLE #abc;
I commented out the one INSERT statement to show how it works across partitioned sets.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply