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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 select query to get daily values using monthly val

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_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

52326 Posts

Posted - 2013-01-12 : 12:54:23
do you've calendar table in your database?

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

Go to Top of Page

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.
Go to Top of Page

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."
Go to Top of Page

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."
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -