Select upon a date difference between two tables


Hi,

I have 2 tables:

1. Companies:
companyId, LastLoginDate.

2. Jobs:
1. JobId, CompanyId, EntryDate.

Each company can have multiple jobs.

I would like to select all companies whose most recent EntryDate is more than 10 days before its LastLogin Date. (LastLoginDate-EntryDate > 10)

I.e., if a company's LastLoginDate is 12/25/04, and the most recent EntryDate of its jobs is 12/14/04 - it will be selected. If it has a job whose EntryDate is 12/16/04 - it will not be selected.

Can this be done?

Thanks!

Agmon,
l_agmonAsked:
Who is Participating?
 
paeloCommented:
Try this:

SELECT C.*
FROM dbo.tblCompanies C
INNER JOIN dbo.tblJobs J
ON C.CompanyID=J.CompanyID
WHERE DATEDIFF(dd,J.EntryDate,C.LastLoginDate)>10

-Paul.
0
 
willcode4coffeeCommented:
Try this:


SELECT Company.CompanyID,
           Job.JobID
LEFT JOIN Jobs ON
          Company.CompanyID = Jobs.CompanyID
WHERE DATEDIFF(d, Company.LastLoginDate, Jobs.EntryDate) > 10




M@
0
 
willcode4coffeeCommented:
paelo,

You beat me by two minutes!

:^)

M@
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
l_agmonAuthor Commented:
It Works - Thanks ! (with some variations - I needed to have a DISTINCT Select)

A related question:

In the Companies Table I also have a date field 'MarketingEmailDate'

Is it possible, within the same stored procedure, to set MarketingEmailDate field to GetDate() for for each company whose record was returned by the Select statement?

My complete stored procedure is:

ALTER PROCEDURE dbo.CompaniesSelectNonVisiting
AS
 SET NOCOUNT ON SELECT DISTINCT dbo.Companies.id, dbo.Companies.name
FROM  dbo.Companies INNER JOIN
               dbo.Jobs ON dbo.Companies.id = dbo.Jobs.companyID INNER JOIN
               dbo.UserJobs ON dbo.Jobs.JobID = dbo.UserJobs.JobId
WHERE (DATEDIFF(d, dbo.Companies.MarketingEmailDate, GETDATE()) > 7) AND (DATEDIFF(d, dbo.Companies.LastLogin, dbo.UserJobs.CVSentDate) >= 10)

Thanks!

Agmon.
0
 
paeloCommented:
Well, the SELECT and UPDATE would need to be separate statements, but since your SELECT statement is affected by the MarketingEmailDate (prior to update), the UPDATE would need to be after the SELECT.  

So try something like this:


ALTER PROCEDURE dbo.CompaniesSelectNonVisiting
AS
BEGIN
SET NOCOUNT ON

--display data
SELECT DISTINCT dbo.Companies.id, dbo.Companies.name
FROM  dbo.Companies INNER JOIN
               dbo.Jobs ON dbo.Companies.id = dbo.Jobs.companyID INNER JOIN
               dbo.UserJobs ON dbo.Jobs.JobID = dbo.UserJobs.JobId
WHERE (DATEDIFF(d, dbo.Companies.MarketingEmailDate, GETDATE()) > 7) AND (DATEDIFF(d, dbo.Companies.LastLogin, dbo.UserJobs.CVSentDate) >= 10)

--update data
UPDATE dbo.Companies
SET MarketingEmailDate=GETDATE()
WHERE dbo.Companies.id IN (
SELECT DISTINCT dbo.Companies.id, dbo.Companies.name
FROM  dbo.Companies INNER JOIN
               dbo.Jobs ON dbo.Companies.id = dbo.Jobs.companyID INNER JOIN
               dbo.UserJobs ON dbo.Jobs.JobID = dbo.UserJobs.JobId
WHERE (DATEDIFF(d, dbo.Companies.MarketingEmailDate, GETDATE()) > 7) AND (DATEDIFF(d, dbo.Companies.LastLogin, dbo.UserJobs.CVSentDate) >= 10))


END


-Paul.
0
 
BulZeyECommented:
something like:

UPDATE c
  set c.MarketingEmailDate = GetDate()
FROM  dbo.Companies c
INNER JOIN dbo.Jobs j
  ON c.id = j.companyID
INNER JOIN dbo.UserJobs uj
  ON j.JobID = uj.JobId
WHERE (DATEDIFF(d, c.MarketingEmailDate, GETDATE()) > 7) AND (DATEDIFF(d, c.LastLogin, uj.CVSentDate) >= 10)

and you may want to check if the Companies.MarketingEmailDate is null??

WHERE (c.MarketingEmailDate IS NULL OR DATEDIFF(d, c.MarketingEmailDate, GETDATE()) > 7) AND (DATEDIFF(d, c.LastLogin, uj.CVSentDate) >= 10)


0
 
BulZeyECommented:
dang... paelo :)  ok.. one minute... I tried..
0
 
paeloCommented:
Unfortunately, the update after the select could cause some problems, especially if you display the results of this procedure in a report.  There are other, slightly more complicated, ways to do this, such as storing the ids in a temp table and performing the UPDATE and SELECT in that order, and other ways.

It depends on what your needs are on this.

-Paul.
0
 
l_agmonAuthor Commented:
Hi Paul,

Thanks for your quick answers!

I am using the results of this procedure in an ASP.NET application - for each record returned - I need to send the company an email.

Would it work for that scenario?

Thanks,

Agmon.
0
 
paeloCommented:
It should, but you may want to separate the SELECT and UPDATE into two separate stored procedures.  Then you could run the first to view the records and send out the emails from your ASP.NET application, then run the second to update the records to mark them as updated.  Then you can be sure that if something goes wrong with the emailing process, you haven't erroneously updated the records as well.

-Paul.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.