January 27, 2014 at 7:31 am
Hi,
On a monthly basis we're downloading a dataset from one of our systems and then processing it so that a record is created for each date between the start and end date of each downloaded record. I had thought of trying to use a CTE, but the number of days is variable so I didn't think it would work.
Is there a better way of doing this in SQL Server 2008 and still allow queries to be written against the data as if it was split into the individual days?
Cheers
Matt
January 27, 2014 at 7:58 am
Matt J (1/27/2014)
Hi,On a monthly basis we're downloading a dataset from one of our systems and then processing it so that a record is created for each date between the start and end date of each downloaded record. I had thought of trying to use a CTE, but the number of days is variable so I didn't think it would work.
Is there a better way of doing this in SQL Server 2008 and still allow queries to be written against the data as if it was split into the individual days?
Cheers
Matt
Pretty sparse on actual details but yes there is a better way. It is called a tally table. I would recommend not generating empty rows as a placeholder for missing values. This is where the tally table can really help. Properly used it can generate your missing rows as needed dynamically.
Take a look at this article about what a tally is and some ways they can be used. This is kind of the sql server swiss army knife.
http://www.sqlservercentral.com/articles/62867/%5B/url%5D
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 27, 2014 at 8:10 am
Hi Sean,
Apologies for the lack of detail, getting what I'm trying to achieve into words is always a struggle.
It's occupied bed data that's downloaded, it has a start and end date, but to query the data each day needs to be "accessible" in the query and not just the start and end dates.
I shall have a read of the tally table article.
Thank you 😀
Matt
January 27, 2014 at 8:36 am
Matt J (1/27/2014)
Hi Sean,Apologies for the lack of detail, getting what I'm trying to achieve into words is always a struggle.
It's occupied bed data that's downloaded, it has a start and end date, but to query the data each day needs to be "accessible" in the query and not just the start and end dates.
I shall have a read of the tally table article.
Thank you 😀
Matt
No problem Matt. It is something we all struggle with. Around here the best thing you can do is to create ddl and sample data along with desired output for the sample data.
Let me know if you have questions or need some help to figure out how you can use a tally table to "fill in the holes" in your data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 27, 2014 at 8:40 am
Thanks Sean, really appreciated.
Matt
January 27, 2014 at 8:44 am
Read that article, and then give us some DDL For your result set and some sample data as insert statements. We can help.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply