July 28, 2014 at 11:52 am
Hello all,
I have recently gotten back into SQL coding after transferring to a different department in the company I work for. Since I have been out of the SQL world for a while, my skill set has basically gone back to that of a newbie. Recently I was given the task to modify an existing code that was created to retrieve all customer accounts that have been isssued a refund of $500 or more. The original code has three temp tables, and is calling data from an external data source. I would first like to modify the code to bring back ANY refunds that were issued no matter what the amount, and I would also like to determine if the customer account that was issued the refund still has any equipment linked to them, because if they do they should not have been issued a refund. I have pasted the original query below. The first issue I have been unable to get past is getting rid of the external data source "call" and modify it to pull from an existing permanent database table. I apologize for the huge code, but I wanted to give you as much information as I can so you can have an understanding of what I am trying to do and what I'm currently working with.
IF OBJECT_ID('TEMPDB..#TMP1A') IS NOT NULL DROP TABLE #TMP1A
IF OBJECT_ID('TEMPDB..#TEMPSUBS1') IS NOT NULL DROP TABLE #TEMPSUBS1
IF OBJECT_ID('TEMPDB..#TEMPSUBS2') IS NOT NULL DROP TABLE #TEMPSUBS2
SELECT
ACCTCORP,
HOUSE,
CUST,
BADATE,
AMOUNT,
ADJRSN,
CASE WHEN BATCH BETWEEN 10000 AND 10999 THEN 'A' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 11000 AND 11999 THEN 'B' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 12000 AND 12999 THEN 'C' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 13000 AND 13999 THEN 'D' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 14000 AND 14999 THEN 'E' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 15000 AND 15999 THEN 'F' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 16000 AND 16999 THEN 'G' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 17000 AND 17999 THEN 'H' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 18000 AND 18999 THEN 'I' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 19000 AND 19999 THEN 'J' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 20000 AND 20999 THEN 'K' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 21000 AND 21999 THEN 'L' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 22000 AND 22999 THEN 'M' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 23000 AND 23999 THEN 'N' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 24000 AND 24999 THEN 'O' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 25000 AND 25999 THEN 'P' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 26000 AND 26999 THEN 'Q' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 27000 AND 27999 THEN 'R' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 28000 AND 28999 THEN 'S' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 29000 AND 29999 THEN 'T' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 30000 AND 30999 THEN 'U' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 31000 AND 31999 THEN 'V' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 32000 AND 32999 THEN 'W' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 33000 AND 33999 THEN 'X' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 34000 AND 34999 THEN 'Y' + RIGHT(BATCH,3)
WHEN BATCH BETWEEN 35000 AND 35999 THEN 'Z' + RIGHT(BATCH,3)
WHEN CONVERT(VARCHAR,BATCH) <= 9999 THEN CONVERT(VARCHAR,BATCH)
ELSE '' END AS 'DDP_BATCH_#',BATCH AS [ORIG_BATCH_#],
--BATCH,
CTLGRP,
CASE WHEN POSTFLAG = ' ' THEN 'No'
ELSE 'Yes' END AS POSTFLAG
INTO #TMP1A
--FROM OPENQUERY(/*INFODDP*/THUNDER_ETL,
FROM OPENQUERY(DSSOPT_DDP,
'
SELECT
BC.ACCTCORP,BD.HOUSE,BD.CUST,BC.BADATE,BD.AMOUNT,BD.ADJRSN,BC.BATCH,BC.CTLGRP,BD.POSTFLAG
FROM DDPDB.IDST_BATCH_CATALOG BC JOIN DDPDB.IDST_BATCH_DETAIL BD
ON(BC.ACCTCORP=BD.ACCTCORP) AND (BC.BATCH=BD.BATCH)
WHERE BD.ACCTCORP IN (''1624'', ''1638'', ''1641'', ''1643'', ''1710'',''01105'',''01636'',''01719'',''09587'',''15515'',''19204'')
AND BC.CTLGRP = ''REFND'' AND BD.AMOUNT >= ''500.00''
')
CREATE TABLE #TEMPSUBS1 (
AcctCorp INT,
House INT,
Cust TINYINT,
Ftax TINYINT,
BADR CHAR(1) DEFAULT ' '
)
INSERT INTO #TEMPSUBS1 (AcctCorp,House,Cust,Ftax,BADR)
SELECTA.ACCTCORP,A.HOUSE,A.CUST,H.Ftax,C.BADR
FROM#TMP1A A INNER JOIN [INFODDPFlorida].[ggs].[IDST_CUSTOMER] C (NOLOCK)
ONA.ACCTCORP=C.ACCTCORP AND
A.HOUSE=C.HOUSE AND
A.CUST=C.CUST
INNER JOIN [INFODDPFlorida].[ggs].[IDST_HOUSE] H (NOLOCK)
ONC.ACCTCORP=H.ACCTCORP AND
C.HOUSE=H.HOUSE
CREATE TABLE #TEMPSUBS2 (
ACCTCORP INT,
HOUSE INT,
CUST TINYINT,
CARE_OF_ADDRESS_LINE1 VARCHAR(50),
ADDRESS_LINE2 VARCHAR(50),
ADDRESS_LINE3 VARCHAR(50),
ADDRESS_LINE4 VARCHAR(50)
)
INSERT INTO #TEMPSUBS2
(ACCTCORP,HOUSE,CUST,CARE_OF_ADDRESS_LINE1,ADDRESS_LINE2,ADDRESS_LINE3,ADDRESS_LINE4)
SELECTA.ACCTCORP,A.HOUSE,A.CUST,' ',
INFODDPFlorida.dbo.udf_ConcatAddress(H.STNUM,H.FRACT,H.DIR,H.NAME,'','') + ' ' + H.APT + ' ' + H.APTN AS [ADDRESS_LINE2],
Z.CITY + ' ' + Z.STATE AS [ADDRESS_LINE3],
LEFT(H.ZIPCODE,5) + '-' + H.ZIP4 AS [ADDRESS_LINE4]
FROM#TEMPSUBS1 A INNER JOIN INFODDPFlorida.GGS.IDST_HOUSE H (NOLOCK)
ONA.ACCTCORP=H.ACCTCORP AND
A.HOUSE=H.HOUSE
LEFT JOIN INFODDPFlorida.GGS.IDST_ZIP Z (NOLOCK)
ONH.ACCTCORP=Z.ACCTCORP AND
H.ZIPCODE BETWEEN Z.ZIPBEG AND Z.ZIPEND
WHEREA.BADR IN (' ','1')
INSERT INTO #TEMPSUBS2
(ACCTCORP,HOUSE,CUST,CARE_OF_ADDRESS_LINE1,ADDRESS_LINE2,ADDRESS_LINE3,ADDRESS_LINE4)
SELECTA.ACCTCORP,A.HOUSE,A.CUST,
ISNULL(G.BIL1,' ') AS [BILLING ADDR LINE 1],
ISNULL(G.BIL2,' ') AS [BILLING ADDR LINE 2],
ISNULL(G.BIL3,' ') AS [BILLING ADDR LINE 3],
ISNULL(G.BIL4,' ') AS [BILLING ADDR LINE 4]
FROM#TEMPSUBS1 A LEFT JOIN INFODDPFlorida.GGS.IDST_CUST_BILLING_ADDRESS G (NOLOCK)
ONA.ACCTCORP=G.ACCTCORP AND
A.HOUSE=G.HOUSE AND
A.CUST=G.CUST
WHEREA.BADR IN ('2')
SELECTDISTINCT CONVERT(VARCHAR(8), GETDATE(), 1) AS ReportDate,
RIGHT('00000' + CAST(A.ACCTCORP AS VARCHAR),5) +
RIGHT('000000' + CAST(A.HOUSE AS VARCHAR), 6) +
RIGHT('00' + CAST(A.CUST AS VARCHAR),2) AS CorpHouseCust,
A.AcctCorp,A.House,A.Cust,H.Ftax,
CASE WHEN C.STAT = '1' THEN 'PendingInstall'
WHEN C.STAT = '2' THEN 'PendingRestart'
WHEN C.STAT = '3' THEN 'PendingChgOfSvc'
WHEN C.STAT = '4' THEN 'PendingDisco'
WHEN C.STAT = '5' THEN 'Active'
WHEN C.STAT = '6' THEN 'Disconnect'
WHEN C.STAT = '7' THEN 'CancelledInstall'
ELSE '' END AS 'CurrentCustomerStatus',
--C.DRSN,
CASE WHEN C.CUSTOMER_TYPE = 'B' THEN 'Business'
WHEN C.CUSTOMER_TYPE = 'R' THEN 'Residential'
ELSE '' END AS 'CustomerType',
C.AnnivDay,
C.DDP_Cycle AS 'Cycle',
ISNULL(CONVERT(VARCHAR(10),AA.BADATE,120),' ') AS BatchDate,
--AA.BADATE,
AA.AMOUNT AS 'RefundAmt',
AA.ADJRSN,AA.[DDP_BATCH_#],AA.[ORIG_BATCH_#],AA.CTLGRP,AA.POSTFLAG,
(C.LNAME + ',' + C.FNAME) AS CustomerName,
CAST(C.RAREACD AS VARCHAR(3)) + C.RPHON AS ResPhone,
RIGHT(C.BPHONE,3) + LEFT(C.BPHONE,7) AS BusinessPhone,
INFODDPFlorida.dbo.udf_ConcatAddress(H.STNUM,H.FRACT,H.DIR,H.NAME,'','') AS ServiceAddress,
H.APT + ' ' + H.APTN AS ServiceAptAndNumber,Z.CITY AS ServiceCity,
Z.STATE AS ServiceState,H.ZIPCODE + '-' + H.ZIP4 AS ServiceZipAndZip4,
A.CARE_OF_ADDRESS_LINE1 AS MailToCareOfAddressLine1,
A.ADDRESS_LINE2 AS MailToAddressLine2,
A.ADDRESS_LINE3 AS MailToAddressLine3,
A.ADDRESS_LINE4 AS MailToAddressLine4,
D.LONGDESC AS DwellingType,H.COMPLEX AS ComplexCode,
ISNULL(HC.CNAME, ' ') AS ComplexName,
C.RATE AS MonthlyRate,
(C.CUR+C.C30+C.C60+C.C90+C.C120) AS TotalBalance --,
FROM#TEMPSUBS2 A INNER JOIN INFODDPFlorida.GGS.IDST_CUSTOMER C (NOLOCK)
ONA.ACCTCORP=C.ACCTCORP AND
A.HOUSE=C.HOUSE AND
A.CUST=C.CUST
INNER JOIN #TMP1A AA
ONC.ACCTCORP=AA.ACCTCORP AND
C.HOUSE=AA.HOUSE AND
C.CUST=AA.CUST
INNER JOIN INFODDPFlorida.GGS.IDST_HOUSE H (NOLOCK)
ONC.ACCTCORP=H.ACCTCORP AND
C.HOUSE=H.HOUSE
LEFT JOIN INFODDPFlorida.GGS.IDST_DWELLING D (NOLOCK)
ONH.ACCTCORP=D.ACCTCORP AND
H.DWELL=D.CODE
LEFT JOIN INFODDPFlorida.GGS.IDST_COMPLEX HC (NOLOCK)
ONH.ACCTCORP=HC.ACCTCORP AND
H.COMPLEX=HC.COMPLEX
LEFT JOIN INFODDPFlorida.GGS.IDST_ZIP Z (NOLOCK)
ONH.ACCTCORP=Z.ACCTCORP AND
H.ZIPCODE BETWEEN Z.ZIPBEG AND Z.ZIPEND
WHEREC.DRSN NOT IN ('9','G','N')
July 31, 2014 at 4:16 pm
Since none of the tables in any of those queries are available to us (the forum users), it's impossible to give much useful insight on what needs to be done to replace the openquery call to a table (or series of tables).
If you just are looking for the basic syntax needed to select from a table, simply replace the "OPENQUERY(...)" with your table(s).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply