SQLTeam.com | Weblogs | Forums

Duplicate records - Case Statement


#1

Hi All,
I have created a view with the below SQL and I get tons of duplicate records for the same Request ID . This view was created to support some calculated fields that are based on a table . I will include the DML as well as sample data for a request .

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE View [dbo].[REPORT_USEROBJECTS] as
SELECT Distinct DBO.REPORT_REQUEST.REQUESTID,DBO.SELECTED_REPORT.Rptdesc,
TargetCompletionDateDeviation =case when datepart(yyyy,dbo.SELECTED_REPORT.completedDt)=1900 then 0
when dbo.SELECTED_REPORT.completedDt Is Null then 0
when dbo.SELECTED_REPORT.completedDt='' then 0 when datepart(yyyy,dbo.SELECTED_REPORT.TargetDt)=1900 then 0
when dbo.SELECTED_REPORT.TargetDt Is Null then 0
else datediff(d,dbo.SELECTED_REPORT.TargetDt,dbo.SELECTED_REPORT.completedDt) end,
[TAT-UW]=datediff(ss,(Convert(varchar(20), dbo.REPORT_REQUEST.receiveddate, 101) +' ' + '00:00:00 '),dbo.SELECTED_REPORT.uwapprovedt)/86400.0,
[Triage TAT-Request Source] = case when dbo.TRIAGE_ITEM.triageitemid = 1 then (case when dbo.TRIAGE_CHECK_LIST_ITEM.cmpltdt ='1/1/1900' then datediff(day,dbo.REPORT_REQUEST.ReceivedDate,dbo.REPORT_REQUEST.TriageDt)
else datediff(day,dbo.REPORT_REQUEST.ReceivedDate,dbo.TRIAGE_CHECK_LIST_ITEM.Cmpltdt) end)
End,
[Triage TAT-Business Need] = case when dbo.TRIAGE_ITEM.triageitemid = 2 then (case when dbo.TRIAGE_CHECK_LIST_ITEM.cmpltdt ='1/1/1900' then datediff(day,dbo.REPORT_REQUEST.ReceivedDate,dbo.REPORT_REQUEST.TriageDt)
else datediff(day,dbo.REPORT_REQUEST.ReceivedDate,dbo.TRIAGE_CHECK_LIST_ITEM.Cmpltdt) end)
End,
[Triage TAT-Sr Mgt Approval] = case when dbo.TRIAGE_ITEM.triageitemid = 3 then (case when dbo.TRIAGE_CHECK_LIST_ITEM.cmpltdt ='1/1/1900' then datediff(day,dbo.REPORT_REQUEST.ReceivedDate,dbo.REPORT_REQUEST.TriageDt)
else datediff(day,dbo.REPORT_REQUEST.ReceivedDate,dbo.TRIAGE_CHECK_LIST_ITEM.Cmpltdt) end)
End,
[Triage TAT-Data] = case when dbo.TRIAGE_ITEM.triageitemid = 4 then (case when dbo.TRIAGE_CHECK_LIST_ITEM.cmpltdt ='1/1/1900' then datediff(day,dbo.REPORT_REQUEST.ReceivedDate,dbo.REPORT_REQUEST.TriageDt)
else datediff(day,dbo.REPORT_REQUEST.ReceivedDate,dbo.TRIAGE_CHECK_LIST_ITEM.Cmpltdt) end)
End,
[Triage TAT-Limitation Acceptance]=case when dbo.TRIAGE_ITEM.triageitemid = 5 then (case when dbo.TRIAGE_CHECK_LIST_ITEM.cmpltdt ='1/1/1900' then datediff(day,dbo.REPORT_REQUEST.ReceivedDate,dbo.REPORT_REQUEST.TriageDt)
else datediff(day,dbo.REPORT_REQUEST.ReceivedDate,dbo.TRIAGE_CHECK_LIST_ITEM.Cmpltdt) end)
End,

