[RESOLVED] Need help with date

[usamaalam]

Hello everybody,

I need some hint about WHERE clause for my query. I have a customer table and a session history table with CustomerID as foreign key and LoginDate. When a customer logs in, a record is entered in session history table with current date and time.

I need to pick all the customers who have NOT logged in for last 15 days.

Any idea??

Thanks.

[Blakk_Majik]

Something like this?

Select CustomerID
From sessionhistorytable
Where loginDate Between '1900-01-01' and Current Date - 15 Days

This should return all users that have NOT logged in within the last 15 days.

Please note, this is DB2, so the syntax may differ slightly depending on what dbms you are using.

[usamaalam]

I'll test it, but I think this will show records between '1900-01-01' and (Current Date - 15) Days. Please note according to the schema, if a customer has not logged in within last 15 days, there is no record with his ID in SessionHistory table for the dates between current date and (current date - 15 days). He may have a record two months ago or 25 days ago or may be not record at all.

Thanks.

[pvbangera]

select CustomerID, LoginDate from Customer, SessionHistory 
where SessionHistory.CustomerID = Customer.CustomerID 
and SessionHistory.LoginDate < getdate() - 15

[usamaalam]

Great, thanks a lot.

[Blakk_Majik]

edit: was posted my response after pvbangera.

[usamaalam]

Sorry for re-opening this thread. My sp gives me some invalid results, it gives me customers who have logged in today as well.

CREATE PROCEDURE pcsn_GetCustomersNotLoggedIn

@NumDays AS INT

AS

SELECT DISTINCT
	(
		SELECT 
			MAX([SessionHistory].[LoginTime])
		FROM
			[SessionHistory]
		WHERE
			[vuCustomers].[ID] = [SessionHistory].[FKCustomerID]
	) AS [LastLoginDate],
	[vuCustomers].[ID],
	[vuCustomers].[CustomerNumber],
	[vuCustomers].[Password],
	[vuCustomers].[MembershipDate],
	[vuCustomers].[RenewalDate],
	[vuCustomers].[ExpiryDate],
	[vuCustomers].[FirstName],
	[vuCustomers].[LastName],
	[vuCustomers].[Email],
	[vuCustomers].[CompanyName],
	[vuCustomers].[PhoneNumber],
	[vuCustomers].[CellNumber],
	[vuCustomers].[Address],
	[vuCustomers].[City],
	[vuCustomers].[Zip],
	[vuCustomers].[FKCountryID],
	[vuCustomers].[Country],
	[vuCustomers].[FKPlanID],
	[vuCustomers].[Plan],
	[vuCustomers].[IsActive]
FROM
	[vuCustomers]
LEFT JOIN
	[SessionHistory]
ON
	[vuCustomers].[ID] = [SessionHistory].[FKCustomerID]
WHERE
	[SessionHistory].[LoginTime]  < GETDATE()  - @NumDays
ORDER BY
	[vuCustomers].[CustomerNumber]
ASC
GO

Any ideas??

Thanks.

[pvbangera]

CREATE PROCEDURE pcsn_GetCustomersNotLoggedIn

@NumDays AS INT

AS

SELECT DISTINCT
	(
		SELECT 
			MAX([SessionHistory].[LoginTime])
		FROM
			[SessionHistory]
		WHERE
			[vuCustomers].[ID] = [SessionHistory].[FKCustomerID]
		AND
		        [SessionHistory].[LoginTime]  < GETDATE()  - @NumDays
	) AS [LastLoginDate],
	[vuCustomers].[ID],
	[vuCustomers].[CustomerNumber],
	[vuCustomers].[Password],
	[vuCustomers].[MembershipDate],
	[vuCustomers].[RenewalDate],
	[vuCustomers].[ExpiryDate],
	[vuCustomers].[FirstName],
	[vuCustomers].[LastName],
	[vuCustomers].[email],
	[vuCustomers].[CompanyName],
	[vuCustomers].[PhoneNumber],
	[vuCustomers].[CellNumber],
	[vuCustomers].[Address],
	[vuCustomers].[City],
	[vuCustomers].[Zip],
	[vuCustomers].[FKCountryID],
	[vuCustomers].[Country],
	[vuCustomers].[FKPlanID],
	[vuCustomers].[Plan],
	[vuCustomers].[IsActive]
FROM
	[vuCustomers]
LEFT JOIN
	[SessionHistory]
ON
	[vuCustomers].[ID] = [SessionHistory].[FKCustomerID]
WHERE
	[SessionHistory].[LoginTime]  < GETDATE()  - @NumDays
ORDER BY
	[vuCustomers].[CustomerNumber]
ASC
GO

[usamaalam]

I think its showing the customers who have logged in before 15 days. Please see the first image where I am trying to get the customers who have NOT logged in within last 15 days and notice the highlighted date with ID = 86.

However, notice the second image from actual table which says that customer with ID = 86 has logged in last time on 30th July, 2008. It means this customer shouldn't show up as a result and only those customers should show up who have not logged in after 14th July, i.e. within last 15 days.

Thanks.

[brucevde]

I think you will have to use Not Exists and a subquery.

SELECT DISTINCT
	(SELECT MAX([SessionHistory].[LoginTime]) FROM [SessionHistory] 
     WHERE [vuCustomers].[ID] = [SessionHistory].[FKCustomerID]) AS [LastLoginDate],
	[vuCustomers].[ID],
	[vuCustomers].[CustomerNumber],
	[vuCustomers].[Password],
	[vuCustomers].[MembershipDate],
	[vuCustomers].[RenewalDate],
	[vuCustomers].[ExpiryDate],
	[vuCustomers].[FirstName],
	[vuCustomers].[LastName],
	[vuCustomers].[email],
	[vuCustomers].[CompanyName],
	[vuCustomers].[PhoneNumber],
	[vuCustomers].[CellNumber],
	[vuCustomers].[Address],
	[vuCustomers].[City],
	[vuCustomers].[Zip],
	[vuCustomers].[FKCountryID],
	[vuCustomers].[Country],
	[vuCustomers].[FKPlanID],
	[vuCustomers].[Plan],
	[vuCustomers].[IsActive]
FROM [vuCustomers]
WHERE Not Exists 
      (Select FKCustomerID From SessionHistory 
       Where [SessionHistory].[LoginTime] > GETDATE()  - @NumDays And
             FKCustomerID = [vuCustomers].[ID])
ORDER BY [vuCustomers].[CustomerNumber] ASC

[usamaalam]

This is perfect, thanks.