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