Logic to create number of rows in a month availabl

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)

Logic to create number of rows in a month availabl

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 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
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 PeriodDate2
from
@Tdyl_values a
cross join master..spt_values m
where
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 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).

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

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.

Jim

Everyday 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_values

Thanks 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 b

Jim

Everyday I learn something that somebody else already knew

enjoydiablo3
Starting Member

4 Posts

Posted - 2013-01-17 : 05:05:49

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources