Thread: Help with SQL for Report

Hi Guys,

I want to pull a list of unshipped orders based on a specific set of criteria.

Here's what I'm using - & it works perfectly :-

C#/VB.NET Code:

SELECT OrderNumber, OrderDate, OrderTotal, 'Not Shipped' AS NotShipped 
FROM Orders WHERE TransactionState = 'AUTHORIZED' AND ShippedOn IS NULL AND IsNew = 0 AND OrderNumber > 110000 


What i'd like to do is add a comma seperated list of SKUs as another column at the end.

I can get a comma seperated list of SKU's for one order like this :-

C#/VB.NET Code:

SELECT LEFT(l.list,LEN(l.list)-1) AS SKUs
FROM
(SELECT OrderedProductSKU + ',' AS [text()]
FROM Orders_ShoppingCart WHERE OrderNumber = 193063
FOR XML PATH(''))l(list) 


But as you see - I have to specify the OrderNumber, & it's only for one order.


Can anyone help me join these 2 queries together?



TTFN

BFG

Try this SQL statement:

Code:

Select a.OrderNumber, a.OrderDate, a.OrderTotal, 'Not Shipped' AS NotShipped,
       Left(a.Ordersku,Len(a.Ordersku)-1) As "Order SKUs"
From(Select distinct o.OrderNumber, o.OrderDate, o.OrderTotal,
           (Select osc.OrderedProductSKU + ',' AS [text()]
            From dbo.Orders_ShoppingCart osc
            Where osc.OrderNumber = o.OrderNumber
            ORDER BY osc.OrderNumber
            For XML PATH ('')) [Ordersku]
     From dbo.Orders o) [a]

Thanks Mate - not quite sure that it's correct though.
It doesn't have any of my criteria - & it's not obvious where I would add them - Ican't see a JOIN anywhere....



TTFN

BFG

You mean the where filters on the orders? Sorry I glanced over including them during my testing to make sure it would return results. The correct code would be :

Code:

Select a.OrderNumber, a.OrderDate, a.OrderTotal, 'Not Shipped' AS NotShipped,
       Left(a.Ordersku,Len(a.Ordersku)-1) As "Order SKUs"
From(Select distinct o.OrderNumber, o.OrderDate, o.OrderTotal,
           (Select osc.OrderedProductSKU + ',' AS [text()]
            From dbo.Orders_ShoppingCart osc
            Where osc.OrderNumber = o.OrderNumber            
            For XML PATH ('')) [Ordersku]
     From dbo.Orders o WHERE o.TransactionState = 'AUTHORIZED' AND o.ShippedOn IS NULL AND o.IsNew = 0 AND o.OrderNumber > 110000 ) [a]

It does have a join just due inner SQL statements it harder to read. This code may be a bit easier to read and does the same thing with an extra join. It also makes it a bit easier to add a column from the orders table.

Code:

Select o.OrderNumber, o.OrderDate, o.OrderTotal, 'Not Shipped' AS NotShipped,
       Left(a.Ordersku,Len(a.Ordersku)-1) As "Order SKUs"
from dbo.Orders o with (NOLOCK)
		join (Select distinct o.OrderNumber, 
           (Select osc.OrderedProductSKU + ',' AS [text()]
            From dbo.Orders_ShoppingCart osc
            For XML PATH ('')) [Ordersku]
     From dbo.Orders o) [a] ON a.OrderNumber = o.OrderNumber

WHERE o.TransactionState = 'AUTHORIZED' AND o.ShippedOn IS NULL AND o.IsNew = 0 AND o.OrderNumber > 110000

Mate,

That is awesome - Thanks!!
You are now officially my favourite SQL Guru.

(I promise not to bug you...).


TTFN

BFG