Subquery Help? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Subquery Help?

Author

Topic

imwild124
Starting Member

3 Posts

Posted - 2011-07-07 : 12:02:04

Hello, I’m trying to get some help with the code below. I’m basically trying to pull all of the transportation data for a particular customer #. For instance, how many pallets does the customer have on a truck, what stores did the truck stop at, etc. The problem however is that multiple (different) customers might ride on the same truck and if that’s the case I’m looking to pull that data as well. Essentially, I’m currently running the query below with a specific customer # specified, finding all of the route #’s that a particular customer rode on, removing the customer # filter and then re-running the query using the route #’s in order to get a comprehensive set of data that includes all of the data for any truck that this particular customer rides on. I know it probably seems confusing and I’m not exactly a SQL expert but any help would be appreciated. Thanks in advance.

select
[Week_Ending],
[Cust_#], Chain,

Round(Sum(Distance),0) Distance, Sum(Weight) Weight, Sum(Volume) Volume, Sum(Pieces) Pieces, Sum(Pallets) Pallets,

Facility,
Ledgend, [Descriptions for Ledgend],
[Route #], [Route Group],
Dispatch, [Original Stop #],
City, State, Zip

From
TBL_Data

Where
Week_Ending = 'WE010111'

--Substring(Week_Ending,7,2) = '11'

AND Facility IN ('Georgia')

--AND (Chain Like '%CHAIN%')

AND (Ledgend Like 'DELIVERED')

--And [Route #] In ('11-222','22-333')

And [Cust_#] in ('999999')

Group By
[Week_Ending],
[Cust_#], Chain,
Facility,
Ledgend, [Descriptions for Ledgend],
[Route #], [Route Group],
Dispatch, [Original Stop #],
City, State, Zip

Order By
Dispatch desc, [Route #] desc, [Original Stop #] asc
;

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-07 : 12:16:22

May be something like this? In the query I separated it into two parts - the first part picking up all the routes of interest, and then using those routes in the second part. You may need to modify the where clause in each part - not very clear to me the business logic.

WITH CustomerRoutes AS
(
   SELECT DISTINCT [Route #] 
   FROM TBL_Data
   WHERE
   Week_Ending = 'WE010111'
   AND Facility IN ('Georgia')
   AND (Ledgend LIKE 'DELIVERED')
   AND [Cust_#] IN ('999999')
)
SELECT
   [Week_Ending],
   [Cust_#],
   Chain,
   ROUND(SUM(Distance), 0) Distance,
   SUM(WEIGHT) WEIGHT,
   SUM(Volume) Volume,
   SUM(Pieces) Pieces,
   SUM(Pallets) Pallets,
   Facility,
   Ledgend,
   [Descriptions for Ledgend],
   [Route #],
   [Route Group],
   Dispatch,
   [Original Stop #],
   City,
   STATE,
   Zip
FROM
   TBL_Data
WHERE
   Week_Ending = 'WE010111'
   AND Facility IN ('Georgia')
   AND (Ledgend LIKE 'DELIVERED')
   AND [Route #]  IN (SELECT [Route #]  FROM CustomerRoutes)
GROUP BY
   [Week_Ending],
   [Cust_#],
   Chain,
   Facility,
   Ledgend,
   [Descriptions for Ledgend],
   [Route #],
   [Route Group],
   Dispatch,
   [Original Stop #],
   City,
   STATE,
   Zip
ORDER BY
   Dispatch DESC,
   [Route #] DESC,
   [Original Stop #] ASC

imwild124
Starting Member

3 Posts

Posted - 2011-07-07 : 14:01:04

Thanks a lot. Do you have any idea what could be causing an "Incorrect syntax near the keyword 'WITH' though?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-07 : 14:02:38

Assuming you are on SQL 2005 or higher, insert a semi-colon just before the word WITH:


;WITH CustomerRoutes AS
(
   SELECT DISTINCT [Route #]
   ....

imwild124
Starting Member

3 Posts

Posted - 2011-07-07 : 14:12:57

Ahhh...I guess that's the problem. I'm definitely on an older version. Is there a simple fix to that or would the code not work on an older version?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-07 : 17:31:37

You should be able to make that into a sub-query instead of a CTE. It would be:


SELECT
   [Week_Ending],
   [Cust_#],
   Chain,
   ...
FROM
   ...
WHERE
   ...
   AND (Ledgend LIKE 'DELIVERED')
   AND [Route #]  IN 
   (
         SELECT DISTINCT [Route #] 
         FROM TBL_Data t
         WHERE
         t.Week_Ending = 'WE010111'
         AND t.Facility IN ('Georgia')
         AND (t.Ledgend LIKE 'DELIVERED')
         AND t.[Cust_#] IN ('999999')

   )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources