INSERT RECORDS INTO EXISTING TABLE

Question

Post reply

INSERT RECORDS INTO EXISTING TABLE

sharonmtowler

Default port

Points: 1495
More actions
August 31, 2016 at 10:03 am

#316847

I have a sp that created a table. I need to add the previous days records into the table daily. I receive the following error no when I add the insert above from
[highlight="#ffff11"]
There is already an object named 'chargecapturecopy' in the database.[/highlight]
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.Valuetime between '08/26/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
[highlight="#ffff11"]INTO dbo.chargecapturecopy[/highlight]
FROM
#TMP_AllNeonates AS F
*******************************************************************************
if I put it at the top I receive error
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'CREATE'.
[/b]
[highlight="#ffff11"]INSERT INTO dbo.chargecapturecopy[/highlight]
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.Valuetime between '08/26/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
please help.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Don't put it at the top of the procedure, put it at the top of the SELECT statement.

The same way you have INSERT INTO #TMP_AllNeonates

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

dbodell Right there with Babe Points: 742 More actions · Answer 2

When you do a SELECT...INTO statement, SQL wants to create the table you are selecting into. It's one of the most common ways to create a table in the first place. If you want to insert records into an existing table, you need to do:

INSERT INTO dbo.chargecapturecopy

(field1,

field 2,

....

)

(SELECT....

FROM

)

The INSERT statement has to list all of the columns of the dbo.chargecapturecopy and the SELECT statement has to list all of the columns that map to those inserted columns in exactly the same order and with the same or compatible data types.

Since you are creating temp tables first, I would still do those first and then once you have all of the source data sets created, do your INSERT INTO statement as shown above.

Hope this helps