February 7, 2013 at 9:10 am
Evening All
I wonder if you can help me, I have created a table but when I query the data I don't get the results in the right order:
Period 1
Period 10
Period 11
Period 12
Period 2
What I would like to see is
Period 01
Period 02
The line of code that I use for this data is:
'Period ' + CAST (MONTH(Dateadd(MONTH, -3,@StartDate)) AS VARCHAR(2)) AS FinancialMonth
It is part of a larger SELECT statement, can you please let me know what to do next because I haven't got the foggiest.
Thanks
Wayne
February 7, 2013 at 9:13 am
ORDER BY MONTH(Dateadd(MONTH, -3,@StartDate))
Without your query really can't tell you much more than that.
February 7, 2013 at 9:15 am
Hi Lynn
Here is my query.
USE Occupancy
CREATE TABLE Time2
(Datedate not null,
CalendarYearint not null,
CalendarMonthvarchar (30) not null,
FinancialYearint not null,
FinancialMonthvarchar (30) not null)
DECLARE
@StartDate Date,
@EndDate Date
SET @StartDate = '01 Jan 2010'
SET @EndDate = '31 Mar 2015'
WHILE @StartDate <= @EndDate
BEGIN
INSERT INTO Time2(Date,CalendarYear,CalendarMonth,FinancialYear,FinancialMonth)
SELECT
@StartDate Date,YEAR(@StartDate) AS CalendarYear,
DATENAME(MONTH,@StartDate) AS CalendarMonth,
YEAR(Dateadd(MONTH,-3,@StartDate)) AS FinancialYear,
'Period ' + CAST (MONTH(Dateadd(MONTH, -3,@StartDate)) AS VARCHAR(2)) AS FinancialMonth
SET @StartDate = DATEADD(dd,1,@StartDate)
END
February 7, 2013 at 9:35 am
wafw1971 (2/7/2013)
Evening AllI wonder if you can help me, I have created a table but when I query the data I don't get the results in the right order:
Period 1
Period 10
Period 11
Period 12
Period 2
What I would like to see is
Period 01
Period 02
The line of code that I use for this data is:
'Period ' + CAST (MONTH(Dateadd(MONTH, -3,@StartDate)) AS VARCHAR(2)) AS FinancialMonth
It is part of a larger SELECT statement, can you please let me know what to do next because I haven't got the foggiest.
Thanks
Wayne
I don't understand at all how your second post with a loop insert has anything to do with this query. However, the values that you posted are sorted correctly. You said your table has:
Period 1
Period 10
Period 11
Period 12
Period 2
This is sorted exactly as a string will sort, from left to right. If that column always contains 'period ' maybe the column should be named "Period" and make the datatype an int so you can sort it correctly.
You can force the sort order but the performance is going to suffer a bit.
Here are a couple of ways it could be done.
;with Periods(Period)
as
(
select 'Period 1' union all
select 'Period 10' union all
select 'Period 11' union all
select 'Period 12' union all
select 'Period 2'
)
select *
from Periods
order by cast(replace(period, 'Period ', '') as int)
OR
;with Periods(Period)
as
(
select 'Period 1' union all
select 'Period 10' union all
select 'Period 11' union all
select 'Period 12' union all
select 'Period 2'
)
select * from
(
select Period, cast(replace(period, 'Period ', '') as int) as PeriodNum
from Periods
)x
order by PeriodNum
_______________________________________________________________
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/
February 7, 2013 at 9:43 am
Everything looks orderd to me. I wouldn't use a while loop to populate the table dbo.Time2 and you really should not use Date as a name for a column.
See below:
CREATE TABLE dbo.Time2(
CalendarDate date not null,
CalendarYear int not null,
CalendarMonth varchar (30) not null,
FinancialYear int not null,
FinancialMonth varchar (30) not NULL
);
DECLARE
@StartDate Date,
@EndDate Date
SET @StartDate = '2010-01-01';
SET @EndDate = '2015-03-31';
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 cross join e1 b),
e4(n) AS (SELECT 1 FROM e2 a cross join e2 b),
eTally(n) AS (SELECT 0 UNION ALL select top (DATEDIFF(dd,@StartDate,@EndDate)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM e4 a cross join e2 b)
INSERT INTO dbo.Time2(
CalendarDate,
CalendarYear,
CalendarMonth,
FinancialYear,
FinancialMonth
)
SELECT
dateadd(dd,n,@StartDate),
YEAR(dateadd(dd,n,@StartDate)),
DATENAME(MONTH,dateadd(dd,n,@StartDate)),
YEAR(Dateadd(MONTH,-3,dateadd(dd,n,@StartDate))),
'Period ' + CAST (MONTH(Dateadd(MONTH, -3,dateadd(dd,n,@StartDate))) AS VARCHAR(2))
FROM
eTally;
select * from dbo.Time2 order by CalendarDate;
drop table dbo.Time2;
February 7, 2013 at 9:44 am
Hi Sean
Would it be possible to add a 0 in front of the first 9 numbers, would this make it easier?
Thanks
Wayne
February 7, 2013 at 9:49 am
wafw1971 (2/7/2013)
Hi SeanWould it be possible to add a 0 in front of the first 9 numbers, would this make it easier?
Thanks
Wayne
Simple change, look at my code below:
CREATE TABLE dbo.Time2(
CalendarDate date not null,
CalendarYear int not null,
CalendarMonth varchar (30) not null,
FinancialYear int not null,
FinancialMonth varchar (30) not NULL
);
DECLARE
@StartDate Date,
@EndDate Date
SET @StartDate = '2010-01-01';
SET @EndDate = '2015-03-31';
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 cross join e1 b),
e4(n) AS (SELECT 1 FROM e2 a cross join e2 b),
eTally(n) AS (SELECT 0 UNION ALL select top (DATEDIFF(dd,@StartDate,@EndDate)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM e4 a cross join e2 b)
INSERT INTO dbo.Time2(
CalendarDate,
CalendarYear,
CalendarMonth,
FinancialYear,
FinancialMonth
)
SELECT
dateadd(dd,n,@StartDate),
YEAR(dateadd(dd,n,@StartDate)),
DATENAME(MONTH,dateadd(dd,n,@StartDate)),
YEAR(Dateadd(MONTH,-3,dateadd(dd,n,@StartDate))),
'Period ' + RIGHT('0' + CAST(MONTH(Dateadd(MONTH, -3,dateadd(dd,n,@StartDate))) AS VARCHAR(2)),2)
FROM
eTally;
select * from dbo.Time2;
drop table dbo.Time2;
February 7, 2013 at 10:00 am
Lynn Pettis (2/7/2013)
Everything looks orderd to me. I wouldn't use a while loop to populate the table dbo.Time2 and you really should not use Date as a name for a column.
Lynn I have suggested all these in another thread. Poor guy is brand new to sql and his boss insists on using a while loop. He has been shown a tally table approach at least 3 times but his hands are tied by a boss who refuses to listen.
_______________________________________________________________
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/
February 7, 2013 at 10:07 am
Sean Lange (2/7/2013)
Lynn Pettis (2/7/2013)
Everything looks orderd to me. I wouldn't use a while loop to populate the table dbo.Time2 and you really should not use Date as a name for a column.Lynn I have suggested all these in another thread. Poor guy is brand new to sql and his boss insists on using a while loop. He has been shown a tally table approach at least 3 times but his hands are tied by a boss who refuses to listen.
Noticed. Doesn't mean I have to use the WHILE LOOP to show how things can be done. It hurts to write WHILE LOOPS, too much unnecessary thinking required to be sure it is done correctly.
February 7, 2013 at 10:09 am
wafw1971 (2/7/2013)
Hi SeanWould it be possible to add a 0 in front of the first 9 numbers, would this make it easier?
Thanks
Wayne
Of course it is possible, but it would be FAR better to drop the text from that column so it can become an int instead.
if object_id('tempdb..#periods') is not null
drop table #periods
create table #Periods
(
Period varchar(20)
)
insert #Periods
select 'Period 1' union all
select 'Period 10' union all
select 'Period 11' union all
select 'Period 12' union all
select 'Period 2'
--demonstrates "incorrect" ordering
select * from #Periods order by Period
update #periods
set Period = replace(Period, 'Period ', 'Period 0')
where len(Period) = 8
--They are no ordered "correctly"
select * from #Periods order by Period
--Now let's demonstrate how much easier this is if the column is an integer instead of a denormalized string.
update #Periods set Period = right(Period, 2)
alter table #Periods
alter column Period int
select * from #Periods order by Period
_______________________________________________________________
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/
February 7, 2013 at 10:12 am
Lynn Pettis (2/7/2013)
Sean Lange (2/7/2013)
Lynn Pettis (2/7/2013)
Everything looks orderd to me. I wouldn't use a while loop to populate the table dbo.Time2 and you really should not use Date as a name for a column.Lynn I have suggested all these in another thread. Poor guy is brand new to sql and his boss insists on using a while loop. He has been shown a tally table approach at least 3 times but his hands are tied by a boss who refuses to listen.
Noticed. Doesn't mean I have to use the WHILE LOOP to show how things can be done. It hurts to write WHILE LOOPS, too much unnecessary thinking required to be sure it is done correctly.
Agreed 100%. I would certainly not suggest using a loop to demonstrate. 😛 I was just filling you in on some of the back story.
_______________________________________________________________
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/
February 7, 2013 at 10:29 am
Sean Lange (2/7/2013)
Lynn Pettis (2/7/2013)
Sean Lange (2/7/2013)
Lynn Pettis (2/7/2013)
Everything looks orderd to me. I wouldn't use a while loop to populate the table dbo.Time2 and you really should not use Date as a name for a column.Lynn I have suggested all these in another thread. Poor guy is brand new to sql and his boss insists on using a while loop. He has been shown a tally table approach at least 3 times but his hands are tied by a boss who refuses to listen.
Noticed. Doesn't mean I have to use the WHILE LOOP to show how things can be done. It hurts to write WHILE LOOPS, too much unnecessary thinking required to be sure it is done correctly.
Agreed 100%. I would certainly not suggest using a loop to demonstrate. 😛 I was just filling you in on some of the back story.
Agreed. If he is learning, though, he needs to start asking "Why," and because his boss said so isn't an answer. He needs to try and get his boss to explain his reasoning behind the way he is asking things to be done. It could simply be his boss doesn't know any better.
February 8, 2013 at 2:42 am
Morning All
Thanks for all your replies and help I just wanted to share with you the code that I have built which populates multiple columns and puts the Financial Month Period into the right order when the table is queried, all I need to do know is make sure the CalendarMonth when queried is in month order for example Jan, Feb and not Apr, Aug etc.
USE Occupancy
CREATE TABLE Time3
(Datedate not null,
CalendarYearint not null,
CalendarMonthvarchar (30) not null,
FinancialYearint not null,
FinancialMonthvarchar (30) not null)
DECLARE
@StartDate Date,
@EndDate Date
SET @StartDate = '01 Jan 2010'
SET @EndDate = '31 Mar 2015'
WHILE @StartDate <= @EndDate
BEGIN
INSERT INTO Time3(Date,CalendarYear,CalendarMonth,FinancialYear,FinancialMonth)
SELECT
@StartDate Date,YEAR(@StartDate) AS CalendarYear,
DATENAME(MONTH,@StartDate) AS CalendarMonth,
YEAR(Dateadd(MONTH,-3,@StartDate)) AS FinancialYear,
'Period ' + RIGHT('0' + CAST(MONTH(Dateadd(MONTH, -3,@StartDate)) AS VARCHAR(2)),2) AS FinancialMonth
SET @StartDate = DATEADD(dd,1,@StartDate)
END
February 8, 2013 at 2:57 am
wafw1971 (2/8/2013)
all I need to do know is make sure the CalendarMonth when queried is in month order for example Jan, Feb and not Apr, Aug etc.
For that you need an order by on the query that retrieves from Calendar month. The inserts into the table is pretty irrelevant when trying to query the table (there is no default order in SQL)
And to re-emphasis the above, there's no need for that while loop.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2013 at 3:15 am
Hi Gail
Do you mean:
DATENAME(MONTH,@StartDate) AS CalendarMonth, <------ Add an ORDER BY Somewhere here
or actually on the query, because I have been asked to hardcode it in the code like I did for the FinancialMonth Periods
'Period ' + RIGHT('0' + CAST(MONTH(Dateadd(MONTH, -3,@StartDate)) AS VARCHAR(2)),2) AS FinancialMonth
Thanks in advance.
Wayne
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply