How can I accomplish this? Would a cursor be appropriate?

Question

I am trying to use TOAD and T-SQL to approximate a user's spreadsheet. Here are the basics of what they want:

Order Number  Customer Name  June  July  Aug  Sept  Oct  Nov  Dec
12345        Bleh Company    1000
                                                    800  200

The first row represents when the order value was received and the second represents the projected ship date of said order.

The following SQL Script delivers this, but it does not alternate between Order Date and Receiving Date.

SELECT 'O',  -- For Order Date
            (SOM.[fcustno] + ' - ' + SOM.[fcompany]) AS [Customer],
            sum(CASE month (SOM.forderdate)
                   WHEN 6 THEN (sor.forderqty * SOR.funetprice)
                   ELSE 0
                END)
               AS [June],
            sum(CASE month (SOM.forderdate)
                   WHEN 7 THEN (sor.forderqty * SOR.funetprice)
                   ELSE 0
                END)
               AS [July],
            sum(CASE month (SOM.forderdate)
                   WHEN 8 THEN (sor.forderqty * SOR.funetprice)
                   ELSE 0
                END)
               AS [Aug],
            sum(CASE month (SOM.forderdate)
                   WHEN 9 THEN (sor.forderqty * SOR.funetprice)
                   ELSE 0
                END)
               AS [Sept],
            sum(CASE month (SOM.forderdate)
                   WHEN 10 THEN (sor.forderqty * SOR.funetprice)
                   ELSE 0
                END)
               AS [Oct],
            sum(CASE month (SOM.forderdate)
                   WHEN 11 THEN (sor.forderqty * SOR.funetprice)
                   ELSE 0
                END)
               AS [Nov],
            sum(CASE month (SOM.forderdate)
                   WHEN 12 THEN (sor.forderqty * SOR.funetprice)
                   ELSE 0
                END)
               AS [Dec]
       FROM SORELS SOR
            JOIN SOMAST SOM
            ON SOM.FSONO = SOR.FSONO
            JOIN SOITEM SOI
            ON SOI.FSONO = SOR.FSONO AND SOI.FINUMBER = SOR.FINUMBER
      WHERE     FMASTERREL = 0
            AND SOM.forderdate >= CONVERT (DATETIME, '05/29/2009')
            AND SOM.forderdate < CONVERT (DATETIME, '08/04/2009')
            AND SOI.fduedate < CONVERT (DATETIME, '01/01/2010')
   GROUP BY (SOM.[fcustno] + ' - ' + SOM.[fcompany])
UNION
     SELECT 'S', -- For Ship Date
            (SOM.[fcustno] + ' - ' + SOM.[fcompany]) AS [Customer],
            sum(CASE month (SOI.fduedate)
                   WHEN 6 THEN (sor.forderqty * SOR.funetprice)
                   ELSE 0
                END)
               AS [June],
            sum(CASE month (SOI.fduedate)
                   WHEN 7 THEN (sor.forderqty * SOR.funetprice)
                   ELSE 0
                END)
               AS [July],
            sum(CASE month (SOI.fduedate)
                   WHEN 8 THEN (sor.forderqty * SOR.funetprice)
                   ELSE 0
                END)
               AS [Aug],
            sum(CASE month (SOI.fduedate)
                   WHEN 9 THEN (sor.forderqty * SOR.funetprice)
                   ELSE 0
                END)
               AS [Sept],
            sum(CASE month (SOI.fduedate)
                   WHEN 10 THEN (sor.forderqty * SOR.funetprice)
                   ELSE 0
                END)
               AS [Oct],
            sum(CASE month (SOI.fduedate)
                   WHEN 11 THEN (sor.forderqty * SOR.funetprice)
                   ELSE 0
                END)
               AS [Nov],
            sum(CASE month (SOI.fduedate)
                   WHEN 12 THEN (sor.forderqty * SOR.funetprice)
                   ELSE 0
                END)
               AS [Dec]
       FROM SORELS SOR
            JOIN SOMAST SOM
            ON SOM.FSONO = SOR.FSONO
            JOIN SOITEM SOI
            ON SOI.FSONO = SOR.FSONO AND SOI.FINUMBER = SOR.FINUMBER
      WHERE     FMASTERREL = 0
            AND SOM.forderdate >= CONVERT (DATETIME, '05/29/2009')
            AND SOM.forderdate < CONVERT (DATETIME, '08/04/2009')
            AND SOI.fduedate < CONVERT (DATETIME, '01/01/2010')
   GROUP BY (SOM.[fcustno] + ' - ' + SOM.[fcompany])

Any suggestions?

Answer 1

Try adding the following:

ORDER BY 2, 1

to the end of your script. It seems as though you just need to sort - first by customer, then by Order / Ship

Answer 2

You should give a name to your 'O'/'S' column (maybe RowType). Then order by Customer, RowType.

Rob