| Author |
 Topic  |
|
|
cplusplus
Constraint Violating Yak Guru
481 Posts |
 Posted - 01/13/2013 : 07:08:01
|
Is it possible, I want to create same number of rows as number of days in a month, with avg_dailyValue Amount.
From the below query for Month_No:1, it has 31 days, so i need to create 31 rows.
for month 2 create 29 rows since month two for 2012 has 29 days
From below data example it has to create total 182 rows for 6 months.
********************************************************
Declare @TDyl_Values table
(Acct_no varchar(6), fiscalYear varchar(4), Total_Mnth_Amount int, Month_No int, days_in_month int, Avg_Daily_Value int)
insert @TDyl_values
Select 'T34526','2012',100000, 1, 31, 3225 union all
Select 'S77256','2012',200000, 2, 29, 6896 union all
Select 'Y67256','2012',62000, 3, 31, 2000 union all
Select 'HG4526','2012',100000, 4, 30, 3333 union all
Select 'WP1226','2012',100000, 7, 31, 3225 union all
Select 'ZX4526','2012',100000, 11, 30, 3333
select * from @TDyl_values
********************************************************
With the above data it has to create 182 rows for above 6 months data(31 + 29 + 31 + 30 + 31 + 30)
Here is the example for jan 2012 month 31 rows.
Declare @TDyl_DailyRows table (Acct_no varchar(6), fiscalYear varchar(4), Total_Mnth_Amount int, Month_No int, days_in_month int, Period_Date varchar(8), Avg_Daily_Value int)
insert @TDyl_DailyRows
Select 'T34526','2012',100000, 1, 31, '01012012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01022012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01032012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01042012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01052012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01062012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01072012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01082012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01092012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01102012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01112012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01122012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01132012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01142012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01152012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01162012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01172012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01182012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01192012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01202012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01212012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01222012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01232012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01242012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01252012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01262012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01272012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01282012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01292012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01302012', 3225 union all
Select 'T34526','2012',100000, 1, 31, '01312012', 3225
Select * from @TDyl_DailyRows
Thank you very much for the helpful info.
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 01/13/2013 : 08:53:57
|
select
a.*,
fiscalYear*10000+month_no*100+number as PeriodDate,
cast(cast(fiscalYear*10000+month_no*100+number as char(8)) as datetime) as PeriodDate2
from
@Tdyl_values a
cross join master..spt_values m
where
m.number between 1 and days_in_month
and m.type = 'P'; If you have a numbers table in your database, use that instead of master..spt_values.
If you are going to store the data, you probably should store it as a date rather than a number (PeriodDate2 in my example above). |
 |
|
|
cplusplus
Constraint Violating Yak Guru
481 Posts |
Posted - 01/13/2013 : 10:34:53
|
Sunita,
It worked perfect thank you very much.
Happy Pongal.
|
Edited by - cplusplus on 01/13/2013 10:39:47 |
|
|
|
Jeff Moden
Aged Yak Warrior
USA
643 Posts |
Posted - 01/13/2013 : 22:19:11
|
quote:
Originally posted by sunitabeck
select
a.*,
fiscalYear*10000+month_no*100+number as PeriodDate,
cast(cast(fiscalYear*10000+month_no*100+number as char(8)) as datetime) as PeriodDate2
from
@Tdyl_values a
cross join master..spt_values m
where
m.number between 1 and days_in_month
and m.type = 'P'; If you have a numbers table in your database, use that instead of master..spt_values.
If you are going to store the data, you probably should store it as a date rather than a number (PeriodDate2 in my example above).
+3 
--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
USA
643 Posts |
Posted - 01/13/2013 : 22:20:02
|
quote:
Originally posted by cplusplus
Sunita,
It worked perfect thank you very much.
Happy Pongal.
The next question is, do you understand how it works and how an explicit loop was avoided here?
--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." |
|
|
|
ScottPletcher
Yak Posting Veteran
USA
79 Posts |
Posted - 01/14/2013 : 18:03:06
|
| And do you understand that table "master..spt_values" could completely go away in a future release of SQL, breaking your code, or worse, have rows removed from it, rendering your results incorrect w/o an error occuring? |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 01/14/2013 : 18:13:28
|
quote:
And do you understand that table "master..spt_values" could completely go away in a future release of SQL, breaking your code, or worse, have rows removed from it, rendering your results incorrect w/o an error occuring?
Yikes! Is MS thinking of getting rid of spt_values? It's so darn convenient.
Jim
Everyday I learn something that somebody else already knew |
|
|
|
ScottPletcher
Yak Posting Veteran
USA
79 Posts |
Posted - 01/14/2013 : 18:19:19
|
The issue is: We can't know.
So the only safe thing is not to use it. Using it requires assuming both: (1) it will still/"always" be there, and (2) it will still have the same key values as before. |
|
|
|
cplusplus
Constraint Violating Yak Guru
481 Posts |
Posted - 01/14/2013 : 18:43:58
|
What is the alternative to use in place of : master..spt_values
Thanks for the helpful info.
|
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 01/14/2013 : 19:50:25
|
It's always a good idea to have a TallyTable -- a table of numbers. There are many ways to create them. Mine also has dates in it, for those queries that need to return a record, even though there's no info for that date. This produces a lot numbers.
select ROW_NUMBER() over(order by (select 1))
from sys.columns a,sys.columns b
Jim
Everyday I learn something that somebody else already knew |
|
|
|
enjoydiablo3
Starting Member
4 Posts |
Posted - 01/17/2013 : 05:05:49
|
| unspammed |
|
|
| |
Topic |
|
|
|
Logic to create number of rows in a month availabl
