Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Select upon a date difference between two tables

Posted on 2004-10-25
10
Medium Priority
?
517 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

618 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