Just wanted to see if anyone out there had a better way to get the data I am gathering with this query. :)
Thanks all!
<cfquery name="Data" datasource="#application.DSN#"
debug="no">
SELECT
ORDBILL.ORODR## AS
ORDERNUM,
ORDBILL.ORBAMT,
ORDBILL.ORTAMT,
ORDER.ORCONS,
ORDER.ORLDAT,
ORDER.OROCTY,
ORDER.OROST,
ORDER.ORDCTY,
ORDER.ORDST,
ORDER.ORSTAT,
ORDER.ORMILE,
ORDER.ORSTP## AS
Stops,
ORDER.ORCUST AS
CustomerCode,
LOADDATE.DIDISP,
LOADDATE.DIDATE,
LOADDATE.DIEMIL as
EmptyMiles,
LOADDATE.DITRLR as
Trailer,
LOADDATE.DITMIL as
TotalMiles,
LOADDATE.DISMNF as
LoadedMiles,
LOADDATE.DICONT,
NMSAREA.NMAREG AS
RegionCode,
(SELECT
SUM(DIEMIL)
FROM
LOAD
WHERE
DIODR## =
ORDER.ORODR##) as TotalEmptyMiles,
(SELECT
SUM(DISMNF)
FROM
LOAD
WHERE
DIODR## =
ORDER.ORODR##) as TotalLoadedMiles,
(SELECT
CUNAME
FROM
CUSTMAST
WHERE
CUCODE =
ORDER.ORCONS) as Customer,
(SELECT
CUSHNM
FROM
CUSTMAST
WHERE
CUCODE =
ORDER.ORCONS) as LoadAt,
(SELECT DISTINCT
CISHNM
FROM
CITIES
WHERE
CIST =
ORDER.OROST
AND CICTY =
ORDER.OROCTY) AS OriginCityName,
(SELECT DISTINCT
CISHNM
FROM
CITIES
WHERE
CIST =
ORDER.ORDST
AND CICTY =
ORDER.ORDCTY) AS DestinationCityName
FROM
(((ORDBILL INNER
JOIN ORDER ON ORDBILL.ORODR## = ORDER.ORODR##)
INNER JOIN LOADDATE
ON LOADDATE.DIODR## = ORDBILL.ORODR##)
INNER JOIN NMSAREA
ON Trim(ORDER.OROST) = Trim(NMSAREA.NMACOD))
WHERE
LOADDATE.DIDATE >=
#FromDate#
AND LOADDATE.DIDATE
<= #ToDate#
AND
LOADDATE.DIDISP = '01'
AND ORDER.ORSTAT IN
('E', 'D')
AND ORDER.ORDV## =
'200'
<cfif Form.Rated eq
'unrated'>
AND ORDBILL.ORBAMT =
0
</cfif>
AND NMSAREA.NMAREG =
'#Trim(Region)#'
ORDER BY
ORLDAT
</cfquery>
Chris Peterson
Gainey Transportation Services
616.530.8558 ext. 286
- some mistakes are just too much fun to make only once...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
