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 table 
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 monht (for example: may-->juny-->july  or september-->october -->november)
have not Sales


Back to top

Solution Design/Possible Solutions *

first we will create this dwh 
i was use hier tempdb
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 *


e.g. 
https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-having-transact-sql?view=sql-server-ver15

Back to top