USE [RADIOLOGY] GO /****** Object: StoredProcedure [dbo].[Radiology_logbook] Script Date: 9/11/2023 2:23:18 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --NEW ALTER Procedure [dbo].[Radiology_logbook]---'03/01/2014','03/01/2014','CT','' @StartDate as varchar(12), @EndDate as varchar(12), @Revenueid as varchar(10), @companyID as varchar(12) = '', @PatientType as varchar(1) as --Declare -- @StartDate as varchar(12) = '05/29/2021', -- @EndDate as varchar(12) = '05/29/2021', -- @Revenueid as varchar(10) = 'CT', -- @companyID as varchar(12) = '' If @PatientType = 'I' Begin select IDNum, [Patient Name], BirthDate, Age, Room, Sex, [Procedure], Doctor, [RequestDate], [Processed Date], [Result Available], [Time Taken], [Time Done], ChargeSlip,ORNumber, Amount, /*Membership, Film1, Film2, Film3, Film4, Film5, Film6, Film7, Film8, Film10, Film11,*/ /*Film14, Film17, */Shift, Date, HospNum, RevenueID, Radiologist, ResultDate, /*FilmsReleased, TotalFilms, DiscountPercentage*/ MSSCLassification,ACCESSIONNUM,PatientType,[Account Type],radtech,Company,UserID[Employee] From ( select DISTINCT B.IDnum, req.[Patient Name] as [Patient Name], Convert(Varchar(10),Req.BirthDate,101) as Birthdate, Laboratory.dbo.fn_LabComputeAge(Req.Birthdate, getdate()) as Age, isnull(Req.Roomid,'') as Room, req.Sex as Sex, req.Description as [Procedure], --Radiology.dbo.fn_GetDoctorsName(B.RequestDocID) as Doctor, dbo.fn_GetRadioDoctorsName2(req.RequestDoctorCode,@Revenueid,req.ItemID,req.IDNum) [Doctor], IsNull((select top 1 requestdate from Station..tbNurseLogbook where revenueid = @Revenueid and referencenum = req.refnum and itemid = Req.ItemID),B.TransDate) as [RequestDate], B.TransDate as [Processed Date], Req.VerifyDate as [Result Available], case when cast((B.TransDate) as datetime) between '00:00:00' and '05:59:59' then DATEADD(day,-1, cast((B.TransDate) as datetime)) else cast((B.TransDate) as datetime) end as [Time Taken], (Req.VerifyDate) as [Time Done], --K.ReferenceNum as B.RefNum as chargeslip, --case when b.RevenueID = 'CP' then B.RefNum else '' end IsNull( (Select Top 1 B2.RefNum From Billing..tbBillDailyBill B2 Where B2.RevenueID IN ('CP','PY','DE','PP','OA') and B2.IDNum = Req.IDNum) ,Req.RefNum) as ORNumber, B.Amount as Amount, case when convert(varchar(10), cast((B.TransDate) as datetime), 108) between '06:00:00' and '13:59:59' then '1st' when convert(varchar(10), cast((B.TransDate) as datetime), 108) between '14:00:00' and '21:59:59' then '2nd' else '3rd' end + ' SHIFT' as Shift, case when cast((B.TransDate) as datetime) between '00:00:00' and '05:59:59' then DATEADD(day,-1, cast((B.TransDate) as datetime)) else cast((B.TransDate) as datetime) end as DATE, Req.HospNum as HospNum, @Revenueid AS RevenueID, Radiology.dbo.fn_GetDoctorsName(Req.RadCode) as Radiologist, Req.ResultDate as ResultDate, Case when isnull(p.MSSDiscountExpiry,'') = '' OR p.MSSDiscountExpiry < GETDATE() then 'PAY' else 'SERVICE' end as MSSCLassification, CASE WHEN Req.swfin In ('C','R') THEN '***' ELSE '' END + /*Req.RequestNum*/ z.FileNum AS ACCESSIONNUM, 'I' as PatientType, case when B.AccountNum = IP.HospNum OR B.AccountNum = 'P01' OR B.REFNUM LIKE 'OR%' OR B.REFNUM LIKE 'CC%'THEN 'CASH' when Comp.CLASS In ('Q') THEN 'PDAF' when Comp.CLASS In ('I') THEN 'HMO' when COMP.CLASS In ('K') THEN 'PRIVATE' ELSE 'OTHERS' END AS [ACCOUNT TYPE] ,req.radtech ,Comp.Company ,dbo.fn_RadioGetUserName(Req.Receptionist)[UserID] from BILLING..tbBillDailyBill B With (NoLock) Left Outer Join Radiology..tbRadiologyRequests Req On Req.RefNum = B.RefNum and B.IDNum = Req.IDNum and Req.RevenueID = B.RevenueID and Req.ItemID = B.ItemID Left Outer Join Patient_data..tbmaster P On (Req.Hospnum = P.hospnum) Left outer join Station..tbNurseLogBook K on K.IDNum = B.idnum AND K.RevenueID = B.revenueid and K.ItemID = B.ItemID and k.ReferenceNum = b.RefNum left outer join Patient_Data..tbpatient IP on B.IdNum = IP.IdNum left outer join Build_File..tbCoCompany Comp on b.AccountNum = Comp.AccountNum Left outer join tbRadiologyFileNum z on req.Hospnum = z.HospNum and B.RevenueID = z.RevenueID INNER Join( select Sum(B.Amount)[Amount],B.IDNum,B.RefNum from Billing..tbBillDailyBill B --where B.Amount <> 0 Group By B.IDNum,B.RefNum Having Sum(B.Amount) > 0 )BA ON Ba.IDNum = B.IDNum AND BA.RefNum = B.RefNum WHERE B.Transdate between @StartDate and @EndDate + ' 23:59:59.99' and B.RevenueID = @Revenueid --and Req.RevenueID = @Revenueid and ISNULL(Req.swfin,'') in ('X','Y') AND (B.AccountNum = @companyID OR @companyID = '') AND B.Amount > 0 and isnull(B.Adjust,'') <> 'X' ) LogBook order by [Processed Date]asc END Else If @PatientType = 'O' Begin select IDNum, [Patient Name], BirthDate, Age, Room, Sex, [Procedure], Doctor, [RequestDate], [Processed Date], [Result Available], [Time Taken], [Time Done], ChargeSlip,ORNumber, Amount, /*Membership, Film1, Film2, Film3, Film4, Film5, Film6, Film7, Film8, Film10, Film11,*/ /*Film14, Film17, */ Shift, Date, HospNum, RevenueID, Radiologist, ResultDate, /*FilmsReleased, TotalFilms, DiscountPercentage*/ MSSCLassification, ACCESSIONNUM, PatientType, [ACCOUNT TYPE] ,radtech,Company,UserID[Employee] From ( select DISTINCT B.IDNum, --Case When IsNUll(B.IDNum,'CASH') IN ('CASH','') Then IsNull(C.[Name],(Select top 1 PaymentFrom From Billing..tbCashORMaster Where RefNum = B.RefNum)) Else Req.[Patient Name] End as [Patient Name], Req.[Patient Name] as [Patient Name], Convert(Varchar(10),req.Birthdate,101) as Birthdate, Laboratory.dbo.fn_LabComputeAge(req.Birthdate, getdate()) as Age, isnull(Req.Roomid,'CASH')as room, req.Sex as Sex, req.Description as [Procedure], --Radiology.dbo.fn_GetDoctorsName(CASE WHEN ISNULL(b.RequestDocID,'') = '' then Req.RequestdoctorCode else b.RequestDocID end) as Doctor, dbo.fn_GetRadioDoctorsName2(req.RequestDoctorCode,@Revenueid,req.ItemID,req.IDNum) [Doctor], IsNull((select top 1 requestdate from Station..tbNurseLogbook where revenueid = @Revenueid and referencenum = req.refnum and itemid = Req.ItemID),B.TransDate) as [RequestDate], B.TransDate as [Processed Date], Req.VerifyDate as [Result Available], case when cast((B.TransDate) as datetime) between '00:00:00' and '05:59:59' then DATEADD(day,-1, cast((B.TransDate) as datetime)) else cast((B.TransDate) as datetime) end as [Time Taken], Req.VerifyDate as [Time Done], CASE WHEN B.RefNum LIKE 'OR%' or B.RefNum LIKE 'CC%' THEN B.ChargeSlip ELSE CASE WHEN ISNULL(cast(B.AccountNum as varchar(15)),'') = '' THEN '' ELSE cast(B.AccountNum as varchar(15)) + '/' END + CAST(B.RefNum as varchar(15)) END as Chargeslip, CASE WHEN B.RefNum LIKE 'OR%' or B.RefNum LIKE 'CC%' THEN B.RefNum ELSE case when b.RevenueID = 'CP' then B.RefNum else '' end END as ORNumber, B.Amount as Amount, case when convert(varchar(10), cast((B.TransDate) as datetime), 108) between '06:00:00' and '13:59:59' then '1st' when convert(varchar(10), cast((B.TransDate) as datetime), 108) between '14:00:00' and '21:59:59' then '2nd' else '3rd' end + ' SHIFT' as SHIFT, CASE WHEN B.CDFAccountNum = B.HospNum OR B.CDFAccountNum = 'P01' OR B.REFNUM LIKE 'OR%' OR B.REFNUM LIKE 'CC%' THEN cast((C.TransDate) as datetime) WHEN CAST(B.TransDate AS DATETIME) BETWEEN '00:00:00' AND '05:59:59' THEN DATEADD(DAY, -1, CAST(B.TransDate AS DATETIME)) ELSE cast((B.TransDate) as datetime) END AS DATE, Req.HospNum as HospNum, @revenueid AS RevenueID, Radiology.dbo.fn_GetDoctorsName(Req.RadCode) as Radiologist, Req.ResultDate as ResultDate, Case when isnull(p.MSSDiscountExpiry,'') = '' OR p.MSSDiscountExpiry < GETDATE() then 'PAY' else 'SERVICE' end as MSSCLassification, CASE WHEN Req.swfin In ('C','R') THEN '***' ELSE '' END + /*Req.RequestNum*/ z.FileNum ACCESSIONNUM, 'O' as PatientType, CASE WHEN b.CDFAccountNum = b.HospNum OR B.CDFAccountNum = 'P01' OR B.REFNUM LIKE 'OR%' OR B.REFNUM LIKE 'CC%'THEN 'CASH' WHEN F.CLASS IN ('Q') THEN 'PDAF' when F.CLASS In ('I') THEN 'HMO' when F.CLASS In ('K') THEN 'PRIVATE' ELSE 'OTHERS' END AS [ACCOUNT TYPE] --CASE WHEN f.CLASS = 'Q' THEN 'PDAF' --AS [PATIENT CLASSIFICATION] ,req.radtech ,F.Company ,dbo.fn_RadioGetUserName(Req.Receptionist)[UserID] from BILLING..tbBillOPDailyOut B With (NoLock) Left Outer Join Radiology..tbRadiologyRequests Req On Req.RefNum = B.RefNum --and Req.IDNum = B.IDNum and Req.RevenueID = B.RevenueID and Req.ItemID = B.ItemID Left Outer Join Patient_data..tbmaster P On (Req.Hospnum = P.hospnum or P.hospnum = B.HospNum) --LEFT OUTER JOIN Build_File..tbCoCompany F ON (B.CDFAccountNum = F.AccountNum OR B.AccountNum = F.AccountNum) left outer join Patient_Data..tboutpatient op on op.IDNum = b.IDNum LEFT OUTER JOIN Build_File..tbCoCompany F ON (op.AccountNum = F.AccountNum) Left outer join tbRadiologyFileNum z on req.Hospnum = z.HospNum and B.RevenueID = z.RevenueID left join Billing..tbCashAssessment C on B.IDNum = C.IdNum --INNER Join( --select Sum(B.Amount)[Amount],B.IDNum,B.RefNum -- from Billing..tbbillopdailyout B -- --where B.Amount <> 0 -- Group By B.IDNum,B.RefNum -- Having Sum(B.Amount) > 0 --)BA ON Ba.IDNum = B.IDNum AND BA.RefNum = B.RefNum WHERE B.Transdate between @StartDate and @EndDate + ' 23:59:59.99' and B.RevenueID = @Revenueid and ISNULL(Req.swfin,'') in ('X','Y') AND ((B.AccountNum = @companyID OR B.CDFAccountNum =@companyID) or @companyID = '') AND B.Amount > 0 and isnull(B.Adjust,'') <> 'X' ) AS LogBookOut order by [Processed Date]asc End Else Begin select IDNum, [Patient Name], BirthDate, Age, Room, Sex, [Procedure], Doctor, [RequestDate], [Processed Date], [Result Available], [Time Taken], [Time Done], ChargeSlip,ORNumber, Amount, /*Membership, Film1, Film2, Film3, Film4, Film5, Film6, Film7, Film8, Film10, Film11,*/ /*Film14, Film17, */Shift, Date, HospNum, RevenueID, Radiologist, ResultDate, /*FilmsReleased, TotalFilms, DiscountPercentage*/ MSSCLassification,ACCESSIONNUM,PatientType,[Account Type],radtech,Company,UserID[Employee] From ( select DISTINCT B.IDnum, req.[Patient Name] as [Patient Name], Convert(Varchar(10),Req.BirthDate,101) as Birthdate, Laboratory.dbo.fn_LabComputeAge(Req.Birthdate, getdate()) as Age, isnull(Req.Roomid,'') as Room, req.Sex as Sex, req.Description as [Procedure], --Radiology.dbo.fn_GetDoctorsName(B.RequestDocID) as Doctor, dbo.fn_GetRadioDoctorsName2(req.RequestDoctorCode,@Revenueid,req.ItemID,req.IDNum) [Doctor], IsNull((select top 1 requestdate from Station..tbNurseLogbook where revenueid = @Revenueid and referencenum = req.refnum and itemid = Req.ItemID),B.TransDate) as [RequestDate], B.TransDate as [Processed Date], Req.VerifyDate as [Result Available], case when cast((B.TransDate) as datetime) between '00:00:00' and '05:59:59' then DATEADD(day,-1, cast((B.TransDate) as datetime)) else cast((B.TransDate) as datetime) end as [Time Taken], (Req.VerifyDate) as [Time Done], --K.ReferenceNum as B.RefNum as chargeslip, --case when b.RevenueID = 'CP' then B.RefNum else '' end IsNull( (Select Top 1 B2.RefNum From Billing..tbBillDailyBill B2 Where B2.RevenueID IN ('CP','PY','DE','PP','OA') and B2.IDNum = Req.IDNum) ,Req.RefNum) as ORNumber, B.Amount as Amount, case when convert(varchar(10), cast((B.TransDate) as datetime), 108) between '06:00:00' and '13:59:59' then '1st' when convert(varchar(10), cast((B.TransDate) as datetime), 108) between '14:00:00' and '21:59:59' then '2nd' else '3rd' end + ' SHIFT' as Shift, case when cast((B.TransDate) as datetime) between '00:00:00' and '05:59:59' then DATEADD(day,-1, cast((B.TransDate) as datetime)) else cast((B.TransDate) as datetime) end as DATE, Req.HospNum as HospNum, @Revenueid AS RevenueID, Radiology.dbo.fn_GetDoctorsName(Req.RadCode) as Radiologist, Req.ResultDate as ResultDate, Case when isnull(p.MSSDiscountExpiry,'') = '' OR p.MSSDiscountExpiry < GETDATE() then 'PAY' else 'SERVICE' end as MSSCLassification, CASE WHEN Req.swfin In ('C','R') THEN '***' ELSE '' END + /*Req.RequestNum*/ z.FileNum AS ACCESSIONNUM, 'I' as PatientType, case when B.AccountNum = IP.HospNum OR B.AccountNum = 'P01' OR B.REFNUM LIKE 'OR%' OR B.REFNUM LIKE 'CC%'THEN 'CASH' when Comp.CLASS In ('Q') THEN 'PDAF' when Comp.CLASS In ('I') THEN 'HMO' when COMP.CLASS In ('K') THEN 'PRIVATE' ELSE 'OTHERS' END AS [ACCOUNT TYPE] ,req.radtech ,Comp.Company ,dbo.fn_RadioGetUserName(Req.Receptionist)[UserID] from BILLING..tbBillDailyBill B With (NoLock) Left Outer Join Radiology..tbRadiologyRequests Req On Req.RefNum = B.RefNum and B.IDNum = Req.IDNum and Req.RevenueID = B.RevenueID and Req.ItemID = B.ItemID Left Outer Join Patient_data..tbmaster P On (Req.Hospnum = P.hospnum) Left outer join Station..tbNurseLogBook K on K.IDNum = B.idnum AND K.RevenueID = B.revenueid and K.ItemID = B.ItemID and k.ReferenceNum = b.RefNum left outer join Patient_Data..tbpatient IP on B.IdNum = IP.IdNum left outer join Build_File..tbCoCompany Comp on b.AccountNum = Comp.AccountNum Left outer join tbRadiologyFileNum z on req.Hospnum = z.HospNum and B.RevenueID = z.RevenueID INNER Join( select Sum(B.Amount)[Amount],B.IDNum,B.RefNum from Billing..tbBillDailyBill B --where B.Amount <> 0 Group By B.IDNum,B.RefNum Having Sum(B.Amount) > 0 )BA ON Ba.IDNum = B.IDNum AND BA.RefNum = B.RefNum WHERE B.Transdate between @StartDate and @EndDate + ' 23:59:59.99' and B.RevenueID = @Revenueid --and Req.RevenueID = @Revenueid and ISNULL(Req.swfin,'') in ('X','Y') AND (B.AccountNum = @companyID OR @companyID = '') AND B.Amount > 0 and isnull(B.Adjust,'') <> 'X' ) LogBook Union ALL select IDNum, [Patient Name], BirthDate, Age, Room, Sex, [Procedure], Doctor, [RequestDate], [Processed Date], [Result Available], [Time Taken], [Time Done], ChargeSlip,ORNumber, Amount, /*Membership, Film1, Film2, Film3, Film4, Film5, Film6, Film7, Film8, Film10, Film11,*/ /*Film14, Film17, */ Shift, Date, HospNum, RevenueID, Radiologist, ResultDate, /*FilmsReleased, TotalFilms, DiscountPercentage*/ MSSCLassification, ACCESSIONNUM, PatientType, [ACCOUNT TYPE] ,radtech,Company,UserID[Employee] From ( select DISTINCT B.IDNum, --Case When IsNUll(B.IDNum,'CASH') IN ('CASH','') Then IsNull(C.[Name],(Select top 1 PaymentFrom From Billing..tbCashORMaster Where RefNum = B.RefNum)) Else Req.[Patient Name] End as [Patient Name], Req.[Patient Name] as [Patient Name], Convert(Varchar(10),req.Birthdate,101) as Birthdate, Laboratory.dbo.fn_LabComputeAge(req.Birthdate, getdate()) as Age, isnull(Req.Roomid,'CASH')as room, req.Sex as Sex, req.Description as [Procedure], --Radiology.dbo.fn_GetDoctorsName(CASE WHEN ISNULL(b.RequestDocID,'') = '' then Req.RequestdoctorCode else b.RequestDocID end) as Doctor, dbo.fn_GetRadioDoctorsName2(req.RequestDoctorCode,@Revenueid,req.ItemID,req.IDNum) [Doctor], IsNull((select top 1 requestdate from Station..tbNurseLogbook where revenueid = @Revenueid and referencenum = req.refnum and itemid = Req.ItemID),B.TransDate) as [RequestDate], B.TransDate as [Processed Date], Req.VerifyDate as [Result Available], case when cast((B.TransDate) as datetime) between '00:00:00' and '05:59:59' then DATEADD(day,-1, cast((B.TransDate) as datetime)) else cast((B.TransDate) as datetime) end as [Time Taken], Req.VerifyDate as [Time Done], CASE WHEN B.RefNum LIKE 'OR%' or B.RefNum LIKE 'CC%' THEN B.ChargeSlip ELSE CASE WHEN ISNULL(cast(B.AccountNum as varchar(15)),'') = '' THEN '' ELSE cast(B.AccountNum as varchar(15)) + '/' END + CAST(B.RefNum as varchar(15)) END as Chargeslip, CASE WHEN B.RefNum LIKE 'OR%' or B.RefNum LIKE 'CC%' THEN B.RefNum ELSE case when b.RevenueID = 'CP' then B.RefNum else '' end END as ORNumber, B.Amount as Amount, case when convert(varchar(10), cast((B.TransDate) as datetime), 108) between '06:00:00' and '13:59:59' then '1st' when convert(varchar(10), cast((B.TransDate) as datetime), 108) between '14:00:00' and '21:59:59' then '2nd' else '3rd' end + ' SHIFT' as SHIFT, CASE WHEN B.CDFAccountNum = B.HospNum OR B.CDFAccountNum = 'P01' OR B.REFNUM LIKE 'OR%' OR B.REFNUM LIKE 'CC%' THEN cast((C.TransDate) as datetime) WHEN CAST(B.TransDate AS DATETIME) BETWEEN '00:00:00' AND '05:59:59' THEN DATEADD(DAY, -1, CAST(B.TransDate AS DATETIME)) ELSE cast((B.TransDate) as datetime) END AS DATE, Req.HospNum as HospNum, @revenueid AS RevenueID, Radiology.dbo.fn_GetDoctorsName(Req.RadCode) as Radiologist, Req.ResultDate as ResultDate, Case when isnull(p.MSSDiscountExpiry,'') = '' OR p.MSSDiscountExpiry < GETDATE() then 'PAY' else 'SERVICE' end as MSSCLassification, CASE WHEN Req.swfin In ('C','R') THEN '***' ELSE '' END + /*Req.RequestNum*/ z.FileNum ACCESSIONNUM, 'O' as PatientType, CASE WHEN b.CDFAccountNum = b.HospNum OR B.CDFAccountNum = 'P01' OR B.REFNUM LIKE 'OR%' OR B.REFNUM LIKE 'CC%'THEN 'CASH' WHEN F.CLASS IN ('Q') THEN 'PDAF' when F.CLASS In ('I') THEN 'HMO' when F.CLASS In ('K') THEN 'PRIVATE' ELSE 'OTHERS' END AS [ACCOUNT TYPE] --CASE WHEN f.CLASS = 'Q' THEN 'PDAF' --AS [PATIENT CLASSIFICATION] ,req.radtech ,F.Company ,dbo.fn_RadioGetUserName(Req.Receptionist)[UserID] from BILLING..tbBillOPDailyOut B With (NoLock) Left Outer Join Radiology..tbRadiologyRequests Req On Req.RefNum = B.RefNum --and Req.IDNum = B.IDNum and Req.RevenueID = B.RevenueID and Req.ItemID = B.ItemID Left Outer Join Patient_data..tbmaster P On (Req.Hospnum = P.hospnum or P.hospnum = B.HospNum) --LEFT OUTER JOIN Build_File..tbCoCompany F ON (B.CDFAccountNum = F.AccountNum OR B.AccountNum = F.AccountNum) left outer join Patient_Data..tboutpatient op on op.IDNum = b.IDNum LEFT OUTER JOIN Build_File..tbCoCompany F ON (op.AccountNum = F.AccountNum) Left outer join tbRadiologyFileNum z on req.Hospnum = z.HospNum and B.RevenueID = z.RevenueID left join Billing..tbCashAssessment C on B.IDNum = C.IdNum --INNER Join( --select Sum(B.Amount)[Amount],B.IDNum,B.RefNum -- from Billing..tbbillopdailyout B -- --where B.Amount <> 0 -- Group By B.IDNum,B.RefNum -- Having Sum(B.Amount) > 0 --)BA ON Ba.IDNum = B.IDNum AND BA.RefNum = B.RefNum WHERE B.Transdate between @StartDate and @EndDate + ' 23:59:59.99' and B.RevenueID = @Revenueid and ISNULL(Req.swfin,'') in ('X','Y') AND ((B.AccountNum = @companyID OR B.CDFAccountNum =@companyID) or @companyID = '') AND B.Amount > 0 and isnull(B.Adjust,'') <> 'X' ) AS LogBookOut order by [Processed Date]asc End