Create Dynamic Global Temp Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Create Dynamic Global Temp Table

Author

Topic

Diantan
Starting Member

11 Posts

Posted - 2010-01-05 : 06:20:23

Hi,
Thank you for reading my question.
I had created a Store Procedure in SQL2000 which allow me to auto trigger the report Named Suspenstion Notice.
Whenever user key in the Credit Limit or Term of the customer is over, it will generate the report.
This report is running pretty fine. Until the time we had so many transaction throughout the region with different Depot, the report sometimes clash with another Suspension Notice, which report for customer A will capture the
infomation of the Customer B. I'm trying to generate the Dynamic Global Temp Table but it give me error, where it cannot recognize the ##Temp+@custid. Please help.

This is the Store Procedure

CREATE PROCEDURE [dbo].[SP_SUSPNOTICEWetMkt]
@DepotCode As VarChar(5),
@Custcode As VarChar(10),
@FrDate As smalldatetime,
@ToDate As smalldatetime,
@CurrDate As smalldatetime,
@FrRRno As VarChar(10)

AS

--====================================================================
--Get SuspdMonth
SELECT DISTINCT FCUSTCODE, year(FINVDATE)*100+month(FINVDATE) AS 'MTH', 0 AS MD INTO #tbsuspdMTH FROM TBINV
WHERE year(FINVDATE)*100+month(FINVDATE) BETWEEN year(@FrDate)*100+month(@FrDate) AND year(@CurrDate)*100+month(@CurrDate)
AND FDEPOTCODE=@DepotCode AND FUPDSTAT='A' AND FCUSTCODE=@Custcode and fstatus<>'X'
UNION
SELECT DISTINCT FCUSTCODE, year(FINVDATE)*100+month(FINVDATE) AS A, DAY(FINVDATE) AS MD FROM TBINV
WHERE year(FINVDATE)*100+month(FINVDATE)=year(@CurrDate)*100+month(@CurrDate)
AND FDEPOTCODE=@DepotCode AND FUPDSTAT='A' AND FCUSTCODE=@Custcode and fstatus<>'X'
UNION
SELECT DISTINCT M.fTraderCode AS fcustcode, year(fsuspdate)*100+month(fsuspdate) AS 'MTH',DAY(fsuspdate) AS MD
FROM TBDOCSUSPMAS M INNER JOIN TBDOCSUSPDET D ON M.FRRNO=D.FRRNO
WHERE M.FRRNO=@FrRRno AND M.FUPDSTAT='A' AND D.FUPDSTAT='A'
UNION
SELECT DISTINCT FCUSTCODE, year(fcndate)*100+month(fcndate) AS 'MTH', 0 AS MD FROM TbCnMaster
WHERE year(fcndate)*100+month(fcndate) BETWEEN year(@FrDate)*100+month(@FrDate) AND year(@CurrDate)*100+month(@CurrDate)
AND FDEPOTCODE=@DepotCode AND ftype='A' and fupdstat='A' AND fstatus<>'X' AND fcustcode=@Custcode
UNION
SELECT DISTINCT FCUSTCODE, year(fcndate)*100+month(fcndate) AS 'MTH', DAY(fcndate) AS MD FROM TbCnMaster
WHERE year(fcndate)*100+month(fcndate)=year(@CurrDate)*100+month(@CurrDate)
AND FDEPOTCODE=@DepotCode AND ftype='A' and fupdstat='A' AND fstatus<>'X' AND fcustcode=@Custcode
UNION
SELECT DISTINCT FCUSTCODE, year(fdndate)*100+month(fdndate) AS 'MTH', 0 AS MD FROM TBdnMASTER
WHERE year(fdndate)*100+month(fdndate) between year(@FrDate)*100+month(@FrDate) AND year(@CurrDate)*100+month(@CurrDate)
AND FDEPOTCODE=@DepotCode AND fupdstat='A' AND fstatus<>'X' AND fcustcode=@Custcode
UNION
SELECT DISTINCT FCUSTCODE, year(fdndate)*100+month(fdndate) AS 'MTH', DAY(fdndate) AS MD FROM TBdnMASTER
WHERE year(fdndate)*100+month(fdndate)= year(@CurrDate)*100+month(@CurrDate)
AND FDEPOTCODE=@DepotCode AND fupdstat='A' AND fstatus<>'X' AND fcustcode=@Custcode
UNION
SELECT DISTINCT FCUSTCODE, year(ftrdate)*100+month(ftrdate) AS 'MTH', 0 AS MD FROM tbTrmaster
WHERE year(ftrdate)*100+month(ftrdate) BETWEEN year(@FrDate)*100+month(@FrDate) AND year(@CurrDate)*100+month(@CurrDate)
AND FDEPOTCODE=@DepotCode AND fupdstat='A' AND fstatus<>'X' AND fcustcode=@Custcode
UNION
SELECT DISTINCT FCUSTCODE, year(ftrdate)*100+month(ftrdate) AS 'MTH', DAY(ftrdate) AS MD FROM tbtrmaster
WHERE year(ftrdate)*100+month(ftrdate)=year(@CurrDate)*100+month(@CurrDate)
AND FDEPOTCODE=@DepotCode AND FUPDSTAT='A' AND FCUSTCODE=@Custcode AND (FBOUNCE IS NULL OR FBOUNCE<>'Y')
--====================================================================
--Generate Every Acc Opening Balance
Select fcustcode,year(@FrDate)*100+month(@FrDate) AS 'MTH',32 AS MD,sum(ftotal) as Amt INTO #tbsuspBAL From tbinv
WHERE fupdstat='A' and year(finvdate)*100+month(finvdate)< year(@FrDate)*100+month(@FrDate) and fstatus='P' and FDEPOTCODE=@DepotCode AND fcustcode=@Custcode
group by fcustcode
UNION
Select fcustcode,year(@FrDate)*100+month(@FrDate) AS 'MTH',33 AS MD,sum(fdnamount) as Amt from tbdnmaster
WHERE fupdstat='A' and year(fdndate)*100+month(fdndate)< year(@FrDate)*100+month(@FrDate) and fstatus='P' and FDEPOTCODE=@DepotCode AND fcustcode=@Custcode
group by fcustcode
UNION
Select fcustcode,year(@FrDate)*100+month(@FrDate) AS 'MTH',34 AS MD,sum(fjnlamount) as Amt from tbjournal
WHERE fupdstat='A' and year(fjnldate)*100+month(fjnldate)< year(@FrDate)*100+month(@FrDate) and fstatus='P' and ftype='D' and FDEPOTCODE=@DepotCode and fcustcode=@Custcode
group by fcustcode
UNION
Select fcustcode,year(@FrDate)*100+month(@FrDate) AS 'MTH',35 AS MD,sum(fcramount) AS Amt from tbcrmaster
WHERE fupdstat='A' and year(fcrdate)*100+month(fcrdate)< year(@FrDate)*100+month(@FrDate) and fstatus='P' and FDEPOTCODE=@DepotCode AND fcustcode=@Custcode
group BY fcustcode
UNION
Select fcustcode,year(@FrDate)*100+month(@FrDate) AS 'MTH',36 AS MD,sum(ftramount)*-1 as Amt from tbtrmaster
WHERE fupdstat='A' and year(ftrdate)*100+month(ftrdate)< year(@FrDate)*100+month(@FrDate) and fstatus='P' and FDEPOTCODE=@DepotCode and fcustcode=@Custcode
group by fcustcode 
UNION
Select fcustcode,year(@FrDate)*100+month(@FrDate) AS 'MTH',37 AS MD,sum(fcnamount)*-1 as Amt from tbcnmaster
WHERE fupdstat='A' and year(fcndate)*100+month(fcndate)< year(@FrDate)*100+month(@FrDate) and fstatus='P' and ftype<>'R' and FDEPOTCODE=@DepotCode and fcustcode=@Custcode
group by fcustcode
UNION
Select fcustcode,year(@FrDate)*100+month(@FrDate) AS 'MTH',38 AS MD,sum(fjnlamount)*-1 as Amt from tbjournal
WHERE fupdstat='A' and year(fjnldate)*100+month(fjnldate)< year(@FrDate)*100+month(@FrDate) and fstatus='P' and ftype='C' and FDEPOTCODE=@DepotCode and fcustcode=@Custcode
group by fcustcode
--====================================================================
--Generate Total Opening Balance
INSERT INTO #tbsuspBAL
SELECT FCUSTCODE, MTH, 0 AS MD, SUM(AMT) AS BALBF FROM #tbsuspBAL
GROUP BY FCUSTCODE, MTH
--====================================================================
--Generate Opening balance if there no transaction done at all at that month
INSERT INTO #tbsuspdMTH
select fcustcode, MTH, MD from #tbsuspBAL where MD=0 and MTH NOT IN(
select mth from #tbsuspdMTH)
--====================================================================
--Generate Monthly & Daily Total Sales
SELECT FCUSTCODE, year(FINVDATE)*100+month(FINVDATE) AS 'MTH', 0 AS MD, SUM(FTOTAL) AS Amt INTO #tbsuspSALES FROM TBINV
WHERE year(FINVDATE)*100+month(FINVDATE) BETWEEN year(@FrDate)*100+month(@FrDate) AND year(@ToDate)*100+month(@ToDate)
AND FDEPOTCODE=@DepotCode AND FUPDSTAT='A' AND FCUSTCODE=@Custcode and fstatus<>'X'
GROUP BY year(FINVDATE)*100+month(FINVDATE), FCUSTCODE
UNION
SELECT FCUSTCODE, year(FINVDATE)*100+month(FINVDATE) AS 'MTH', DAY(FINVDATE) AS MD, SUM(FTOTAL) AS Amt FROM TBINV
WHERE year(FINVDATE)*100+month(FINVDATE)=year(@CurrDate)*100+month(@CurrDate)
AND FDEPOTCODE=@DepotCode AND FUPDSTAT='A' AND FCUSTCODE=@Custcode and fstatus<>'X'
GROUP BY DAY(FINVDATE), year(FINVDATE)*100+month(FINVDATE), FCUSTCODE
--====================================================================
--Get New Suspended Sales
SELECT M.FRRNO, B.FDODATE, M.fTraderCode AS fcustcode, year(fsuspdate)*100+month(fsuspdate) AS 'MTH',DAY(fsuspdate) AS MD, SUM(fqty*funitprice) AS Amt INTO #tbsuspCur
FROM TBDOCSUSPMAS M INNER JOIN TBDOCSUSPDET D ON M.FRRNO=D.FRRNO
INNER JOIN TBDO B ON D.fDocno=B.FDONO
WHERE year(fsuspdate)*10000+month(fsuspdate)*100+day(fsuspdate)=year(@CurrDate)*10000+month(@CurrDate)*100+day(@CurrDate)
AND M.FUPDSTAT='A' AND D.FUPDSTAT='A' AND M.fTraderCode=@Custcode AND M.fDepotcode=@DepotCode
AND B.fcustcode=@Custcode AND B.fUpdstat='A' AND B.fDepotcode=@DepotCode AND M.fStatus='S'
GROUP BY M.FRRNO, M.fTraderCode, year(fsuspdate)*100+month(fsuspdate), DAY(fsuspdate), B.FDODATE
UNION
select FDONO, FDODATE, fcustcode, year(FDODATE)*100+month(FDODATE) AS 'MTH',DAY(FDODATE) AS MD, fTotal
from TBDO WHERE 
fcustcode=@Custcode AND fUpdstat='A' AND fDepotcode=@DepotCode AND FDODATE=@CurrDate
AND fDono NOT IN(
SELECT DISTINCT fDocno FROM TBDOCSUSPMAS M INNER JOIN TBDOCSUSPDET D ON M.FRRNO=D.FRRNO
WHERE year(fsuspdate)*10000+month(fsuspdate)*100+day(fsuspdate)=year(@CurrDate)*10000+month(@CurrDate)*100+day(@CurrDate)
AND M.FUPDSTAT='A' AND D.FUPDSTAT='A' AND M.fTraderCode=@Custcode AND M.FDEPOTCODE=@DepotCode AND M.fStatus<>'J'
)
ORDER BY M.FRRNO DESC
--====================================================================
--Generate Total Current New Order
INSERT INTO #TBSUSPCUR
SELECT 0, null, FCUSTCODE, MTH, MD, SUM(AMT) AS AMT FROM #TBSUSPCUR
GROUP BY FCUSTCODE, MTH, MD
--====================================================================
--Get Monthly & Daily Total Collection
SELECT FCUSTCODE, year(ftrdate)*100+month(ftrdate) AS 'MTH', 0 AS MD, SUM(ftramount) AS Amt INTO #tbsuspTR FROM tbtrmaster
WHERE year(ftrdate)*100+month(ftrdate) BETWEEN year(@FrDate)*100+month(@FrDate) AND year(@ToDate)*100+month(@ToDate)
AND FDEPOTCODE=@DepotCode AND FUPDSTAT='A' AND FCUSTCODE=@Custcode AND FSTATUS<>'X' AND (FBOUNCE IS NULL OR FBOUNCE<>'Y')
GROUP BY year(ftrdate)*100+month(ftrdate), FCUSTCODE
UNION
SELECT FCUSTCODE, year(ftrdate)*100+month(ftrdate) AS 'MTH', DAY(ftrdate) AS MD, SUM(ftramount) AS Amt FROM tbtrmaster
WHERE year(ftrdate)*100+month(ftrdate)=year(@CurrDate)*100+month(@CurrDate)
AND FDEPOTCODE=@DepotCode AND FUPDSTAT='A' AND FCUSTCODE=@Custcode AND FSTATUS<>'X' AND (FBOUNCE IS NULL OR FBOUNCE<>'Y')
GROUP BY DAY(ftrdate), year(ftrdate)*100+month(ftrdate), FCUSTCODE
--====================================================================
--Generate Every Acc Total Outstanding
SELECT fcustcode, year(@FrDate)*100+month(@FrDate) AS 'MTH', 32 AS MD, sum(fosamt) as Amt INTO #tbsuspOUT FROM tbinv
WHERE fdepotcode=@DepotCode and  fupdstat='A' and fstatus<>'X' and fcustcode=@Custcode
AND DATEDIFF(mm, finvdate,getDate())>8
GROUP BY fcustcode
UNION
select fcustcode, year(@FrDate)*100+month(@FrDate) AS 'MTH', 33 AS MD, sum(fdnosamt) as Amt from tbdnmaster
where fdepotcode=@DepotCode and  fupdstat='A' and fstatus <>'X' and fcustcode=@Custcode
and DATEDIFF(mm, fdndate,getDate())>8
GROUP BY fcustcode
UNION
select fcustcode, year(@FrDate)*100+month(@FrDate) AS 'MTH', 34 AS MD, sum(fjnlunapplyamt) as Amt from tbjournal
where fdepotcode=@DepotCode and  fupdstat='A' and fstatus<>'X' and ftype='D' and fcustcode=@Custcode
and DATEDIFF(mm, fjnldate,getDate())>8
GROUP BY fcustcode
UNION
select fcustcode, year(@FrDate)*100+month(@FrDate) AS 'MTH', 35 AS MD, sum(funapplyamt)*-1 as Amt from tbTrmaster
where fdepotcode=@DepotCode and  fupdstat='A' and fstatus<>'X' and fcustcode=@Custcode
and DATEDIFF(mm, ftrdate,getDate())>8
GROUP BY fcustcode
UNION
select fcustcode, year(@FrDate)*100+month(@FrDate) AS 'MTH', 36 AS MD, sum(fcnunapplyamt)*-1 as Amt from tbcnmaster
where fdepotcode=@DepotCode and  fupdstat='A' and fstatus<>'X' and ftype='A' and fcustcode=@Custcode
and DATEDIFF(mm, fcndate,getDate())>8
GROUP BY fcustcode
UNION
select fcustcode, year(@FrDate)*100+month(@FrDate) AS 'MTH', 37 AS MD, sum(fjnlunapplyamt)*1 as Amt from tbjournal
where fdepotcode=@DepotCode and  fupdstat='A' and fstatus<>'X' and ftype='C' and fcustcode=@Custcode
and DATEDIFF(mm, fjnldate,getDate())>8
GROUP BY fcustcode
UNION
SELECT FCUSTCODE, year(FINVDATE)*100+month(FINVDATE) AS 'MTH', 40 AS MD, SUM(fosamt) AS Amt FROM tbinv
WHERE year(FINVDATE)*100+month(FINVDATE) BETWEEN year(@FrDate)*100+month(@FrDate) AND year(@CurrDate)*100+month(@CurrDate)
AND FDEPOTCODE=@DepotCode AND FUPDSTAT ='A' and fstatus<>'X' and fcustcode=@Custcode
GROUP BY year(FINVDATE)*100+month(FINVDATE), FCUSTCODE
UNION
SELECT FCUSTCODE, year(ftrdate)*100+month(ftrdate) AS 'MTH', 41 AS MD, sum(funapplyamt)*-1 as Amt FROM tbTrmaster
WHERE year(ftrdate)*100+month(ftrdate) BETWEEN year(@FrDate)*100+month(@FrDate) AND year(@CurrDate)*100+month(@CurrDate)
AND FDEPOTCODE=@DepotCode AND fupdstat='A' AND fstatus<>'X' AND fcustcode=@Custcode
GROUP BY year(ftrdate)*100+month(ftrdate), FCUSTCODE
UNION
SELECT FCUSTCODE, year(fcndate)*100+month(fcndate) AS 'MTH', 42 AS MD, SUM(fcnunapplyamt)*-1 AS Amt FROM TbCnMaster
WHERE year(fcndate)*100+month(fcndate) BETWEEN year(@FrDate)*100+month(@FrDate) AND year(@CurrDate)*100+month(@CurrDate)
AND FDEPOTCODE=@DepotCode AND ftype ='A' and fupdstat='A' AND fstatus<>'X' AND fcustcode=@Custcode AND fcnunapplyamt<>0
GROUP BY year(fcndate)*100+month(fcndate), FCUSTCODE
--====================================================================
--Generate Total Oustanding Amt
INSERT INTO #tbsuspOUT
SELECT FCUSTCODE, MTH, 0 AS MD, SUM(AMT) AS AMT FROM #tbsusPOUT
GROUP BY FCUSTCODE, MTH
--====================================================================
--Get PD Cheque
SELECT FCUSTCODE, year(ftrdate)*100+month(ftrdate) AS 'MTH', 0 AS MD, SUM(ftramount) AS Amt INTO #tbsuspPDQ FROM tbtrmaster
WHERE ftype='Q' AND FDEPOTCODE=@DepotCode and DATEDIFF(dd,fchequedate,getDate())<0 and fupdstat='A' and fstatus<>'X' AND fcustcode=@Custcode
GROUP BY year(ftrdate)*100+month(ftrdate), FCUSTCODE
--====================================================================
--Generate Every Acc Monthly & Daily Total Adjustment
SELECT FCUSTCODE, year(fcndate)*100+month(fcndate) AS 'MTH', 32 AS MD, SUM(fcnamount) AS Amt INTO #tbsuspADJ FROM TbCnMaster
WHERE year(fcndate)*100+month(fcndate) BETWEEN year(@FrDate)*100+month(@FrDate) AND year(@ToDate)*100+month(@ToDate)
AND FDEPOTCODE=@DepotCode AND ftype ='A' and fupdstat='A' AND fstatus<>'X' AND fcustcode=@Custcode
GROUP BY year(fcndate)*100+month(fcndate), FCUSTCODE
UNION
SELECT FCUSTCODE, year(fdndate)*100+month(fdndate) AS 'MTH', 33 AS MD, SUM(fdnamount)*-1 AS Amt FROM TBdnMASTER
WHERE year(fdndate)*100+month(fdndate) BETWEEN year(@FrDate)*100+month(@FrDate) AND year(@ToDate)*100+month(@ToDate)
AND FDEPOTCODE=@DepotCode AND fupdstat='A' AND fstatus<>'X' AND fcustcode=@Custcode
GROUP BY year(fdndate)*100+month(fdndate), FCUSTCODE
UNION
SELECT FCUSTCODE, year(fcndate)*100+month(fcndate) AS 'MTH', DAY(fcndate) AS MD, SUM(fcnamount) AS Amt FROM TbCnMaster
WHERE year(fcndate)*100+month(fcndate)=year(@CurrDate)*100+month(@CurrDate)
AND FDEPOTCODE=@DepotCode AND ftype ='A' and fupdstat='A' AND fstatus<>'X' AND fcustcode=@Custcode
GROUP BY DAY(fcndate), year(fcndate)*100+month(fcndate), FCUSTCODE
UNION
SELECT FCUSTCODE, year(fdndate)*100+month(fdndate) AS 'MTH', DAY(fdndate) AS MD, SUM(fdnamount)*-1 AS Amt FROM TBdnMASTER
WHERE year(fdndate)*100+month(fdndate)=year(@CurrDate)*100+month(@CurrDate)
AND FDEPOTCODE=@DepotCode AND fupdstat='A' AND fstatus<>'X' AND fcustcode=@Custcode
GROUP BY DAY(fdndate), year(fdndate)*100+month(fdndate), FCUSTCODE
--====================================================================
--Get All Monthly Total Adjustment
INSERT INTO #tbsuspADJ
select FCUSTCODE, MTH, 0 AS MD, SUM(AMT) AS AMT from #tbsuspADJ
where MD>31 GROUP BY FCUSTCODE, MTH
--====================================================================
--CONVERT DAY TO NEGATIVE VALUE FOR AMT IS NEGATIVE & DAY IS VALID
UPDATE #tbsuspADJ SET MD=MD*-1 WHERE EXISTS (
SELECT FCUSTCODE, MTH, MD, SUM(AMT) AS AMT from #tbsuspADJ
WHERE MD<32
GROUP BY FCUSTCODE, MTH, MD)
AND AMT<0
--====================================================================
--UPDATE TOTAL AMT REGARDLESS OF -MD OR +MD
UPDATE A
SET A.AMT=CASE WHEN A.MTH=B.MTH AND A.MD=B.MD THEN B.AMT END
FROM #tbsuspADJ A INNER JOIN (
SELECT FCUSTCODE, MTH, ABS(MD) AS MD, SUM(AMT) AS AMT from #tbsuspADJ
WHERE MD<32
GROUP BY FCUSTCODE, MTH, ABS(MD)) AS B ON A.MTH=B.MTH AND A.MD=B.MD
WHERE A.AMT>0
--====================================================================
--COMPILE INTO ONE TABLE
SELECT #tbsuspdMTH.*, #tbsuspBAL.Amt AS BALBF, #tbsuspSALES.Amt AS Sales, #tbsuspCur.FRRNO, #tbsuspCur.fDOdate, #tbsuspCur.Amt AS Cur, #tbsuspTR.Amt AS TR, #tbsuspADJ.Amt AS ADJ, #tbsuspOUT.Amt AS Ost, #tbsuspPDQ.Amt AS PDQ INTO [##tbsuspNotice&@Custcode]
FROM #tbsuspdMTH LEFT JOIN #tbsuspBAL ON #tbsuspdMTH.FCUSTCODE=#tbsuspBAL.FCUSTCODE AND #tbsuspdMTH.MTH=#tbsuspBAL.MTH AND #tbsuspdMTH.MD=#tbsuspBAL.MD
LEFT JOIN #tbsuspSALES ON #tbsuspdMTH.FCUSTCODE=#tbsuspSALES.FCUSTCODE AND #tbsuspdMTH.MTH=#tbsuspSALES.MTH AND #tbsuspdMTH.MD=#tbsuspSALES.MD
LEFT JOIN #tbsuspCur ON #tbsuspdMTH.FCUSTCODE=#tbsuspCur.FCUSTCODE AND #tbsuspdMTH.MTH=#tbsuspCur.MTH AND #tbsuspdMTH.MD=#tbsuspCur.MD
LEFT JOIN #tbsuspTR ON #tbsuspdMTH.FCUSTCODE=#tbsuspTR.FCUSTCODE AND #tbsuspdMTH.MTH=#tbsuspTR.MTH AND #tbsuspdMTH.MD=#tbsuspTR.MD
LEFT JOIN #tbsuspADJ ON #tbsuspdMTH.FCUSTCODE=#tbsuspADJ.FCUSTCODE AND #tbsuspdMTH.MTH=#tbsuspADJ.MTH AND #tbsuspdMTH.MD=#tbsuspADJ.MD
LEFT JOIN #tbsuspOUT ON #tbsuspdMTH.FCUSTCODE=#tbsuspOUT.FCUSTCODE AND #tbsuspdMTH.MTH=#tbsuspOUT.MTH AND #tbsuspdMTH.MD=#tbsuspOUT.MD
LEFT JOIN #tbsuspPDQ ON #tbsuspdMTH.FCUSTCODE=#tbsuspPDQ.FCUSTCODE AND #tbsuspdMTH.MTH=#tbsuspPDQ.MTH AND #tbsuspdMTH.MD=#tbsuspPDQ.MD
--====================================================================
--CLEAR OTHER FIELDS
UPDATE ##tbsuspNotice SET FRRNO=NULL WHERE FRRNO='0'
GO

This is the code written in ASP, whenever the above Store Procedure finish executing, the ASP code will continue to retrieve the infomation from Global Table so that it able to write into the permanent table.

set rs = server.CreateObject("ADODB.Recordset")
SQL = "select m.fShortName, m.fCreditTerm, m.fCreditLimit, m.fgraceperiod, s.* from ##tbsuspNotice"&custid&" s " & _
      "inner join tbcustomer m on s.fCustCode=m.fcustID " & _
      "where m.fstatus='A' and m.fupdstat='A' ORDER BY MTH, MD"

rs.Open SQL, objconn, 3, 1


SQL = "SELECT * FROM tbdocsuspmas where frrno='"&rrno&"' AND FDEPOTCODE='"&dcode&"' AND FUPDSTAT='A'"
uptbDocsuspmasRs.Open SQL, objConn, 2, 3
if not uptbDocsuspmasRs.EOF then
    uptbDocsuspmasRs("fsuspnotice") = lsr
    uptbDocsuspmasRs.Update
end if

lsr is the HTML code which will be updated into fsuspnotice field so that whenever use retrieve the report, it is permanently there.
Please help as i do not know solution for this problem. Thank you.
I will try to post the image if the above image fail.

DT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 03:47:53

can i ask need of appending custcode to global temp table name? is it enough to hold a customer code column in table to identify which customer's data it represents?

