Solved

TSQL split?

Posted on 2014-11-17
2
162 Views
Last Modified: 2014-11-17
Hello,
There is a table with [periodGain] metric and that has a start and end date. I want to convert that data in such a way so that periodGain metric and its info are repeated for several rows at a single date level – where the date is between the start and the end. Please see attached excel. Let me know how I can do it.
Thank you
R
tsql-date-everyday.xlsx
0
Comment
Question by:Rayne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 13

Accepted Solution

by:
Koen Van Wielink earned 500 total points
ID: 40448940
This should do the trick:

--Test data

Create table #PeriodGain
(	periodGain		nvarchar(12)
,	category		nvarchar(12)
,	sub_group		nvarchar(12)
,	[start_DATE]	datetime
,	end_DATE		datetime
)

insert into #PeriodGain
values	('p0045', 'cat12', 's1', '20140101', '20140109')
	,	('p0098', 'cat19', 's2', '20140305', '20140321');
	
-- Insert the record with the start date in a CTE
-- then add a recursive part to add all the 
-- required dates between the start and end date.
With A
	(	periodGain
	,	category
	,	sub_group
	,	date_everyday	)
as
(
Select		periodGain
		,	category
		,	sub_group
		,	[start_DATE]
from	#PeriodGain

union all

Select		p.periodGain
		,	p.category
		,	p.sub_group
		,	DATEADD(day, 1, A.date_everyday)
from	A
		inner join #PeriodGain p
			on a.periodGain = p.periodGain
			and a.category = p.category
			and a.sub_group = p.sub_group
Where	A.date_everyday < p.end_DATE
)

-- Select final data set

select *
from A
order by	a.periodGain
		,	a.category
		,	a.sub_group
		,	a.date_everyday

-- Drop the temp table
drop table #PeriodGain

Open in new window


Using a recursive query you can keep adding the dates to the dataset until the condition A.date_everyday < p.end_DATE is met.
0
 

Author Comment

by:Rayne
ID: 40449132
Thank you Koen, this is AWESOME :)
I may ask question later if any

Thank you friend
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Viewers will learn how the fundamental information of how to create a table.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question