Order Issue

have a character field that represents a date in the format YYYYMM.
This data is broken down in three month blocks,
12 Dec
11 Nov
10 Oct
by this year and the corresponding 3 month block from last year.
So the data will look like this
200512
200511
200510
200412
200411
200410
I have a report that requires this order, but my result set is already being
put into another order and I want to know if I can convert the current YYYYM
M
format to a valid date.
Can this be done and how?
Thank you~~I'm not sure I follow you. If you have:
ORDER BY thecolumn DESC
don't you get the desired order? Also, why don't you store these as a smalld
atetime column?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anthony W DiGrigoli" <AnthonyWDiGrigoli@.discussions.microsoft.com> wrote in
message
news:6368A787-C7D5-4DBA-B0DE-4E7D2B6473D3@.microsoft.com...
> have a character field that represents a date in the format YYYYMM.
> This data is broken down in three month blocks,
> 12 Dec
> 11 Nov
> 10 Oct
> by this year and the corresponding 3 month block from last year.
> So the data will look like this
> 200512
> 200511
> 200510
> 200412
> 200411
> 200410
> I have a report that requires this order, but my result set is already bei
ng
> put into another order and I want to know if I can convert the current YYY
YMM
> format to a valid date.
> Can this be done and how?
> Thank you~~|||You can use multiple expressions in the "order by" clause.
Example:
select c1, ..., cn
from table1
order by
cast(left(c1, 4) as int) desc, -- here you order by year
cast(right(c1, 2) as int) desc -- here by month
AMB
"Anthony W DiGrigoli" wrote:

> have a character field that represents a date in the format YYYYMM.
> This data is broken down in three month blocks,
> 12 Dec
> 11 Nov
> 10 Oct
> by this year and the corresponding 3 month block from last year.
> So the data will look like this
> 200512
> 200511
> 200510
> 200412
> 200411
> 200410
> I have a report that requires this order, but my result set is already bei
ng
> put into another order and I want to know if I can convert the current YYY
YMM
> format to a valid date.
> Can this be done and how?
> Thank you~~

Order Group in specified order

Hi all,
We have recently migrated from Crystal Reports to SSRS. We are converting
all our company reports from one format to another. Some of our reports have
groupings in a specified order. When you sort order in Crystal you can select
"Specified Order" and then pick the names of the groups and place them in a
specified order to appear on the report, as opposed to ascending alphabetical
order for example.
How can I specify "Specified Order" when creating a Reporting Services report?
Thanks
JHI have never used Crystal Reports. But I think I understand what you
are asking. I had a similar issue where I had to explicity specify the
order of matrix columns. Neither RS nor my SQL SELECT ORDER BY did the
trick because I could do only Ascending/Descending or ASC/DESC
respectively. I would like to believe RS has some way to control
explicit order, but I sure cannot find it.
I solved my problem by doing the following
1) returned a dummy INT column from my SELECT statement. Made sure rows
in the dataset returned appropriate value for this column based on the
column data.
2) Changed the Column group in my matrix to Sort Ascending on THIS INT
column instead of using a data field.
Kludgy perhaps, but at the moment I could care less.
HTH.
Best.
JH wrote:
> Hi all,
> We have recently migrated from Crystal Reports to SSRS. We are converting
> all our company reports from one format to another. Some of our reports have
> groupings in a specified order. When you sort order in Crystal you can select
> "Specified Order" and then pick the names of the groups and place them in a
> specified order to appear on the report, as opposed to ascending alphabetical
> order for example.
> How can I specify "Specified Order" when creating a Reporting Services report?
> Thanks
> JH

ORDER BY Issue on funky field names

Hello,
I am using FOR XML EXPLICIT

Problem is, I need to sort by [MyColumn!1!MyCol].

This column contains date in string format, And I want it to be sorted as if it was a date.

so I tried this

ORDER BY CONVERT(DATETIME, [MyColumn!1!MyCol])

it gives me error that ORDER BY items must be in select list
The whole query is actually a UNION of 2 queries

Please help me with this

Thanks,Order by has problems with column aliases. So try:
ORDER BY CONVERT(DATETIME, <statement for column value>)
i you have
...
, my_string_date+' '+my_string_time as [MyColumn!1!MyCol]
....

use

ORDER BY CONVERT(DATETIME, my_string_date+' '+my_string_time)

ORDER BY Issue on funky field names

Hello,
I am using FOR XML EXPLICIT

Problem is, I need to sort by [MyColumn!1!MyCol].

This column contains date in string format, And I want it to be sorted as if it was a date.

so I tried this

ORDER BY CONVERT(DATETIME, [MyColumn!1!MyCol])

it gives me error that ORDER BY items must be in select list
The whole query is actually a UNION of 2 queries

Please help me with this

Thanks,Order by has problems with column aliases. So try:
ORDER BY CONVERT(DATETIME, <statement for column value>)
i you have
...
, my_string_date+' '+my_string_time as [MyColumn!1!MyCol]
....

use

ORDER BY CONVERT(DATETIME, my_string_date+' '+my_string_time)

Order By Date Format

I have inherited some code with a view that formats dates like
Mar 26, 2003
May 06, 2006
Jun 05, 2006
Example
SELECT DISTINCT TOP 5000 createDate, responsibilities
FROM vw_someview
ORDER BY createDate DESC,responsibilities
the order by doesnt come out right. The view select for the field is
convert( varchar(50), pp.createDate, 107 ) as createDate,
It is stored as 2003-03-26 14:29:41.880
Id like to order by properly but was wondering if I can do another
convert to make it order by properly?
Thanksuse order by convert(datetime,CreateDate)
take a look at these 2 example
select * from
(select 'Mar 26, 2003' CreateDate union all
select 'May 06, 2006' union all
select 'Jun 05, 2006') z
order by CreateDate
select * from
(select 'Mar 26, 2003' CreateDate union all
select 'May 06, 2006' union all
select 'Jun 05, 2006') z
order by convert(datetime,CreateDate)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Brian wrote:
> I have inherited some code with a view that formats dates like
> Mar 26, 2003
> May 06, 2006
> Jun 05, 2006
> Example
> SELECT DISTINCT TOP 5000 createDate, responsibilities
> FROM vw_someview
> ORDER BY createDate DESC,responsibilities
> the order by doesnt come out right. The view select for the field is
> convert( varchar(50), pp.createDate, 107 ) as createDate,
> It is stored as 2003-03-26 14:29:41.880
> Id like to order by properly but was wondering if I can do another
> convert to make it order by properly?
> Thanks|||Brian,
Sounds to me like your view needs changing, to report the date as a date.
How about leaving the formatting until you need to display it?
Currently, you're converting the date into a string and then querying that,
and then trying to sort by the result of a function on that string. That's
messy. Instead, let your view report the date itself, and tell whatever uses
that view to display the date in an appropriate format. If you really need
the view to report the date as string, then put it in a different column.
Of course, refactoring like this will be so much nicer in Visual Studio Team
Edition for Database Professionals, because you should be able to rename a
column in a view (to, say, CreateDateFormatted), and have it run through
your solution and do all the renaming for you.
Rob
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1149525868.872085.191850@.i40g2000cwc.googlegroups.com...
> use order by convert(datetime,CreateDate)
> take a look at these 2 example
> select * from
> (select 'Mar 26, 2003' CreateDate union all
> select 'May 06, 2006' union all
> select 'Jun 05, 2006') z
> order by CreateDate
>
> select * from
> (select 'Mar 26, 2003' CreateDate union all
> select 'May 06, 2006' union all
> select 'Jun 05, 2006') z
> order by convert(datetime,CreateDate)
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> Brian wrote:
>

Order By