Diantan
Starting Member

11 Posts

Posted - 2010-01-06 : 05:37:58

That is not necessary, but in my opinion that is the easiest way to avoid duplication, I think the error might occur when the system try to update fsuspnotice field in tbdocsuspmas Table after the Stored Procedure completed, the report capture the wrong custcode.

DT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 05:57:15

sorry didnt get the last part. can you elaborate?

Diantan
Starting Member

11 Posts

Posted - 2010-01-06 : 06:54:11

This is because the design of the report is, it will generate a Global Temp Report at the end of the Stored Procedure, and the system will continue running the ASP script which is this part

***************************************************************************************
set rs = server.CreateObject("ADODB.Recordset")
SQL = "select m.fShortName, m.fCreditTerm, m.fCreditLimit, m.fgraceperiod, s.* from ##tbsuspNotice"&custid&" s " & _
"inner join tbcustomer m on s.fCustCode=m.fcustID " & _
"where m.fstatus='A' and m.fupdstat='A' ORDER BY MTH, MD"

rs.Open SQL, objconn, 3, 1
**************************************************************************************
The Global Temp Table will join with Customer Table so that it will retrieve the info like CustName and go on with hard cord HTML Table String & at last it will update the tbdocsuspmas Table's fsuspnotice field, which the Data Type is Text, length 16.

