The following SP I want to use in a job to insert yesterdays records into a table.
I am having an issue with this. Can anyone advise what I am doing incorrectly?
INSERT INTO DBO.CHARGECAPTURE
CREATE TABLE #FirstNeonate
(
SessionID int,
DeliveryTime datetime,
PRIMARY KEY (SessionID)
)
INSERT INTO #FirstNeonate
SELECT DISTINCT
F.Sessionid,
F.Valuetime AS DeliveryTime
FROM datamartdb2.dbo.BVFIndings AS F
INNER JOIN datamartdb2.dbo.BLSession_Extended AS S
ON S.Sessionid = F.Sessionid
WHERE
F.Atomid = 24130
and F.Instance = char(127)+'34323[1]'
and F.valuestr = 'true'
--and ( s.facilityid = @FacilityID )
AND (
(
F.EntryTime between DATEADD(day, -1, DATEADD(day, DATEDIFF(day,'19000101', GETDATE()),'19000101'))
and DATEADD(day, DATEDIFF(day,'19000101', GETDATE()),'19000101')
--between '08/01/2016' and getdate()--@StartTimeOut and @EndTimeOut
)
)
--####################################################################################################
CREATE TABLE #TMP_AllNeonates
(
SessionID int,
Instance varchar(255),
MultiCardNumber INT,
[MRN] varchar(255),
DeliveryTime datetime,
entrytime datetime,
[LastName] varchar(255),
[FirstName] varchar(255),
[FacilityName] varchar(255),
[FacilityID] int,
DeliveryTimeOfFirstNeonate DateTime/,
PRIMARY KEY (SessionID, Instance)/
)
INSERT INTO #TMP_AllNeonates
SELECT DISTINCT
FirstNeonate.Sessionid,
F.Instance,
F.MultiCardNumber,
S.MRN,
F.ValueTime AS DeliveryTime,
f.EntryTime,
S.LastName,
S.FirstName,
S.Facility_Name AS [FacilityName],
S.FacilityID AS [FacilityID],
FirstNeonate.DeliveryTime AS DeliveryTimeOfFirstNeonate
FROM #FirstNeonate AS FirstNeonate
INNER JOIN datamartdb2.dbo.BVFIndings AS F
ON FirstNeonate.Sessionid = F.Sessionid
JOIN datamartdb2.dbo.BLSession_Extended AS S
ON S.Sessionid = F.Sessionid
WHERE F.ObjectName = 'Time_Of_Delivery!Delivery_Report_Neonate[#]'
AND F.valuestr = 'true'
--since we might have duplicates in the finding
and entrytime = (select max(entrytime) from datamartdb2.dbo.BVFIndings F1
where F1.sessionid = f.sessionid
and F1.AtomID = F.AtomID
And F1.Instance = F.Instance
)
--####################################################################################################
SELECT
F.SessionID,
F.Instance,
F.MultiCardNumber AS [Neonate#],
(SELECT MAX(MultiCardNumber)
FROM #TMP_AllNeonates F1
WHERE F1.sessionid = F.sessionid
) AS [Total Neonates],
F.LastName AS [Last Name],
F.FirstName AS [First Name],
F.MRN AS [MRN],
dbo.EFgetAccountNum(F.Sessionid) AS [Account],
F.DeliveryTime AS [Delivery Date],
dbo.CRChargeCaptureTypeOfDelivery(F.Sessionid,F.Instance) AS [Delivery Type],
CASE
WHEN dbo.EFgetFindingValueByValueSTR_2Objects('General_Anesthesia!Cesarean_Section','General_Anesthesia!Normal_Vaginal_Delivery',F.Sessionid,'true')='true'
THEN 'General'
WHEN dbo.EFgetFindingValueByValueSTR_2Objects('Epidural_Anesthesia!Cesarean_Section','Epidural_Anesthesia!Normal_Vaginal_Delivery',F.Sessionid,'true')='true'
THEN 'Regional'
WHEN dbo.EFgetFindingValueByValueSTR_2Objects('Spinal_Anesthesia!Cesarean_Section','Spinal_Anesthesia!Normal_Vaginal_Delivery',F.Sessionid,'true')='true'
THEN 'Regional'
WHEN dbo.EFgetFindingValueByValueSTR_2Objects('CLE_Anesthesia!Cesarean_Section','CLE_Anesthesia!Normal_Vaginal_Delivery',F.Sessionid,'true')='true'
THEN 'Regional'
WHEN dbo.EFgetFindingValueByValueSTR_2Objects('Combined_Spinal_Epidural_Anesth!Cesarean_Section','Combined_Spinal_Epidural_Anesth!Normal_Vaginal_Delivery',F.Sessionid,'true')='true'
THEN 'Regional'
WHEN dbo.EFgetFindingValueByValueSTR_2Objects('Pudendal_Block!Cesarean_Section','Pudendal_Block!Normal_Vaginal_Delivery',F.Sessionid,'true')='true'
THEN 'Pudendal'
WHEN dbo.EFgetFindingValueByValueSTR_2Objects('Local_Anesthesia!Cesarean_Section','Local_Anesthesia!Normal_Vaginal_Delivery',F.Sessionid,'true')='true'
THEN 'Local'
WHEN dbo.EFgetFindingValueByValueSTR_2Objects('Anesthesia_None!Cesarean_Section','Anesthesia_None!Normal_Vaginal_Delivery',F.Sessionid,'true')='true'
THEN 'None'
WHEN dbo.EFgetFindingValue (1959, '15216[1]', F.sessionid) = 'true' -- Spinal_Anesthesia!General_Operative_Procedure[#]
THEN 'Regional'
WHEN dbo.EFgetFindingValue (1958, '15216[1]', F.sessionid) = 'true' -- Epidural_Anesthesia!General_Operative_Procedure[#]
THEN 'Regional'
WHEN dbo.EFgetFindingValue (31134, '15216[1]', F.sessionid) = 'true' -- Combined_Spinal_Epidural_Anesth!General_Operative_Procedure[#]
THEN 'Regional'
WHEN dbo.EFgetFindingValue (8077, '15216[1]', F.sessionid) = 'true' -- General_Anesthesia!General_Operative_Procedure[#]
THEN 'General'
ELSE ''
END AS [Anesthesia],
CASE
WHEN dbo.EFgetFindingValueByValueSTR_2Objects('General_Anesthesia!Normal_Vaginal_Delivery','General_Anesthesia!Cesarean_Section',F.Sessionid,'true') = 'true'
THEN '65000011'
WHEN EXISTS (select top 1 1
from datamartdb2.dbo.BVFindings f1
where f1.sessionId = f.sessionId
and f1.AtomID in (1958,1959,31134,38170)
and f1.Instance in (CHAR(127)+'5995',CHAR(127)+'1996')
and f1.ValueStr = 'true'
)
THEN '65000010'
END AS [Anesth. Code],
replace(replace(dbo.CRGetEvent(F.sessionid,F.Instance,'Maternal Complications'),'Tubal Ligation ; ',''),'Tubal Ligation','')
AS [Complications], --
dbo.CRGetEvent(F.Sessionid,F.Instance,'Lacerations') AS [Lacerations],
CASE
WHEN dbo.EFgetFindingValueByValueSTR_2Objects('Entry_Time_To_OR!Cesarean_Section','Time_Out_Of_OR!Cesarean_Section',F.Sessionid,'true') = 'true'
THEN '67000553'
END AS [PACU Code],
CASE WHEN
(Select top 1 valuestr
From BVfindings F2
WHERE F2.Sessionid = F.Sessionid
AND F2.Objectname like 'Entry_Time_To_OR!%'
AND F2.Valuestr = 'true') = 'true'
THEN '67000553'
ELSE ''
END AS PACU2,
CASE
WHEN (SELECT MAX(MultiCardNumber)
FROM #TMP_AllNeonates F1
WHERE F1.sessionid = F.sessionid
) >= 2
THEN '67000138'
WHEN dbo.EFgetFindingValue(2069,F.Instance,F.SessionID) = 'true' --Vacuum
THEN '67000135'
WHEN dbo.EFgetFindingValue(2070,F.Instance,F.SessionID) = 'true' --Forceps
THEN '67000135'
WHEN (select top 1 f1.valuestr
from datamartdb2.dbo.BVFindings f1
where f1.sessionId = F.sessionid
and f1.ObjectName in ('Placental_Manual_Lysis!Delivery_Report_Neonate[#]',
'Placental_Manual_Lysis!Birth_Canal_Revision',
'Retained_Placenta_P!Delivery_Report_Neonate[#]',
'Retained_Placenta_P!Birth_Canal_Revision',
'Pelvic_Hematoma_P!Birth_Canal_Revision',
'Postpartum_Hemorrhage!Normal_Vaginal_Delivery',
'Postpartum_Hemorrhage!Birth_Canal_Revision'
)
and dbo.EFgetFindingValue(1996,F.Instance,F.sessionid) is null --no CS
) = 'true'
THEN '67000135'
WHEN dbo.EFgetFindingValueByObjectName('Perineal_Laceration_Degree!Normal_Vaginal_Delivery',F.Sessionid) in ('III','IV')
THEN '67000135'
WHEN dbo.EFgetFindingValueByObjectName('Vaginal_Laceration_Degree!Normal_Vaginal_Delivery',F.Sessionid) in ('III','IV')
THEN '67000135'
WHEN dbo.EFgetFindingValue(1996,F.Instance,F.sessionid) = 'true' --CS
THEN '67000137'
ELSE '67000136'
END AS [CDM],
CASE WHEN EXISTS (select top 1 1
from BVFindings f1
where f1.sessionId = f.sessionId
and F1.Objectname IN ('Tubal_Ligation!Recovery_Report_T[#]','Tubal_Ligation!General_Operative_Procedure[#]',
'Tubal_Ligation!Discharge', 'Tubal_Ligation!Cesarean_Section', 'Surgeon_Present!Tubal_Ligation')
and f1.ValueStr = 'true')
AND dbo.CRChargeCaptureTypeOfDelivery(F.Sessionid,F.Instance) NOT LIKE 'Cesarean%' --no CS
THEN 'Tubal Ligation after VD'
WHEN EXISTS (select top 1 1
from BVFindings f1
where f1.sessionId = f.sessionId
and F1.Objectname IN ('Tubal_Ligation!Recovery_Report_T[#]','Tubal_Ligation!General_Operative_Procedure[#]',
'Tubal_Ligation!Discharge', 'Tubal_Ligation!Cesarean_Section', 'Surgeon_Present!Tubal_Ligation')
and f1.ValueStr = 'true')
AND dbo.CRChargeCaptureTypeOfDelivery(F.Sessionid,F.Instance) LIKE 'Cesarean%'
THEN 'CS Tubal Ligation'
WHEN EXISTS (select top 1 1
from BVFindings f1
where f1.sessionId = f.sessionId
and F1.Objectname IN ('Total_Abdominal_Hysterectomy!Recovery_Report_T[#]',
'Total_Abdominal_Hysterectomy',
'Supracervical_Hysterectomy!Recovery_Report_T[#]',
'Supracervical_Hysterectomy!General_Operative_Procedure[#]',
'Supracervical_Hysterectomy!Discharge',
'SP_Hysterectomy',
'Hysterectomy!G[#]',
'Hysterectomy!Discharge',
'Hysterectomy!Cesarean_Section',
'Hysterectomy',
'Cesarean_Section!Emergency[Hysterectomy]',
'Cesarean_Hysterectomy!Discharge')
and f1.ValueStr = 'true')
AND dbo.CRChargeCaptureTypeOfDelivery(F.Sessionid,F.Instance) LIKE 'Cesarean%'
THEN 'CS Hysterectomy'
WHEN EXISTS (select top 1 1
from BVFindings f1
where f1.sessionId = f.sessionId
and F1.Objectname IN ('Total_Abdominal_Hysterectomy!Recovery_Report_T[#]',
'Total_Abdominal_Hysterectomy',
'Supracervical_Hysterectomy!Recovery_Report_T[#]',
'Supracervical_Hysterectomy!General_Operative_Procedure[#]',
'Supracervical_Hysterectomy!Discharge',
'SP_Hysterectomy',
'Hysterectomy!G[#]',
'Hysterectomy!Discharge',
'Hysterectomy!Cesarean_Section',
'Hysterectomy',
'Cesarean_Section!Emergency[Hysterectomy]',
'Cesarean_Hysterectomy!Discharge')
and f1.ValueStr = 'true')
AND dbo.CRChargeCaptureTypeOfDelivery(F.Sessionid,F.Instance) NOT LIKE 'Cesarean%'
THEN 'Hysterectomy after VD'
WHEN
(SELECT MAX (A.Text_Str)
FROM dbo.BLFollowupLog A
WHERE A.sessionid = F.sessionid
AND A.Text_Str LIKE '%iud insertion%')LIKE '%iud insertion%'
THEN 'IUD_Insertion'
WHEN EXISTS (select top 1 1
from BVFindings f1
where f1.sessionId = f.sessionId
and F1.Objectname IN ('Levonorgestrel','IUD_Insertion','Copper')
and f1.ValueStr = 'true'
)
THEN 'IUD_Insertion'
WHEN EXISTS (select top 1 1
from BVFindings f1
where f1.sessionId = f.sessionId
and F1.Objectname = 'Etonogestrel'
and f1.ValueStr = 'true'
)
THEN 'Implant'
ELSE ''
END AS [Procedure],
CASE WHEN EXISTS (select top 1 1
from BVFindings f1
where f1.sessionId = f.sessionId
and F1.Objectname IN ('Tubal_Ligation!Recovery_Report_T[#]','Tubal_Ligation!General_Operative_Procedure[#]',
'Tubal_Ligation!Discharge', 'Tubal_Ligation!Cesarean_Section', 'Surgeon_Present!Tubal_Ligation')
and f1.ValueStr = 'true')
AND dbo.CRChargeCaptureTypeOfDelivery(F.Sessionid,F.Instance) NOT LIKE 'Cesarean%' --no CS
THEN '67000147'
WHEN EXISTS (select top 1 1
from BVFindings f1
where f1.sessionId = f.sessionId
and F1.Objectname IN ('Tubal_Ligation!Recovery_Report_T[#]','Tubal_Ligation!General_Operative_Procedure[#]',
'Tubal_Ligation!Discharge', 'Tubal_Ligation!Cesarean_Section', 'Surgeon_Present!Tubal_Ligation')
and f1.ValueStr = 'true')
AND dbo.CRChargeCaptureTypeOfDelivery(F.Sessionid,F.Instance) LIKE 'Cesarean%'
THEN '67000148'
WHEN EXISTS (select top 1 1
from BVFindings f1
where f1.sessionId = f.sessionId
and F1.Objectname IN ('Total_Abdominal_Hysterectomy!Recovery_Report_T[#]',
'Total_Abdominal_Hysterectomy',
'Supracervical_Hysterectomy!Recovery_Report_T[#]',
'Supracervical_Hysterectomy!General_Operative_Procedure[#]',
'Supracervical_Hysterectomy!Discharge',
'SP_Hysterectomy',
'Hysterectomy!G[#]',
'Hysterectomy!Discharge',
'Hysterectomy!Cesarean_Section',
'Hysterectomy',
'Cesarean_Section!Emergency[Hysterectomy]',
'Cesarean_Hysterectomy!Discharge')
and f1.ValueStr = 'true')
AND dbo.CRChargeCaptureTypeOfDelivery(F.Sessionid,F.Instance) LIKE 'Cesarean%'
THEN '67000150'
WHEN EXISTS (select top 1 1
from BVFindings f1
where f1.sessionId = f.sessionId
and F1.Objectname IN ('Total_Abdominal_Hysterectomy!Recovery_Report_T[#]',
'Total_Abdominal_Hysterectomy',
'Supracervical_Hysterectomy!Recovery_Report_T[#]',
'Supracervical_Hysterectomy!General_Operative_Procedure[#]',
'Supracervical_Hysterectomy!Discharge',
'SP_Hysterectomy',
'Hysterectomy!G[#]',
'Hysterectomy!Discharge',
'Hysterectomy!Cesarean_Section',
'Hysterectomy',
'Cesarean_Section!Emergency[Hysterectomy]',
'Cesarean_Hysterectomy!Discharge')
and f1.ValueStr = 'true')
AND dbo.CRChargeCaptureTypeOfDelivery(F.Sessionid,F.Instance) NOT LIKE 'Cesarean%'
THEN '67000151'
WHEN
(SELECT MAX (A.Text_Str)
FROM dbo.BLFollowupLog A
WHERE A.sessionid = F.sessionid
AND A.Text_Str LIKE '%iud insertion%')LIKE '%iud insertion%'
THEN '65000139'
WHEN EXISTS (select top 1 1
from BVFindings f1
where f1.sessionId = f.sessionId
and F1.Objectname IN ('Levonorgestrel','IUD_Insertion','Copper')
and f1.ValueStr = 'true'
)
THEN '65000139'
WHEN EXISTS (select top 1 1
from BVFindings f1
where f1.sessionId = f.sessionId
and F1.Objectname = 'Etonogestrel'
and f1.ValueStr = 'true'
)
THEN '65000140'
ELSE ''
END AS 'Proc_Charge',
CASE
WHEN dbo.EFgetFindingValue(213693, '1996', F.Sessionid) = 'true'
THEN 'yes'
ELSE ''
END AS 'Adhesive Barrier',
ISNULL (dbo.EFgetFindingValue(156991,'213694[1996]',F.Sessionid), '') AS Manufacturer,
ISNULL (dbo.EFgetFindingValue(135321,'213694[1996]',F.Sessionid), '') AS CatalogNumber,
ISNULL (dbo.EFgetFindingValue(68613,'213694[1996]',F.Sessionid), '') AS LotNumber,
ISNULL (dbo.EFgetFindingValue(73736,'213694[1996]',F.Sessionid), '') AS Qty,
CASE
WHEN dbo.EFgetFindingValue(156991,'213694[1996]',F.Sessionid) LIKE '%inter%'
THEN '67000635'
WHEN dbo.EFgetFindingValue(156991,'213694[1996]',F.Sessionid) LIKE 'sepra%'
THEN '67000629'
WHEN dbo.EFgetFindingValue(156991,'213694[1996]',F.Sessionid) LIKE 'surgi%'
THEN '67000603'
WHEN dbo.EFgetFindingValue(156991,'213694[1996]',F.Sessionid) LIKE 'bakri%'
THEN '67000608'
WHEN dbo.EFgetFindingValue(156991,'213694[1996]',F.Sessionid) LIKE 'floseal%'
THEN '67000634'
ELSE ''
END AS 'Implant Charge',
f.entrytime ,
F.FacilityName AS [FacilityName],
F.[FacilityID] AS [FacilityID],
F.DeliveryTimeOfFirstNeonate
FROM
#TMP_AllNeonates AS F
SELECT * FROM DBO.CHARGECAPTURE ORDER BY [Delivery Date] DESC
--DROP TABLE DBO.CHARGECAPTURE
drop table #FirstNeonate
drop table #TMP_AllNeonates