Have data in a view in the following format:
DateType Date FileNumber
-- -- --
Order 1/1/01 00000001
Invoice 2/2/02 00000001
Inspection 3/3/03 00000001
... ... 00000002
Not all FileNumbers will have a record with a DateType of "Order", but I
need to order by this (no pun intended). Essentially I want to order by
date desc, if the DateType is "Order"...and leave all the file numbers
without an OrderDate at the bottom. I'm sure this is possible, I'm just not
the SQL guy around here, and he's on vacation.
Any suggestions?
Thanks,
JamesSELECT * FROM <tablename> WHERE DateType = 'Order' ORDERY BY Date DESC
This should work. Let me know if you get what you are looking for.
HTH
Bhanu.
"James" <cppjames@.aol.com> wrote in message
news:ekf$Fq5tEHA.1272@.TK2MSFTNGP12.phx.gbl...
> Have data in a view in the following format:
> DateType Date FileNumber
> -- -- --
> Order 1/1/01 00000001
> Invoice 2/2/02 00000001
> Inspection 3/3/03 00000001
> ... ... 00000002
>
> Not all FileNumbers will have a record with a DateType of "Order", but I
> need to order by this (no pun intended). Essentially I want to order by
> date desc, if the DateType is "Order"...and leave all the file numbers
> without an OrderDate at the bottom. I'm sure this is possible, I'm just
not
> the SQL guy around here, and he's on vacation.
> Any suggestions?
> Thanks,
> James
>
>|||The problem lies in the fact that I need the other records as well...the
ones with the Inspected and Invoiced dates.
"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:%23gKYZ45tEHA.1008@.tk2msftngp13.phx.gbl...
> SELECT * FROM <tablename> WHERE DateType = 'Order' ORDERY BY Date DESC
>
> This should work. Let me know if you get what you are looking for.
> HTH
> Bhanu.
> "James" <cppjames@.aol.com> wrote in message
> news:ekf$Fq5tEHA.1272@.TK2MSFTNGP12.phx.gbl...
> > Have data in a view in the following format:
> >
> > DateType Date FileNumber
> > -- -- --
> > Order 1/1/01 00000001
> > Invoice 2/2/02 00000001
> > Inspection 3/3/03 00000001
> > ... ... 00000002
> >
> >
> > Not all FileNumbers will have a record with a DateType of "Order", but I
> > need to order by this (no pun intended). Essentially I want to order by
> > date desc, if the DateType is "Order"...and leave all the file numbers
> > without an OrderDate at the bottom. I'm sure this is possible, I'm just
> not
> > the SQL guy around here, and he's on vacation.
> >
> > Any suggestions?
> >
> > Thanks,
> > James
> >
> >
> >
> >
>|||Try this unless you get better answers
SELECT * INTO #temp FROM [Order]
WHERE datetype = 'order'
ORDER BY Date DESC
SELECT * FROM #temp
UNION ALL
SELECT * FROM [Order]
WHERE datetype <> 'order'
Thanks
GYK
"Bhanu" wrote:
> SELECT * FROM <tablename> WHERE DateType = 'Order' ORDERY BY Date DESC
>
> This should work. Let me know if you get what you are looking for.
> HTH
> Bhanu.
> "James" <cppjames@.aol.com> wrote in message
> news:ekf$Fq5tEHA.1272@.TK2MSFTNGP12.phx.gbl...
> > Have data in a view in the following format:
> >
> > DateType Date FileNumber
> > -- -- --
> > Order 1/1/01 00000001
> > Invoice 2/2/02 00000001
> > Inspection 3/3/03 00000001
> > ... ... 00000002
> >
> >
> > Not all FileNumbers will have a record with a DateType of "Order", but I
> > need to order by this (no pun intended). Essentially I want to order by
> > date desc, if the DateType is "Order"...and leave all the file numbers
> > without an OrderDate at the bottom. I'm sure this is possible, I'm just
> not
> > the SQL guy around here, and he's on vacation.
> >
> > Any suggestions?
> >
> > Thanks,
> > James
> >
> >
> >
> >
>
>|||On Thu, 21 Oct 2004 14:58:35 -0400, James wrote:
>The problem lies in the fact that I need the other records as well...the
>ones with the Inspected and Invoiced dates.
Hi James,
Nasty, nasty.
This is untested (as you didn't provide CREATE TABLE and INSERT statements
for me to base my tests on), but it might work.
SELECT a.DateType, a.Date, a.FileNumber
FROM YourTable AS a
LEFT JOIN YourTable AS b
ON b.FileNumber = a.FileNumber
AND b.DateType = 'Order'
ORDER BY b.DateType DESC
, a.DateType ASC -- Big assumption here!!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Oracle LIKE

I have column C and i need to enforce the constraint
C is in the format (char)(number)(number)
eg, A12 B09 etc.
Cheers.Please ignore the previous post.