T-SQL Query Help

Transact-SQL
1

CREATE TABLE Link_Table
(
Scode VARCHAR(10)
,ICode VARCHAR(10)
,SDate DATETIME
,EDate DATETIME
,CCode VARCHAR(10)
)

CREATE TABLE Source_Table
(
Scode_S VARCHAR(10)
,ICode_S VARCHAR(10)
,ADate_S DATETIME
,CCode_Final VARCHAR(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!

2

I'm note sure if this is what you are looking for... It doesn't look like your data has any duplicates or not found's, but how about this:

SELECT LT.Scode,
LT.ICode,
LT.SDate,
CASE COUNT(ST.ADate_S) WHEN 0 THEN 'Not Found'
WHEN 1 THEN 'GOOD'
ELSE 'DUPLICATE'
END AS Status
FROM dbo.Link_Table LT
LEFT 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
GROUP BY LT.Scode,
LT.ICode,
LT.SDate

Good Luck!

3

Hi Rnoldz,

Thank You for your reply. However, I am getting three records. It should be two records.

ICode SDate Scode Status
789456 2016-11-23 00:00:00.000 TWMR GOOD
123456 2016-11-10 00:00:00.000 TWNR DUPLICATE

Please advise.