Author |
Topic |
cplusplus
Aged Yak Warrior
567 Posts |
Posted - 2013-01-13 : 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 daysFrom 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_valuesSelect 'T34526','2012',100000, 1, 31, 3225 union allSelect 'S77256','2012',200000, 2, 29, 6896 union allSelect 'Y67256','2012',62000, 3, 31, 2000 union allSelect 'HG4526','2012',100000, 4, 30, 3333 union allSelect 'WP1226','2012',100000, 7, 31, 3225 union allSelect 'ZX4526','2012',100000, 11, 30, 3333select * 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_DailyRowsSelect 'T34526','2012',100000, 1, 31, '01012012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01022012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01032012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01042012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01052012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01062012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01072012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01082012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01092012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01102012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01112012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01122012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01132012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01142012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01152012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01162012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01172012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01182012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01192012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01202012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01212012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01222012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01232012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01242012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01252012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01262012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01272012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01282012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01292012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01302012', 3225 union allSelect 'T34526','2012',100000, 1, 31, '01312012', 3225Select * from @TDyl_DailyRows Thank you very much for the helpful info. |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2013-01-13 : 08:53:57
|
[code]select a.*, fiscalYear*10000+month_no*100+number as PeriodDate, cast(cast(fiscalYear*10000+month_no*100+number as char(8)) as datetime) as PeriodDate2from @Tdyl_values a cross join master..spt_values mwhere m.number between 1 and days_in_month and m.type = 'P';[/code]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
Aged Yak Warrior
567 Posts |
Posted - 2013-01-13 : 10:34:53
|
Sunita,It worked perfect thank you very much.Happy Pongal. |
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-01-13 : 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 PeriodDate2from @Tdyl_values a cross join master..spt_values mwhere 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
652 Posts |
Posted - 2013-01-13 : 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
Aged Yak Warrior
550 Posts |
Posted - 2013-01-14 : 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
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-14 : 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.JimEveryday I learn something that somebody else already knew |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-01-14 : 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
Aged Yak Warrior
567 Posts |
Posted - 2013-01-14 : 18:43:58
|
What is the alternative to use in place of : master..spt_valuesThanks for the helpful info. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-14 : 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 bJimEveryday I learn something that somebody else already knew |
|
|
enjoydiablo3
Starting Member
4 Posts |
Posted - 2013-01-17 : 05:05:49
|
unspammed |
|
|
|
|
|