February 8, 2013 at 4:09 am
No, I mean that the query that retrieves from that table needs to have an ORDER BY clause on it.
How the data is inserted into the table is absolutely and completely irrelevant to the ordering of data in a query that retrieves from the table. If a query that reads from the table needs the data in a particular order, that query must have an order by clause
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 5:08 am
Hi Gail
Thank you for your replies, however I have been tasked with making sure that the Month name is already ordered in Jan, Feb etc when query is made, so for that to happen it needs to hardcoded into the code. I had a similar problem yesterday with the Financial Month column and was able to solve this within the SQL code, I was hoping I could do something similar with the Calendar Month column:
Financial Month
'Period ' + RIGHT('0' + CAST(MONTH(Dateadd(MONTH, -3,@StartDate)) AS VARCHAR(2)),2) AS FinancialMonth
With the above SQL in place when I the following query:
USE Occupancy
SELECT DISTINCT FinancialMonth
FROM Time3
ORDER BY FinancialMonth
I get Period 01, Period 02, Period 03 and not Period 1, Period 10, Period 12.
Hopefully when I can do something similar to the Calendar Month SQL Code, and run the following query:
USE Occupancy
SELECT DISTINCT CalendarMonth
FROM Time3
ORDER BY CalendarMonth
I get January, February, March and not April, August, December.
I hope that makes more sense.
Thanks again
Wayne
February 8, 2013 at 5:09 am
I believe Gail had this in mind,
SELECT
CalendarMonth
, CaldendarDate
From Table
Order by CalendarMonth
By Default the data is ordered ASCENDING, NOTE : varchar/char (etc) fields will sort differently depending on factors such as the Collation setting of the column/database.
However you can also specify the Order by using
SELECT
CalendarMonth
, CaldendarDate
From Table
Order by CalendarMonth ASC
--OR
SELECT
CalendarMonth
, CaldendarDate
FROM Table
ORDER BY CalendarMonth DESC
The first query order by calendar month ASC, the second by DESC
you can also order columns in differnent ways, eg
SELECT
CalendarMonth
, CaldendarDate
FROM Table
ORDER BY
CalendarMonth ASC
,CalendarDate DESC
This will order the CalendarMonth Ascending and all the dates DESC.
Hope this helps.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 8, 2013 at 5:21 am
Hi Jason
Thanks for that, but I need to know how to add a sort to the SQL Code, not the query. It maybe me not making it clear and I apologise for that but as you can from my previous post to Gail I need to hardcode it so when someone does query the data it comes out in the right order.
Thanks
Wayne
February 8, 2013 at 5:39 am
wafw1971 (2/8/2013)
...SELECT DISTINCT CalendarMonth
FROM Time3
ORDER BY CalendarMonth
I get January, February, March and not April, August, December.
No. When you ORDER BY CalendarMonth, it will do exactly that, not what you automagically want it to do. If you want it to order by the sequence of the months in the year, you will have to get a little smarter:
SELECT CalendarMonth
FROM dbo.IF_Calendar ('01 Jan 2010', '31 Mar 2015')
GROUP BY CalendarMonth
ORDER BY MIN([date])
In this case dbo.IF_Calendar is an inline function which outputs the whole sequence of dates in the range you specify. Here's the function definition:
ALTER FUNCTION [dbo].[IF_Calendar]
(
@StartDate Date,
@EndDate Date
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
(
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),
Tally(n) AS (SELECT 0 UNION ALL
SELECT TOP(DATEDIFF(dd,@StartDate,@EndDate))
n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM e4 a cross join e2 b)
SELECT
[Date]= StartDate,
CalendarYear= YEAR(StartDate),
CalendarMonth= DATENAME(MONTH,StartDate),
FinancialYear= YEAR(Dateadd(MONTH,-3,StartDate)),
FinancialMonth= 'Period ' + RIGHT('0' + CAST(MONTH(Dateadd(MONTH, -3,StartDate)) AS VARCHAR(2)),2)
FROM (
SELECT StartDate = DATEADD(dd,b.n,@StartDate)
FROM Tally b
) d
)
GO
Edit: manky code
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 8, 2013 at 5:45 am
Thanks everyone for your help, but I am new to SQL so I maybe not getting across what I need, so apologies for that.
Below is my entire string, as you can see the Financial Month is working brilliantly so when I query this table I get Period 01, Period 02, Period 03 instead of Period 1, Period 10, Period 11. So when I query the Table for Calendar Month I want to see January, February and not April, August. I know I can change the query but I have been told to hardcode like the Financial Month Section below.
USE Occupancy
CREATE TABLE Time3
(Date 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 = '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
I hope this makes a little bit more sense.
Thanks again
Wayne
February 8, 2013 at 5:56 am
wafw1971 (2/8/2013)
Thanks for that, but I need to know how to add a sort to the SQL Code, not the query. It maybe me not making it clear and I apologise for that but as you can from my previous post to Gail I need to hardcode it so when someone does query the data it comes out in the right order.
There's no way to do that. If a query needs data in a specific order, that query absolutely must have an order by. There is no other way to guarantee order of a returned recordset.
The order that you insert rows is irrelevent to querying them. There is nothing you can do when populating a table that will absolutely always return the data in the 'correct' order. The Insert is not the code to look at. You need an Order By on the final select.
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 6:01 am
wafw1971 (2/8/2013)
... I have been told to hardcode like the Financial Month Section below.
That would be
01 January
02 February
03 March
Are you sure you want to do this?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 8, 2013 at 6:05 am
Hi Chris
Not exactly like the FinancialMonth line just something similar.
Thanks
Wayne
February 8, 2013 at 6:19 am
This will give you the CalendarMonth as you asked. For ordering, you could just have ordered by the Date column and it would have sorted correctly without this
CREATE TABLE Time3
(Date 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 = '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,
RIGHT('0' + CAST(MONTH(@StartDate) AS VARCHAR(2)),2) + ' ' + 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
SELECT * FROM Time3
ORDER BY CalendarYear, CalendarMonth
SELECT * FROM Time3
ORDER BY [Date]
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 6:46 am
Knowledge test for wafw1971.
You do know that there is no guarantee on the order of the data returned by SQL Server if you don't have an ORDER BY clause on the query that returns the data, correct?
February 8, 2013 at 6:52 am
Lynn Pettis (2/8/2013)
Knowledge test for wafw1971.You do know that there is no guarantee on the order of the data returned by SQL Server if you don't have an ORDER BY clause on the query that returns the data, correct?
Also, as Gail has pointed out, the data in a table isn't guaranteed to be in any particular order anyway - even if the INSERT statements make it appear as if they might.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 8, 2013 at 6:53 am
Hi Lynn
Yes I knew that, the questions isn't really about about querying the data, I know there are plenty of ways to get the information from a query. What the questions is about is setting up the SELECT statement within the table to have that information sorted already so when some one does query the data its not in the wrong order. And also it doesn't affect the Table itself.
So if you run this script and then query the date the FinancialMonths are in Period Order
USE Occupancy
CREATE TABLE Time
(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 Time(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
Thanks
Wayne
February 8, 2013 at 6:58 am
wafw1971 (2/8/2013)
What the questions is about is setting up the SELECT statement within the table to have that information sorted already so when some one does query the data its not in the wrong order. And also it doesn't affect the Table itself.
Did you try the code I posted?
The query that retrieves from the table still needs an Order By, you're never going to get away from needing that
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 7:06 am
wafw1971 (2/8/2013)
Hi LynnYes I knew that, the questions isn't really about about querying the data, I know there are plenty of ways to get the information from a query. What the questions is about is setting up the SELECT statement within the table to have that information sorted already so when some one does query the data its not in the wrong order. And also it doesn't affect the Table itself.
So if you run this script and then query the date the FinancialMonths are in Period Order
USE Occupancy
CREATE TABLE Time
(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 Time(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
Thanks
Wayne
Yes I knew that, the questions isn't really about about querying the data, I know there are plenty of ways to get the information from a query. What the questions is about is setting up the SELECT statement within the table to have that information sorted already so when some one does query the data its not in the wrong order. And also it doesn't affect the Table itself.
You can't guarantee the order of the data by hard-coding information in the data, there is no order in a table except that specified by the ORDER BY clause in a query selecting the data from the table. No ORDER BY, NO GUARANTEE of the order of the data.
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply