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

Currently I have a query which is loading a per year sales of a given Tenant. Using this code below, I am able to produce this result (see image table)

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

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')

Part of the code, take note that parameters @Year1-5 have value like (2011,2012 etc)

部分代码，请注意参数@ Year1-5的值如（2011,2012等）

result

结果

What I want now is to get the minimum date and maximum date per year. RESULTING TO SOMETHING LIKE THIS

我现在想要的是获得每年的最短日期和最长日期。导致像这样的东西

The source database is actually a collection of sales per date. So I want to get the max and min date found in the given date range (year)

源数据库实际上是每个日期的销售集合。所以我想获得在给定日期范围（年）中找到的最大和最小日期

Any help will be very much appreciated

任何帮助将非常感谢

1 个解决方案

#1

This is the done with the same technique you are already using. Only it's two queries, one for min, one for max, that you'd glue together with UNION ALL.

这是使用您已经使用的相同技术完成的。只有两个查询，一个用于分钟，一个用于最大，你将与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'
  ,MAX(CASE WHEN YEAR(DATE) = @Year4 THEN DATE END) AS 'Year4'
  ,MAX(CASE WHEN YEAR(DATE) = @Year5 THEN DATE END) AS 'Year5'
FROM dailymod 
WHERE tenantcode = @RP;