Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

TSQL split?

Posted on 2014-11-17
2
Medium Priority
?
167 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 2000 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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

721 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