-------------------------------------------------------------------------------------------------------------------
----------------PROBLEM STATEMENT.
-------------------------------------------------------------------------------------------------------------------
--IN THE FINANCE/ACCOUNTING SCENARIOS, PREVIOUS MONTH ENDING BALANCE SHOULD BE EQUALS TO THE NEXT/CURRENT MONTH
--BEGGNING BALANCE, WE HAVE TO IDENTIFY THE RECORDS WHERE PREVIOUS MONTH ENDING BALANCE IS NOT EQUALS TO THE 
--CURRENT/NEXT MONTH BEG BALANCE AND GIVE THOSE RECORDS TO THE MANAGEMENT/LEADERSHIP TEAM TO CHECK ON.

-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
CREATE TABLE MONTH_CATEGORY_SALES
(
FISCAL_PERIOD DATE,
CATEGORY VARCHAR(100),
BEG_BALANCE_USD DECIMAL(5,2),
END_BALANCE_USD DECIMAL(5,2)
)
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
INSERT INTO MONTH_CATEGORY_SALES VALUES('2025-03-01','DATA CENTER',11.50,20.00)
INSERT INTO MONTH_CATEGORY_SALES VALUES('2025-03-01','REWS',21.50,50.00)
INSERT INTO MONTH_CATEGORY_SALES VALUES('2025-03-01','NETWORK EQUIPMENT',31.00,60.00)
INSERT INTO MONTH_CATEGORY_SALES VALUES('2025-03-01','PRODUCT EQUIPMENT',41.00,70.00)
INSERT INTO MONTH_CATEGORY_SALES VALUES('2025-03-01','TIFINANCE',101.50,200.00)
INSERT INTO MONTH_CATEGORY_SALES VALUES('2025-03-01','TEST-FIN',200.50,400.00)
INSERT INTO MONTH_CATEGORY_SALES VALUES('2025-03-01','CORP-ASSETS',100.50,200.00)


INSERT INTO MONTH_CATEGORY_SALES VALUES('2025-04-01','DATA CENTER',20.00,40.00)
INSERT INTO MONTH_CATEGORY_SALES VALUES('2025-04-01','REWS',50.00,80.00)
INSERT INTO MONTH_CATEGORY_SALES VALUES('2025-04-01','NETWORK EQUIPMENT',60.00,90.00)
INSERT INTO MONTH_CATEGORY_SALES VALUES('2025-04-01','PRODUCT EQUIPMENT',70.00,95.00)
INSERT INTO MONTH_CATEGORY_SALES VALUES('2025-04-01','TIFINANCE',200.00,300.00)
INSERT INTO MONTH_CATEGORY_SALES VALUES('2025-04-01','TEST-FIN',450.50,550.00)
INSERT INTO MONTH_CATEGORY_SALES VALUES('2025-04-01','CORP-ASSETS',400.50,600.00)


--INSERT INTO MONTH_CATEGORY_SALES VALUES('2025-03-01','CORP-ASSETS',100.50,200.00)
--INSERT INTO MONTH_CATEGORY_SALES VALUES('2025-04-01','CORP-ASSETS',400.50,600.00)----400.50 ,200.00 =


-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
SELECT * FROM MONTH_CATEGORY_SALES
ORDER BY FISCAL_PERIOD;

-------------------------------------------------------------------------------------------------------------------
-------------------SOLUTION PATTERN-1------USING UNION ALL STATEMENT
-------------------------------------------------------------------------------------------------------------------
SELECT CATEGORY,SUM(BEG_BALANCE_USD) AS BEG_BALANCE_USD,SUM(END_BALANCE_USD) AS END_BALANCE_USD,
SUM(END_BALANCE_USD) - SUM(BEG_BALANCE_USD) AS DIFF 
FROM(
SELECT CATEGORY,0 AS BEG_BALANCE_USD,END_BALANCE_USD FROM MONTH_CATEGORY_SALES
WHERE FISCAL_PERIOD ='2025-03-01'
UNION ALL
SELECT CATEGORY,BEG_BALANCE_USD,0 AS END_BALANCE_USD FROM MONTH_CATEGORY_SALES
WHERE FISCAL_PERIOD ='2025-04-01'

)T
GROUP BY CATEGORY
ORDER BY CATEGORY;
;

-------------------------------------------------------------------------------------------------------------------
-------------------SOLUTION PATTERN-2-------USING LEAD/LAG FUNCTIONS AND DERIVED TABLE CONCEPTS
-------------------------------------------------------------------------------------------------------------------

SELECT CATEGORY,SUM(BEG_BALANCE_USD) AS BEG_BALANCE_USD,SUM(END_BALANCE_USD) AS END_BALANCE_USD,
SUM(END_BALANCE_USD)-SUM(BEG_BALANCE_USD) AS DIFF
FROM(
SELECT FISCAL_PERIOD,CATEGORY,
LEAD(BEG_BALANCE_USD) OVER(PARTITION BY CATEGORY ORDER BY FISCAL_PERIOD) AS BEG_BALANCE_USD, 
LAG(END_BALANCE_USD) OVER(PARTITION BY CATEGORY ORDER BY FISCAL_PERIOD) AS END_BALANCE_USD
FROM MONTH_CATEGORY_SALES
)T
GROUP BY CATEGORY
ORDER BY CATEGORY;

-------------------------------------------------------------------------------------------------------------------
-------------------SOLUTION PATTERN-3-------MULTIPLE CTEs CONCEPTS
-------------------------------------------------------------------------------------------------------------------
;WITH CTE 
AS 
(
SELECT CATEGORY,BEG_BALANCE_USD,END_BALANCE_USD FROM MONTH_CATEGORY_SALES
WHERE FISCAL_PERIOD ='2025-03-01'
),
CTE2 AS 
(
SELECT CATEGORY,BEG_BALANCE_USD,END_BALANCE_USD FROM MONTH_CATEGORY_SALES
WHERE FISCAL_PERIOD ='2025-04-01'
)
SELECT CTE.CATEGORY,CTE.END_BALANCE_USD,CTE2.BEG_BALANCE_USD,
CTE.END_BALANCE_USD-CTE2.BEG_BALANCE_USD AS DIFF 
FROM CTE 
INNER JOIN CTE2 ON CTE.CATEGORY = CTE2.CATEGORY
ORDER BY CATEGORY
;

-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------