Share via

T-SQL: Giving Analytics Result with "Recursion" and Complex "Having" clause

  • Article

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

 Back to top

Sample Scenario/ Problem Statement

We have a 2 tables:

  1. first table save name of firms and
  2. 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

Back to top

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)

Back to top

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

Back to top

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

Back to top

See Also

References

Back to top