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)
 Logic to create number of rows in a month availabl
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cplusplus
Aged Yak Warrior

536 Posts

Posted - 01/13/2013 :  07:08:01  Show Profile  Reply with Quote
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

5155 Posts

Posted - 01/13/2013 :  08:53:57  Show Profile  Reply with Quote
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).
Go to Top of Page

cplusplus
Aged Yak Warrior

536 Posts

Posted - 01/13/2013 :  10:34:53  Show Profile  Reply with Quote
Sunita,
It worked perfect thank you very much.
Happy Pongal.

Edited by - cplusplus on 01/13/2013 10:39:47
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/13/2013 :  22:19:11  Show Profile  Reply with Quote
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."
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/13/2013 :  22:20:02  Show Profile  Reply with Quote
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."
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
366 Posts

Posted - 01/14/2013 :  18:03:06  Show Profile  Reply with 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?
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

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

ScottPletcher
Constraint Violating Yak Guru

USA
366 Posts

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

cplusplus
Aged Yak Warrior

536 Posts

Posted - 01/14/2013 :  18:43:58  Show Profile  Reply with Quote
What is the alternative to use in place of : master..spt_values

Thanks for the helpful info.
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/14/2013 :  19:50:25  Show Profile  Reply with Quote
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
Go to Top of Page

enjoydiablo3
Starting Member

4 Posts

Posted - 01/17/2013 :  05:05:49  Show Profile  Reply with Quote
unspammed
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