?
Solved

Select upon a date difference between two tables

Posted on 2004-10-25
10
Medium Priority
?
518 Views
Last Modified: 2008-01-09

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,
0
Comment
Question by:l_agmon
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 9

Accepted Solution

by:
paelo earned 2000 total points
ID: 12405226
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
 
LVL 4

Expert Comment

by:willcode4coffee
ID: 12405242
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
 
LVL 4

Expert Comment

by:willcode4coffee
ID: 12405250
paelo,

You beat me by two minutes!

:^)

M@
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:l_agmon
ID: 12405933
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
 
LVL 9

Expert Comment

by:paelo
ID: 12406303
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
 
LVL 4

Expert Comment

by:BulZeyE
ID: 12406307
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
 
LVL 4

Expert Comment

by:BulZeyE
ID: 12406310
dang... paelo :)  ok.. one minute... I tried..
0
 
LVL 9

Expert Comment

by:paelo
ID: 12406488
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
 

Author Comment

by:l_agmon
ID: 12408727
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
 
LVL 9

Expert Comment

by:paelo
ID: 12412372
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question