asked
please help converting a table
I have a payment table which has followings in one row - example/scenario Fields Pay_From - 1/1/2010 Pay_to - 5/30/2010 No_Months - 5 Payment_AMT1 - $1000 Payment_AMT2 - $1500 Member_ID - XXX There can be many scenarios like above... I would like to convert this table which can have a row for monthly payment per member. Examples of rows belwo... Fields Payment_month - 01-JAN-2010 Pay_From - 1/1/2010 Pay_to - 5/30/2010 No_Months - 5 Payment_AMT1 - $200 Payment_AMT2 - $300 Member_ID - XXX Payment_month - 01-FEB-2010 Pay_From - 1/1/2010 Pay_to - 5/30/2010 No_Months - 5 Payment_AMT1 - $200 Payment_AMT2 - $300 Member_ID - XXX Payment_month - 01-MAR-2010 Pay_From - 1/1/2010 Pay_to - 5/30/2010 No_Months - 5 Payment_AMT1 - $200 Payment_AMT2 - $300 Member_ID - XXX etc etc.... Can someone please help with this? I will really really appreciate it...
Something like WITH numbers (num) AS (SELECT ROW_NUMBER() OVER ( ORDER BY [ac].[OBJECT_ID]) FROM [sys].[all_columns] AS [ac] CROSS JOIN [sys].[all_columns] AS [ac1]) SELECT DATEADD(m, n.num, pay_from) as Payment_month, pay_from, pay_to, no_months, payment_amt1 / no_months as payment_amt1, payment_amt2 / no_months as payment_amt2, member_id FROM [whatever_table] wt INNER JOIN [numbers] n on n.num BETWEEN 1 and wt.No_Months; This probably won't work exactly, but to be honest, I don't have the time to make up the right tables, write the insert statements etc. Post DDL & DML = Get better answers...
@Matt Whitfield: +1. This is a very good answer. I was playing with the script similar to yours with the only difference of a cheaper tally: declare @top int; select @top = max(No_Months) from [whatever_table]; and then select top (@top) row_number() etc... I am not sure though that cheaper tally results in the better solution or not due to the existence of an extra statement to actually figure the top :(
Yeah it's something I've done a few times in the past (extrapolation of date ranges correlating to input rows) and I've generally gone for something like this... I think it's six of one and half a dozen of the other... I would hope that the CTE wouldn't be too expensive because it's only materialised once, but it would need some testing :)
Excellent answer Matt, i was going to post something similar but i am on vacation, so you was quicker :) A little too Quick i guess, some of the where Clause is missing
@nicklqs If the columns pay_from and pay_to are not datetime then: if pay_from is varchar storing date in YYYYMMDD format, just replace the occurence of DATEADD(m, n.num, pay_from) in Matt's answer with DATEADD(m, n.num, cast(pay_from as datetime)). If pay_from is an integer, replace the occurence of DATEADD(m, n.num, pay_from) in Matt's answer with DATEADD(m, n.num, cast(cast(pay_from as varchar(8)) as datetime)).
is this the one that you are looking for? SELECT CONVERT(VARCHAR,Pay_From -DATEPART(dd,Pay_from),106),Pay_From,Pay_to,DATEDIFF(MM,Pay_from,Pay_to), Payment_AMT1 ,Payment_AMT2, Member_ID FROM Table Name
Unfortunately, Pay_From and Pay_To are not date fields. They are in YYYYMMDD format. ex. 20100719. Any other idea?
