-- SQLScript Window Functions

-- Create Table Statements

Create Table COSTCENTREGROUP(
COSTCENTREGROUPID INT PRIMARY KEY,
COSTCENTREGROUPTEXT NVARCHAR(50)
);

Create Table COSTCENTREITEM(
COSTCENTREITEMID INT PRIMARY KEY,
COSTCENTREITEMTEXT NVARCHAR(50),
COSTCENTREGROUPID INT
);

Create Table COSTLINEITEM (
COSTLINEITEMID INT PRIMARY KEY,
COSTCENTREITEMID INT,
COST INT,
CURRENCY NVARCHAR(3),
COSTBOOKINGDATE DATE
);



-- Insert Data Statements

INSERT INTO COSTCENTREGROUP VALUES (1, 'Material');
INSERT INTO COSTCENTREGROUP VALUES (2, 'Manufacturing');
INSERT INTO COSTCENTREGROUP VALUES (3, 'Sales');
INSERT INTO COSTCENTREGROUP VALUES (4, 'Administration');
INSERT INTO COSTCENTREGROUP VALUES (5, 'Marketing');

INSERT INTO COSTCENTREITEM VALUES (1, 'Procurement', 1);
INSERT INTO COSTCENTREITEM VALUES (2, 'Incoming Goods', 1);
INSERT INTO COSTCENTREITEM VALUES (3, 'Storage', 1);
INSERT INTO COSTCENTREITEM VALUES (4, 'Internal Transport', 1);
INSERT INTO COSTCENTREITEM VALUES (5, 'QA', 2);
INSERT INTO COSTCENTREITEM VALUES (6, 'R&D', 2);
INSERT INTO COSTCENTREITEM VALUES (7, 'Production', 2);
INSERT INTO COSTCENTREITEM VALUES (8, 'Shipping', 3);
INSERT INTO COSTCENTREITEM VALUES (9, 'Billing', 3);
INSERT INTO COSTCENTREITEM VALUES (10, 'Order Management', 3);
INSERT INTO COSTCENTREITEM VALUES (11, 'IT', 4);
INSERT INTO COSTCENTREITEM VALUES (12, 'Facility Management', 4);
INSERT INTO COSTCENTREITEM VALUES (13, 'Accounting & Controlling', 4);
INSERT INTO COSTCENTREITEM VALUES (14, 'Events', 5);
INSERT INTO COSTCENTREITEM VALUES (15, 'Website', 5);
INSERT INTO COSTCENTREITEM VALUES (16, 'PR', 5);

INSERT INTO COSTLINEITEM VALUES (1, 14, 1736, 'EUR', '26.04.2021');
INSERT INTO COSTLINEITEM VALUES (2, 4, 3572, 'EUR', '12.04.2021');
INSERT INTO COSTLINEITEM VALUES (3, 2, 5691, 'EUR', '15.04.2021');
INSERT INTO COSTLINEITEM VALUES (4, 4, 5430, 'EUR', '16.04.2021');
INSERT INTO COSTLINEITEM VALUES (5, 4, 5287, 'EUR', '02.04.2021');
INSERT INTO COSTLINEITEM VALUES (6, 13, 3157, 'EUR', '09.04.2021');
INSERT INTO COSTLINEITEM VALUES (7, 2, 7944, 'EUR', '03.04.2021');
INSERT INTO COSTLINEITEM VALUES (8, 14, 5232, 'EUR', '15.04.2021');
INSERT INTO COSTLINEITEM VALUES (9, 2, 6181, 'EUR', '16.04.2021');
INSERT INTO COSTLINEITEM VALUES (10, 8, 9862, 'EUR', '13.04.2021');
INSERT INTO COSTLINEITEM VALUES (11, 8, 5224, 'EUR', '06.04.2021');
INSERT INTO COSTLINEITEM VALUES (12, 14, 8794, 'EUR', '24.04.2021');
INSERT INTO COSTLINEITEM VALUES (13, 2, 4045, 'EUR', '11.04.2021');
INSERT INTO COSTLINEITEM VALUES (14, 3, 2376, 'EUR', '22.04.2021');
INSERT INTO COSTLINEITEM VALUES (15, 5, 9173, 'EUR', '06.04.2021');
INSERT INTO COSTLINEITEM VALUES (16, 11, 6986, 'EUR', '01.04.2021');
INSERT INTO COSTLINEITEM VALUES (17, 16, 2489, 'EUR', '16.04.2021');
INSERT INTO COSTLINEITEM VALUES (18, 6, 5157, 'EUR', '13.04.2021');
INSERT INTO COSTLINEITEM VALUES (19, 8, 5922, 'EUR', '26.04.2021');
INSERT INTO COSTLINEITEM VALUES (20, 12, 8432, 'EUR', '02.04.2021');
INSERT INTO COSTLINEITEM VALUES (21, 12, 1993, 'EUR', '04.04.2021');
INSERT INTO COSTLINEITEM VALUES (22, 5, 9953, 'EUR', '22.04.2021');
INSERT INTO COSTLINEITEM VALUES (23, 4, 1648, 'EUR', '09.04.2021');
INSERT INTO COSTLINEITEM VALUES (24, 5, 1135, 'EUR', '12.04.2021');
INSERT INTO COSTLINEITEM VALUES (25, 9, 2277, 'EUR', '29.04.2021');
INSERT INTO COSTLINEITEM VALUES (26, 10, 7080, 'EUR', '12.04.2021');
INSERT INTO COSTLINEITEM VALUES (27, 13, 8791, 'EUR', '22.04.2021');
INSERT INTO COSTLINEITEM VALUES (28, 1, 3691, 'EUR', '20.04.2021');
INSERT INTO COSTLINEITEM VALUES (29, 14, 6404, 'EUR', '20.04.2021');
INSERT INTO COSTLINEITEM VALUES (30, 5, 624, 'EUR', '08.04.2021');
INSERT INTO COSTLINEITEM VALUES (31, 10, 5946, 'EUR', '27.04.2021');
INSERT INTO COSTLINEITEM VALUES (32, 1, 1212, 'EUR', '24.04.2021');
INSERT INTO COSTLINEITEM VALUES (33, 16, 9553, 'EUR', '19.04.2021');
INSERT INTO COSTLINEITEM VALUES (34, 1, 2190, 'EUR', '13.04.2021');
INSERT INTO COSTLINEITEM VALUES (35, 4, 1808, 'EUR', '15.04.2021');
INSERT INTO COSTLINEITEM VALUES (36, 8, 2205, 'EUR', '30.04.2021');
INSERT INTO COSTLINEITEM VALUES (37, 11, 3395, 'EUR', '19.04.2021');
INSERT INTO COSTLINEITEM VALUES (38, 3, 9623, 'EUR', '16.04.2021');
INSERT INTO COSTLINEITEM VALUES (39, 9, 7859, 'EUR', '25.04.2021');
INSERT INTO COSTLINEITEM VALUES (40, 13, 8003, 'EUR', '29.04.2021');
INSERT INTO COSTLINEITEM VALUES (41, 14, 3493, 'EUR', '16.04.2021');
INSERT INTO COSTLINEITEM VALUES (42, 4, 9148, 'EUR', '18.04.2021');
INSERT INTO COSTLINEITEM VALUES (43, 1, 5642, 'EUR', '18.04.2021');
INSERT INTO COSTLINEITEM VALUES (44, 9, 9257, 'EUR', '20.04.2021');
INSERT INTO COSTLINEITEM VALUES (45, 8, 2257, 'EUR', '29.04.2021');
INSERT INTO COSTLINEITEM VALUES (46, 15, 2261, 'EUR', '01.04.2021');
INSERT INTO COSTLINEITEM VALUES (47, 4, 1085, 'EUR', '29.04.2021');
INSERT INTO COSTLINEITEM VALUES (48, 14, 6703, 'EUR', '29.04.2021');
INSERT INTO COSTLINEITEM VALUES (49, 12, 9300, 'EUR', '10.04.2021');
INSERT INTO COSTLINEITEM VALUES (50, 15, 2236, 'EUR', '30.04.2021');
INSERT INTO COSTLINEITEM VALUES (51, 5, 482, 'EUR', '13.04.2021');
INSERT INTO COSTLINEITEM VALUES (52, 6, 724, 'EUR', '27.04.2021');
INSERT INTO COSTLINEITEM VALUES (53, 5, 4072, 'EUR', '06.04.2021');
INSERT INTO COSTLINEITEM VALUES (54, 9, 7650, 'EUR', '03.04.2021');
INSERT INTO COSTLINEITEM VALUES (55, 3, 2491, 'EUR', '25.04.2021');
INSERT INTO COSTLINEITEM VALUES (56, 4, 5240, 'EUR', '12.04.2021');
INSERT INTO COSTLINEITEM VALUES (57, 10, 9370, 'EUR', '02.04.2021');
INSERT INTO COSTLINEITEM VALUES (58, 16, 3446, 'EUR', '22.04.2021');
INSERT INTO COSTLINEITEM VALUES (59, 2, 6605, 'EUR', '17.04.2021');
INSERT INTO COSTLINEITEM VALUES (60, 1, 327, 'EUR', '10.04.2021');
INSERT INTO COSTLINEITEM VALUES (61, 7, 4489, 'EUR', '15.04.2021');
INSERT INTO COSTLINEITEM VALUES (62, 5, 4249, 'EUR', '07.04.2021');
INSERT INTO COSTLINEITEM VALUES (63, 15, 6860, 'EUR', '24.04.2021');
INSERT INTO COSTLINEITEM VALUES (64, 2, 7969, 'EUR', '28.04.2021');
INSERT INTO COSTLINEITEM VALUES (65, 2, 3889, 'EUR', '11.04.2021');
INSERT INTO COSTLINEITEM VALUES (66, 13, 341, 'EUR', '03.04.2021');
INSERT INTO COSTLINEITEM VALUES (67, 7, 9622, 'EUR', '24.04.2021');
INSERT INTO COSTLINEITEM VALUES (68, 14, 8111, 'EUR', '03.04.2021');
INSERT INTO COSTLINEITEM VALUES (69, 5, 4401, 'EUR', '17.04.2021');
INSERT INTO COSTLINEITEM VALUES (70, 7, 7744, 'EUR', '15.04.2021');
INSERT INTO COSTLINEITEM VALUES (71, 15, 8959, 'EUR', '26.04.2021');
INSERT INTO COSTLINEITEM VALUES (72, 9, 9563, 'EUR', '18.04.2021');
INSERT INTO COSTLINEITEM VALUES (73, 1, 7723, 'EUR', '21.04.2021');
INSERT INTO COSTLINEITEM VALUES (74, 13, 2863, 'EUR', '12.04.2021');
INSERT INTO COSTLINEITEM VALUES (75, 2, 7977, 'EUR', '28.04.2021');
INSERT INTO COSTLINEITEM VALUES (76, 5, 4832, 'EUR', '02.04.2021');
INSERT INTO COSTLINEITEM VALUES (77, 15, 4740, 'EUR', '05.04.2021');
INSERT INTO COSTLINEITEM VALUES (78, 4, 2415, 'EUR', '06.04.2021');
INSERT INTO COSTLINEITEM VALUES (79, 7, 5395, 'EUR', '18.04.2021');
INSERT INTO COSTLINEITEM VALUES (80, 4, 7282, 'EUR', '23.04.2021');
INSERT INTO COSTLINEITEM VALUES (81, 2, 653, 'EUR', '23.04.2021');
INSERT INTO COSTLINEITEM VALUES (82, 5, 254, 'EUR', '24.04.2021');
INSERT INTO COSTLINEITEM VALUES (83, 14, 7308, 'EUR', '19.04.2021');
INSERT INTO COSTLINEITEM VALUES (84, 3, 1973, 'EUR', '10.04.2021');
INSERT INTO COSTLINEITEM VALUES (85, 14, 3670, 'EUR', '22.04.2021');
INSERT INTO COSTLINEITEM VALUES (86, 8, 3140, 'EUR', '28.04.2021');
INSERT INTO COSTLINEITEM VALUES (87, 8, 132, 'EUR', '25.04.2021');
INSERT INTO COSTLINEITEM VALUES (88, 15, 2570, 'EUR', '08.04.2021');
INSERT INTO COSTLINEITEM VALUES (89, 7, 8912, 'EUR', '28.04.2021');
INSERT INTO COSTLINEITEM VALUES (90, 10, 7483, 'EUR', '19.04.2021');
INSERT INTO COSTLINEITEM VALUES (91, 9, 6574, 'EUR', '07.04.2021');
INSERT INTO COSTLINEITEM VALUES (92, 8, 662, 'EUR', '01.04.2021');
INSERT INTO COSTLINEITEM VALUES (93, 5, 7069, 'EUR', '21.04.2021');
INSERT INTO COSTLINEITEM VALUES (94, 8, 3032, 'EUR', '26.04.2021');
INSERT INTO COSTLINEITEM VALUES (95, 12, 391, 'EUR', '22.04.2021');
INSERT INTO COSTLINEITEM VALUES (96, 6, 5020, 'EUR', '10.04.2021');
INSERT INTO COSTLINEITEM VALUES (97, 4, 3379, 'EUR', '04.04.2021');
INSERT INTO COSTLINEITEM VALUES (98, 3, 6925, 'EUR', '13.04.2021');
INSERT INTO COSTLINEITEM VALUES (99, 12, 3510, 'EUR', '01.04.2021');
INSERT INTO COSTLINEITEM VALUES (100, 13, 2476, 'EUR', '20.04.2021');


-- Example Code used

-- Chapter: Intro

SELECT
	cost."COSTLINEITEMID",
	ccitem."COSTCENTREITEMTEXT",	
	ccgrp."COSTCENTREGROUPTEXT",
	cost."CURRENCY",
	cost."COST",
	sum("COST") OVER (PARTITION BY ccgrp."COSTCENTREGROUPTEXT") as "RegularSum"
FROM
	COSTCENTREGROUP as ccgrp
	left join
	COSTCENTREITEM as ccitem on ccgrp."COSTCENTREGROUPID" = ccitem."COSTCENTREGROUPID"
	left join
	COSTLINEITEM as cost on ccitem."COSTCENTREITEMID" = cost."COSTCENTREITEMID";



--  Chapter: Syntax

SELECT
	cost."COSTLINEITEMID",
	ccgrp."COSTCENTREGROUPTEXT",
	cost."CURRENCY",
	cost."COST",
	cost."COSTBOOKINGDATE",
	sum("COST") OVER 	(PARTITION BY ccgrp."COSTCENTREGROUPTEXT"
	) as "RegularSum",

	sum("COST") OVER 	(PARTITION BY ccgrp."COSTCENTREGROUPTEXT"
				ORDER BY cost."COSTBOOKINGDATE"
	) as "RunningTotal",

	sum("COST") OVER 	(PARTITION BY ccgrp."COSTCENTREGROUPTEXT"
				ORDER BY cost."COSTBOOKINGDATE"
				ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
	) as "3DaysRunningTotal"
FROM
	COSTCENTREGROUP as ccgrp
	left join
	COSTCENTREITEM as ccitem on ccgrp."COSTCENTREGROUPID" = ccitem."COSTCENTREGROUPID"
	left join
	COSTLINEITEM as cost on ccitem."COSTCENTREITEMID" = cost."COSTCENTREITEMID";



-- Chapter: Case Study

SELECT
	*
FROM
	"SYS"."TABLE_COLUMNS"
WHERE
	"TABLE_NAME" in ('COSTCENTREGROUP', 'COSTCENTREITEM', 'COSTLINEITEM');


SELECT
	cost."COSTLINEITEMID",
	ccitem."COSTCENTREITEMTEXT",	
	ccgrp."COSTCENTREGROUPTEXT",
	cost."COST",
	cost."CURRENCY",
	TO_VARCHAR(cost."COST" / sum("COST") OVER (PARTITION BY ccgrp."COSTCENTREGROUPID"), '99.99%') as "% of CCGROUPCOST"
FROM
	COSTCENTREGROUP as ccgrp
	left join
	COSTCENTREITEM as ccitem on ccgrp."COSTCENTREGROUPID" = ccitem."COSTCENTREGROUPID"
	left join
	COSTLINEITEM as cost on ccitem."COSTCENTREITEMID" = cost."COSTCENTREITEMID"
ORDER BY
	cost."COST" / sum("COST") OVER (PARTITION BY ccgrp."COSTCENTREGROUPID") DESC;



-- Appendix

SELECT
	cost."COSTLINEITEMID",
	ccgrp."COSTCENTREGROUPTEXT",
	ccitem."COSTCENTREITEMTEXT",
	cost."CURRENCY",
	cost."COST",
	cost."COSTBOOKINGDATE",
-- regular aggregations
	MAX("COST") OVER (PARTITION BY ccgrp."COSTCENTREGROUPTEXT") as "MAX",
	ROUND(AVG("COST") OVER (PARTITION BY ccgrp."COSTCENTREGROUPTEXT"), 0) as "AVG",
	COUNT(*) OVER (PARTITION BY ccgrp."COSTCENTREGROUPTEXT") as "COUNT",
-- special functions
	LEAD(cost."COSTBOOKINGDATE", 1) OVER (PARTITION BY ccgrp."COSTCENTREGROUPTEXT" ORDER BY ccitem."COSTCENTREITEMTEXT", cost."COSTBOOKINGDATE") as "LEAD",
	DENSE_RANK() OVER (PARTITION BY ccgrp."COSTCENTREGROUPTEXT" ORDER BY cost."COST" DESC) as "D_RANK",
	ROW_NUMBER() OVER (PARTITION BY ccitem."COSTCENTREITEMTEXT" ORDER BY cost."COSTBOOKINGDATE") as "ROWNUM"	
FROM
	COSTCENTREGROUP as ccgrp
	left join
	COSTCENTREITEM as ccitem on ccgrp."COSTCENTREGROUPID" = ccitem."COSTCENTREGROUPID"
	left join
	COSTLINEITEM as cost on ccitem."COSTCENTREITEMID" = cost."COSTCENTREITEMID"
WHERE
	ccgrp."COSTCENTREGROUPTEXT" = 'Administration'
ORDER BY
	ccitem."COSTCENTREITEMTEXT", cost."COSTBOOKINGDATE";
