Stored Procedure counters

[thorny00]

I'm new to creating stored procedure and in need of help. I have the following code so far, but, need additional data. In the FILE HEADER RECORD, I need to count the number of detail records and put it in the place of the 9 zeros. I then need to create a trailer record that would count the header record + # of detail records plus the trailer record(or just count the detail records and add 2) then I need the sum of the tblIncentives.Amount field for all the detail records.

Thanks for your help and time in advance, it's greatly appreicated!

CREATE PROCEDURE hht_dellincentiveexport

as

--VARIABLES FOR COUNTER

DROP TABLE ##DELLINCENTIVE
DECLARE @RecCnt AS TINYINT
SET @RecCnt = 0

--CREATE TEMPORARY TABLE
CREATE TABLE[##DELLINCENTIVE](
[Record] CHAR(46) DEFAULT
('9999999999999999999999999999999999999999999999999')
)

--INSERT FILE HEADER RECORD

INSERT INTO [##DELLINCENTIVE]
VALUES ('01'+ convert(char(10), getdate(), 112)+ 'HEWITTDELL000000000')

GO

--INSERT EMPLOYEE DETAIL RECORD

DECLARE @RECORD_TYPE CHAR(2)
DECLARE @EMPLOYEE_SSN CHAR(9)
DECLARE @CREDIT_AMOUNT CHAR(19)
DECLARE @EFFECTIVE_DOC CHAR(8)
DECLARE @EMPLOYEE_ID CHAR(8)
DECLARE @DETAIL_COUNT CHAR(9)

DECLARE [EMPLOYEE DETAIL RECORD] CURSOR FOR
SELECT '02',
dbo.tblNames.SSN,
(SUM(dbo.tblIncentives.Amount)*100),
convert(char(10), getdate(), 112),
dbo.tblSupplemental.FldValue,
COUNT(dbo.tblNames.SSN)
FROM dbo.tblIncentives INNER JOIN
dbo.tblNames ON
dbo.tblIncentives.PIN = dbo.tblNames.PIN INNER JOIN
dbo.tblSupplemental ON
dbo.tblNames.PIN = dbo.tblSupplemental.PIN
WHERE
(dbo.tblSupplemental.SupportID = 152)
and
(dbo.tblincentives.ymdcreate > '8/01/2005') -- THIS CHANGES BI-MONTHLY
and
(dbo.tblIncentives.ClientIncentiveID IN (5, 6, 7, 8, 9, 10, 11,13, 14, 15))
GROUP BY dbo.tblNames.SSN, dbo.tblSupplemental.FldValue

OPEN [EMPLOYEE DETAIL RECORD]

FETCH NEXT FROM [EMPLOYEE DETAIL RECORD]
INTO @RECORD_TYPE, @EMPLOYEE_SSN, @CREDIT_AMOUNT, @EFFECTIVE_DOC, @EMPLOYEE_ID

WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO [##DELLINCENTIVE]
VALUES (@RECORD_TYPE + @EMPLOYEE_SSN + @CREDIT_AMOUNT + @EFFECTIVE_DOC + @EMPLOYEE_ID)

FETCH NEXT FROM [EMPLOYEE DETAIL RECORD]
INTO @RECORD_TYPE, @EMPLOYEE_SSN, @CREDIT_AMOUNT, @EFFECTIVE_DOC, @EMPLOYEE_ID
END

CLOSE [EMPLOYEE DETAIL RECORD]
DEALLOCATE [EMPLOYEE DETAIL RECORD]
GO

 

[DrSql]

To Approch this you need to expand your SP.
Its a four step process,
Setp1 is to Select and enter the data in a TempTableData
Step2 is to Select your heaeder and insert into a TemptableHeader
Step3 is to Select your heaeder and insert into a TemptableTrailer

Step 4 is to enter all the above data into TempAll

Insert into TempAll select 2 as sort, Col1,Col2,Col3... from TempTableData

Insert into TempAll select 1 as sort, Col1,Col2,Col3... from TemptableHeader

Insert into TempAll select 3 as sort, Col1,Col2,Col3... from TemptableTrailer

Select * from TempTableData order by sort should give your the results.


Dr. Sql

 

[thorny00]

Thanks Dr. Sql.
I have created 1 SP to handle the detail records and 1 SP to handle the header record. I'm stumped as to how to create the SP for the trailer record. The trailer recordonly has 2 fields - Record Count(9), which must total the detail records + the header record + the trailer record, it also contains the total dollar amount(22). Example if there are 400 detail records, and a total of $5000, the trailer record whould look like
0000004020000000000000000500000. How do I count the records and sum the dollar amount?

 

[DrSql]

declare @count as c(har(4)
decalre @amount as char(6)
decalre @trailer as char (25)
select @count=cast(count(*) from TempTableData)as char(4)
select @@amount=Cast(sum(amount) from sometable) as char(6)

select @trailer=@count+@amount

Try this, if you still havving issue I can get some more details.

Dr.Sql
Good Luck.