March 5, 2016 at 5:46 am
Hello!
A simple question!
How can I generate 30 columns that are days of the month, between two dates ?
Thank you!
March 5, 2016 at 4:03 pm
Try this
DECLARE @StartDate date= '2016-03-01';
DECLARE @EndDate date= '2016-03-31';
DECLARE @Days int;
SELECT @Days = DATEDIFF(dd, @StartDate, @EndDate) + 1;
SELECT TOP (@Days)
[DateKey] = CONVERT(int, REPLACE(CONVERT(date, DATEADD(dd, ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) - 1, @StartDate)), '-', '')),
[Date] = CONVERT(date, DATEADD(dd, ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) - 1, @StartDate))
FROM sys.all_columns AS ac1
CROSS JOIN sys.all_columns AS ac2;
Igor Micev,My blog: www.igormicev.com
March 7, 2016 at 2:46 pm
JoseACJr (3/5/2016)
Hello!A simple question!
How can I generate 30 columns that are days of the month, between two dates ?
Thank you!
What do you mean "generate 30 columns"?
How should they look like?
A table? A dynamic dataset?
_____________
Code for TallyGenerator
March 7, 2016 at 3:38 pm
Sergiy (3/7/2016)
JoseACJr (3/5/2016)
Hello!A simple question!
How can I generate 30 columns that are days of the month, between two dates ?
Thank you!
What do you mean "generate 30 columns"?
How should they look like?
A table? A dynamic dataset?
I presume he needs the columns in the table be the dates of the months.
With the query above you have the columns. With a little work he can generate the CREATE statement for the tables, but it's not clear what he exactly needs.
Igor Micev,My blog: www.igormicev.com
March 7, 2016 at 7:15 pm
Igor Micev (3/7/2016)
but it's not clear what he exactly needs.
It's always the story about those "simple questions".
🙂
_____________
Code for TallyGenerator
March 8, 2016 at 7:36 am
JoseACJr (3/5/2016)
Hello!A simple question!
How can I generate 30 columns that are days of the month, between two dates ?
Thank you!
Give us something here. You just want 30 empty columns? Do you care what they are named? Why 30? Or do you really want a dynamic number of columns based and the number of days between two dates? What about the data for these columns? I could come up with several possible answers here but they all depend on what you are really trying to do.
_______________________________________________________________
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/
March 8, 2016 at 7:51 am
Here is one such possibility. This will generate any number of empty columns with the name of the date using 2 date variables as the start and end values. This is using the concepts of a dynamic cross tab where we use a tally table to generate dynamic sql. I have a view in my system for a tally that looks like this.
create View [dbo].[cteTally] as
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select N from cteTally
So we can leverage the power of a tally table here to make this kind of thing pretty simple. You can read more about tally tables here. http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]. Using the power of this table we can do things like dynamic cross tabs. You can follow the links in my signature about cross tabs for a more in depth look at those concepts. Putting them together you can do something like this.
declare @StartDate date = '20160101'
, @EndDate date = '20160115'
declare @DynamicColumns nvarchar(max) = '';
select @DynamicColumns = @DynamicColumns +
', NULL as [' + CONVERT(char(10), DATEADD(day, N - 1, @StartDate)) + ']'
from cteTally t
where t.N <= DATEDIFF(DAY, @StartDate, @EndDate) + 1
declare @SqlToExecute nvarchar(max) = 'Select' + STUFF(@DynamicColumns, 1, 1, '')
exec sp_executesql @SqlToExecute
_______________________________________________________________
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/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply