Solved

Select upon a date difference between two tables

Posted on 2004-10-25
508 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
Question by:l_agmon
    10 Comments
     
    LVL 9

    Accepted Solution

    by:
    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
    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
    paelo,

    You beat me by two minutes!

    :^)

    M@
    0
     

    Author Comment

    by:l_agmon
    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
    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
    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
    dang... paelo :)  ok.. one minute... I tried..
    0
     
    LVL 9

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    The steps for moving the system databases to a new location are documented in the following technical article: http://msdn.microsoft.com/en-us/library/ms345408(v=SQL.90).aspx. However sometimes after the moving process is finished, though SQL i…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    913 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now