November 23, 2016 at 12:29 pm
CREATE TABLE Link_Table
(
ScodeVARCHAR(10)
,ICodeVARCHAR(10)
,SDateDATETIME
,EDateDATETIME
,CCodeVARCHAR(10)
)
CREATE TABLE Source_Table
(
Scode_SVARCHAR(10)
,ICode_SVARCHAR(10)
,ADate_SDATETIME
,CCode_FinalVARCHAR(10)
)
INSERT INTO dbo.Link_Table
( Scode, ICode, SDate, EDate, CCode )
VALUES ( 'TWNR', -- Scode - varchar(10)
'123456', -- ICode - varchar(10)
'2016-11-23 00:00:00.000', -- SDate - datetime
'2016-12-20 00:00:00.000', -- EDate - datetime
'SNBE' -- CCode - varchar(10)
)
INSERT INTO dbo.Link_Table
( Scode, ICode, SDate, EDate, CCode )
VALUES ( 'TWNR', -- Scode - varchar(10)
'123456', -- ICode - varchar(10)
'2016-11-10 00:00:00.000', -- SDate - datetime
'2016-12-20 00:00:00.000', -- EDate - datetime
'SNWE' -- CCode - varchar(10)
)
INSERT INTO dbo.Link_Table
( Scode, ICode, SDate, EDate, CCode )
VALUES ( 'TWMR', -- Scode - varchar(10)
'789456', -- ICode - varchar(10)
'2016-11-23 00:00:00.000', -- SDate - datetime
'2016-12-20 00:00:00.000', -- EDate - datetime
'SNBE' -- CCode - varchar(10)
)
INSERT INTO dbo.Source_Table
( Scode_S ,
ICode_S ,
ADate_S ,
CCode_Final
)
VALUES ( 'TWNR' , -- Scode_S - varchar(10)
'123456' , -- ICode_S - varchar(10)
'2016-12-16 00:00:00.000' , -- ADate_S - datetime
'' -- CCode_Final - varchar(10)
)
INSERT INTO dbo.Source_Table
( Scode_S ,
ICode_S ,
ADate_S ,
CCode_Final
)
VALUES ( 'TWMR' , -- Scode_S - varchar(10)
'789456' , -- ICode_S - varchar(10)
'2016-12-16 00:00:00.000' , -- ADate_S - datetime
'' -- CCode_Final - varchar(10)
)
SELECT
LT.CCode
,Status = ???
FROM dbo.Link_Table LT
INNER JOIN dbo.Source_Table ST ON LT.Scode = ST.Scode_S
AND LT.ICode = ST.ICode_S
AND ST.ADate_S BETWEEN LT.SDate AND LT.EDate
Above is Sample SQL Code.
Question:- My question is I want to Populate a STATUS Field. If I have ONE Matching row Status Should be "GOOD Record"
NO Matching rows Status Should be "NOT FOUND"
Duplicate Matching Rows Status Should be "DUPLICATE"
Please advise How can I do With above Scenario. If I use Rank Function with Partition By and Order By with (Scode, ICode, SDate, EDate, CCode) It will not detect as a Duplicate for Row Second.
Thanks for your help!
November 23, 2016 at 1:37 pm
Don't understand why you think you need windowing functions...
Does this do what you want? The inner query, inside the parentheses... query "x", gets the counts for each grouping, and then the outer query just assigns a value to "CheckDupes" using the counts done in the "x" query.
SELECT x.Scode_S
, x.ICode_S
, x.ADate_S
, x.LTCount
, CASE
WHEN x.LTCount = 0 THEN 'Not found'
WHEN x.LTCount = 1 THEN 'Found'
WHEN x.LTCount > 1 THEN 'Duplicates' END AS CheckDupes
FROM
(SELECT s.Scode_S
, s.ICode_S
, s.ADate_S
, count(*) AS LTcount
FROM dbo.Source_Table s
LEFT JOIN dbo.Link_Table lt ON
s.ADate_S BETWEEN lt.SDate AND lt.EDate
AND s.ICode_S = lt.ICode
and S.Scode_S = LT.Scode
GROUP BY s.Scode_S
, s.ICode_S
, s.ADate_S) x
November 23, 2016 at 5:33 pm
Thanks for your help. It works great with the source data. I will apply this on my real data and get back to you.
Really appreciate your help and happy holidays!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply