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: 6565

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

yogeshc

0 6

Reply
User: yogeshc
Replied: on Jul 18, 2017 11:25 AM
Modified: on Jul 18, 2017 11:29 AM
Report

table add_view_log

Ticket_id	user_id	ReqDate
6	yogesh.0650@gmail.com	2017-07-17
4	yogesh.0650@gmail.com	2017-07-17
4	yogesh.0650@gmail.com	2017-07-17
6	yogesh.0650@gmail.com	2017-02-17

table responce

ReqDate	ticket_id		User_id	
2017-07-15	7		yogesh.0650@gmail.com	
2017-07-15	7	yogesh.0650@gmail.com	
2017-07-15	8	yogesh.0650@gmail.com	
2017-07-15	8	yogesh.0650@gmail.com	
2016-02-23	3	yogesh.0650@gmail.com	
2016-02-23	5	yogesh.0650@gmail.com

sir i wants count how many responce and add_view_log are resent in both table where user_id ="some id" and between date1 and date2 in the form of above mentioned format

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