September 3, 2013 at 1:40 am
Create Table #temp
(
Lnno varchar(15),
Inst_Date Datetime ,
Amount Numeric(12,2)
)
Create Table #temp1
(
Lnno varchar(15),
No_of_Month Int,
Date_Pattern varchar(15),
)
Insert Into #temp
Values ('1','2013-01-01',1000),
('1','2013-02-15',2000),
('1','2013-03-15',2000),
('1','2013-04-15',2000),
('1','2013-05-15',2000),
('2','2013-02-15',2000),
('2','2013-03-15',2000),
('2','2013-04-15',2000),
('2','2013-05-15',2000),
('2','2013-06-15',2000)
Insert Into #temp1(Lnno,No_of_Month)
Values ('1','4'),('2','2')
Select * from #temp
Select * from #temp1
/*
Hi,
Above are the two table,
Now my requirement is,I want to update in table #temp1, Date_Pattern a Date string based on No_Of_Months,
For eg,
In #temp For Lnno = 1,there are 4 records,and it consist of Inst_Date.
So for,Lnno = 1,In Table #temp1 if No_of_Month = 4,
then In Date_pattern column , it should be update as 'Jan/Feb/mar/apr' based on the Inst_date in #temp table.
So for,Lnno = 2,In Table #temp1 if No_of_Month = 2,
then In Date_pattern column , it should be update as 'Feb/mar' based on the Inst_date in #temp table.
Please help me.
Thanks in Advance!!
*/
September 3, 2013 at 7:39 am
Great job posting ddl and sample data. However your description is very unclear. Can you post what you expect as output and try again at explaining the logic?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 3, 2013 at 9:03 am
It's not a very difficult task to do, you just need to have the correct tools to do it. For reference on the methods used for this solution, you should check the following links
Formatting Dates with 3 Character Months[/url]
Creating a comma-separated list[/url]
If you have any question, feel free to ask.
WITH CTE AS(
Select DISTINCT Lnno,
CONVERT( char(3), Inst_Date) ShortMonth,
MONTH(Inst_Date) MonthNo
from #temp
),
CTE2 AS(
SELECT *, ROW_NUMBER() OVER( PARTITION BY Lnno ORDER BY MonthNo) rn
FROM CTE
)
UPDATE t SET
Date_Pattern = STUFF((SELECT '/' + ShortMonth
FROM CTE2 c
WHERE c.Lnno = t.Lnno AND c.rn <= t.No_of_Month
FOR XML PATH('')),1,1, ''
)
FROM #temp1 t
SELECT * FROM #temp1
EDIT: Shortening code
September 4, 2013 at 6:44 am
This I want exactly.
Thanks !!
September 4, 2013 at 9:06 am
It's good to know that you got what you needed. Be sure to understand what it does and be able to explain it to someone else (even if it's a rubber duck), that way you can replicate it and support it if something changes.
September 4, 2013 at 11:14 pm
Hi,
Thanks for your feedback.I will sure take care of it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply