aggregate last 12 months (Network Steve Forum)

aggregate last 12 months

hi all i have a table with sample data as shown in the table below. for each month end date, I need to aggregate (sum) all the quantities for each product and customer and include the current month plus the past 12 months (including current month).
for example, for customer C123, P123 and for 7/31/2015, the query should add 7/31/2015 quantity and the quantity for 1/31/2015.

DECLARE @TEMP TABLE (Customer VARCHAR (10), Product VARCHAR(10), Month_end_date DATE, Quantity INT)
INSERT INTO @TEMP VALUES ('C123',	'P123',	'1/31/2015',	10)
INSERT INTO @TEMP VALUES ('C124',	'P124',	'2/28/2015',	20)
INSERT INTO @TEMP VALUES ('C125',	'P125',	'3/31/2015',	30)
INSERT INTO @TEMP VALUES ('C126',	'P126',	'4/30/2015',	40)
INSERT INTO @TEMP VALUES ('C127',	'P127',	'5/31/2015',	50)
INSERT INTO @TEMP VALUES ('C128',	'P128',	'6/30/2015',	60)
INSERT INTO @TEMP VALUES ('C123',	'P123',	'7/31/2015',	70)
INSERT INTO @TEMP VALUES ('C124',	'P124',	'8/31/2015',	80)
INSERT INTO @TEMP VALUES ('C125',	'P125',	'9/30/2015',	90)
INSERT INTO @TEMP VALUES ('C126',	'P126',	'10/31/2015',	20)
INSERT INTO @TEMP VALUES ('C127',	'P127',	'11/30/2015',	10)
INSERT INTO @TEMP VALUES ('C128',	'P128',	'12/31/2015',	40)

the output for the above example should be
C123 P123 7/31/2015 70+10=80

is this possible?

Edited by PeaceOut 6 hours 1 minutes ago

September 1st, 2015 9:12pm

Hi PeaceOut,

To get the expected running aggregation result, please see below sample.

DECLARE @TEMP TABLE (Customer VARCHAR (10), Product VARCHAR(10), Month_end_date DATE, Quantity INT)
INSERT INTO @TEMP VALUES ('C123',	'P123',	'1/31/2015',	10)
INSERT INTO @TEMP VALUES ('C124',	'P124',	'2/28/2015',	20)
INSERT INTO @TEMP VALUES ('C125',	'P125',	'3/31/2015',	30)
INSERT INTO @TEMP VALUES ('C126',	'P126',	'4/30/2015',	40)
INSERT INTO @TEMP VALUES ('C127',	'P127',	'5/31/2015',	50)
INSERT INTO @TEMP VALUES ('C128',	'P128',	'6/30/2015',	60)
INSERT INTO @TEMP VALUES ('C123',	'P123',	'7/31/2015',	70)
INSERT INTO @TEMP VALUES ('C124',	'P124',	'8/31/2015',	80)
INSERT INTO @TEMP VALUES ('C125',	'P125',	'9/30/2015',	90)
INSERT INTO @TEMP VALUES ('C126',	'P126',	'10/31/2015',	20)
INSERT INTO @TEMP VALUES ('C127',	'P127',	'11/30/2015',	10)
INSERT INTO @TEMP VALUES ('C128',	'P128',	'12/31/2015',	40)
 

SELECT * FROM @TEMP t
CROSS APPLY
(
SELECT SUM(Quantity) rollingupQuantity FROM @TEMP WHERE Customer=t.Customer AND Product=t.Product AND Month_end_date BETWEEN DATEADD(YEAR,-1,T.Month_end_date) AND T.Month_end_date
)cat
ORDER BY Customer,Product,Month_end_date

If you have any question, feel free to let me know.

Free Windows Admin Tool Kit Click here and download it now

September 1st, 2015 9:52pm

>>I have a table with sample data as shown in the table below. <<

