Matrix Trouble

• Kerrie Sorrell

  SSC Eights!

  Points: 921

  More actions

  June 12, 2006 at 1:53 pm

  #172755

  Hi all,

  I report that calls for a matrix. A weeks time span in the column headings and description of expense in the rows. Select statement looks like this....

  SELECT    

  DL.DisbursementId

  , DL.Cost

  , DL.EmployeeId

  , DL.TransactionDate

  , CASE WHEN DL.DisbursementId = 1

         THEN 'Lodging'

         WHEN DL.DisbursementId = 2

         THEN 'Meals'

         WHEN DL.DisbursementId = 3

         THEN 'Plane, Rail, etc.'

         WHEN DL.DisbursementId = 4

         THEN 'Taxi, Bus, etc.'

         WHEN DL.DisbursementId = 5

         THEN 'Mileage'

         WHEN DL.DisbursementId = 6

         THEN 'Telephone'

         WHEN DL.DisbursementId = 7

         THEN 'Entertainment'

         WHEN DL.DisbursementId = 8

         THEN 'Auto Rentel'

         WHEN DL.DisbursementId = 9

         THEN 'Other'

         WHEN DL.DisbursementId = 10

         THEN 'Air Travel (Agency)'

         WHEN DL.DisbursementId = 11

         THEN 'Parking'

         WHEN DL.DisbursementId = 12

         THEN 'Cell Phone'

         WHEN DL.DisbursementId = 13

         THEN 'Lodging Tax'

         WHEN DL.DisbursementId = 14

         THEN 'Gas'

         WHEN DL.DisbursementId = 15

         THEN 'Tips'

         WHEN DL.DisbursementId = 16

         THEN 'Tolls'

         WHEN DL.DisbursementId = 17

         THEN 'Postage/Supplies'

         ELSE 'Air Travel Fee (Agency)'

    END as Decription  

  FROM         dbo.DisbursementLines DL

  WHERE    

  (DL.EmployeeId = 118) AND

  (DL.TransactionDate BETWEEN '05/21/2006' AND '05/27/2006') AND

  (DL.DisbursementId NOT IN (10, 18))

  ORDER BY DL.TransactionDate

  I can not get the dates to show up in the matrix if there is no data in those date buckets. I have a sample of how to do it if there are two or more tables (outer joins and 'is null') but not with one.

  Can anyone help me with this? If you need mor information about this let me know.

  Any help would be greatly appreciated.

  Thanks, Kerrie

• Dave Klug

  SSCertifiable

  Points: 5811

  More actions

  June 13, 2006 at 5:34 am

  #643318

  Create a database table with all possible dates you're looking for, and then from here, left join to your DisbursementsLines table on the date.