如果数据库包含当前日期数据，则选择降序排列的前1位，否则选择升序排列的前一位

我有一个月的列表，这些月被填充到我的数据库中。然后将这些月份填充到web应用程序的下拉列表中。客户端希望当前月份成为下拉列表中的默认选项。但是，当前月份的数据并不总是可用的（或者数据库中的ActiveFlag=Y）。因此，在这种情况下，客户希望默认为一年中的第一个月。

因此，我正在使用Selenium自动化和C#测试下拉列表和DB表是否匹配。我有一个SQL查询，它返回按降序排序的Top 1（这可以返回一年中最后一个活动月份，因为BroadcastMonthofYearNbr包含1到12个月的整数）：

SELECT top 1 CONCAT(RTRIM(left(M.Name, CHARINDEX(',',M.Name)-1)), ' (', STUFF(REPLACE('/'+CONVERT( VARCHAR(10), M.StartDate, 101), '/0', '/'), 1, 1, ''), '-', STUFF(REPLACE('/'+CONVERT(VARCHAR(10), M.EndDate, 101), '/0', '/'), 1, 1, ''), ')') 
            FROM [AudienceProjections].[dbo].[BroadcastCalendarQuarterMonthMap] QMP,
                AudienceProjections.dbo.BroadcastCalendarYear Y,
                AudienceProjections.dbo.BroadcastCalendarMonth M,
                AudienceProjections.dbo.BroadcastCalendarQuarter Q
            WHERE y.BroadcastCalendarYearUuid = q.BroadcastCalendarYearUuid
                AND q.BroadcastCalendarQuarterUuid = qmp.BroadcastCalendarQuarterUuid
                AND qmp.BroadcastCalendarMonthUuid = m.BroadcastCalendarMonthUuid
                AND M.ActiveStatus = 'ACTIVE'
                AND y.ActiveStatus = 'ACTIVE'
                AND q.BroadcastQuarterType = 'STANDARD'
                AND y.BroadcastCalendarYear = YEAR(GETDATE())
            ORDER BY qmp.BroadcastMonthofYearNbr DESC

上面的查询返回这个：

No Column Name April (3/28/2016-4/24/2016)

我发布这个问题的当月是5月5日。请注意，上面的查询结果返回的是April，而不是May。这是因为May的数据尚未导入到DB中（请参阅下表以区分是否存在数据。即Active和Inactive）。所以我的查询在这种情况下不起作用。如果这种情况发生，我现在需要一个CASE THEN ELSE或IF EXISTS类型的函数来返回一月

这是您可以看到的月份表，其中五月为无效：

Name           StartDate    EndDate     ActiveStatus
January, 2016  2015-12-28   2016-01-31  ACTIVE
February, 2016 2016-02-01   2016-02-28  ACTIVE
March, 2016    2016-02-29   2016-03-27  ACTIVE
April, 2016    2016-03-28   2016-04-24  ACTIVE
May, 2016      2016-04-25   2016-05-29  INACTIVE

注：我忘记了当前日期是1月的情况，如果没有1月，那么年份也不会出现。因此，查询中的条件需要考虑Year。年份表（BroadcastCalendarYear）还有一个可以使用的ActiveStatus列。

因此，如果年份也是非活动的，那么返回一个字符串，上面写着"DB中没有年份的数据"。

我认为您将能够根据您的特定模式调整以下内容，但这里有一个按顺序排序的示例。请注意，您可能不需要也应该删除一些在不了解数据集的情况下很难知道的where语句。

此外，如果是当前年份的1月，并且没有任何活动，则应返回哪个月？您可能需要根据答案调整顺序。

SELECT TOP 1 *
FROM
    @Months
WHERE
    ActiveStates = 'ACTIVE'
    AND ????
ORDER BY
    CASE WHEN YEAR(GETDATE()) = YearInt THEN 9999 ELSE YearInt END DESC -- Assumes you want to order by most recent to oldest and that youcould potentially have a future year loaded in the table.  Othwerwuse simply use YearInt DESC
    ,CASE WHEN MONTH(GETDATE()) = MonthInt THEN 0 ELSE 1 END --Puts precedence on current month
    ,MonthInt DESC

我希望这能有所帮助。

IF OBJECT_ID(N'tempdb..#Month') IS NOT NULL
DROP TABLE #Month
CREATE TABLE #Month
(Year INT, Month VARCHAR(3), ActiveStatus CHAR(1))

INSERT INTO #Month(Year, Month, ActiveStatus)
VALUES(2016,'Jan','A')
,(2016,'Feb','A')
,(2016,'Mar','A')
,(2016,'Apr','A')
,(2016,'May','I')
;WITH x AS 
(
SELECT 
*,EOMONTH(CONCAT(Year,'-',Month,'-','01')) As Date
FROM #Month
)
SELECT 
*
FROM X
WHERE 
 -- current month is active or current month is inactive then first month of the current year
 Year = YEAR(GETDATE()) AND 
 (DATEPART(MONTH,Date)=DATEPART(MONTH,GETDATE()) AND ActiveStatus='A') OR     (1=DATEPART(MONTH,Date) AND EXISTS(SELECT 1 FROM x WHERE     DATEPART(MONTH,Date)=DATEPART(MONTH,GETDATE()) AND ActiveStatus='I'))

我用一个漫长而复杂的解决方案解决了这个问题，但它确实有效。

SELECT CASE WHEN t.month = (SELECT left(M.Name, CHARINDEX(',',M.Name)-1) FROM [AudienceProjections].[dbo].[BroadcastCalendarMonth] M
            WHERE StartDate <= GETDATE() AND EndDate >= GETDATE() ) THEN CONCAT(RTRIM(left(t.Name, CHARINDEX(',',t.Name)-1)), ' (', STUFF(REPLACE('/'+CONVERT( VARCHAR(10), t.StartDate, 101), '/0', '/'), 1, 1, ''), '-', STUFF(REPLACE('/'+CONVERT(VARCHAR(10), t.EndDate, 101), '/0', '/'), 1, 1, ''), ')')  ELSE ( SELECT CONCAT(RTRIM(left(M.Name, CHARINDEX(',',M.Name)-1)), ' (', STUFF(REPLACE('/'+CONVERT( VARCHAR(10), M.StartDate, 101), '/0', '/'), 1, 1, ''), '-', STUFF(REPLACE('/'+CONVERT(VARCHAR(10), M.EndDate, 101), '/0', '/'), 1, 1, ''), ')')  FROM AudienceProjections.dbo.BroadcastCalendarMonth M WHERE  NAME = CONCAT( DATENAME(month,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)), ', ', DATENAME(year,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))  ) ) END month FROM    (                 
         SELECT top 1 left(M.Name, CHARINDEX(',',M.Name)-1) AS month, 
         M.Name, M.StartDate, M.EndDate
         FROM [AudienceProjections].[dbo].[BroadcastCalendarQuarterMonthMap] QMP,
                AudienceProjections.dbo.BroadcastCalendarYear Y,
                AudienceProjections.dbo.BroadcastCalendarMonth M,
                AudienceProjections.dbo.BroadcastCalendarQuarter Q
            WHERE y.BroadcastCalendarYearUuid = q.BroadcastCalendarYearUuid
                AND q.BroadcastCalendarQuarterUuid = qmp.BroadcastCalendarQuarterUuid
                AND qmp.BroadcastCalendarMonthUuid = m.BroadcastCalendarMonthUuid
                AND M.ActiveStatus = 'ACTIVE'
                AND q.BroadcastQuarterType = 'STANDARD'
            AND y.ActiveStatus = 'ACTIVE'
                AND y.BroadcastCalendarYear = YEAR(GETDATE())
            ORDER BY qmp.BroadcastMonthofYearNbr DESC) t

使用IF EXISTS、ELSE IF EXISTS，ELSE完成所有3个条件：

IF EXISTS (
SELECT CONCAT(q.DisplayName, ' (', STUFF(REPLACE('/'+CONVERT( VARCHAR(10), q.StartDate, 101), '/0', '/'), 1, 1, ''), '-', STUFF(REPLACE('/'+CONVERT(VARCHAR(10), q.EndDate, 101), '/0', '/'), 1, 1, ''), ')')
FROM AudienceProjections.dbo.BroadcastCalendarYear Y,
     AudienceProjections.dbo.BroadcastCalendarQuarter Q
WHERE y.BroadcastCalendarYearUuid = q.BroadcastCalendarYearUuid
      AND q.BroadcastQuarterType = 'STANDARD'
      AND y.ActiveStatus = 'ACTIVE'
      AND q.ActiveStatus = 'ACTIVE'
      AND y.BroadcastCalendarYear = YEAR(GETDATE()) 
          AND Q.StartDate <= GETDATE()
          and Q.EndDate >= GETDATE()) 
SELECT CONCAT(q.DisplayName, ' (', STUFF(REPLACE('/'+CONVERT( VARCHAR(10), q.StartDate, 101), '/0', '/'), 1, 1, ''), '-', STUFF(REPLACE('/'+CONVERT(VARCHAR(10), q.EndDate, 101), '/0', '/'), 1, 1, ''), ')')
FROM AudienceProjections.dbo.BroadcastCalendarYear Y,
     AudienceProjections.dbo.BroadcastCalendarQuarter Q
WHERE y.BroadcastCalendarYearUuid = q.BroadcastCalendarYearUuid
      AND q.BroadcastQuarterType = 'STANDARD'
      AND y.ActiveStatus = 'ACTIVE'
      AND q.ActiveStatus = 'ACTIVE'
      AND y.BroadcastCalendarYear = YEAR(GETDATE()) 
          AND Q.StartDate <= GETDATE()
          and Q.EndDate >= GETDATE() ELSE IF EXISTS (
SELECT TOP 1 CONCAT(q.DisplayName, ' (', STUFF(REPLACE('/'+CONVERT( VARCHAR(10), q.StartDate, 101), '/0', '/'), 1, 1, ''), '-', STUFF(REPLACE('/'+CONVERT(VARCHAR(10), q.EndDate, 101), '/0', '/'), 1, 1, ''), ')')
FROM [AudienceProjections].[dbo].[BroadcastCalendarQuarterMonthMap] QMP,
     AudienceProjections.dbo.BroadcastCalendarYear Y,
     AudienceProjections.dbo.BroadcastCalendarQuarter Q
WHERE y.BroadcastCalendarYearUuid = q.BroadcastCalendarYearUuid
      AND q.BroadcastCalendarQuarterUuid = qmp.BroadcastCalendarQuarterUuid
      AND q.BroadcastQuarterType = 'STANDARD'
      AND y.ActiveStatus = 'ACTIVE'
      AND q.ActiveStatus = 'ACTIVE'
      AND y.BroadcastCalendarYear = YEAR(GETDATE()) ORDER BY qmp.BroadcastMonthofYearNbr ASC ) SELECT TOP 1 CONCAT(q.DisplayName, ' (', STUFF(REPLACE('/'+CONVERT( VARCHAR(10), q.StartDate, 101), '/0', '/'), 1, 1, ''), '-', STUFF(REPLACE('/'+CONVERT(VARCHAR(10), q.EndDate, 101), '/0', '/'), 1, 1, ''), ')')
FROM [AudienceProjections].[dbo].[BroadcastCalendarQuarterMonthMap] QMP,
     AudienceProjections.dbo.BroadcastCalendarYear Y,
     AudienceProjections.dbo.BroadcastCalendarQuarter Q
WHERE y.BroadcastCalendarYearUuid = q.BroadcastCalendarYearUuid
      AND q.BroadcastCalendarQuarterUuid = qmp.BroadcastCalendarQuarterUuid
      AND q.BroadcastQuarterType = 'STANDARD'
      AND y.ActiveStatus = 'ACTIVE'
      AND q.ActiveStatus = 'ACTIVE'
      AND y.BroadcastCalendarYear = YEAR(GETDATE()) 
ORDER BY qmp.BroadcastMonthofYearNbr ASC; ELSE SELECT 'No Active quarters are in the database for the current year yet';

相关内容

最新更新

热门标签：