[Triage Value-Request Source] = case when dbo.TRIAGE_ITEM.triageitemid = 1 then (case when dbo.TRIAGE_CHECK_LIST_ITEM.cmpltdt ='1/1/1900' then datediff(day,dbo.REPORT_REQUEST.ReceivedDate,dbo.REPORT_REQUEST.TriageDt)
else datediff(day,dbo.REPORT_REQUEST.ReceivedDate,dbo.TRIAGE_CHECK_LIST_ITEM.Cmpltdt) end) * dbo.SELECTED_REPORT.NbrRpts
End,
[Triage Value-Business Need] = case when dbo.TRIAGE_ITEM.triageitemid = 2 then (case when dbo.TRIAGE_CHECK_LIST_ITEM.cmpltdt ='1/1/1900' then datediff(day,dbo.REPORT_REQUEST.ReceivedDate,dbo.REPORT_REQUEST.TriageDt)
else datediff(day,dbo.REPORT_REQUEST.ReceivedDate,dbo.TRIAGE_CHECK_LIST_ITEM.Cmpltdt) end) * dbo.SELECTED_REPORT.NbrRpts
End,
[Triage Value-Sr Mgt Approval] = case when dbo.TRIAGE_ITEM.triageitemid = 3 then (case when dbo.TRIAGE_CHECK_LIST_ITEM.cmpltdt ='1/1/1900' then datediff(day,dbo.REPORT_REQUEST.ReceivedDate,dbo.REPORT_REQUEST.TriageDt)
else datediff(day,dbo.REPORT_REQUEST.ReceivedDate,dbo.TRIAGE_CHECK_LIST_ITEM.Cmpltdt) end) * dbo.SELECTED_REPORT.NbrRpts
End,
[Triage Value-Data] = case when dbo.TRIAGE_ITEM.triageitemid = 4 then (case when dbo.TRIAGE_CHECK_LIST_ITEM.cmpltdt ='1/1/1900' then datediff(day,dbo.REPORT_REQUEST.ReceivedDate,dbo.REPORT_REQUEST.TriageDt)
else datediff(day,dbo.REPORT_REQUEST.ReceivedDate,dbo.TRIAGE_CHECK_LIST_ITEM.Cmpltdt) end) * dbo.SELECTED_REPORT.NbrRpts
End,
[Triage Value-Limitation Acceptance]=case when dbo.TRIAGE_ITEM.triageitemid = 5 then (case when dbo.TRIAGE_CHECK_LIST_ITEM.cmpltdt ='1/1/1900' then datediff(day,dbo.REPORT_REQUEST.ReceivedDate,dbo.REPORT_REQUEST.TriageDt)
else datediff(day,dbo.REPORT_REQUEST.ReceivedDate,dbo.TRIAGE_CHECK_LIST_ITEM.Cmpltdt) end) * dbo.SELECTED_REPORT.NbrRpts
End
from DBO.REPORT_REQUEST inner join DBO.SELECTED_REPORT
ON DBO.REPORT_REQUEST.REQUESTID = DBO.SELECTED_REPORT.Requestid inner join DBo.TRIAGE_CHECK_LIST_ITEM
on DBO.REPORT_REQUEST.Requestid = DBo.TRIAGE_CHECK_LIST_ITEM.Requestid inner join Triage_Item on
DBo.TRIAGE_CHECK_LIST_ITEM.triageitemid = dbo.Triage_item.triageitemid
where DBO.REPORT_REQUEST.MSGN is not null
GO

Data for a single request based on the view
How do I change the logic such that the data comes in a single record instead of multiple records

requestid rptdesc targetcompletiondatedeviation Triage TAT-Request Source Triage TAT-Business Need Triage TAT-Sr Mgt Approval Triage TAT-Data Triage TAT-Limitation Acceptance Triage Value-Request Source Triage Value-Business Need Triage Value-Sr Mgt Approval Triage Value-Data Triage Value-Limitation Acceptance
538659 194 -12 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
538659 194 -12 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
538659 194 -12 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
538659 194 -12 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
538659 194 -12 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
538659 194 -12 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
538659 194 -12 NULL NULL NULL NULL 4 NULL NULL NULL NULL 16
538659 194 -12 NULL NULL NULL 4 NULL NULL NULL NULL 16 NULL
538659 194 -12 NULL NULL 4 NULL NULL NULL NULL 16 NULL NULL
538659 194 -12 NULL 4 NULL NULL NULL NULL 16 NULL NULL NULL
538659 194 -12 4 NULL NULL NULL NULL 16 NULL NULL NULL NULL