DT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 07:00:15

so even then are you not able to add cust code field in temp table and then filter based on that in asp query?

Diantan
Starting Member

11 Posts

Posted - 2010-01-06 : 21:17:00

I had applied every custcode to the temp table, as listed above, @Custcode, but the when come to final result, the report has the different result. This case is happen once or twice in a day, out of total 100+ report. The rest of the report is running fine.

DT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 01:16:24

sorry still I'm unable to understand your exact scenario. May be you could explain with some sample data to illustrate problem

Diantan
Starting Member

11 Posts

Posted - 2010-01-07 : 02:16:17

This is what I got from my Global Temp Table
custcod mth MD BalB/F Sales frrno Cur TR Adj Ost PDQ
F0031 200902 0 737.70 930.00 NULL NULL 727.20 NULL .0000 NULL
F0031 200903 0 NULL 330.00 NULL NULL NULL NULL .0000 NULL
F0031 200904 0 NULL NULL NULL NULL 930.00 NULL .0000 NULL
F0031 200905 0 NULL 718.00 NULL NULL 330.00 26.00 .0000 NULL
F0031 200907 0 NULL 873.00 NULL NULL 692.00 NULL 873.00 NULL
F0031 200908 0 NULL NULL NULL NULL NULL 65.50 -65.50 NULL
F0031 200909 0 NULL 708.00 NULL NULL NULL NULL 708.00 NULL
F0031 200910 0 NULL NULL NULL NULL NULL NULL 480.00 NULL
F0031 200910 8 NULL NULL NULL NULL NULL NULL NULL NULL
F0031 200910 9 NULL 480.00 NULL 860.00 NULL NULL NULL NULL

