SQL Function for Last 12 Months

How can I get the last 12 months from the current date PLUS extra days till 1st of the last month retrieved

Using DATEADD and DATEDIFF:

DECLARE @ThisDate DATE = '20150817'
SELECT DATEADD(YEAR, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @ThisDate), '19000101'))

For more common date routines, see this article by Lynn Pettis.

To use in your WHERE clause:

DECLARE @ThisDate DATE = '20150817'
SELECT *
FROM <your_table>
WHERE
    <date_column> >= DATEADD(YEAR, -1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @ThisDate), '19000101'))

How to get previous 12 months data in SQL Server and avoiding the current month

The following should work, evaluates to >='2020-04-01 00:00:00.000' and <'2021-04-01 00:00:00.000' (which encompasses to end of March 23:59:59)

where Datecolumn >=DateAdd(month, DateDiff(month, 0, DateAdd(month,-12,GetDate())), 0)
and dateColumn < DateAdd(month, DateDiff(month, 0, GetDate()), 0)

Rolling Sum for Last 12 Months in SQL

Basically, you want to remove the partition by clause from the rolling 12 month sum. I would also suggest a few optimizations to the query:

select 
    x.payout_month, 
    sum(ah.principal_amt) payout_amt, 
    sum(sum(ah.principal_amt)) over (
        partition by year(x.payout_month) 
        order by x.payout_month
    ) as yearrollingsum,
    sum(sum(ah.principal_amt)) over (
        order by x.payout_month
        rows between 12 preceding and 1 preceding
    )  as twelvemonthrollingsum
from accounthistory ah
cross apply (values (datefromparts(year(ah.entry_date), month(entry_date), 1))) x(ah.payout_month)
where 
    left(ah.token_string, 4) like '%py%'
    and ah.focus_teller_id = 6056
    and ah.principal_amt > 0 and principal_amt < 25
    and ah.entry_date >= '20190101'
group by x.payout_month
order by x.payout_month

The main change is that the payout_month is computed only once, in a lateral join, using datefromparts(). You can then use it all over the query, and consistently in the order by clauses of the window functions.

Note that your strategy will fail to produce a proper results if you ever have a month without any sale (the rows clause of the window function will spread over the preceding month, which is not what you want). If that's something that may happen, then an alternative is a subquery, or another lateral join.

SQL function for last 12 months

Please try using CTE:

ALTER FUNCTION [dbo].[Last12Months]
(
    @Date datetime
)   RETURNS @tbl TABLE (Start datetime, EndDate datetime)
AS   
BEGIN
    WITH T AS(
    SELECT 
        DATEADD(month, DATEDIFF(month, 0, @Date), 0) AS Start,
        DATEADD(d, -DAY(DATEADD(m,1,@date)),DATEADD(m,1,@date)) AS EndDate,
        12 Cnt
    UNION ALL
    SELECT 
        DATEADD(month, -1, Start),
        DATEADD(d, -DAY(DATEADD(m,1,Start-1)),DATEADD(m,1,Start-1)),
        Cnt-1
    FROM
        T
    WHERE
        Cnt-1>0
    )
    INSERT INTO @tbl 
        (Start, EndDate)
    SELECT 
        Start, EndDate
    FROM T

    RETURN
END

How do i get the last 12 months in sql

Declare @Date Date,@DateR1 Date,@DateR2 Date

Set @Date=GetDate()
Set @DateR2=DateAdd(DAY,-Day(@Date)+1,@Date) 
Set @DateR1=DateAdd(MONTH,-13,@DateR2)

Select @DateR1,@DateR2

Returns

2015-08-01           2016-09-01

Calculate the average of the last 12 months for every month?

You can use window functions with the proper window clause:

select 
    boxes,
    dateMonthly,
    avg(boxes) 
        over(order by dateMonthly rows between 11 preceding and current row) avg_boxes
from monthleyavarage

This gives you the average of the current month and the 11 preceding.

Count of A Column For Last 12 Months and Individual Month

This answers the question for SQL Server 2012+ (the SQL Server version was not specified when I answered the question).

I would recommend using cumulative sums. This assumes that you have data for each month, but that seems reasonable. For a cumulative year-to-date:

SELECT YEAR(m.InvoiceDate) as yyyy,
       MONTH(m.InvoiceDate) as mm,
       SUM(m.Quantity) as Quantity,
       SUM(SUM(m.Quantity)) OVER (PARTITION BY YEAR(m.InvoiceDate)
                                  ORDER BY MONTH(m.InvoiceDate)
                                 ) 
FROM CustomerInvoice m
WHERE YEAR(m.InvoiceDate) = 2018
GROUP BY YEAR(m.InvoiceDate), MONTH(m.InvoiceDate)
ORDER BY YEAR(m.InvoiceDate), MONTH(m.InvoiceDate);

Note that this uses SUM(m.Quantity) rather than COUNT(). SUM() makes more sense based on your description.

If you want a cumulative running 12-month sum, ignoring year boundaries, use a windowing clause:

SELECT YEAR(m.InvoiceDate) as yyyy,
       MONTH(m.InvoiceDate) as mm,
       SUM(m.Quantity) as Quantity,
       SUM(SUM(m.Quantity)) OVER (PARTITION BY YEAR(m.InvoiceDate)
                                  ORDER BY MONTH(m.InvoiceDate)
                                  ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
                                 ) 
FROM CustomerInvoice m
GROUP BY YEAR(m.InvoiceDate), MONTH(m.InvoiceDate)
ORDER BY YEAR(m.InvoiceDate), MONTH(m.InvoiceDate);

EDIT:

In SQL Server 2008, you can express the first query as:

WITH ym as (
      SELECT YEAR(m.InvoiceDate) as yyyy,
             MONTH(m.InvoiceDate) as mm,
             SUM(m.Quantity) as Quantity,
      FROM CustomerInvoice m
      WHERE YEAR(m.InvoiceDate) = 2018
      GROUP BY YEAR(m.InvoiceDate), MONTH(m.InvoiceDate)
     )
SELECT ym.yyyymm, ym.quantity, ym2.sum_Quantity)
FROM ym CROSS APPLY
     (SELECT SUM(ym2.quantity) as sum_quantity
      FROM ym ym2
      WHERE ym.yyyy = ym2.yyyy AND ym2.month <= ym.month
     ) ym2
ORDER BY YEAR(m.InvoiceDate), MONTH(m.InvoiceDate);

For a rolling sum in SQL Server 2008:

WITH ym as (
      SELECT YEAR(m.InvoiceDate) as yyyy,
             MONTH(m.InvoiceDate) as mm,
             SUM(m.Quantity) as Quantity,
      FROM CustomerInvoice m
      WHERE YEAR(m.InvoiceDate) = 2018
      GROUP BY YEAR(m.InvoiceDate), MONTH(m.InvoiceDate)
     )
SELECT ym.yyyymm, ym.quantity, ym2.sum_Quantity)
FROM ym CROSS APPLY
     (SELECT SUM(ym2.quantity) as sum_quantity
      FROM ym ym2
      WHERE ym2.InvoiceDate >= DATEADD(year, -1, ym.InvoiceDate) AND
            ym2.InvoiceDate <= ym.InvoiceDate
     ) ym2
ORDER BY YEAR(m.InvoiceDate), MONTH(m.InvoiceDate);