locked
First and Last Date and a Total Sum RRS feed

  • Question

  • I have a table that contains the following columns:  ID, SampleDate, Type

    What I would like to do is write a query that will return data in the following format:

    ID		FirstSample		LastSample		Type             TotalSamples
    -------------------------------------------------------------------------------------------------
    SiteA          8/1/2001                   9/9/2011              ABC                       12


    So, for each site I would like to display the first and last date samples of each type were taken, and the total number of that sample type over that date range.  In the example above, the first sample was taken on 8/1 and the last on 9/9 with a total of 12 samples of type ABC taken altogether.  

    How can I do this?

    Tuesday, September 27, 2011 4:08 PM

Answers

  • HI Jay !
     
    You may get the desired ouput using below;

    SELECT ID, MIN(SampleDate) AS FirstSample
      , MAX(SampleDate) AS LastSample
      ,Type , COUNT(*) AS TotalSamples
    FROM #YourTable
    GROUP BY ID , Type
    

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks, Hasham

    • Marked as answer by Jay Mazz Tuesday, September 27, 2011 4:34 PM
    Tuesday, September 27, 2011 4:15 PM
    Answerer

All replies

  • HI Jay !
     
    You may get the desired ouput using below;

    SELECT ID, MIN(SampleDate) AS FirstSample
      , MAX(SampleDate) AS LastSample
      ,Type , COUNT(*) AS TotalSamples
    FROM #YourTable
    GROUP BY ID , Type
    

    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks, Hasham

    • Marked as answer by Jay Mazz Tuesday, September 27, 2011 4:34 PM
    Tuesday, September 27, 2011 4:15 PM
    Answerer
  • The other option would be using CTE:

    Declare @myTable Table (ID Varchar(10), SampleDate Date, Type_ Varchar(10))
    Insert Into @myTable 
    Select 'SiteA', '01/01/2011', 'ABC' Union All 
    Select 'SiteA', '03/01/2011', 'ABC' Union All 
    Select 'SiteA', '02/01/2011', 'ABC' Union All 
    Select 'SiteA', '04/01/2011', 'ABC' Union All 
    Select 'SiteA', '05/01/2011', 'ABC' Union All 
    Select 'SiteA', '06/01/2011', 'ABC' Union All 
    Select 'SiteB', '01/01/2011', 'ABC' Union All 
    Select 'SiteB', '02/01/2011', 'ABC' Union All 
    Select 'SiteB', '03/01/2011', 'ABC' Union All 
    Select 'SiteB', '04/01/2011', 'ABC' Union All 
    Select 'SiteB', '05/01/2011', 'ABC' 
    
    ;With CTE
    As
    (
    	Select 
    		ID 
    		,SampleDate 
    		,Type_ 
    		,ROW_NUMBER() Over (Partition by ID, Type_ Order by SampleDate) As FirstSampleDateRN  
    		,ROW_NUMBER() Over (Partition by ID, Type_ Order by SampleDate Desc) As LastSampleDateRN 
    		,COUNT(*) Over(Partition by ID, Type_) As CountAll 
    	From @myTable 
    )
    
    Select 
    	FirstQuery.ID 
    	,FirstQuery.SampleDate As FirstSampleDate  
    	,LastQuery.SampleDate As LastSampleDate 
    	,FirstQuery.Type_ 
    	,FirstQuery.CountAll As TotalSamples 
    From 
    	CTE As FirstQuery 
    	Left Outer Join CTE As LastQuery On FirstQuery.ID = LastQuery.ID And FirstQuery.Type_ = LastQuery.Type_ And LastQuery.LastSampleDateRN = 1 
    Where 
    	FirstQuery.FirstSampleDateRN = 1 
    
    --output
    ID         FirstSampleDate LastSampleDate Type_      TotalSamples
    ---------- --------------- -------------- ---------- ------------
    SiteA      2011-01-01      2011-06-01     ABC        6
    SiteB      2011-01-01      2011-05-01     ABC        5
    
    

     


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    Tuesday, September 27, 2011 4:24 PM
  • Hi Hasham,

    That appears to have done the trick.  Thank you! :-)

    Tuesday, September 27, 2011 4:34 PM
  • Instead of using group by, use the over clause to define how you sum or fetch your min and max values.

    select id,
    min(yourdate) over (partition by type) as firstsample,   --first date for the type of the current row
    max(yourdate) over (partition by type) as lastsample,   --last date for the type of the current row
    type,
    sum(samples) over (partition by type) as totalsamples     --total samples for the type of the current row
    from yourtable

     

    Tuesday, September 27, 2011 4:37 PM