No, that is not a table. It has no key, and no way to ever have a key! Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data (https://xkcd.com/1179/).

A useful idiom is a report period calendar. It gives a name to a range of dates. The worst way would be to use temporal math; it tells the world you do not think in sets or understand declarative programming yet. Here is a skeleton:

CREATE TABLE Month_Periods
(month_name CHAR(10) NOT NULL PRIMARY KEY,
month_start_date DATE NOT NULL,
month_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (month_start_date < month_end_date),
ordinal_period INTEGER NOT NULL UNIQUE
CHECK(ordinal_period > 0)
etc);

I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. A real table will look like this:

CREATE TABLE Sales
(customer_id CHAR (10) NOT NULL,
product_gtin CHAR (15) NOT NULL, -- google it
sale_month_name CHAR(10) NOT NULL
REFERENCES Month_Periods(month_name),
PRIMARY KEY (customer_id, product_gtin, sale_month_name),
sale_qty INTEGER NOT NULL
CHECK (sale_qty > 0)

>> for each month end date, I need to aggregate (sum) all the quantities for each product and customer and include the current month plus the past 12 months (including current month). <<

The ordinal period is a simple count of the periods from the start of the Periods table. It is easy to find the current number with a query

SELECT S.customer_id, S.product_gtin, S.sale_month_name, S.sale_qty
FROM Sales AS S
WHERE S.sale_month_name
IN (SELECT N.sale_month_name
FROM Month_Periods AS M
WHERE M.ordinal_period
BETWEEN (SELECT ordinal_period
FROM Month_Periods WHERE CURRENT_TIMESTAMP
BETWEEN month_start_date AND month_end_date)
AND (SELECT ordinal_period FROM Month_Periods WHERE CURRENT_TIMESTAMP
BETWEEN month_start_date AND month_end_date) -12)

September 1st, 2015 10:26pm

Try

select Customer, Product, max(Month_End_Date) as LastDate, sum(Quantity) as TotalQty
from @TEMP
where Month_end_date between dateadd(month, -12, CURRENT_TIMESTAMP) and CURRENT_TIMESTAMP
GROUP BY Customer, Product
ORDER BY Customer, Product

Free Windows Admin Tool Kit Click here and download it now

September 1st, 2015 11:12pm

Try

select Customer, Product, max(Month_End_Date) as LastDate, sum(Quantity) as TotalQty
from @TEMP
where Month_end_date between dateadd(month, -12, CURRENT_TIMESTAMP) and CURRENT_TIMESTAMP
GROUP BY Customer, Product
ORDER BY Customer, Product

September 2nd, 2015 12:32am

Hi PeaceOut,

To get the expected running aggregation result, please see below sample.

DECLARE @TEMP TABLE (Customer VARCHAR (10), Product VARCHAR(10), Month_end_date DATE, Quantity INT)
INSERT INTO @TEMP VALUES ('C123',	'P123',	'1/31/2015',	10)
INSERT INTO @TEMP VALUES ('C124',	'P124',	'2/28/2015',	20)
INSERT INTO @TEMP VALUES ('C125',	'P125',	'3/31/2015',	30)
INSERT INTO @TEMP VALUES ('C126',	'P126',	'4/30/2015',	40)
INSERT INTO @TEMP VALUES ('C127',	'P127',	'5/31/2015',	50)
INSERT INTO @TEMP VALUES ('C128',	'P128',	'6/30/2015',	60)
INSERT INTO @TEMP VALUES ('C123',	'P123',	'7/31/2015',	70)
INSERT INTO @TEMP VALUES ('C124',	'P124',	'8/31/2015',	80)
INSERT INTO @TEMP VALUES ('C125',	'P125',	'9/30/2015',	90)
INSERT INTO @TEMP VALUES ('C126',	'P126',	'10/31/2015',	20)
INSERT INTO @TEMP VALUES ('C127',	'P127',	'11/30/2015',	10)
INSERT INTO @TEMP VALUES ('C128',	'P128',	'12/31/2015',	40)
 

SELECT * FROM @TEMP t
CROSS APPLY
(
SELECT SUM(Quantity) rollingupQuantity FROM @TEMP WHERE Customer=t.Customer AND Product=t.Product AND Month_end_date BETWEEN DATEADD(YEAR,-1,T.Month_end_date) AND T.Month_end_date
)cat
ORDER BY Customer,Product,Month_end_date

If you have any question, feel free to let me

Free Windows Admin Tool Kit Click here and download it now

September 2nd, 2015 12:38am

Try

SELECT Customer, Product, MAX(Month_End_Date) AS LastDate, SUM(Quantity) AS TotalQty
FROM @TEMP
WHERE Month_end_date BETWEEN DATEADD(YEAR,-1,Month_end_date) AND Month_end_date
GROUP BY Customer, Product
ORDER BY Customer, Product

September 2nd, 2015 1:15am

This topic is archived. No further replies will be accepted.