ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.Query to select record based on Month in SQL Server<p>Hi <a class="username" rel="abualmazen"> abualmazen</a>,</p> <p>Please refer below query.</p> <p><span style="text-decoration: underline;"><strong>SQL</strong></span></p> <pre class="brush: sql">DECLARE @Department AS TABLE(id INT, department_name VARCHAR(20)) INSERT INTO @Department VALUES(1,&#39;IT&#39;) INSERT INTO @Department VALUES(2,&#39;CS&#39;) INSERT INTO @Department VALUES(3,&#39;EXTC&#39;) INSERT INTO @Department VALUES(4,&#39;EE&#39;) INSERT INTO @Department VALUES(5,&#39;EC&#39;) DECLARE @Unit AS TABLE(id INT, unit_name VARCHAR(20),department_id INT) INSERT INTO @Unit VALUES(1,&#39;u1&#39;,1) INSERT INTO @Unit VALUES(2,&#39;u2&#39;,2) INSERT INTO @Unit VALUES(3,&#39;u3&#39;,3) INSERT INTO @Unit VALUES(4,&#39;u4&#39;,4) INSERT INTO @Unit VALUES(5,&#39;u4&#39;,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,&#39;Mudassar&#39;,&#39;Mudassar Khan&#39;,1,1) INSERT INTO @User VALUES(2,&#39;John&#39;,&#39;John Hammond&#39;,2,2) INSERT INTO @User VALUES(3,&#39;Suzanne&#39;,&#39;Suzanne Mathews&#39;,3,3) INSERT INTO @User VALUES(4,&#39;Robert&#39;,&#39;Robert Schidner&#39;,4,4) INSERT INTO @User VALUES(5,&#39;Robert&#39;,&#39;Robert Schidner&#39;,5,5) DECLARE @Visiting AS TABLE(id INT, date DATETIME, user_file_no INT, school_id INT) INSERT INTO @Visiting VALUES(1,&#39;8/1/2018&#39;,1,1) INSERT INTO @Visiting VALUES(2,&#39;8/4/2018&#39;,2,2) INSERT INTO @Visiting VALUES(3,&#39;9/2/2018&#39;,3,3) INSERT INTO @Visiting VALUES(4,&#39;9/3/2018&#39;,2,4) INSERT INTO @Visiting VALUES(5,&#39;9/5/2018&#39;,5,5) DECLARE @School AS TABLE(id INT, school_name VARCHAR(20)) INSERT INTO @School VALUES(1,&#39;TCSC&#39;) INSERT INTO @School VALUES(2,&#39;Patel Colllege&#39;) INSERT INTO @School VALUES(3,&#39;St.Xavier&#39;) INSERT INTO @School VALUES(4,&#39;ST.george&#39;) INSERT INTO @School VALUES(5,&#39;ST.george&#39;) 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())) +&#39;-&#39;+ CONVERT(VARCHAR(10),@month) +&#39;-&#39; +&#39;01&#39; SELECT @total = DATEDIFF(DAY, @Date, DATEADD(MONTH, 1, @Date)) SET @Counter = 1 WHILE @Counter &lt;= @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</pre> <p><span style="text-decoration: underline;"><strong>Output</strong></span></p> <p>1/9/2018 &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; NULL &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;NULL &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NULL &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;NULL</p> <p>2/9/2018 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; EXTC &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; u3 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Suzanne Mathews &nbsp;&nbsp;St.Xavier</p> <p>.</p> <p>.</p> <p>.</p> <p>.</p> <p>.</p> <p>&nbsp;</p> <p>30/9/2018 &nbsp; &nbsp;&nbsp; NULL &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;NULL &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NULL &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;NULL</p>https://www.aspforums.net:443/Threads/107288/Query-to-select-record-based-on-Month-in-SQL-Server/https://www.aspforums.net:443/Threads/107288/Query-to-select-record-based-on-Month-in-SQL-Server/Wed, 17 Oct 2018 00:32:27 GMT