Hi Guys,
I am trying to break a table that contains records accross in 40 columns and create multiple records out of the 40 columns record.
I have 2 Tables, TableA the one that has 40 Columns per Enrollment(Matricula) and the other TableB the one that only has 5 Columns which will store the multiple records broken down from the TableA
TableA = tblPayments
TableB = Payment_Details
Some how the records do not fall in the appropriate columns and this is the part where I am frustrated
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
uspCalculateTimeSheet
Syntax: spBreakDownPayments
*/
CREATE PROC [dbo].[spBreakDownPayments]
AS
DECLARE @Matricula varchar(50)
DECLARE @LineCnt int
DECLARE @sqlMethod NVARCHAR(4000)
DECLARE @OPMethod NVARCHAR(50)
DECLARE @sqlDate NVARCHAR(4000)
DECLARE @OPDate NVARCHAR(50)
DECLARE @sqlAmt NVARCHAR(4000)
DECLARE @OPAmt NVARCHAR(50)
DECLARE @TheMethod varchar(50)
DECLARE @TheDate datetime
DECLARE @TheAmount money
SET @LineCnt=0
SET @TheAmount=0
DECLARE Cursor_Payments CURSOR FOR
SELECT DISTINCT [Matricula] FROM [DBF_File].[dbo].[tblPayments] WHERE [Matricula] IS NOT NULL
OPEN Cursor_Payments
FETCH NEXT FROM Cursor_Payments INTO @Matricula
WHILE @@FETCH_STATUS=0
BEGIN
SET @LineCnt = @LineCnt +1
SET @sqlMethod = 'SELECT @TheMethod=method'+RTRIM(LTRIM(STR(@LineCnt)))+' FROM [DBF_Payments] WHERE [matricula]=@Matricula'
EXEC sp_executesql @sqlMethod, N'@Matricula INT, @TheMethod NVARCHAR(50) OUTPUT',@Matricula = @Matricula, @TheMethod = @TheMethod OUTPUT
---SELECT @TheMethod=ISNULL(@OPMethod,'CASH')
SET @sqlDate ='SELECT @TheDate=paiddate'+RTRIM(LTRIM(STR(@LineCnt)))+' FROM [DBF_Payments] WHERE [matricula]=@Matricula'
EXEC sp_executesql @sqlDate, N'@Matricula INT, @TheDate NVARCHAR(50) OUTPUT',@Matricula = @Matricula, @TheDate = @TheDate OUTPUT
---SELECT @TheDate=TRY_CONVERT(DATETIME,@OPDate)
SET @sqlAmt = 'SELECT @TheAmount=Amount'+RTRIM(LTRIM(STR(@LineCnt)))+' FROM [DBF_Payments] WHERE [matricula]=@Matricula'
EXEC sp_executesql @sqlAmt, N'@Matricula INT, @TheAmount NVARCHAR(50) OUTPUT',@Matricula = @Matricula, @TheAmount = @TheAmount OUTPUT
---SELECT @TheAmount=ISNULL(TRY_CONVERT(MONEY,@OPAmt),0)
------------Append new record
IF NOT EXISTS(select * from Payment_Details where [reference]=@Matricula and [method]=@TheMethod and MONTH([date_paid])=MONTH(@TheDate) and DAY([date_paid])=DAY(@TheDate) and YEAR([date_paid])=YEAR(@TheDate) and [amount_paid]=@TheAmount)
BEGIN
BEGIN TRANSACTION
INSERT INTO [Payment_Details]([date_paid],[method],[amount_paid],[reference])
VALUES(@TheDate,@TheMethod,@TheAmount,@Matricula)
COMMIT
END
----Reset
SET @TheMethod=''
SET @TheDate=NULL
SET @TheAmount=0.00
---- It Reaches the columns count then go to the next
IF (@LineCnt>=40)
BEGIN
SET @LineCnt = 0
END
-----Next Record
FETCH NEXT FROM Cursor_Payments INTO @Matricula
END
CLOSE Cursor_Payments
Deallocate Cursor_Payments
Download the data and tables script -->"github.com/netstair/SQLBreakdownData/blob/main/DBF_File_Script_With_Data.sql"
Thank you in advanced.
Jose
Jose SaizPosted May 14, 2024, 10:43 AM
Thanks Naimish for the reply, but that does not works, remember I have columns as follow
Method1,method2,method3 etc
Date1,Date2,Date3 etc
AmoutPaid1, AmountPaid2, AmountPaid3 etc.
That is why the @LineCnter to add the number to the columns, instead of coding for the 40 columns that Method, date and AmountPaid have.
I think I got it, by changing my store proc to this one
Your comment will be very much appriciated, Please let me know if I am still doing it wrong or missing any.
Thank you guys
Naimish MakwanaPosted May 14, 2024, 4:21 AM
From your code, it seems like you’re trying to break down the records from
tblPayments(TableA) into multiple records inPayment_Details(TableB).One thing I noticed is that you’re using
@Matricula INTin yoursp_executesqlcalls, but@Matriculais declared asvarchar(50). This could be causing some issues. You should change@Matricula INTto@Matricula varchar(50)in yoursp_executesqlcalls.Another potential issue could be the way you’re constructing your dynamic SQL queries. You’re using
RTRIM(LTRIM(STR(@LineCnt)))to convert the line count to a string. However,STRfunction can return a string with leading spaces for positive numbers, andLTRIMonly removes spaces from the left side. This could lead to unexpected spaces in your column names. You might want to useCAST(@LineCnt AS varchar(10))instead.Here’s how you can modify your
@sqlMethod,@sqlDate, and@sqlAmtassignments:And here’s how you can modify your
sp_executesqlcalls:Try these modifications and see if they solve your problem.
Thanks