Help with SQL please 1

[redwoodly]

we have accounts that are assigned a status: red, yellow, green (actually these are values 3, 2, 1)
every time the status of an account changes - the change is date stamped and stored in tblAcctHist (history). this table records AcctID, old status, new status, date & time.


my question: i want to know how many accounts were red every day for the last month. (show count for every day)


I'm not sure how to go about it because i don't have a record for everyday the account is red.
just a record when it went red (3) and another when it changed "out of red" ( to yellow (2) or green (1))

help please?

thanks
laurel

 

[Golom]

The accounts that were red at the beginning of the time period should be

Select AcctID From tbl T

Where  Status = 3 and DateTime = 
       (Select MAX(DateTime) From tbl X
        Where  X.AcctID = T.AcctID And
               DateTime <= [Start DateTime])

I.e. Those Accounts that turned RED on or before the beginning of the month and that's the last change to them. If we call that "qryStartRed" then

The ones that stayed RED for the whole month are

Select S.AcctID 

From qryStartRed S LEFT JOIN tbl T ON S.AcctID = T.AcctID

Where T.AcctID IS NULL AND
      T.DateTime BETWEEN [Start DateTime] AND [End DateTime]

I.e. Those accounts that started the period RED and didn't change status during the period.

Assuming that a status change from Red to Red is not recorded.