This is the sample report, the <Table> tag will be written into fsuspnotice field.


<!--#
    * ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ *
    *              Programmer   : 		                                           *
    *              Department   :                                                          *
    *              Branch       : 		                                           *
    *              Date         :                                                          *
    *              Requestor By : CCD (Credit Control Department)                          *
    *              Requestor    : 				                           *
    *              Function     : Suspend Notice Report                                    *
    * ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ *
-->
 
<html>
<head>
<title></title>
<link href='../../intranet.css' rel='stylesheet' type='text/css'>
</head>
<body>
<center>
 
 
 
<!--<table width="70%" border="1" bordercolor=FF99FF cellspacing=0 cellpadding=1>
 <tr>
  	<td colspan='9' class='subtitleTD' align="center" style="border-top:dashed thin blue;border-left:dashed thin blue;border-right:dashed thin blue">Key Account Auto Matching Screening</td>
  </tr>-->
 
<form name=RRManua method=POST  ID='Form1'><input type=hidden name='DepotCode' value='99'/><input type=hidden name='frcustomer' /><input type=hidden name='frRR' /><table width='80%' border='1' bordercolor='FF99FF' cellspacing='0' cellpadding='1' ID='Table1'><tr><td colspan=3 style='border-style:none'><u>Suspension Notice</u></td><td colspan=4 style='border-style:none'>Depot 99</td></tr><tr><td class=subtitleTD>Customer</td><td colspan=4 style='font-size:8px'>GOLDEN BAKASA S/B(CSMG005)</td><td class=subtitleTD>Date of issue</td><td style='font-size:8px'>07/01/2010</td></tr><tr><td class=subtitleTD width='12%'>Trading Term</td><td class=subtitleTD width='12%' align=center>Term</td><td class=subtitleTD width='12%' align=center>Credit Limit</td><td colspan=2 width='12%'></td><td class=subtitleTD width='12%'>Our Ref</td><td width='12%' style='font-size:8px'>SN/CSMG005/2010/1/7</td></tr><tr><td></td><td align=center style='font-size:8px'>0</td><td align=center style='font-size:8px'>200</td><td colspan=2></td><td class=subtitleTD>Time</td><td style='font-size:8px'>14:53:00</td></tr><tr><td colspan=7 width='12%'>Outstanding</td></tr><tr><td width='12%'></td><td class=subtitleTD align=center>Un-Paid</td><td class=subtitleTD align=center>PD Cheque</td><td class=subtitleTD align=center>Balance</td><td colspan=3></td></tr><tr><td align=right style='font-size:8px'>Oct 2009</td><td align=right style='font-size:8px'>0</td><td align=right style='font-size:8px'>0</td><td align=right style='font-size:8px'>0</td></tr><tr><td align=right style='font-size:8px'>Nov 2009</td><td align=right style='font-size:8px'>0</td><td align=right style='font-size:8px'>0</td><td align=right style='font-size:8px'>0</td></tr><tr><td align=right style='font-size:8px'>Dec 2009</td><td align=right style='font-size:8px'>0</td><td align=right style='font-size:8px'>0</td><td align=right style='font-size:8px'>0</td></tr><tr><td align=right style='font-size:8px'>Jan 2010</td><td align=right style='font-size:8px'>82.90</td><td align=right style='font-size:8px'>0</td><td align=right style='font-size:8px'>82.90</td></tr><tr><td colspan=7 style='border-style:none'><u>SALES & COLLECTION REPORT AS AT 07/01/2010</u></td></tr><tr><td width='12%' align=center style='font-size:xx-small'>Month</td><td width='12%' align=center style='font-size:xx-small'>Balance B/F</td><td width='12%' align=center style='font-size:xx-small'>Sales</td><td width='12%' align=center style='font-size:xx-small'>New Order</b></td><td width='12%' align=center style='font-size:xx-small'>Collection</td><td width='12%' align=center style='font-size:xx-small'>Adjustment</td><td width='12%' align=center style='font-size:xx-small'>Balance C/F</td></tr><td align=right style='font-size:8px'>Oct 2009</td><td align=right style='font-size:8px'>0</td><td align=right style='font-size:8px'>4,159.65</td><td align=right style='font-size:8px'>0</td><td align=right style='font-size:8px'>3,781.05</td><td align=right style='font-size:8px'>0</td><td align=right style='font-size:8px'>378.60</td></tr><td align=right style='font-size:8px'>Nov 2009</td><td align=right style='font-size:8px'>378.60</td><td align=right style='font-size:8px'>4,043.05</td><td align=right style='font-size:8px'>0</td><td align=right style='font-size:8px'>4,421.65</td><td align=right style='font-size:8px'>0</td><td align=right style='font-size:8px'>0</td></tr><td align=right style='font-size:8px'>Dec 2009</td><td align=right style='font-size:8px'>0</td><td align=right style='font-size:8px'>3,412.55</td><td align=right style='font-size:8px'>0</td><td align=right style='font-size:8px'>3,290.35</td><td align=right style='font-size:8px'>0</td><td align=right style='font-size:8px'>122.20</td></tr></tr><td align=right style='font-size:8px'>1/01/2010</td><td align=right style='font-size:8px'>122.20</td><td align=right style='font-size:8px'>165.60</td><td align=right style='font-size:8px'> 0</td><td align=right style='font-size:8px'>122.20</td><td align=right style='font-size:8px'>0</td><td align=right style='font-size:8px'>165.60</td></tr><td align=right style='font-size:8px'>2/01/2010</td><td align=right style='font-size:8px'>165.60</td><td align=right style='font-size:8px'>333.60</td><td align=right style='font-size:8px'> 0</td><td align=right style='font-size:8px'>499.20</td><td align=right style='font-size:8px'>0</td><td align=right style='font-size:8px'>0.00</td></tr><td align=right style='font-size:8px'>5/01/2010</td><td align=right style='font-size:8px'>0.00</td><td align=right style='font-size:8px'>117.80</td><td align=right style='font-size:8px'> 0</td><td align=right style='font-size:8px'>0</td><td align=right style='font-size:8px'>0</td><td align=right style='font-size:8px'>117.80</td></tr><td align=right style='font-size:8px'>6/01/2010</td><td align=right style='font-size:8px'>117.80</td><td align=right style='font-size:8px'>82.90</td><td align=right style='font-size:8px'> 0</td><td align=right style='font-size:8px'>117.80</td><td align=right style='font-size:8px'>0</td><td align=right style='font-size:8px'>82.90</td></tr><td align=right style='font-size:8px'>7/01/2010</td><td align=right style='font-size:8px'>82.90</td><td align=right style='font-size:8px'>0</td><td align=right style='font-size:8px'> 224.67</td><td align=right style='font-size:8px'>0</td><td align=right style='font-size:8px'>0</td><td align=right style='font-size:8px'>307.57</td></tr></tr><tr><td class='bodyTD'><b>Total</b></td><td align=right><b>0</b></td><td align=right><b>12,315.15</b></td><td align=right><b>224.67</b></td><td align=right><b>12,232.25</b></td><td align=right><b>0.00</b></td><td align=right><b>307.57</b></td></tr></table><table width='80%' cellspacing='0' cellpadding='1' ID='Table2'><tr><td colspan=7><b>Note:</b></td></tr><tr><td colspan=4><u><b>Post Dated Cheque</b></u></td><td class='bodyTD'><u><b>Bank in date</b></u></td><td class='bodyTD'><u><b>Clearance date</b></u></td><td class='bodyTD'><u><b>Remark</b></u></td></tr><tr><td colspan=7> </td></tr><tr><td colspan=3> </td><td class='bodyTD'> </td><td colspan=3 style='border-style:none'> </td></tr><tr><td colspan=7 style='border-top:dashed thin blue;border-left:dashed thin blue;border-right:dashed thin blue'><u><b>Reason:</b></u></td></tr><tr><td colspan=7 style='border-left:dashed thin blue;border-right:dashed thin blue'><u><b>Today proposed order</b></u></td></tr><tr><td colspan=2 style='border-left:dashed thin blue'>CSM002237   07/01/2010</td><td align=right>224.67</td><td colspan=4 style='border-right:dashed thin blue'> </td></tr><tr><td colspan=2 style='border-left:dashed thin blue'> </td><td style='border-top:solid thin black' align=right>224.67</td><td colspan=4 style='border-right:dashed thin blue'> </td></tr><tr><td colspan=7 style='border-left:dashed thin blue;border-right:dashed thin blue'> </td></tr><tr><td colspan=2 style='border-left:dashed thin blue'>Current outstanding:</td><td align=right>82.90</td><td colspan=4 style='border-right:dashed thin blue'> </td></tr><tr><td colspan=2 style='border-left:dashed thin blue'>Proposed order:</td><td align=right>224.67</td><td colspan=4 style='border-right:dashed thin blue'> </td></tr><tr><td colspan=2 style='border-left:dashed thin blue'>New oustanding amt:</td><td align=right>307.57</td><td colspan=4 style='border-right:dashed thin blue'> </td></tr><tr><td colspan=2 style='border-left:dashed thin blue'>Exceed Limit:</td><td align=right style='border-top:solid thin black'>107.57</td><td colspan=4 style='border-right:dashed thin blue'> </td></tr><tr><td colspan=7 style='border-bottom:dashed thin blue;border-left:dashed thin blue;border-right:dashed thin blue'>Issued by: Credit Control Department</td></tr><tr><td colspan=7><b>Remark</b></td></tr><tr><td colspan=7>1.  Is the balance mentioned above correct and remains outstanding from the date of this notice</td></tr><tr><td align=center width='15%'>Yes</td><td align=center width='14%'><input type='checkbox' name='chk1'></td><td colspan=5>(if Yes, kindly stop supply immediately)</td></tr><tr><td align=center>No</td><td align=center><input type='checkbox' name='chk2'></td><td colspan=5>(if No, please re-state the balance in the space given below)</td></tr><tr><td colspan=7> </td></tr><tr><td colspan=7>2.  I, the undersigned hereby release the suspension and undertake all outstanding balances above the approved credit limit.</td></tr><tr><td colspan=7> </td></tr><tr><td colspan=7> </td></tr><tr><td colspan=7> </td></tr><tr><td colspan=7> </td></tr><tr><td colspan=7>National Sales Manager/Regional Sales Manager:</td></tr><tr><td colspan=3>Reply by:</td><td colspan=4 align=right><b>Suspension No:</b>CSM002237</td></tr></table></form>
 
