按促销状态排序(开始日期<getDate()<结束日期)？

Question

我有一个如下表：

ID    Title    StartDate    EndDate
1     PromoA   2012-06-01   2012-08-01
2     PromoB   2011-01-01   2011-02-01
3     PromoC   2012-09-01   2012-10-01
4     PromoD   2012-07-01   2012-09-01

因此，有4种状态的促销：1-OnGoing; 2-OutOfDate; 3-Waiting; 4-OnGoing

如何根据他们的状态对他们进行排序？

Answer 1

DECLARE @Table TABLE     
(    
     id int,  
     status varchar(20)  
)   
insert @table  
    SELECT  id,  
        CASE WHEN (EndDate < getdate()) THEN 'OutOfDate'  
             WHEN (StartDate > getdate()) THEN 'Waiting'  
             ELSE 'Ongoing'  
        END AS Status  
    FROM Promotion

SELECT *  
FROM Promotion p  
inner join @Table t on t.id = p.id  
ORDER BY Status

Answer 2

您可以使用CASE语句对每一行进行分类，如下所示（注意，这假设所有行的StartDate <= EndDate：

;WITH cte AS
(
    SELECT ID, Title, StartDate, EndDate, 
        CASE WHEN (EndDate < CURRENT_TIMESTAMP) THEN 'OutOfDate' 
             WHEN (StartDate > CURRENT_TIMESTAMP) THEN 'Waiting' 
             ELSE 'Ongoing'
        END AS [Status]
    FROM Promotion
)
SELECT ID, Title, StartDate, EndDate
FROM cte
ORDER BY [Status]