Hi abualmazen,
Please refer below query.
SQL
DECLARE @Department AS TABLE(id INT, department_name VARCHAR(20))
INSERT INTO @Department VALUES(1,'IT')
INSERT INTO @Department VALUES(2,'CS')
INSERT INTO @Department VALUES(3,'EXTC')
INSERT INTO @Department VALUES(4,'EE')
INSERT INTO @Department VALUES(5,'EC')
DECLARE @Unit AS TABLE(id INT, unit_name VARCHAR(20),department_id INT)
INSERT INTO @Unit VALUES(1,'u1',1)
INSERT INTO @Unit VALUES(2,'u2',2)
INSERT INTO @Unit VALUES(3,'u3',3)
INSERT INTO @Unit VALUES(4,'u4',4)
INSERT INTO @Unit VALUES(5,'u4',5)
DECLARE @User AS TABLE(id INT, user_name VARCHAR(20), full_user_name VARCHAR(20), unit_id INT, file_no INT)
INSERT INTO @User VALUES(1,'Mudassar','Mudassar Khan',1,1)
INSERT INTO @User VALUES(2,'John','John Hammond',2,2)
INSERT INTO @User VALUES(3,'Suzanne','Suzanne Mathews',3,3)
INSERT INTO @User VALUES(4,'Robert','Robert Schidner',4,4)
INSERT INTO @User VALUES(5,'Robert','Robert Schidner',5,5)
DECLARE @Visiting AS TABLE(id INT, date DATETIME, user_file_no INT, school_id INT)
INSERT INTO @Visiting VALUES(1,'8/1/2018',1,1)
INSERT INTO @Visiting VALUES(2,'8/4/2018',2,2)
INSERT INTO @Visiting VALUES(3,'9/2/2018',3,3)
INSERT INTO @Visiting VALUES(4,'9/3/2018',2,4)
INSERT INTO @Visiting VALUES(5,'9/5/2018',5,5)
DECLARE @School AS TABLE(id INT, school_name VARCHAR(20))
INSERT INTO @School VALUES(1,'TCSC')
INSERT INTO @School VALUES(2,'Patel Colllege')
INSERT INTO @School VALUES(3,'St.Xavier')
INSERT INTO @School VALUES(4,'ST.george')
INSERT INTO @School VALUES(5,'ST.george')
DECLARE @tempinsert AS TABLE(Date DATETIME, department_name VARCHAR(20),unit_name VARCHAR(20),full_user_name VARCHAR(20), School_name VARCHAR(20))
DECLARE @Counter INT,@total INT,@month INT,@fileNo INT
SET @month = 9
SET @fileNo = 3
DECLARE @Date DATETIME
SET @Date = CONVERT(VARCHAR(10),DATEPART(YEAR,GETDATE())) +'-'+ CONVERT(VARCHAR(10),@month) +'-' +'01'
SELECT @total = DATEDIFF(DAY, @Date, DATEADD(MONTH, 1, @Date))
SET @Counter = 1
WHILE @Counter <= @total
BEGIN
DECLARE @DeptName VARCHAR(20),@UnitName VARCHAR(20),@UserName VARCHAR(20),@SchoolName VARCHAR(20)
SELECT @DeptName=d.department_name
,@UnitName=u.unit_name
,@UserName=us.full_user_name
,@SchoolName=School_name
FROM @Department d
INNER JOIN @Unit u ON d.id = u.department_id
INNER JOIN @User us ON us.unit_id = u.id
INNER JOIN @Visiting v ON us.file_no=v.user_file_no
INNER JOIN @School sc ON v.school_id=sc.id
WHERE Date = @Date AND us.file_no = @fileNo
INSERT INTO @tempinsert(Date, department_name, unit_name, full_user_name, School_name)VALUES(@Date,@DeptName,@UnitName,@UserName,@SchoolName)
SET @DeptName = NULL
SET @UnitName = NULL
SET @UserName = NULL
SET @SchoolName = NULL
SET @Counter = @Counter + 1;
SET @Date = DATEADD(DAY, 1, @Date)
CONTINUE;
END
SELECT * FROM @tempinsert
Output
1/9/2018 NULL NULL NULL NULL
2/9/2018 EXTC u3 Suzanne Mathews St.Xavier
.
.
.
.
.
30/9/2018 NULL NULL NULL NULL
