[thelist] SQL - searching a history table

[Phil Turmel]

Matt Warden wrote:
> thelist,
> 
> Suppose the following table structure:
> 
> WIDGET
> ----------------------------
> id
> status
> 
> WIDGET_STATUS
> ----------------------------
> widgetid
> status
> date_updated
> 
> In english, I need the number of widgets with a widget status record
> for status 'X' and date_updated within the range 01-MAY-2006 and
> 01-JUN-2006. The catch is that this must be the last update to the
> status in that time period.
> 
> Any ideas? I have something close now, but I will refrain from
> describing it to avoid boxing in creativity.
> 
> 
Hi Matt,

You didn't mention what DB platform, but they'll all be similar if they 
support subqueries.  Something like this:

Select status, Count(widgetid) As wcount
   From (Select status, widgetid, Max(date_updated) As date_lastupdated
     From WIDGET_STATUS
     Where date_updated Between '20060501' And '20060601'
     Group By status, widgetid)
   Group By status
   Order By wcount Desc;

Note that the 'WIDGET' table is superfluous here, and the 
'date_lastupdated' column is discarded by the outer query.  It could 
just as easily been Min().

various SQL flavors support DISTINCT within a Count function, so you 
could also try this:

Select status, Count(Distinct widgetid) As wcount
   From WIDGET_STATUS
   Where date_updated Between '20060501' And '20060601'
   Group By status
   Order By wcount Desc;


HTH,

Phil