6 Replies Latest reply: Jun 25, 2013 3:25 PM by Srikanth P RSS

    No of Shipments at Starting and Ending of the month

    Srikanth P

      Hi Folks, I am trying to create a table to Show, Shipments at SOM (Starting on Month), Shipments Started, Shipments Closed, Shipments at EOM (End of Months) for each MonthName. Please find the below sample data:

       

      SHIPMENT_ID, STATUS, DATEVALUE

      1000, Started, 5/25/2013

      1000, Closed, 5/30/2013

      1001, Started, 5/25/2013

      1001, Closed, 6/2/2013

      1002, Started, 5/29/2013

      1002, Closed, 6/5/2013

      1003, Started, 5/20/2013

      1003, Closed, 5/26/2013

      1004, Started, 6/2/2013

      1004, Closed, 6/10/2013

      1005, Started, 6/15/2013

      1005, Closed, 6/22/2013

      1006, Started, 6/23/2013

      1006, Started, 6/22/2013

       

      I am expecting the Table (Out Put) like below:

       

      MonthName     Shipments (SOM)     Shipments Started     Shipments Closed     Shipments (EOM)

      May2013                 0                             4                                    3                            2

      June2013                2                             4                                     4                           

       

      Shipments (SOM) --> # of Open Shipments at Starting of Month

      Shipments Started --> # of Shipments Started   Count({<STATUS={'Started'}>}SHIPMENT_ID)

      Shipments Closed --> # of Shipments Closed    Count({<STATUS={'Closed'}>}SHIPMENT_ID)

      Shipments (EOM) --. # of Open Shipments at End of Month

       

      Please help me how to calculate Shipments (SOM) & Shipments (EOM)??

       

      Appreciated for any help!....