Query to select record based on Month in SQL Server

Last Reply 5 months ago By pandeyism

Posted 5 months ago

Hello

I have project using asp.net web form and vb.net code

The tables :

Departments:

  • Department_name
  • Id

Units:

  • Id
  • Department_id
  • Unit_name

Users:

  • User_name
  • File_no 
  • Unit_id
  • Full_user_name
  • Id

Schools:

  • School_name
  • Id

Visiting_table:

  • Date
  • School_id

I want to select data from this tables monthly and all of dates in the month

For example if I want data for September will be like :

By user_id or unit_id or department_id

User_file_no

Id

date

department

unit

Full_user_name

School_name

01/09/2018

 

 

 

 

02/09/2018

 

 

 

 

03/09/2018

 

 

 

 

04/09/2018

 

 

 

 

05/09/2018

 

 

 

 

06/09/2018

 

 

 

 

07/09/2018

 

 

 

 

08/09/2018

 

 

 

 

09/09/2018

 

 

 

 

10/09/2018

 

 

 

 

11/09/2018

 

 

 

 

12/09/2018

 

 

 

 

13/09/2018

 

 

 

 

14/09/2018

 

 

 

 

15/09/2018

 

 

 

 

16/09/2018

 

 

 

 

17/09/2018

 

 

 

 

18/09/2018

 

 

 

 

19/09/2018

 

 

 

 

20/09/2018

 

 

 

 

21/09/2018

 

 

 

 

22/09/2018

 

 

 

 

23/09/2018

 

 

 

 

24/09/2018

 

 

 

 

25/09/2018

 

 

 

 

26/09/2018

 

 

 

 

27/09/2018

 

 

 

 

28/09/2018

 

 

 

 

29/09/2018

 

 

 

 

30/09/2018

 

 

 

 

 thank yoy .

You are viewing reply posted by: pandeyism 5 months ago.
Posted 5 months ago

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