</center>

</body>
</html>
[/code]

DT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 03:33:04

my doubt still remains. I see here data for only one customer. in that case whats the problem? even if there are multiple customers cant you make report render separately for each?

Diantan
Starting Member

11 Posts

Posted - 2010-01-07 : 03:56:22

It happens when 2 report run at the same minute and interval of 1 - 2 seconds. When The report is generated, user retrieve the report, but it shows other customer data(which is also running the same stored procedure), so, there is no choice, but to manually re-run the store procedure.

DT

Diantan
Starting Member

11 Posts

Posted - 2010-01-07 : 03:59:13

the error happen at here,, when I want to update the fsuspnotice field.


SQL = "SELECT * FROM tbdocsuspmas where frrno='"&rrno&"' AND FDEPOTCODE='"&dcode&"' AND FUPDSTAT='A'"
uptbDocsuspmasRs.Open SQL, objConn, 2, 3
if not uptbDocsuspmasRs.EOF then
    uptbDocsuspmasRs("fsuspnotice") = lsr
    uptbDocsuspmasRs.Update
end if

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 04:07:11

quote:
Originally posted by Diantan

It happens when 2 report run at the same minute and interval of 1 - 2 seconds. When The report is generated, user retrieve the report, but it shows other customer data(which is also running the same stored procedure), so, there is no choice, but to manually re-run the store procedure.

