ÿþSELECT

   gd.ResNumber,

   GuestName(n.FirstName,n.LastName, n.Company) as "Name",

   gd.Arrival,

   gd.Departure,

   sum(gr.Cost + gr.StateTax + gr.CountyTax + gr.CityTax + gr.RoomTax) as "Total Charges",

   sum(gr.StateTax + gr.CountyTax + gr.CityTax + gr.RoomTax) as "Total Tax",

   CASE WHEN gd.Cancelled is NULL THEN 'Room'  ELSE 'Withheld'  END as "Taxes Applied",

   sum(gr.StateTax) as "State Taxes",

   sum(gr.CountyTax) as "County Taxes",

   sum(gr.RoomTax) as "Room Taxes",

   sum(gr.CityTax) as "City Taxes" ,

   COALESCE(gg.TaxExemptNumber, '') as "Tax Exemption Number",

   CASE WHEN gg.TaxStatus = 0 THEN

      'Not taxable'

   WHEN gg.TaxStatus = 1 THEN

      'All taxable'

   WHEN gg.TaxStatus = 2 THEN

      'Room only not taxable'

   WHEN gg.TaxStatus = 3 THEN

      'No tax but room is room taxable only'

   WHEN gg.TaxStatus = 4 THEN

      'Taxable no room tax'

   END as "Tax Status"

FROM  

   tblguestdates gd

INNER JOIN

   tblGuestRoom gr on gr.ResNumber = gd.ResNumber

INNER JOIN

   tblNames n on n.ResNumber = gd.ResNumber

INNER JOIN

   tblguestgeneral gg on gg.ResNumber = gd.ResNumber



WHERE

   gd.Departure >= DATE '2015-10-1' AND

   gd.Departure < DATE '2015-11-1' AND

   CASE  WHEN 0 =1 THEN --Comp/GCT/Owner

      gd.Cancelled IS NULL AND

      gd.Deposit is NULL AND

      gr.Cost = 0

   WHEN 1=2 THEN

         CASE  WHEN 1=1 THEN --Both WithHeld and Non

            ((gd.Cancelled IS NULL) OR

            (gd.Cancelled >= DATE '2015-10-1' AND

            gd.Cancelled < DATE '2015-11-1' AND

            gd.Deposit IS NOT NULL))

         WHEN 1=2 THEN --ONLY Withheld

            (gd.Cancelled < DATE '2015-10-1' AND

             gd.Deposit IS NOT NULL)

         WHEN 1=3 THEN --NO Withheld

             (gd.Cancelled IS NULL)

         END AND n.nameType= 'GST'

   WHEN 2=2 THEN

         (gd.Cancelled IS NULL AND

         gd.Deposit is NULL AND

         gr.Cost = 0) OR

         (CASE 1 WHEN 1 THEN --Both WithHeld and Non

            ((gd.Cancelled IS NULL) OR

            (gd.Cancelled >= DATE '2015-10-1' AND

            gd.Cancelled < DATE '2015-11-1' AND

            gd.Deposit IS NOT NULL))

         WHEN 2 THEN --ONLY Withheld

            (gd.Cancelled < DATE '2015-10-1' AND

             gd.Deposit IS NOT NULL)

         WHEN 3 THEN --NO Withheld

             (gd.Cancelled IS NULL)

         END)

            

         

   END

   





  

    

GROUP BY

 CASE WHEN 1=0 THEN CAST(CURRENT_DATE() AS TIMESTAMP) ELSE gd.DateLastUpdated END

ORDER BY

 "Taxes Applied",ResNumber

ita