Author |
Topic |
cplusplus
Aged Yak Warrior
567 Posts |
Posted - 2013-01-12 : 08:32:29
|
I have to get daily based values, right now i have values based on Fiscal_yr, acct_no and month_1 ...to... month_12. now want to get daily values.Example: Fiscal_yr=2012, Acct_no='T34526' and Month_1=100000 month_1 has 31 days. Month_1/31 will get 3225.80 per day basis, i need to insert to a temp table 31 rows with dates jan 1 2012 to jan 31 2012 with daily values.Declare @TDyl_value table (acct_No varchar(6), Fiscal_yr varchar(4),Month_1 int, Month_2 int, Month_3 int, Month_4 int,Month_5 int, Month_6 int, Month_7 int, Month_8 int,Month_9 int, MOnth_10 int, MOnth_11 int, Month_12 int)insert @TDyl_valueSelect 'T34526','2012',100000,56000,60000,75000,74354,34256,86749,64738,74354,34256,86749,64738 union allSelect 'S77256','2012',300000,120000,60000,75000,74354,34256,86749,64738,74354,34256,86749,64738 union allSelect 'Y67256','2012',70000,87000,60000,75000,74354,34256,86749,64738,74354,34256,86749,64738Select * from @TDyl_value; Thank you very much for the helpful info. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-12 : 12:54:23
|
do you've calendar table in your database?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
cplusplus
Aged Yak Warrior
567 Posts |
Posted - 2013-01-12 : 15:29:36
|
Hello Visakh, I have the dim_calendar table in my database with the following. Based on fiscalmonth & fiscalyear, is it possible to populate the number of days available in a month.create table dim_Calendar (CalDate datetime,YYYYMMDD char(8),MonthName varchar(20),MonthShortName char(3),MNTH int,YR int,FiscalYear int,FiscalMNTH int,CalQTR int,FiscalQTR int,DayOfWeek int,WeekDay varchar(20),WeekDayShort char(3),CalPeriod varchar(20));Thanks. |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-01-12 : 18:34:28
|
Thank you very much for posting readily consummable data. The following should do the trick for you. Speaking of tricks, notice the COUNT(*) but no GROUP BY in this code. SELECT td.Acct_No, td.Fiscal_Yr, ca.Month#, (ca.MonthValue+0.0)/COUNT(*)OVER(PARTITION BY td.Fiscal_Yr,ca.Month#) FROM @TDyl_value td CROSS APPLY ( SELECT 1,Month_1 UNION ALL SELECT 2,Month_2 UNION ALL SELECT 3,Month_3 UNION ALL SELECT 4,Month_4 UNION ALL SELECT 5,Month_5 UNION ALL SELECT 6,Month_6 UNION ALL SELECT 7,Month_7 UNION ALL SELECT 8,Month_8 UNION ALL SELECT 9,Month_9 UNION ALL SELECT 10,Month_10 UNION ALL SELECT 11,Month_11 UNION ALL SELECT 12,Month_12 )ca(Month#,MonthValue) JOIN dbo.dim_Calendar cal ON cal.FiscalYear = td.Fiscal_Yr AND cal.FiscalMnth = ca.Month#; --Jeff Moden RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".First step towards the paradigm shift of writing Set Based code:"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it." |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-01-12 : 18:44:49
|
p.s.Hopefully, you have something like the following indexes on your calendar table. ALTER TABLE dbo.dim_Calendar ADD PRIMARY KEY CLUSTERED (CalDate) CREATE INDEX IX_01 ON dim_Calendar (FiscalYear,FiscalMnth) --Jeff Moden RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".First step towards the paradigm shift of writing Set Based code:"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it." |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-13 : 23:03:03
|
quote: Originally posted by cplusplus Hello Visakh, I have the dim_calendar table in my database with the following. Based on fiscalmonth & fiscalyear, is it possible to populate the number of days available in a month.create table dim_Calendar (CalDate datetime,YYYYMMDD char(8),MonthName varchar(20),MonthShortName char(3),MNTH int,YR int,FiscalYear int,FiscalMNTH int,CalQTR int,FiscalQTR int,DayOfWeek int,WeekDay varchar(20),WeekDayShort char(3),CalPeriod varchar(20));Thanks.
Does fiscal start and fiscal end for a month overlap with actual month start and end for you?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|