Count of last 3months and 6months record from two table in SQL Server

yogeshc

SOLVED CLOSED ACTIVE ANSWERED
User: yogeshc
Posted: on Jul 17, 2017 08:10 PM
Forum: SQL Server
Answer: 1
Views: 6570

i have two table

table responces
column name	tada type
-----------------------------
ReqDate		date
ticket_id	int
User_id		varchar(50)

log_view table  
column name	tada type
-----------------------------
Ticket_id	int
user_id		varchar(30)
view_date	date

my code behind is

 Dim curr_date As Date = Date.Today.Date
        Dim datebefore90 As Date = Date.Now.AddDays(-90)
        Dim datebefore180 As Date = Date.Now.AddDays(-180)

        Dim constring As String = ConfigurationManager.ConnectionStrings("RealEstatePortalConnectionString").ConnectionString
        Using con As New SqlConnection(constring)
            Using cmd As New SqlCommand("sp_getcountresponses", con)
                cmd.CommandType = CommandType.StoredProcedure
                '@c_date,@db90,@db180,@uid
                cmd.Parameters.AddWithValue("@c_date", curr_date)
                cmd.Parameters.AddWithValue("@db90", datebefore90)
                cmd.Parameters.AddWithValue("@db180", datebefore180)
                cmd.Parameters.AddWithValue("@u_id", usernm)

                con.Open()

now i wants to write sql procedure please help

zameer

0 7

Reply
ANSWER
User: zameer
Replied: on Jul 19, 2017 05:23 AM
Report

Hi yogeshc,

I have create a sample procedure as per you provided details. Please check the query.

SQL

-- EXEC GetLastMonthsRecords 'yogesh.0650@gmail.com'
CREATE PROCEDURE GetLastMonthsRecords
	@userId varchar(50)
AS
BEGIN
	DECLARE  @responces TABLE (ReqDate DATETIME,ticket_id INT,User_ids varchar(50))
	INSERT INTO @responces VALUES('2017-07-15' , 7,  'yogesh.0650@gmail.com' )
	INSERT INTO @responces VALUES('2017-07-15' , 7,  'yogesh.0650@gmail.com' )
	INSERT INTO @responces VALUES('2017-07-15' , 8 , 'yogesh.0650@gmail.com' )
	INSERT INTO @responces VALUES('2017-07-15' , 8 ,  'yogesh.0650@gmail.com' )
	INSERT INTO @responces VALUES('2017-06-23' , 4 ,  'yogesh.0650@gmail.com' )
	INSERT INTO @responces VALUES('2017-02-23' , 3 ,  'yogesh.0650@gmail.com' )
	INSERT INTO @responces VALUES('2017-02-23' , 5 ,  'yogesh.0650@gmail.com' )

	DECLARE @add_view_log TABLE(Ticket_id  INT,user_ids  varchar(30),view_date  DATETIME)
	INSERT INTO @add_view_log VALUES(6,'yogesh.0650@gmail.com','2017-07-17')
	INSERT INTO @add_view_log VALUES(4,'yogesh.0650@gmail.com','2017-07-17')
	INSERT INTO @add_view_log VALUES(4,'yogesh.0650@gmail.com','2017-07-17')
	INSERT INTO @add_view_log VALUES(6,'yogesh.0650@gmail.com','2017-02-17')
	INSERT INTO @add_view_log VALUES(6,'yogesh.0650@gmail.com','2017-01-17')

	SELECT res.User_ids ,
	(SELECT COUNT(*) FROM  @responces responces WHERE (responces.ReqDate BETWEEN DATEADD(Month,-3,GETDATE()) AND GETDATE()) AND user_ids = @userId) Response_3Month,
	(SELECT COUNT(*) FROM  @responces responces WHERE (responces.ReqDate BETWEEN DATEADD(Month,-6,GETDATE()) AND GETDATE()) AND user_ids = @userId) Response_6Month,  	
	(SELECT COUNT(*)  FROM  @add_view_log addview WHERE (addview.view_date BETWEEN DATEADD(Month,-3,GETDATE()) AND GETDATE()) AND user_ids = @userId) viewlog_3month,
	(SELECT COUNT(*)  FROM  @add_view_log addview WHERE (addview.view_date BETWEEN DATEADD(Month,-6,GETDATE()) AND GETDATE()) AND user_ids = @userId) viewlog_6month  
	FROM @responces res
	GROUP BY res.user_ids
END
GO

Output

User_ids	Response_3Month	Response_6Month	viewlog_3month	viewlog_6month
yogesh.0650@gmail.com	5	7	3	4

Count of last 3months and 6months record from two table in SQL Server

Company

Explore

Follow us

Contact Us