| Author |
 Topic  |
|
|
cplusplus
Constraint Violating Yak Guru
481 Posts |
 Posted - 01/12/2013 : 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_value
Select 'T34526','2012',100000,56000,60000,75000,74354,34256,86749,64738,74354,34256,86749,64738 union all
Select 'S77256','2012',300000,120000,60000,75000,74354,34256,86749,64738,74354,34256,86749,64738 union all
Select 'Y67256','2012',70000,87000,60000,75000,74354,34256,86749,64738,74354,34256,86749,64738
Select * from @TDyl_value;
Thank you very much for the helpful info.
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 01/12/2013 : 12:54:23
|
do you've calendar table in your database?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
cplusplus
Constraint Violating Yak Guru
481 Posts |
Posted - 01/12/2013 : 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. |
Edited by - cplusplus on 01/12/2013 15:31:53 |
|
|
|
Jeff Moden
Aged Yak Warrior
USA
643 Posts |
Posted - 01/12/2013 : 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." |
Edited by - Jeff Moden on 01/12/2013 18:46:12 |
|
|
|
Jeff Moden
Aged Yak Warrior
USA
643 Posts |
Posted - 01/12/2013 : 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." |
Edited by - Jeff Moden on 01/12/2013 18:45:36 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47040 Posts |
Posted - 01/13/2013 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
select query to get daily values using monthly val
