Thanks to everyone for their help with this!
Will
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
I have your typical tblorders. I'm trying to query it to get order totals by
month, and I'm outputting them in cfchart
All goes fine, except in 2009. We have no order data for April-December of
course, so the query returns just three rows (Jan, Feb, March).
I need it to return all 12 months,
, March 25, 2009 6:26 AM
To: cf-talk
Subject: SQL server - Order totals by month, even without month data
I have your typical tblorders. I'm trying to query it to get order
totals by month, and I'm outputting them in cfchart
All goes fine, except in 2009. We have no order data for April-December
-talk
Subject: SQL server - Order totals by month, even without month data
I have your typical tblorders. I'm trying to query it to get order
totals by month, and I'm outputting them in cfchart
All goes fine, except in 2009. We have no order data for April-December
of course, so the query returns
that helps you on your way.
Rob
-Original Message-
From: Will Tomlinson [mailto:w...@wtomlinson.com]
Sent: 25 March 2009 11:26
To: cf-talk
Subject: SQL server - Order totals by month, even without month data
I have your typical tblorders. I'm trying to query it to get order totals by
month
The only way to get data into a query is to have data in a table. So there are
a couple of options.
1. Create a months table that has 12 records
2. Create a temp table with 12 records
You may also need to create a years table as well.
Then your query would be something like
SELECT year,
missing
months, then create a new query using QueryNew to hold the old query, plus
the new rows of months that have a 0 in them.
William
-Original Message-
From: Will Tomlinson [mailto:w...@wtomlinson.com]
Sent: Wednesday, March 25, 2009 4:26 AM
To: cf-talk
Subject: SQL server - Order totals
posted a reply hours ago, but it still hasn't showed up... reposting...
when i had to do a similar thing, this is how i did it:
your query returns monthName and totalOrders columns.
create a structure from your query with Month as key and Total as value:
cfset structOrderMonths = structnew()
when i had to do a similar thing, this is how i did it:
your query returns monthName and totalOrders columns.
create a structure from your query with Month as key and Total as value:
cfset structOrderMonths = structnew()
cfoutput query=...
cfset structOrderMonths[monthName] = totalOrders
Create a dummy table with the information for the current year and
preform a UNION to your existing data
On Wed, Mar 25, 2009 at 6:26 AM, Will Tomlinson w...@wtomlinson.com wrote:
I have your typical tblorders. I'm trying to query it to get order totals by
month, and I'm outputting them in
Original Message
Subject: Re: SQL server - Order totals by month, even without month
data
From: Greg Morphis gmorp...@gmail.com
Date: Wed, March 25, 2009 7:26 am
To: cf-talk cf-talk@houseoffusion.com
Create a dummy table with the information for the current year and
preform a UNION to your
I actually prefer to do this in the ORDER BY clause (keeping the ordering
logic in the ORDER BY instead of in the SELECT) but the end result is the
same. If you won't or can't add a sort column to the table, a CASE statement
is about the only other way to do this in the query itself.
On Jan 25,
something like...
SELECT
Colleges,
CASE
when Colleges = 'Harvard' THEN 1
when Colleges = 'Princeton' THEN 2
when Colleges = 'Dartmouth' THEN 3
END AS collegeOrder
FROM
myTable
ORDER BY
collegeOrder
(not tested) :)
On Jan 25, 2008 10:41 AM,
I was trying to do that w/o adding another column. Can it be done?
-Original Message-
From: Todd [mailto:[EMAIL PROTECTED]
Sent: Friday, January 25, 2008 1:50 PM
To: CF-Talk
Subject: Re: OT: SQL Question -- Order by a column's value?
Nope, add a sort_order column and sort your colleges
There is no way to do it with pure SQL alone.
Dominic
Well actually you can do in pure SQL. SQL has code that can be used to create
dynamic columns and values on the fly in your record set and then one can order
on this set. It sort of depends on whether the desire order is permanent or
Che Vilnonis wrote:
I was trying to do that w/o adding another column. Can it be done?
Yes, see Crow's, Charlie's or my post on using CASE to create an inline
sort column with SQL.
~|
Adobe® ColdFusion® 8 software 8 is the
Cool. That did the trick. Thanks to all!
-Original Message-
From: Gaulin, Mark [mailto:[EMAIL PROTECTED]
Sent: Friday, January 25, 2008 2:08 PM
To: CF-Talk
Subject: RE: SQL Question -- Order by a column's value?
Yes, you can do this with a CASE statement. The syntax may depend on you
Sorry, add sort_order column and then do an ORDER BY sort_order and set all
the colleges in the appropriate sorting that you want it to be.
On Jan 25, 2008 1:49 PM, Todd [EMAIL PROTECTED] wrote:
Nope, add a sort_order column and sort your colleges appropriately.
On Jan 25, 2008 1:41 PM, Che
Nope, add a sort_order column and sort your colleges appropriately.
On Jan 25, 2008 1:41 PM, Che Vilnonis [EMAIL PROTECTED] wrote:
Suppose I have a small set of data with a column named Colleges. Is
there
a way to write an ORDER BY statement to say something like...
ORDER BY Colleges
Yes, you can do this with a CASE statement. The syntax may depend on
you db, but on SQL Server
ORDER BY
CASE Colleges
WHEN 'Harvard' THEN 1
WHEN 'Princeton' THEN 2
WHEN 'Dartmouth' THEN 3
ELSE 100
END
This would
, January 25, 2008 12:55 PM
To: CF-Talk
Subject: Re: OT: SQL Question -- Order by a column's value?
Do you mean put them in a predifind order based on the college, other
than alphabetical?
If so, and if you have a lookup table for your colleges, you will have
to add a numerical column called 'Ordinal
Yes, you can do this using case statements in your order by:
example:
select * from viewoffers where [EMAIL PROTECTED]
order by
case status
when 'active' then 1
when 'rejected' then 2
else 99
end
Of course, this is really a kludge. The DB should be deisgned a little
better, but
Che Vilnonis wrote:
Suppose I have a small set of data with a column named Colleges. Is there
a way to write an ORDER BY statement to say something like...
ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'???
Just wondering... Che
If I understand your question
Do you mean put them in a predifind order based on the college, other than
alphabetical?
If so, and if you have a lookup table for your colleges, you will have to
add a numerical column called 'Ordinal' (or something else) with which you
can set their order. Then simply order by that in your SQL
Suppose I have a small set of data with a column named Colleges. Is there
a way to write an ORDER BY statement to say something like...
ORDER BY Colleges 'Harvard', Colleges 'Princeton', Colleges 'Dartmouth'???
Just wondering... Che
mysql
the relevant sql
order by serial asc
this is what some of the current list looks like.
193932
19399
1948148
194874
194878
194886
1949157
1949160
~|
Get the answers you are looking for on the ColdFusion Labs
Forum direct
before 19509 which it shouldnt but mathematically is correct. Having
the client add 0's to serial numbers isnt an option, there aren't very
many like 20 but I would like them to be in order, is there anything
in sql that can do this?
btw~ using mysql
the relevant sql
order by serial asc
you could add 2 columns in your sql: 1 with left(serial_number_column,
4) [if the year part of sn is always 4 digits], the other with the rest
of the serial number, and sort the results first by one and hen y the
other. exact sql syntax would be db-specific
--
---
Azadi Saryev
ORDER BY LEFT(FieldName, 4), Right(FieldName, (LENGTH(FieldName)-4) )
That should do it, assuming the date part is always the same length.
=]
--
Alan Rother
Adobe Certified Advanced ColdFusion MX 7 Developer
Manager, Phoenix Cold Fusion User Group, AZCFUG.org
@Azadi Saryev d l
I need to use the data that is already there and it's not split up.
@Alan Rother
I had already tried that but it gives same results as reg order by asc
~|
Check out the new features and enhancements in the
nope urgg lol
How about this?
SORT BY val(left(serial, 4)), val(substring(serial, 3))
~|
Get involved in the latest ColdFusion discussions, product
development sharing, and articles on the Adobe Labs wiki.
How about this?
SORT BY val(left(serial, 4)), val(substring(serial, 3))
--
___
REUSE CODE! Use custom tags;
See http://www.contentbox.com/claude/customtags/tagstore.cfm
(Please send any spam to this address: [EMAIL PROTECTED])
Thanks.
If ordering by the Left() and Right() don't work, can you Select
Left(SerialNum,4) as sYear, Right(SerialNum,Length(SerialNum)-4) as sNum and
then order by sYear, sNum?
Been a while since I've worked in mysql, but several of the suggestions here
look like they should be working.
Thanks to all who tried, i got it working with the following:
order by left(serial, 4) asc, abs(mid(serial, 5,3)) asc
when in doubt visit the office lol
~|
Download the latest ColdFusion 8 utilities including Report Builder,
Dave,
Try Alan's solution again, but cast as integers.
something like:
order by cast(left(FieldName, 4) as int), cast(Right(FieldName,
(LENGTH(FieldName)-4) ) as int)
I haven't tested the above so you may need to adjust the syntax.
Antony
On Nov 29, 2007 7:59 AM, Dave l [EMAIL PROTECTED]
thanks but I already had gotten it and posted it as solved, i ended up with
order by left(serial, 4) asc, abs(mid(serial, 5,3)) asc
If ordering by the Left() and Right() don't work, can you Select
Left(SerialNum,4) as sYear, Right(SerialNum,Length(SerialNum)-4) as sNum and
then order by
query.
Andy
-Original Message-
From: Mark W. Breneman [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 09, 2004 1:24 PM
To: CF-Talk
Subject: SQL custom Order by
I doubt this is possible, but is there a way to do a custom order by based
on the order I provide?I need the SQL results
I doubt this is possible, but is there a way to do a custom order by based
on the order I provide?I need the SQL results to be in a specific order
based on the catID, but not in ascending or descending order. The order
that I want them in is 10,3,5,1,7,4,9,2,6,8.
Example
Select *
>From table
-Original Message-
From: Mark W. Breneman
Sent: Tuesday, March 09, 2004 1:24 PM
To: CF-Talk
Subject: SQL custom Order by
I doubt this is possible, but is there a way to do a custom order by
based
on the order I provide?I need the SQL results to be in a specific order
based
Mark W. Breneman said:
I doubt this is possible, but is there a way to do a custom order by
based on the order I provide?I need the SQL results to be in a
specific order based on the catID, but not in ascending or
descending order. The order that I want them in is
10,3,5,1,7,4,9,2,6,8.
*
FROMcategory
WHERE
CatID = 1
Try it,
David
-Original Message-
From: Plunkett, Matt [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 09, 2004 1:39 PM
To: CF-Talk
Subject: RE: SQL custom Order by
-Original Message-
From: Mark W. Breneman
Sent: Tuesday, March 09, 2004 1:24 PM
To: CF
SELECT*
FROMcategory
WHERE
CatID = 1
Try it,
David
-Original Message-
From: Plunkett, Matt [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 09, 2004 1:39 PM
To: CF-Talk
Subject: RE: SQL custom Order by
-Original Message-
From: Mark W. Breneman
Sent: Tuesday, March 09, 2004 1:24 PM
42 matches
Mail list logo
