SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to repate a value for all months
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vandana
Starting Member

29 Posts

Posted - 02/26/2013 :  23:46:15  Show Profile  Reply with Quote
Hi All,

i have some sales(say 1200) in a year.
i should get a output in the below formate

name year sales month sale

a 2012 1200 1 100
a 2012 1200 2 100
a 2012 1200 3 100

like this i should get for all months and same for all diff years



can anyone suggest me how to achive this thanks in advance

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/26/2013 :  23:49:08  Show Profile  Reply with Quote
you need to use a cross join logic with table containing year month information? do you've such a calendar table in your database?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

vandana
Starting Member

29 Posts

Posted - 02/27/2013 :  01:06:05  Show Profile  Reply with Quote
Hi ,

this is my table


Id startdate Maintermsperiod annualamount
1 2012-01-01 12 1200


now above table is for 12 months it is 1200

based on this table i should get output as mentioned above

Thanks in advance
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/27/2013 :  01:11:49  Show Profile  Reply with Quote
create a tally table and use it like below


;With Months
AS
(
SELECT id,Startdate,1 AS MonthVal,Maintermsperiod,annualamount,annualamount/Maintermsperiod AS MonthAmt
FROM Table
UNION ALL
SELECT id,DATEADD(mm,1,Startdate),MonthVal + 1,Maintermsperiod,annualamount,MonthAmt
FROM Months
WHERE MonthVal + 1 < = Maintermsperiod
)

SELECT id,YEAR(Startdate),annualamount AS Sales,MonthVal,MonthAmt
FROM Months
OPTION(MAXRECURSION 0)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

vandana
Starting Member

29 Posts

Posted - 02/27/2013 :  01:43:30  Show Profile  Reply with Quote
Thanks visakh16 it worked
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/27/2013 :  01:49:32  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.48 seconds. Powered By: Snitz Forums 2000