TSQL loop months in sequence

问题:

I have an query that I'm feeling out-of-my depth with.

I need to loop through months between two dates and return a subset of data for each month with a blank row for months with no data.

For example:

TransactionID    |    Date          |    Value
1                |    01/01/2015    |    £10
2                |    16/01/2015    |    £15
3                |    21/01/2015    |    £5
4                |    15/03/2015    |    £20
5                |    12/03/2015    |    £15
6                |    23/04/2015    |    £10

Needs to return:

Month            |    Amount
January          |    £30
February         |    £0
March            |    £35
April            |    £10

My query will rely on specifying a date range so I can set the first and last date of the query.

I feel like I maybe over thinking this, but have gotten to that stage where you start to feel like you tying yourself in knots.

回答1:

The key is having access to a list of integers to represent the months in the range. If you don't have a Numbers Table, then spt_values will do in a pinch.

SqlFiddle Demo

SELECT
  [Year]   = YEAR(DATEADD(month,[i],@range_start))
 ,[Month]  = DATENAME(month,DATEADD(month,[i],@range_start))
 ,[Amount] = ISNULL(SUM([Value]),0)
FROM (
  SELECT TOP (DATEDIFF(month,@range_start,@range_end)+1)
    ROW_NUMBER() OVER(ORDER BY (SELECT 1))-1 [i]
  FROM master.dbo.spt_values
) t1
LEFT JOIN #MyTable t2
  ON (t1.[i] = DATEDIFF(month,@range_start,t2.[Date]) )
GROUP BY [i]
ORDER BY [i]

回答2:

SQL is a tricky language at first. You actually do not want a loop. In fact, you pretty much never want to loop in SQL except in very few cases. Try this out:

DECLARE @StartDate  DATE,
        @EndDate    DATE;

SET @StartDate  = '01 January 2015';
SET @EndDate    = '30 April 2015';

WITH CTE_Months
AS
(
    SELECT @StartDate dates
    UNION ALL
    SELECT DATEADD(MONTH,1,dates)
    FROM CTE_Months
    WHERE DATEADD(MONTH,1,dates) < @EndDate
)

SELECT  YEAR(B.[date]) AS yr,
        DATENAME(MONTH,B.[Date]) AS month_name,
        SUM(ISNULL(B.Value,0)) AS Amount
FROM CTE_Months A
LEFT JOIN yourTable B
ON  YEAR(A.[date]) = YEAR(B.[date])
    AND MONTH(A.[date]) = MONTH(B.[date])
GROUP BY YEAR(B.[date]),DATENAME(MONTH,B.[Date])

回答3:

One way: create a table called months with a monthnum int field and 12 rows of [1..12]

declare @start date = '01 jan 2015',
        @end   date = '30 apr 2015'

select 
    datename(month, dateadd(month, monthnum, 0) - 1),
    isnull(Amount, 0)
from months
left join (
    select
        month(date) Month,
        sum(Value) Amount
    from tbl 
    where date between @start and @end
    group by month(date)
) T on (T.Month = months.monthnum)
where months.monthnum between month(@start) and month(@end)
order by monthnum

回答4:

The following code will generate one output row for each month between the first and last transaction dates. Spanning a year boundary, or multiple years, is handled correctly.

-- Some sample data.
declare @Transactions as Table
  ( TransactionId Int Identity, TransactionDate Date, Value Int );
insert into @Transactions ( TransactionDate, Value ) values
  ( '20141125', 10 ), ( '20150311', 20 ), ( '20150315', 5 ), ( '20150509', 42 );
select * from @Transactions;

with
  -- Determine the first and last dates involved.
  Range as (
  select Min( TransactionDate ) as FirstDate, Max( TransactionDate ) as LastDate
    from @Transactions ),
  -- Generate a set of all of the months in the range.
  Months as (
    select DateAdd( month, DateDiff( month, 0, FirstDate ), 0 ) as Month,
      DateAdd( month, DateDiff( month, 0, LastDate ), 0 ) as LastMonth
      from Range
    union all
    select DateAdd( month, 1, Month ), LastMonth
      from Months
      where Month < LastMonth )
  -- Summarize the transactions.
  select M.Month, Coalesce( Sum( T.Value ), 0 ) as Total
    from Months as M left outer join
      @Transactions as T on DateAdd( month, DateDiff( month, 0, T.TransactionDate ), 0 ) = M.Month
    group by M.Month
    order by M.Month
  option ( MaxRecursion 1000 );