DT

doesnt user send a parameter indicating his cust code while rendering report? on other side cant u retrieve cust code from user when he tries to run report and render only his/her data?

Diantan
Starting Member

11 Posts

Posted - 2010-01-07 : 04:31:20

yes, user send a parameter, (CustCode, Suspension No) by clicking the retrieve button.

DT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 04:56:06

quote:
Originally posted by Diantan

yes, user send a parameter, (CustCode, Suspension No) by clicking the retrieve button.

DT

then just use that value to filter out only results for him

Diantan
Starting Member

11 Posts

Posted - 2010-01-07 : 05:34:50

I mean, this tbdocsuspmas Table, it has a field name fsuspnotice, this text field is already updated with wrong customer info, which is not tally with its custcode field

DT

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-07 : 05:39:51

I suspect the queryies are real slow too?
When you write WHERE clauses like these, no index in the world will help you to gain performance

WHERE	year(FINVDATE) * 100 + month(FINVDATE) BETWEEN year(@FrDate) * 100 + month(@FrDate) AND year(@CurrDate) * 100 + month(@CurrDate)

WHERE	year(FINVDATE) * 100 + month(FINVDATE) = year(@CurrDate) * 100 + month(@CurrDate)

Instead, rewrite your queries to have the WHERE clause like these instead!

