Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to repate a value for all months

Author  Topic 

vandana
Starting Member

29 Posts

Posted - 2013-02-26 : 23:46:15
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

52326 Posts

Posted - 2013-02-26 : 23:49:08
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 - 2013-02-27 : 01:06:05
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

52326 Posts

Posted - 2013-02-27 : 01:11:49
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 - 2013-02-27 : 01:43:30
Thanks visakh16 it worked
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-27 : 01:49:32
welcome

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

Go to Top of Page
   

- Advertisement -