T-SQL: Giving Analytics Result with "Recursion" and Complex "Having" clause
Introduction
For giving analytics data many developers used "R" or "Phyton" programming lang but with Transact Sql we can give also this result very simple too
Sample Scenario/ Problem Statement
We have a 2 tables:
- first table save name of firms and
- in second table we are saving Sales of this firms
Problem is that
we need to give result from this data 3 months (for example: "May--> June -->July" or "September --> October --> November")
have not Sales
Solution Design/Possible Solutions
first we will create new tables
use tempdb
go
CREATE TABLE FIRMS
(ID INT NOT NULL IDENTITY(1,1) primary key,
NAME_OF_FIRMS NCHAR(100))
CREATE TABLE SALES_OF_FIRMS
(ID INT NOT NULL IDENTITY(1,1) primary key,
FIRM_ID INT ,
MONTH_OF_SALES INT,
AMONT_OF_SALES INT
)
we will add foreign key in SALES_OF_FIRMS table
ALTER TABLE SALES_OF_FIRMS ADD CONSTRAINT fk_firm_id FOREIGN KEY (FIRM_ID) REFERENCES FIRMS(ID);
and we will insert same data for this relation tables
INSERT FIRMS
VALUES
('FIRM_1'),('FIRM_2')
INSERT SALES_OF_FIRMS
VALUES
(1,1,1000),(1,2,2000),(1,3,0),(1,4,0),(1,5,500),(1,6,3000),
(2,1,5000),(2,2,1500),(2,3,0),(2,4,0),(2,5,0),(2,6,8000)
Sample Tests
from this dwh we need to give results of query FIRM_2 with 3,4,5 month because in this 3 month FIRM_2 have not any SALES
select f.NAME_OF_FIRMS,sf.MONTH_OF_SALES,sf.AMONT_OF_SALES
from FIRMS f inner join SALES_OF_FIRMS sf on f.ID=sf.FIRM_ID
Sample Code
DECLARE @REPORT TABLE --- THIS TABLE FOR SHOW OUR REPORT ,WE WILL INSERT THIS TABLE WITH RECURSION ALL ID OF FIRMS
(
NAME_OF_FIRMS NCHAR(100),
MONTH_OF_SALES INT,
AMOUNT_OF_SALES INT
)
DECLARE @ID INT
SELECT @ID=MIN(ID) FROM FIRMS ---STARTING FIRST ELEMENT OF RECURSION
WHILE @ID IS NOT NULL
BEGIN
INSERT INTO @REPORT (NAME_OF_FIRMS,MONTH_OF_SALES,AMOUNT_OF_SALES)
SELECT
F.NAME_OF_FIRMS,
S.MONTH_OF_SALES,
S.AMONT_OF_SALES
FROM SALES_OF_FIRMS S JOIN SALES_OF_FIRMS T
ON
(S.AMONT_OF_SALES=0) and S.FIRM_ID=@ID AND T.FIRM_ID=@ID
INNER JOIN FIRMS F ON F.ID=S.FIRM_ID
GROUP BY
S.MONTH_OF_SALES,S.AMONT_OF_SALES,F.NAME_OF_FIRMS
HAVING (
ISNULL(
MIN (CASE WHEN T.MONTH_OF_SALES>S.MONTH_OF_SALES AND T.AMONT_OF_SALES!=0 THEN T.MONTH_OF_SALES ELSE NULL END)-1,
MAX(CASE WHEN T.MONTH_OF_SALES>S.MONTH_OF_SALES AND T.AMONT_OF_SALES!=0 THEN T.MONTH_OF_SALES
ELSE S.MONTH_OF_SALES END))-
ISNULL(
MAX(CASE WHEN T.MONTH_OF_SALES<S.MONTH_OF_SALES AND T.AMONT_OF_SALES !=0 THEN T.MONTH_OF_SALES ELSE NULL END)+1,
MIN(CASE WHEN T.MONTH_OF_SALES <S.MONTH_OF_SALES THEN T.MONTH_OF_SALES ELSE S.MONTH_OF_SALES END)))+1
>=3
SELECT @ID=MIN (ID ) FROM FIRMS WHERE ID>@ID --- FOR GIVING ANOTHER ID OF FIRMS
END
SELECT * FROM @REPORT
DROP TABLE SALES_OF_FIRMS
DROP TABLE FIRMS
GO
NAME_OF_FIRMS MONTH_OF_SALES AMOUNT_OF_SALES
FIRM_2 3 0
FIRM_2 4 0
FIRM_2 5 0
and result was succesed