So, this may be a little too much. Currently I have the following procedure in SQL. I am trying to replicate in Access and having all kinds of issues. Are there any types of programs that will convert from SQL to Access?
USE [Enertia_Working]
GO
/****** Object: StoredProcedure [dbo].[sp_DoiMonitorFirstProdSchedule] Script Date: 3/20/2017 1:31:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Create date: 04/09/2014
-- Description: DOI Monitor Query
-- Start with a complete list of wells that are Oasis Operated and have
-- a first production date within the last 365 days
-- If a well has revenue then exclude else display
-- First Production, WellId, WellName, State, Prospect, Days from First Production
-- and NewDeck Incident Status
-- =============================================
ALTER PROCEDURE [dbo].[sp_DoiMonitorFirstProdSchedule]
-- Add the parameters for the stored procedure here
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#tmp_DeckIncident') IS NOT NULL
DROP TABLE #tmp_DeckIncident
IF OBJECT_ID('tempdb..#tmp_DeckIncidentAction ') IS NOT NULL
DROP TABLE #tmp_DeckIncidentAction
IF OBJECT_ID('tempdb..#tmp_OwnNoPdRevenue ') IS NOT NULL
DROP TABLE #tmp_OwnNoPdRevenue
IF OBJECT_ID('tempdb..#tmp_firstproddate ') IS NOT NULL
DROP TABLE #tmp_firstproddate
IF OBJECT_ID('tempdb..#tmp_table ') IS NOT NULL
DROP TABLE #tmp_table
IF OBJECT_ID('tempdb..#tmp_txn ') IS NOT NULL
DROP TABLE #tmp_txn
---temp table to get first prod date within the last year
SELECT WellCompHid as 'CompId'
, Comp.WellCompTid
, MIN(WcFirstProdDate) as FirstProdDate
INTO #tmp_firstproddate
FROM Enertia.dbo.pdMasWellCompletion Comp
INNER JOIN Enertia.dbo.pdMasWellCompFirstDate CompDt
ON Comp.WellCompTID = CompDt.WellCompTID
WHERE WCFirstProdDate > DATEADD(year,-1,GETDATE())
GROUP BY WellCompHid, Comp.WellCompTID
CREATE TABLE #tmp_OwnNoPdRevenue
(OwnPmtStatCode varchar(10) NULL
,OwnPropHid int)
SELECT TxnDtlTid, H.TxnHdrTid, TxnRevPropHid, TxnDeckCode, TxnAcctDate, TxnProdDate
INTO #tmp_txn
FROM Enertia.dbo.rvTxnDtl d
INNER JOIN Enertia.dbo.rvTxnHdr h
ON D.TxnHdrTID = H.TxnHdrTID
WHERE TxnCorpHid = 1014630 -- 342
INSERT INTO #tmp_OwnNoPdRevenue
SELECT DISTINCT NULL, OwnPropHid
FROM Enertia.dbo.rvBalOwnTxn
WHERE OwnPropHid IN (SELECT T.txnRevPropHid
FROM Enertia.dbo.rvMasDeckDcml d
INNER JOIN (SELECT DISTINCT TxnDeckCode, TxnRevPropHid
--, TxnDeckCode, TxnAcctDate, txnRevPropHid
FROM #tmp_txn D
WHERE TxnAcctDate = (SELECT MAX(TxnAcctDate) FROM #tmp_txn a where txnRevPropHid = D.TxnRevPropHid )
AND TxnProdDate = (SELECT MAX(TxnProdDate) FROM #tmp_txn a where txnRevPropHid = D.TxnRevPropHid )
and TxnDeckCode IS NOT NULL ) T
ON D.DcmlDeckCode = T.TxnDeckCode
where DcmlOwnHid = '1002871'
and DcmlExpired= 0 )
--------------------------------------------------
--This section was altered for performance reasons
--------------------------------------------------
INSERT INTO #tmp_OwnNoPdRevenue
--SELECT DISTINCT OwnPmtStatCode, OwnPropHid
--FROM Enertia.dbo.rvBalOwnTxn
--WHERE (OwnPmtStatCode = 'PD')
-- AND OwnPropHID NOT IN (SELECT OwnPropHid FROM #tmp_OwnNoPdRevenue)
SELECT DISTINCT a.OwnPmtStatCode, a.OwnPropHid
FROM Enertia.dbo.rvBalOwnTxn a
left join #tmp_OwnNoPdRevenue b
on a.OwnPropHID = b.OwnPropHID
WHERE (a.OwnPmtStatCode = 'PD')
and b.OwnPropHID is NULL
--Get A comp, deck, incident, incident actions codes
SELECT DISTINCT
Rev.DeckRevPropHID,
Inc.IncidentTID,
ActionCode,
Inc.RecByDate,
IncidentType,
(SELECT FirstProdDate FROM #tmp_firstproddate WHERE CompId = Rev.DeckRevPropHID) as 'FirstProdDate'
INTO #tmp_DeckIncident
FROM Enertia.dbo.rvMasDeckProp Rev
INNER JOIN Enertia.dbo.aaMasIncidentDeckAprv IncDec
ON Rev.DeckCode = IncDec.DeckCode
INNER JOIN Enertia.dbo.rvMasDeckProp RevDeck
ON RevDeck.DeckCode = IncDec.DeckCode
INNER JOIN Enertia.dbo.aaMasIncident Inc
ON IncDec.IncidentTID = Inc.IncidentTid
INNER JOIN Enertia.dbo.aaMasIncidentAction Act
ON Inc.IncidentTID = Act.IncidentTID
WHERE Inc.IncidentType = 'NEWDECK'
ORDER BY Rev.DeckRevPropHid
--- Goal is to get action code and list of comp that might or might not have incidents
SELECT DeckRevPropHID,
(SELECT Count(1) FROM #tmp_DeckIncident WHERE ActionCode = 'ACCTGREV' AND DeckRevPropHID = temp.DeckRevPropHID) as ActionCount
INTO #tmp_DeckIncidentAction
FROM #tmp_DeckIncident temp
GROUP BY DeckRevPropHID
SELECT
HdrHid
, HdrCode
, HdrName
, Oper.OperatorHid
, FirstProdDate
, (SELECT MapState FROM Enertia.dbo.aaMasMapLocation WHERE MapUseRev = 1 AND MapHdrHid = Comp.HdrHid GROUP BY MapHdrHid, MapState) as 'State'
, (SELECT R1.HdrName AS P
FROM Enertia.dbo.fbMasHdr H
LEFT JOIN Enertia.dbo.fbMasHdrRelation C ON H.HdrHID = C.HdrRelChildHID
LEFT JOIN Enertia.dbo.fbMasHdr R ON C.HdrRelParentHID = R.HdrHID
LEFT JOIN Enertia.dbo.fbMasHdrType T2 ON R.HdrTypeTID = T2.HdrTypeTID
LEFT JOIN Enertia.dbo.fbMasHdrRelation C1 ON R.HdrHID = C1.HdrRelChildHID
LEFT JOIN Enertia.dbo.fbMasHdr R1 ON C1.HdrRelParentHID = R1.HdrHID
LEFT JOIN Enertia.dbo.fbMasHdrType T4 ON R1.HdrTypeTID = T4.HdrTypeTID
WHERE H.HdrHID = Comp.HdrHid
AND T2.HdrTypeCode IN ('Well')
AND T4.HdrTypeCode IN ('Prospect')
) AS 'Prospect'
, (SELECT DATEDIFF (DAY, FirstProdDate,GETDATE()) FROM #tmp_firstproddate WHERE CompId = Comp.HdrHid) as 'DaysFromFirstProdDate'
, ActionCount
, (SELECT ActionCode = CASE WHEN ActionCount = 0 THEN 'Finalized By Accounting'
WHEN ActionCount >= 1 THEN 'Completed By DO'
ELSE 'Not Started' END) NewDeckIncidentStatus
, OwnPmtStatCode
INTO #tmp_table
FROM Enertia.dbo.fbMAsHdr Comp
INNER JOIN Enertia.dbo.pdMasOperated Oper
ON Comp.HdrHid = Oper.OperatedHID
AND HdrTypeTid = '400065' -- Oasis
AND Oper.OperatedEffEnd = '2078-12-31 00:00:00'
AND OperatorHID = 1014630
LEFT JOIN #tmp_OwnNoPdRevenue Rev
ON Rev.OwnPropHid = Comp.HdrHid
INNER JOIN #tmp_firstproddate Fp
ON FP.CompId = Comp.HdrHid
LEFT JOIN #tmp_DeckIncidentAction Act
ON Act.DeckRevPropHID = Comp.HdrHid
--WHERE Comp.HdrCode IN ('15812.1')
AND OwnPmtStatCode IS NULL OR OwnPmtStatCode = ''
ORDER BY HdrCode
SELECT
FirstProdDate
, HdrCode as 'Well ID'
, HdrName as 'Well Name'
, State
, Prospect
, DaysFromFirstProdDate
, NewDeckIncidentStatus
, ActionCount
FROM #tmp_table
WHERE OwnPmtStatCode IS NULL
ORDER BY FirstProdDate asc, HdrName asc
END