Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Date range in select statement
Message
De
05/12/2001 13:06:09
 
 
À
04/12/2001 16:58:33
Information générale
Forum:
Visual FoxPro
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Divers
Thread ID:
00589506
Message ID:
00589997
Vues:
36
>>>I have a consumer status table that contains the consumer id, the consumer's status (active, inactive, etc.) and the begin date of the status (the end date is not included - it's assumed that the previous status ended the day the new status began). I would like to determine the date range (or # of days) that a consumer had a certain status. Is there any way to do this with a SQL statement?
>>>
>>>TIA
>>>
>>>John
>>
>>OTTOMH.
>>
>>You need to join the table to itself and then compare the dates, finding the closest end date to the beginning. A union with the most current status completes the query.
>>
>Noting what Dan says, I believe, that with the aggregate function in the having, you can calculate the number of days as follows:
>
>
>>SELECT A.CustID, A.CustStatus, A.BeginDate, B.BeginDate AS EndDate,  ;
>>   B.BeginDate - A.BeginDate AS Duration;
>>   FROM Status A INNER JOIN Status B ;
>>   ON A.CustID = B.CustID ;
>>      <b>AND A.CustStatus = B.CustStatus ;</b>
>>      AND A.BeginDate < B.BeginDate ;
>>   GROUP BY A.CustID, <b>A.BeginDate, A.CustStatus </b> ;
>>   <b>ORDER BY 1, 3, 2</b>
>>   HAVING MIN (B.BeginDate) ;
>>UNION ;
>>SELECT  Status.CustID, Status.CustStatus, Status.BeginDate, {} AS EndDate, ;
>>    DATE - Status.BeginDate AS Duration ;
>>    FROM Status ;
>>    GROUP BY Status.CustID ;
>>    HAVING MAX (Status.BeginDate)
>
>> Jay

John --

I just wanted to bring the code up to date with the feedback and new info that you provided. In addition to the new group by columns, it's necessary to join the tables also on the status field.

HTH,

Jay
Précédent
Répondre
Fil
Voir

Click here to load this message in the networking platform