declare @d datetime; set @d = getdate(); select * from YourTable where @d between StartDate and EndDate;
I need sql script that can split single row in multiple rows based on start date and end date column in table.
Thank you
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 ?
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.
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
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.
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.
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 :-)
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.