March 19, 2015 at 10:05 am
From my query I am getting results like below in one of the column:
'immediate due 14,289.00
04/15/15 5,213.00
05/15/15 5,213.00
06/15/15 5,213.00
07/15/15 5,213.00
08/15/15 5,213.00
09/15/15 5,213.00
10/15/15 5,213.00
11/15/15 5,210.00'
this same type of many rows are there (i just mentioned one) but having same pattern with tabs as delimiter in between dates and amount.
My questions are:
I need something that shows Date on one side representing particular amount on the other
For Immediate Due it will be current date and the amount besides it.
Please show me how can I achieve this.
March 19, 2015 at 10:23 am
Something like this might be a good starting point.
DECLARE @Table TABLE
(
aValue VARCHAR(100)
)
INSERT INTO @Table
VALUES
('01/01/2013,250')
,('02/01/2013,200')
,('03/01/2013,350')
select
o.DateValue
,o1.Value
from @Table
cross apply (select Item AS DateValue FROM [Reporting].[SplitParam_test](aValue,',') WHERE ItemNumber = 1) o --Date
cross apply (select Item AS Value FROM [Reporting].[SplitParam_test](aValue,',') WHERE ItemNumber = 2) o1 --value--
NOTE : The Reporting.SplitParam Function is a slightly modified version of Jeff Modens Split8K string function which can be found herehttp://www.sqlservercentral.com/articles/Tally+Table/72993/
You might have to do a Replace on the string to remove the Tab, and replace it with a comma, or you then change the cross apply as required (eg to 3).
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 19, 2015 at 10:37 am
This works without calling functions
DECLARE @t table (Col1 varchar(50))
INSERT @t
values
('immediate due 14,289.00'),
('04/15/15 5,213.00'),
('05/15/15 5,213.00'),
('06/15/15 5,213.00'),
('07/15/15 5,213.00'),
('08/15/15 5,213.00'),
('09/15/15 5,213.00'),
('10/15/15 5,213.00'),
('11/15/15 5,210.00')
UPDATE @t
SET
col1 = REPLACE(REPLACE(Col1,'immediate due','immediatedue'),',','')
SELECT
DueDate=
CASE
WHEN LEFT(Col1,CHARINDEX(' ',Col1) -1) = 'immediatedue' THEN CAST(CAST(GETDATE() as date) as datetime)
ELSE
CAST(LEFT(Col1,CHARINDEX(' ',Col1) -1) as datetime)
END,
AmtDue= CAST(SUBSTRING(Col1,CHARINDEX(' ',Col1) +1,50) as decimal(18,2))
FROM @t
March 19, 2015 at 10:58 am
Thank you for replying and giving me right direction...I have one more thing...I have a table with a column which includes atleast 2000 rows same as I mentioned one...So how can I use whole table in a query rather than just Inserting value one at a time....
Thank you for your valuable time...
March 19, 2015 at 11:13 am
by mistake i pasted twice....
March 19, 2015 at 11:14 am
Do you have the table definition (DDL) for this table, and 4/5 rows of sample data as that would be a great help in allowing people to give you a bit more guidance.
If the data you posted in your original post is just one column then theres no reason you cant simply add in the extra columns to the query, and would allow you to insert into another table.
But its difficult to help without the DDL.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 19, 2015 at 11:18 am
you can check the attachment..
In this the immediate due should give me the date from dt_pcp_eff and
if schedule_pymnts is empty then no_curr_amt should have dt_pcp_eff date
and other rows from schedule_pymnts should written the date and amount listed into it.
March 19, 2015 at 11:27 am
Its a little more complex than I first thought, I need to sleep on it, as it feels as though you have to run each row through the splitter then pivot it back up.
I'm not sure that you can do this in SQL very easily or efficiently.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 19, 2015 at 11:30 am
Thank you for all help...appreciate it
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply