获取每年日期范围的最小和最大日期的SQL查询

0 人关注

目前我有一个查询，正在加载一个给定租户的年销售额。使用下面的代码，我能够产生这样的结果（见图像表）。

SELECT DATENAME(month,date) [month]
    ,ISNULL(SUM(CASE WHEN YEAR (DATE) = @Year1 THEN gsc END), 0) AS 'Year1'
    ,ISNULL(SUM(CASE WHEN YEAR (DATE) = @Year2 THEN gsc END), 0) AS 'Year2'
    ,ISNULL(SUM(CASE WHEN YEAR (DATE) = @Year3 THEN gsc END), 0) AS 'Year3'
    ,ISNULL(SUM(CASE WHEN YEAR (DATE) = @Year4 THEN gsc END), 0) AS 'Year4'
    ,ISNULL(SUM(CASE WHEN YEAR (DATE) = @Year5 THEN gsc END), 0) AS 'Year5'
INTO #LRSalesAnalysis
FROM dailymod 
WHERE tenantcode = @RP 
GROUP BY DATENAME(month,date)
ORDER BY DATEPART(MM,DATENAME(month,date)+' 01 2011')
代码的一部分，请注意参数@Year1-5的值是（2011,2012等）。
我现在想得到的是每年的最小日期和最大日期。结果是这样的
源数据库实际上是一个每个日期的销售集合。所以我想得到在给定的日期范围（年）内发现的最大和最小日期。
如果有任何帮助，我将非常感激

sql


         sql-server


         sql-server-2008


        
         
         
          rickyProgrammer
         
        
        
         发布于
         
         2015-09-08


        1
        
        个回答


          
           
           
            Thorsten Kettner
           
          
          
           发布于
           
           2015-09-08


          已采纳


         0
         
         人赞同


          
           这是用你已经使用的相同技术完成的。只是它是两个查询，一个是
           
            min
           
           ，一个是
           
            max
           
           ，你要用
           
            UNION ALL
           
           。
          
          SELECT 
  'Min Date' AS what
  ,MIN(CASE WHEN YEAR(DATE) = @Year1 THEN DATE END) AS 'Year1'
  ,MIN(CASE WHEN YEAR(DATE) = @Year2 THEN DATE END) AS 'Year2'
  ,MIN(CASE WHEN YEAR(DATE) = @Year3 THEN DATE END) AS 'Year3'
  ,MIN(CASE WHEN YEAR(DATE) = @Year4 THEN DATE END) AS 'Year4'
  ,MIN(CASE WHEN YEAR(DATE) = @Year5 THEN DATE END) AS 'Year5'
FROM dailymod 
WHERE tenantcode = @RP 
UNION ALL
SELECT
  'Max Date' AS what
  ,MAX(CASE WHEN YEAR(DATE) = @Year1 THEN DATE END) AS 'Year1'
  ,MAX(CASE WHEN YEAR(DATE) = @Year2 THEN DATE END) AS 'Year2'
  ,MAX(CASE WHEN YEAR(DATE) = @Year3 THEN DATE END) AS 'Year3'