October 19, 2007 at 2:20 pm
Hi Guys
This is my first post so I hope I get my query over to you all in a reasonable way.
I’m trying to create a TSQL query on the following tables.
Orders table
OrderOwnervarchar(20)
OrderNumvarchar(20)
OrderDatedatetime
OrderStatusvarchar(1)
OrderLines table
OrderOwnervarchar(20)
OrderNumvarchar(20)
OrderValueflaot
The above tables are a simpler version of my tables but hold the important data.
There is a one to many relationship between these tables. I.e. there can be many OrderLines records linked to one Order record.
The tables join via the OrderOwner and OrderNum fields.
The OrderStatus field contains different status codes such as ‘N’ and ‘C’.
Now with the above two tables an order record may have several order lines and some of the order lines may have their OrderValue field set to either a floating point number or 0.
So it’s possible to say that an Order may have none of its OrderLines OrderValue fields set to a value or some of its OrderValue fields set to a value or all of its OrderValue fields set to a value.
So I'm trying to report on this and trying to write a TSQL query that produces a report that looks like as follows:-
Order Owner-------OrderStatus----Total Orders---------Non---------Some---------All
AAA---------------------C--------------20----------------------10----------10-------------0
AAA---------------------N--------------10-----------------------0-----------5-------------5
BBB---------------------C--------------10-----------------------01----------0-------------0
CCC--------------------C --------------5-----------------------0-----------0-------------5
CCC---------------------N--------------10-----------------------5-----------5-------------5
(Sorry about the above horrid report layout!!)
I've written some code and got nowhere fast, so now I'm using temporary tables and cursors. But I'm sure there’s a simpler and more elegant way to achieve the above report without cursors.
I hope you guys can help.
Many thanks.
Nick.
[font="System"][/font]
October 19, 2007 at 5:09 pm
A query like this will give the report you're looking for. The trick is to perform a subquery to return some valuess that can be used to identify the target cases you're interested in, and then in the main query to use a sum of a case statement that returns 1 when the data for the record matches the criteria for the given case you're reporting in that column, and 0 when it does not.
Select a.OrderOwner, A.OrderStatus,
count(a.OrderNum) as TotalOrders,
sum(case when SumValue > 0 and
NumZeroNull = 0 then 1 else 0 end) as [All],
sum(case when SumValue > 0 and
NumZeroNull > 0 then 1 else 0 end) as [Some],
sum(case when SumValue = 0 then 1 else 0 end)
as [None]
from orders a inner join
(select OrderOwner, OrderNum,
sum(isnull(OrderValue,0)) as SumValue,
-- when SumValue equals zero, then all ordervalues are zero or null
sum(case when isnull(OrderValue,0) = 0
then 1 else 0 end) as NumZeroNull
-- if NumZeroNull is zero then all ordervalue are > 0
from OrderLines
group by OrderOwner, OrderNum) as b
on a.OrderOwner = b.OrderOwner and
a.OrderNum = b.OrderNum
group by a.OrderOwner, a.OrderStatus
order by a.OrderOwner, a.OrderStatus
Ed Heistand
October 20, 2007 at 1:12 pm
Hi Ed
Many thanks for the quick reply.
I'll try your solution.
Once again many thanks!!!
Nick.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply