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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 select query to get daily values using monthly val
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cplusplus
Aged Yak Warrior

540 Posts

Posted - 01/12/2013 :  08:32:29  Show Profile  Reply with Quote
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
52325 Posts

Posted - 01/12/2013 :  12:54:23  Show Profile  Reply with Quote
do you've calendar table in your database?

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

Go to Top of Page

cplusplus
Aged Yak Warrior

540 Posts

Posted - 01/12/2013 :  15:29:36  Show Profile  Reply with Quote
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
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/12/2013 :  18:34:28  Show Profile  Reply with Quote
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
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/12/2013 :  18:44:49  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/13/2013 :  23:03:03  Show Profile  Reply with Quote
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
  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.08 seconds. Powered By: Snitz Forums 2000