CODEWITHSUNDEEP
sqlsql-server-2005t-sqlreporting-services
Axle
Axle

Reputation: 181

Adding zero values to report

Ok This is a good question I think.

At the moment I have a report showing amount of tickets per machine and how much each machine made in ticket sales.

Some machines sell Zero tickets but they are not includded in my report.

Now i want to include them. there is a full list of all machines in machconfig table which I could compare to the ticketssold table which also has a field corresponding to the machine that sold it.

So I guess I could find all of the machines that havent sold any tickets by looking for machine id's (MCHterminalid) that dont appear in the ticketssold table (TKtermid column)

here is the code I've got so far.. 

SELECT TKtermID,
   MCHlocation,
   Count (TKvouchernum) AS Totaltickets,
   Cast(Sum(TKcomission) AS FLOAT) / 100 AS Total_Comission
FROM   ticketssold(NOLOCK)
   INNER JOIN machconfig (NOLOCK)
     ON MCHterminalID = TKtermID
WHERE  cfglocationcountry = 'UK'
   AND dateadded BETWEEN Getdate() - 100 AND Getdate()
GROUP  BY vstermID,
      cfglocation
ORDER  BY Total_comission DESC

Upvotes: 1

Views: 148

Answers (5)

onedaywhen
onedaywhen

Reputation: 57023

You could UNION the 'zero' rows to your original e.g. 

<original query here>
...
UNION
SELECT MCHterminalID,
       MCHlocation,
       0 AS Totaltickets,
       0 AS Total_Comission
  FROM machconfig
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM ticketssold
                    WHERE MCHterminalID = TKtermID
                  )

(Review for hints).

Upvotes: 0

Axle
Axle

Reputation: 181

Finally got it working the way I want.. Here is the proper code:

SELECT MCHTerminalID, MCHLocation, ISNULL(CONVERT(varchar(16), batch.LastBatchIn, 103),                  

 'Did not batch in') AS LastBatchIn, 
 ISNULL(COUNT(Ticket.VoucherNum), 0) AS TotalVouchers,
   ISNULL(SUM(Ticket.Sale), 0) AS TotalGrossAmount, ISNULL(SUM(Ticket.Refund),0) AS            TotalRefundAmount, ISNULL(SUM(Ticket.Comission),0) AS TotalComission
 FROM termConfig AS config WITH (NOLOCK)
 LEFT OUTER JOIN 

(SELECT bsTerminalID, MAX(bsDateTime) AS LastBatchIn
 FROM batchSummary WITH (NOLOCK)
  WHERE bsDateTime BETWEEN getdate()-50 AND getdate()
GROUP BY bsTerminalID
 ) 
AS batch

  ON config.MCHTerminalID = batch.bsTerminalID
 LEFT OUTER JOIN 

   (SELECT DISTINCT TKTermID, 
   TKVoucherNum AS VoucherNum,
   CAST(TKGrossTotal AS float)/100 AS Sale,
   CAST(TKRefundAmount AS float)/100 AS Refund,
   CAST(TKComission AS float)/100 AS Comission
  FROM TicketVouchers WITH (NOLOCK)
     WHERE dateAdded BETWEEN getdate()-50 AND getdate()
    ) 
  AS Ticket
    ON
       config.MCHTerminalID = Ticket.TKTermID




    WHERE
      config.MCHLocationCountry = 'uk'
     AND config.MCHProductionTerminal = 'Y'
     GROUP BY config.MCHTerminalID, config.MCHLocation, LastBatchIn
    ORDER BY TotalComission desc

Upvotes: 0

K Richard
K Richard

Reputation: 1984

OCD Version not totally proofed (also killing me that table names are not included before the fields). Use the outer join in combination with COALESCE

SELECT
    TKTermID TicketTerminalId,
    MchLocation MachineLocation,
    COALESCE(COUNT(TKVoucherNum),0) TotalTickets,
    COALESCE(CAST(SUM(TKComission) AS float),0) / 100 TotalComission
FROM
    MachConfig (NOLOCK)
    LEFT JOIN
    TicketsSold (NOLOCK)
    ON
        TKtermID = MCHterminalID 
WHERE
    CfgLocationCountry = 'UK'
    AND
    DateAdded BETWEEN DATEADD(DAY, -100, GETDATE()) AND GETDATE()
GROUP BY
    VSTermID,
    CfgLocation
ORDER BY
    COALESCE(CAST(SUM(TKComission) AS float),0) / 100 DESC; --Do this in reporting!

Upvotes: 1

DiningPhilanderer
DiningPhilanderer

Reputation: 2767

Do not use inner joins because they will eliminate rows. I start my joins with the table that has all the data. In this case machconfig and then do a left outer join to the table with the problematic data ticketssold.

You may also want to think about doing your grouping on the report side for flexibility.

Upvotes: 0

Bridge
Bridge

Reputation: 30651

Change the inner join between ticketssold and machconfig to a right outer join to get all machines, regardless of a match in the tickets sold table. The count of TKVouchernum will return the zeros for you:

SELECT TKtermID,
   MCHlocation,
   Count (TKvouchernum) AS Totaltickets,
   Cast(Sum(TKcomission) AS FLOAT) / 100 AS Total_Comission
FROM   ticketssold(NOLOCK)
   RIGHT OUTER JOIN machconfig (NOLOCK)
     ON MCHterminalID = TKtermID
WHERE  cfglocationcountry = 'UK'
   AND dateadded BETWEEN DateAdd(DAY, -100, GetDate()) AND Getdate()
GROUP  BY vstermID,
      cfglocation
ORDER  BY Total_comission DESC

Upvotes: 3

Related Questions