#2

You can format your code in a post to make it easier to read (use the </> button). As for your problem, You have this:

FROM DBO.REPORT_REQUEST
INNER JOIN DBO.SELECTED_REPORT
      ON DBO.REPORT_REQUEST.REQUESTID = DBO.SELECTED_REPORT.Requestid
INNER JOIN DBo.TRIAGE_CHECK_LIST_ITEM
      ON DBO.REPORT_REQUEST.Requestid = DBo.TRIAGE_CHECK_LIST_ITEM.Requestid
INNER JOIN Triage_Item
      ON DBo.TRIAGE_CHECK_LIST_ITEM.triageitemid = dbo.Triage_item.triageitemid

Likely, some of those joins are 1 to many. You need to figure out which one and add additional join predicates or something. What I usually do is start with

SELECT *
FROM DBO.REPORT_REQUEST
INNER JOIN DBO.SELECTED_REPORT
      ON DBO.REPORT_REQUEST.REQUESTID = DBO.SELECTED_REPORT.Requestid
INNER JOIN DBo.TRIAGE_CHECK_LIST_ITEM
      ON DBO.REPORT_REQUEST.Requestid = DBo.TRIAGE_CHECK_LIST_ITEM.Requestid
INNER JOIN Triage_Item
      ON DBo.TRIAGE_CHECK_LIST_ITEM.triageitemid = dbo.Triage_item.triageitemid 

and build it up join by join to see where the duplication starts. Then I know where to put additional predicates


#3

The table with 1 to many relations is DBo.TRIAGE_CHECK_LIST_ITEM .
If we look at the data each column is creating a new record instead .Am I able to resolve it using join predicates?


#4

is it sufficient just to have the "most recent" row from TRIAGE_CHECK_LIST_ITEM?

Or perhaps the condition is "If there EXISTS a matching row from TRIAGE_CHECK_LIST_ITEM"?

Or some other criteria perhaps?


#5

I would like the data to be like
538659 194 -12 4 4 4 4 4 16 16 16 16 16.

Each Request has 5 records in the Triage_Check_List _Item and I'm trying to calculate turn around time for each item in the table based on the case condition

Thank You


#6

JOIN that table five times, with specific JOIN keys so you only get one match on each JOIN, and then use those separately within your report for the 5 columns you need?

There isn't enough information in your description for me to figure out what your data contains, and thus how to get the result you need

If you provide:

CREATE TABLE #TEMP
(
 ... your column definitions ...
)
INSERT INTO #TEMP
VALUES(... your sample data values ...)

for each table then you will get far better answers from folk here as they will be able to fiddle using your data and work up a solution for you.


#7

I do not see dbo.Triage_Item being used.
Try pivoting dbo.TRIAGE_CHECK_LIST_ITEM:

WITH CompletedDates
AS
(
	SELECT RequestId, [1] AS CmpltDt1, [2] AS CmpltDt2, [3] AS CmpltDt3, [4] AS CmpltDt4, [5] AS CmpltDt5
	FROM 
	(
		SELECT RequestId, TriageItemId, CmpltDt
		FROM dbo.TRIAGE_CHECK_LIST_ITEM
	) S
	PIVOT
	(
		MAX(Cmpltdt)
		FOR TriageItemId IN ( [1], [2], [3], [4], [5] )
	) AS P
)
SELECT R.RequestID
	--, cols
	,[Triage TAT-Request Source] =
		CASE
			WHEN T.CmpltDt1 = '19000101'
			THEN DATEDIFF(day, R.ReceivedDate, R.TriageDt) 
			ELSE DATEDIFF(day, R.ReceivedDate, T.Cmpltdt1)
		END
	--, cols
FROM DBO.REPORT_REQUEST R
	JOIN DBO.SELECTED_REPORT S
		ON R.REQUESTID = S.Requestid
	JOIN CompletedDates T
		ON R.RequestId = T.RequestId
WHERE R.MSGN IS NOT NULL;