WHERE	fInvDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @FrDate), 0)
	AND fInvDate < DATEADD(MONTH, DATEDIFF(MONTH, -1, @CurrDate), 0)

WHERE	fInvDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @CurrDate), 0)
	AND fInvDate < DATEADD(MONTH, DATEDIFF(MONTH, -1, @CurrDate), 0)

And, since you have DISTINCT for each statement, replace UNION with UNION ALL instead to get more speed!

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 05:41:32

quote:
Originally posted by Diantan

I mean, this tbdocsuspmas Table, it has a field name fsuspnotice, this text field is already updated with wrong customer info, which is not tally with its custcode field

DT

where is update happening? why its updating wrong value?

Diantan
Starting Member

11 Posts

Posted - 2010-01-07 : 06:19:16

This update happen when the Stored Procedure is being triggered. it is inside


function GenerateSuspNotice(dcode,dCur,custid,rrno)
dFrom = ymd(DateAdd("m",-8,dCur))
dTo = ymd(DateAdd("m",-1,dCur))
dCurr = ymd(DateAdd("m",0,dCur))
Dim lsr


    SQL = "exec SP_SUSPNOTICEWetMkt '"&dcode&"','"&custid&"','"&dFrom&"','"&dTo&"','"&dCurr&"','"&rrno&"'"
    objconn.CommandTimeout=0
    set rs=objconn.Execute(SQL)

set rs = server.CreateObject("ADODB.Recordset")
  SQL = "select m.fShortName, m.fCreditTerm, m.fCreditLimit, s.* from ##tbsuspNotice s " & _
        "inner join tbcustomer m on s.fCustCode=m.fcustID " & _
        "where m.fstatus='A' and m.fupdstat='A' ORDER BY MTH, MD"

rs.Open SQL, objconn, 3, 1
if not rs.eof then
    lsr=lsr&"<table width='80%' border='1' bordercolor='FF99FF' cellspacing='0' cellpadding='1' ID='Table1'>"
    lsr=lsr&"<tr>"
    lsr=lsr&"<td colspan=3 style='border-style:none'><u>Suspension Notice</u></td>"
    lsr=lsr&"<td colspan=4 style='border-style:none'>Depot "&dcode&"</td>"
    lsr=lsr&"</tr>"
    lsr=lsr&"<tr>"
    lsr=lsr&"<td class=subtitleTD>Customer</td>"
    lsr=lsr&"<td class='bodytd' colspan=4 style='font-size:8px'>"&Replace(rs("fShortName"),"'","'")&"("& rs("FCUSTCODE")&")</td>"

set uptbDocsuspmasRs = server.createobject("adodb.recordset")
SQL = "SELECT * FROM tbdocsuspmas where frrno='"&rrno&"' AND FDEPOTCODE='"&dcode&"' AND FUPDSTAT='A'"
uptbDocsuspmasRs.Open SQL, objConn, 2, 3
if not uptbDocsuspmasRs.EOF then
    uptbDocsuspmasRs("fsuspnotice") = lsr
    uptbDocsuspmasRs.Update
end if

end function

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 06:30:51

is it done by this step?

if not uptbDocsuspmasRs.EOF then
uptbDocsuspmasRs("fsuspnotice") = lsr
uptbDocsuspmasRs.Update
end if

Diantan
Starting Member

11 Posts

Posted - 2010-01-07 : 06:46:18

Thank you Peso, I will give it a go. May be my stored procedure is really slow.
Thank you visakh16 for your time.

DT

Thank you Peso, there is no more error happen in the report and the result is very good.

Regards,
DT

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources