Split single row in multiple rows based on date range
declare @d datetime;
set @d = getdate();

select *
from   YourTable
where  @d between StartDate and EndDate;

November 15th, 2014 1:35am

I need sql script that can split single row in multiple rows based on start date and end date column in table.

Thank you


Free Windows Admin Tool Kit Click here and download it now
November 15th, 2014 2:02am

I didn't understand - "split single row in multiple row"

Could you please provide sample input row, start date and end date and the output you expect ?

November 15th, 2014 2:04am

Hi Tanuja,

Your question is not clear, but I can try to guess that you have a table with column for starting data and column for end date (let's named them SD and ED for this discussion), and you want to get a result SET with all the dates from SD to ED.

If this is the case, the you need to use an accessory's table, like a dates table or numbers table. The idea of dates table is to store all dates for 1000 years (it is only about 365k rows, but you can store less years if you like). This table is well indexed and can help you in lot of calenders needs like this question. It is highly recommend to have dates table for most databases that deal with calendars.

Using this table you only need to use JOIN the original table with this table, in order to get what you need.

something like : select T.A,T.b D.D from Original_Table as T left JOIN Your_Date_Table as D on D.D between SD and ED

* If you dont have dates table then you can use Numbers table (which is  another accessory's table, but this table you must have in any server in my opinion... It is very useful for lots of queries). Using numbers table with the function DATEADD you can use the same solution above.

** If this is not what you are looking for, than please clarify your question by posting your table create query, a query to insert some sample data, and posting the result SET that you want to get according to the sample data.

Free Windows Admin Tool Kit Click here and download it now
November 15th, 2014 2:48am

I think you are looking to have your single row displayed in multiple rows (word rap), In T-SQL you cant do it. If you are displaying that text in a application text box , then check the text box properties to enable multi-line.
November 15th, 2014 3:22am

I guess this

;With Numbers
AS
(
SELECT 1 AS N UNION ALL
SELECT 1  UNION ALL
SELECT 1  UNION ALL
SELECT 1  UNION ALL
SELECT 1  UNION ALL
SELECT 1  UNION ALL
SELECT 1  UNION ALL
SELECT 1  UNION ALL
SELECT 1  UNION ALL
SELECT 1  UNION ALL
SELECT 1  
),Num_Matrix
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Seq
FROM Numbers n1
CROSS JOIN Numbers n2
CROSS JOIN Numbers n3
CROSS JOIN Numbers n4
CROSS JOIN Numbers n5
)
SELECT DATEADD(dd,Seq-1,StartDate), ... other fields
FROM YourTable t
INNER JOIN Num_Matrix
WHERE Seq BETWEEN 1 AND DATEDIFF(dd,StartDate,EndDate)+1

Free Windows Admin Tool Kit Click here and download it now
November 15th, 2014 3:44am

Yes... Thanks Visakh, this is the basic implementation of the solution, that i suggested above (using numbers table), but if I may comment something very small :-)

The whole idea of using numbers table (or dates table which is better in this case), is to have a real table in the database with clustered index and not creating it on-the-fly, as in-line code like CTE, or variable, or even temporary table (which is better then virtual table like in CTE since we can index it). This table only needs to created once for each instance (we dont even have to create in each database... I use a read only database with those accessory's tables for example), and this table will help us in lot of cases.

November 15th, 2014 8:49am

I agree to your suggestion of having a dates table permanently in the database. Thats how we also do for most of our projects as well

But in most projects the ownership  of table creation etc lies with the client as they will be the DBAs and will be design approval authorities. What I've seen is the fact that though many of them are in favour of having calendar table they dont generally prefer having a permanent table for numbers in the db. The best that they would agree is for creating a UDF which will have tally table functionality built into it based on a number range and can be used in cases where we need to multiply records as above.

Wherever we have the freedom of doing design then I would also prefer creating it as a permanent table with required indexes as you suggested.

Free Windows Admin Tool Kit Click here and download it now
November 15th, 2014 8:59am

I agree to your suggestion of having a dates table permanently in the database. Thats how we also do for most of our projects as well

But in most projects the ownership  of table creation etc lies with the client as they will be the DBAs and will be design approval authorities. What I've seen is the fact that though many of them are in favour of having calendar table they dont generally prefer having a permanent table for numbers in the db. The best that they would agree is for creating a UDF which will have tally table functionality built into it based on a number range and can be used in cases where we need to multiply records as above.

Wherever we have the freedom of doing design then I would also prefer creating it as a permanent table with required indexes as you suggested.

>> many of them are in favour of having calendar table they dont generally prefer having a permanent table

Those people do not understand about database and are not DBAs :-)
It is our job to tell them what is right or wrong.

** This is a real story! I had a client several years back, who was the CEO of a software company.
He use the query:
select * from table_name
In order to get the last ID!
The table_name was actually a view that took data from several tables, and the main table that he wanted to get the ID included several string columns, with lot of data! he actually pulled all this data to the application, just to get the lat ID in a specific table!

It is our job as Consultants or DBAs to fix's his misunderstanding :-)

November 15th, 2014 9:14am

Sorry it doesn't always work that way Pituach :-)

Most of them are very keen on fact that they need only those table which have any direct business relevance and forms part of their approved data model. That's why they're against having permanent number table even when in favor of calendar table. 



Free Windows Admin Tool Kit Click here and download it now
November 15th, 2014 9:40am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics