November 3, 2008 at 1:12 pm
I took Seth's advice and visited the link on how to post data. Please see below. I hope I've done it correctly. I will leave my original post below it, just in case it is of any assistance. I apologize for my original post.
Just to clarify. This is for a report (not going into a table as I originally posted). The report will just be 12 columns - 2 columns for each possible monthly statement.
Ex output: month1 pmt1 | month1 pmt2 | month2 pmt1 | month2 pmt2 | ......... | month5 pmt1 | month5 pmt2 | month6 pmt1 | month6 pmt2
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#AcctTrans','U') IS NOT NULL
DROP TABLE #AcctTrans
--===== Create the test table with
CREATE TABLE #AcctTrans
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Payment INT,
Type NVARCHAR(10),
DateValue DATETIME,
Amount MONEY
)
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT mdy
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #AcctTrans ON
--===== Insert the test data into the test table
INSERT INTO #AcctTrans
(ID, Payment, Type, DateOfTrans, Amount)
SELECT '112','1','S1','1/30/2008 13:10',125 UNION ALL
SELECT '112','2','S1','2/28/2008 13:13',125 UNION ALL
SELECT '112','3','S1','3/28/2008 12:14',125 UNION ALL
SELECT '112','4','S4','6/9/2008 12:13',75 UNION ALL
SELECT '112','5','S4','6/9/2008 12:13',75 UNION ALL
SELECT '112','6','S4','6/9/2008 12:13',75 UNION ALL
SELECT '112','7','S4','6/11/2008 12:14',75 UNION ALL
SELECT '112','8','S4','6/26/2008 12:14',125
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #AcctTrans OFF
Original Post:
---------------------
I am a newbie and am having trouble writing a query for the following project. Any help is greatly appreciated. I am using SQL 2005 and trying to do an IF statement or a CASE statement based upon payments called S1 and S2.
S1 = Full Payment
S2 = Split Payment
There are 6 monthly payments in total, and each monthly payment can be paid in 2 split payments or 1 full payment. The results will go into a table with 12 columns:
Column 1: 1st month, payment 1: This will hold either the 1st split payment of month 1 or the full payment of month 1.
Column 2: 1st month, payment 2: This will hold either the 2nd split payment of month 1 or Null if the 1st payment was a full payment.
Column 3: 2nd month, payment 1: This will hold either the 1st split payment of month 2 or the full payment of month 2.
Column 4: 2nd month, payment 2: This will hold either the 2nd split payment of month 2 or Null if the 1st payment was a full payment.
Column 5: 3rd month, payment 1: This will hold either the 1st split payment of month 3 or the full payment of month 3.
Column 6: 3rd month, payment 2: This will hold either the 2nd split payment of month 3 or Null if the 1st payment was a full payment.
Column 7: 4th month, payment 1: This will hold either the 1st split payment of month 4 or the full payment of month 4.
Column 8: 4th month, payment 2: This will hold either the 2nd split payment of month 4 or Null if the 1st payment was a full payment.
Column 9: 5th month, payment 1: This will hold either the 1st split payment of month 5 or the full payment of month 5.
Column 10: 5th month, payment 2: This will hold either the 2nd split payment of month 5 or Null if the 1st payment was a full payment.
Column 11: 6th month, payment 1: This will hold either the 1st split payment of month 6 or the full payment of month 6.
Column 12: 6th month, payment 2: This will hold either the 2nd split payment of month 6 or Null if the 1st payment was a full payment.
The problem: At any time, a client can switch between 1 full payment or 2 split payments until all 6 monthly payments have been fulfilled. For example, say for the 1st month the client makes 1 full payment (S1), it would go into Column 1, but then the client decides for the 2nd month they would like to do split payments. The next payment, an S2 (Split Payment), can not be in 1st payment part 2 (Column 2) it has to go into 2nd payment part 1 (Column 3).
Thanks for any help you can provide.
November 3, 2008 at 1:22 pm
Please refer to the link in my signature for an example of how to post table definition and sample data. There will probably be additional questions on this one, such as:
What happens if a client makes a payment that is > than what was due on the previous month? (IE. Pays 2+ months at once).
Is there any chance of more than 2 payments in a month?
Is this for a report, or for data storage? If this is to store the data going forward... this is likely a mistake, as you are denormalizing your data which makes it considerably harder to work with and requires that your table also grow horizontally rather than just vertically.
November 3, 2008 at 1:41 pm
Thank you, Garadin. I will edit my post.
In response to your questions...
What happens if a client makes a payment that is > than what was due on the previous month? (IE. Pays 2+ months at once)? If this happens we still only draft what the monthly payment (or split payment) is, so this would not affect this report.
Is there any chance of more than 2 payments in a month? No
Is this for a report, or for data storage? A report
Thanks again for your help.
November 3, 2008 at 4:19 pm
Mark, it would be most helpful if you could provide an example (or better yet examples) of input data in table form and what you would expect the output from your table or tables to look like.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 3, 2008 at 7:10 pm
Thank you bhovious. Seth suggested the same thing earlier. I edited my original post with this information but thought I'd respond to you as well. I hope this helps.
Just to clarify. This is for a report (not going into a table as I originally posted). The report will just be 12 columns - 2 columns for each possible monthly statement.
Ex output: month1 pmt1 | month1 pmt2 | month2 pmt1 | month2 pmt2 | ......... | month5 pmt1 | month5 pmt2 | month6 pmt1 | month6 pmt2
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#AcctTrans','U') IS NOT NULL
DROP TABLE #AcctTrans
--===== Create the test table with
CREATE TABLE #AcctTrans
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Payment INT,
Type NVARCHAR(10),
DateValue DATETIME,
Amount MONEY
)
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT mdy
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #AcctTrans ON
--===== Insert the test data into the test table
INSERT INTO #AcctTrans
(ID, Payment, Type, DateOfTrans, Amount)
SELECT '112','1','S1','1/30/2008 13:10',125 UNION ALL
SELECT '112','2','S1','2/28/2008 13:13',125 UNION ALL
SELECT '112','3','S1','3/28/2008 12:14',125 UNION ALL
SELECT '112','4','S4','6/9/2008 12:13',75 UNION ALL
SELECT '112','5','S4','6/9/2008 12:13',75 UNION ALL
SELECT '112','6','S4','6/9/2008 12:13',75 UNION ALL
SELECT '112','7','S4','6/11/2008 12:14',75 UNION ALL
SELECT '112','8','S4','6/26/2008 12:14',125
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #AcctTrans OFF
November 3, 2008 at 7:12 pm
Sorry about the ;). It's supposed to be a closed parenthesis.
November 4, 2008 at 2:37 pm
I got some help with this and found a resolution. Thanks to anyone who took a look at it.
November 4, 2008 at 2:49 pm
Glad you got an answer Mark. Sorry I couldn't get back to check on this quicker.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 4, 2008 at 2:58 pm
Mark.Brown (11/4/2008)
I got some help with this and found a resolution. Thanks to anyone who took a look at it.
The proper thing to do now is post your resolution to your problem. You asked a question, and although noone here provided an answer, the fact you have one is important. There could be others with the same or similiar problem and seeing a resolution will be helpful to them.
November 4, 2008 at 3:33 pm
That's ok. Thanks, bhovious.
November 4, 2008 at 3:38 pm
Thanks, Lynn. Sorry, this is my first post. The resolution is below. Please know that this is not my work. I hope to be able to do this someday.:)
DECLARE @tblSourceData TABLE
(
ClientID int,
[Type] varchar(100),
[Amount] SmallMoney,
[Date] SmallDateTime
)
CREATE TABLE temp_tblAmountData
(
ClientID int,
P1Amount SmallMoney null,
P2Amount SmallMoney null,
P3Amount SmallMoney null,
P4Amount SmallMoney null,
P5Amount SmallMoney null,
P6Amount SmallMoney null,
P7Amount SmallMoney null,
P8Amount SmallMoney null,
P9Amount SmallMoney null,
P10Amount SmallMoney null,
P11Amount SmallMoney null,
P12Amount SmallMoney null
)
--Insert some test data
INSERT INTO @tblSourceData
(ClientID, [Type], [Date], [Amount])
SELECT 112, 'S1', '01/30/2008', 125
UNION ALL SELECT 112, 'S1', '02/28/2008', 125
UNION ALL SELECT 112, 'S1', '03/28/2008', 125
UNION ALL SELECT 112, 'S2', '04/28/2008', 125
UNION ALL SELECT 112, 'S3', '05/28/2008', 75
UNION ALL SELECT 112, 'S4', '06/28/2008', 75
UNION ALL SELECT 112, 'S4', '06/28/2008', 75
UNION ALL SELECT 113, 'S4', '07/28/2008', 75
UNION ALL SELECT 113, 'S4', '08/28/2008', 75
--Create at least one row for each client in our destination table
INSERT INTO temp_tblAmountData (ClientID)
SELECT ClientID FROM @tblSourceData
GROUP BY ClientID
DECLARE CURSOR_SOURCE_DATA CURSOR
READ_ONLY
FOR SELECT ClientID, [Type], [Amount]
FROM @tblSourceData
ORDER BY [Date] ASC --Force data into date arrived ascending, business rule
DECLARE @ClientID int
DECLARE @Type varchar(100)
DECLARE @Amount smallmoney
DECLARE @pPos int --Position to apply payment
DECLARE @lastClientID int
DECLARE @bLastPaymentWasFullPayment bit --if full payment is applied, next payment should apply +1 from current pos
DECLARE @tSQL varchar(4000)
SET @bLastPaymentWasFullPayment = 0 --false
OPEN CURSOR_SOURCE_DATA
FETCH NEXT FROM CURSOR_SOURCE_DATA INTO @ClientID, @Type, @Amount
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
--DEBUG SELECT @ClientID, @Type, @Amount, @pPos
IF @lastClientID <> @ClientID OR @lastClientID IS NULL
BEGIN
SET @lastClientID = @ClientID
SET @pPos = 1
END
IF @pPos = 1
BEGIN
--Payment Type does not matter, first payment applied to P1
UPDATE temp_tblAmountData
SET P1Amount = @Amount
WHERE ClientID = @ClientID
END
ELSE
BEGIN
SET @tSQL = 'UPDATE temp_tblAmountData SET P' + CONVERT(varchar(2), @pPos) + 'Amount = ' + CONVERT(varchar(100), @Amount) + ' WHERE ClientID = ' + CONVERT(varchar(100), @ClientID) + ';'
EXEC (@tSQL)
END
IF @Type = 'S1' OR @Type = 'S2'
BEGIN
SET @pPos = @pPos + 2 --increment(biz logic +1)
END
ELSE
BEGIN
SET @pPos = @pPos + 1 --increment(simple, no biz logic)
END
END
FETCH NEXT FROM CURSOR_SOURCE_DATA INTO @ClientID, @Type, @Amount
END
--Cleanup
CLOSE CURSOR_SOURCE_DATA
DEALLOCATE CURSOR_SOURCE_DATA
SELECT * FROM temp_tblAmountData
SELECT * FROM @tblSourceData
ORDER BY [Date] ASC
DROP TABLE temp_tblAmountData
November 4, 2008 at 3:59 pm
Arrrrrrgggggggggg CURSORS!!!!
MY EYES!!! :w00t:
I'm blind...........:cool:
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 4, 2008 at 4:24 pm
Mark.Brown (11/4/2008)
...The resolution is below. Please know that this is not my work. I hope to be able to do this someday.:)
This is a Cursor routine. Let's hope that someday you will do a whole lot better than this. Seriously.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 4, 2008 at 4:57 pm
Mark, please consider something like this as an alternative solution to the cursor approach. On my box, this is running in under 1 ms, while the cursor is taking 30-40 ms. Like Barry said.
Good luck,
Bob
--------------------------------------------------------------------------------
DECLARE @PmtPeriod int
DECLARE @timer datetime
DECLARE @tblSourceData TABLE
(
rowNo int identity(1,1) primary key
,ClientID int
,[Type] varchar(100)
,[Amount] SmallMoney
,[Date] SmallDateTime
,PmtPeriod int
)
CREATE TABLE temp_tblAmountData
(
ClientID int,
P1Amount SmallMoney null,
P2Amount SmallMoney null,
P3Amount SmallMoney null,
P4Amount SmallMoney null,
P5Amount SmallMoney null,
P6Amount SmallMoney null,
P7Amount SmallMoney null,
P8Amount SmallMoney null,
P9Amount SmallMoney null,
P10Amount SmallMoney null,
P11Amount SmallMoney null,
P12Amount SmallMoney null
)
--Insert some test data
INSERT INTO @tblSourceData
(ClientID, [Type], [Date], [Amount])
SELECT 112 as clientID, 'S1' as [type], '01/30/2008' as [date], 125 as [amount]
UNION ALL SELECT 112, 'S1', '02/28/2008', 125
UNION ALL SELECT 112, 'S1', '03/28/2008', 125
UNION ALL SELECT 112, 'S2', '04/28/2008', 125
UNION ALL SELECT 112, 'S3', '05/28/2008', 75
UNION ALL SELECT 112, 'S4', '06/28/2008', 75
UNION ALL SELECT 112, 'S4', '06/28/2008', 75
UNION ALL SELECT 113, 'S4', '07/28/2008', 75
UNION ALL SELECT 113, 'S4', '08/28/2008', 75
-- do we want this sorted ??
set @timer = getdate()
set @PmtPeriod = 0
-- using cte to get around issue of updating a table variable joined to itself
;with cteSourceData as (select * from @tblSourceData)
update @tblSourceData
--------------------- use business rules to determine pay period ----------------------------------
-- assumes sequence of clustered key for input data will be clientID,date,type etc in production
-- to insure the updates occur in the correct sequence
set @PmtPeriod = PmtPeriod = case when t2.clientID is null then 1
when t2.[Type] in ('S1','S2') then @PmtPeriod+2
else @PmtPeriod + 1
end
from @tblSourceData t1
left join cteSourceData t2 on t1.rowNo = t2.rowNo+1 and t1.clientID = t2.clientID
select *
from @tblSourceData t1
left join @tblSourceData t2 on t1.rowNo = t2.rowNo+1 and t1.clientID = t2.clientID
select clientID
,sum(case when PmtPeriod = 1 then [amount] else null end) as P1Amount
,sum(case when PmtPeriod = 2 then [amount] else null end) as P2Amount
,sum(case when PmtPeriod = 3 then [amount] else null end) as P3Amount
,sum(case when PmtPeriod = 4 then [amount] else null end) as P4Amount
,sum(case when PmtPeriod = 5 then [amount] else null end) as P5Amount
,sum(case when PmtPeriod = 6 then [amount] else null end) as P6Amount
,sum(case when PmtPeriod = 7 then [amount] else null end) as P7Amount
,sum(case when PmtPeriod = 8 then [amount] else null end) as P8Amount
,sum(case when PmtPeriod = 9 then [amount] else null end) as P9Amount
,sum(case when PmtPeriod = 10 then [amount] else null end) as P10Amount
,sum(case when PmtPeriod = 11 then [amount] else null end) as P11Amount
,sum(case when PmtPeriod = 12 then [amount] else null end) as P12Amount
from @tblSourceData
group by clientID
order by clientID
select datediff(ms,@timer,getdate())
DROP TABLE temp_tblAmountData
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 5, 2008 at 8:33 am
Wow, I didn't know this about cursors. Thanks to everyone for their input. And, thanks Bob for the alternate solution, I really appreciate it.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply