report

• sql_2005_fan

  SSCommitted

  Points: 1871

  More actions

  November 1, 2006 at 9:53 am

  #174826

  I have  a table with the following data.

   

  prod_id   booked_date   delivered_Date

  1           10/21/2006     NULL

  1           10/22/2006     10/25/2006

  2           10/23/2006     10/24/2006

  1           10/21/2006     10/29/2006

  1           10/19/2006     10/22/2006

  2           10/30/2006     NULL

  2           10/28/2006     10/31/2006

  1           10/27/2006     NUULL

  I need to generate the following report.

  prod_id total_booked total_delivered totol_undeliverd

  1             5                 3                  2

  2             3                 2                  1

   

  Can anyone help me in this.

   

  Thanks.

• sql_2005_fan

  SSCommitted

  Points: 1871

  More actions

  November 1, 2006 at 10:04 am

  #669255

  SELECT prod_id,COUNT(booked_date),COUNT(delivered_Date),COUNT(booked_date)-COUNT(delivered_Date) FROM products

  GROUP BY prod_id

   

  I am using the above query and its giving the correct result. But I am thinking if there a better way to get the  above report.

   

  Thanks.