TSQL query

  • 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]

  • 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

  • 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