CODEWITHSUNDEEP
sqlsql-serverstored-procedures
user3369060
user3369060

Reputation: 81

Store procedure for a select statement which should take input

Can anyone please help me with the store procedure for the following

select statement which should take an input of completeddatekey which

is at the bottom of the select statement

CompletedDateKey is filled with a key in this code but I need a store procedure that should take an input of completeddatekey

SELECT FactId
      ,UserType
      ,wr.WorkRequestId
      ,wr.XerisUserKey
      ,xu.CsuserUserID UserId
      ,u.fname UserFName
      ,u.lname UserLName
      ,b.PatientId
      ,p.firstname PatFName
      ,p.lastname PatLName
      ,GroupId
      ,HospiceGroupKey GroupKey
      ,WR.ContactKey
      ,C.ContactId
      ,C.FirstName
      ,C.LastName
      ,Convert(datetime,
               (Convert(varchar, SD.Date,101) + ' ' + ST.TimeOfDay ))  Start_dtm
      ,Convert(datetime,
               (Convert(varchar, CD.Date,101) + ' ' + CT.TimeOfDay )) End_dtm
      ,DATEDIFF(s,
                Convert(datetime,
                        (Convert(varchar, SD.Date,101) + ' ' + ST.TimeOfDay)),
                Convert(datetime,
                        (Convert(varchar, CD.Date,101) + ' ' + CT.TimeOfDay ))) WRDuration
      ,(Convert(Decimal(18,3), DATEDIFF(s,Convert(datetime,(Convert(varchar,  SD.Date,101) + ' ' + ST.TimeOfDay )), Convert(datetime,(Convert(varchar, CD.Date,101) + ' '          + CT.TimeOfDay ))))) * (Convert(Decimal(18,3),LineItemCount)/Convert(Decimal(18,3),PatientBucketItemCount)) Duration
      ,CallBackNumber
      ,WorkRequestType
      ,B.LineItemCount
      ,ArchiveLocation
      ,Processed
      ,ArchiveQueueType
      ,TQA
      ,Exclude
      ,CallId
 FROM bi.dbo.FactWorkRequestTouches (NOlock) WR
 INNER JOIN bi.dbo.BridgePatientWorkRequest B ON B.WorkRequestId = WR.WorkRequestId
 INNER JOIN bi.dbo.dimPatient (NOlock) P ON B.PatientId = P.CphPatientID
 INNER JOIN bi.dbo.DimXerisUsers (NOlock) XU ON WR.XerisUserKey = XU.XerisUserKey
 INNER JOIN cdc.dbo.csuser (NOlock) U ON XU.CsuserUserID = u.user_id
 INNER JOIN bi.dbo.DimTimeOfDay (NOlock) ST ON WR.StartTimeOfDayKey = ST.TimeKey
 INNER JOIN bi.dbo.DimTimeOfDay (NOlock) CT ON WR.CompletedTimeOfDayKey = CT.TimeKey
 INNER JOIN bi.dbo.DimDate (NOlock) SD ON WR.StartDateKey = SD.DateKey
 INNER JOIN bi.dbo.DimDate (NOlock) CD ON WR.CompletedDateKey = CD.DateKey
 LEFT OUTER JOIN bi.dbo.DimContact (Nolock) C ON WR.ContactKey = C.ContactKey
 left outer join ssdba.excelleRx_WebFOCUS.dbo.DimHospiceHiearchy as h with (nolock) on                 b.groupid = h.group_id

 WHERE CompletedDateKey = '20140131'
   AND ArchiveQueueType = 0
   AND PatientBucketItemCount <> 0
   AND Exclude = 0
   AND P.ENDDate is Null

Upvotes: 1

Views: 54

Answers (1)

DevelopmentIsMyPassion
DevelopmentIsMyPassion

Reputation: 3591

Its really simple. Just create procedure like below

create procedure MyProc(
@CompletedDateKey varchar(20) )

as 
Begin
  SELECT FactId
  ,UserType
  ,wr.WorkRequestId
  ,wr.XerisUserKey
  ,xu.CsuserUserID UserId
  ,u.fname UserFName
  ,u.lname UserLName
  ,b.PatientId
  ,p.firstname PatFName
  ,p.lastname PatLName
  ,GroupId
  ,HospiceGroupKey GroupKey
  ,WR.ContactKey
  ,C.ContactId
  ,C.FirstName
  ,C.LastName
  ,Convert(datetime,
           (Convert(varchar, SD.Date,101) + ' ' + ST.TimeOfDay ))  Start_dtm
  ,Convert(datetime,
           (Convert(varchar, CD.Date,101) + ' ' + CT.TimeOfDay )) End_dtm
  ,DATEDIFF(s,
            Convert(datetime,
                    (Convert(varchar, SD.Date,101) + ' ' + ST.TimeOfDay)),
            Convert(datetime,
                    (Convert(varchar, CD.Date,101) + ' ' + CT.TimeOfDay ))) WRDuration
  ,(Convert(Decimal(18,3), DATEDIFF(s,Convert(datetime,(Convert(varchar,  SD.Date,101) + ' ' + ST.TimeOfDay )), Convert(datetime,(Convert(varchar, CD.Date,101) + ' '          + CT.TimeOfDay ))))) * (Convert(Decimal(18,3),LineItemCount)/Convert(Decimal(18,3),PatientBucketItemCount)) Duration
  ,CallBackNumber
  ,WorkRequestType
  ,B.LineItemCount
  ,ArchiveLocation
  ,Processed
  ,ArchiveQueueType
  ,TQA
  ,Exclude
  ,CallId
  FROM bi.dbo.FactWorkRequestTouches (NOlock) WR
 INNER JOIN bi.dbo.BridgePatientWorkRequest B ON B.WorkRequestId = WR.WorkRequestId
 INNER JOIN bi.dbo.dimPatient (NOlock) P ON B.PatientId = P.CphPatientID
 INNER JOIN bi.dbo.DimXerisUsers (NOlock) XU ON WR.XerisUserKey = XU.XerisUserKey
 INNER JOIN cdc.dbo.csuser (NOlock) U ON XU.CsuserUserID = u.user_id
 INNER JOIN bi.dbo.DimTimeOfDay (NOlock) ST ON WR.StartTimeOfDayKey = ST.TimeKey
 INNER JOIN bi.dbo.DimTimeOfDay (NOlock) CT ON WR.CompletedTimeOfDayKey = CT.TimeKey
 INNER JOIN bi.dbo.DimDate (NOlock) SD ON WR.StartDateKey = SD.DateKey
 INNER JOIN bi.dbo.DimDate (NOlock) CD ON WR.CompletedDateKey = CD.DateKey
 LEFT OUTER JOIN bi.dbo.DimContact (Nolock) C ON WR.ContactKey = C.ContactKey
 left outer join ssdba.excelleRx_WebFOCUS.dbo.DimHospiceHiearchy as h with (nolock) on                   b.groupid = h.group_id

 WHERE CompletedDateKey = @CompletedDateKey
 AND ArchiveQueueType = 0
 AND PatientBucketItemCount <> 0
 AND Exclude = 0
 AND P.ENDDate is Null
End

Execute it like

Execute MyProc '20140131'

Upvotes: 1

Related Questions