SQL Query using mulitple dates

Posted on 2012-08-15
Last Modified: 2012-08-16

I have 2 tables, one holds user details and the other table holds dates that are available for the users. Im wanting to return all users where there available date matchs a users date. For example:

User 1 is available on the 1/1/2012, 2/1/2012, 3/1/2012 and 4/01/2012
User 2 is available on the 2/1/2012
User 3 is available on the 2/1/2012
User 4 is available on the 3/01/2012 and 4/01/2012

I would like to return all users that match User 1 available dates (in this case, User 2, User 3 and User 4 would be returned along with there dates). I started with this query but its just returning 1 row (User 4), when i know theres more than one match

DECLARE @MyDate date
DECLARE @Id varchar

SELECT @MyDate = AvailableDate From AvailableDates where Id in (1) --this is User 1
SET @Id = 1

FROM UserDetails
JOIN AvailableDates
ON UserDetails.Id = AvailableDates.Id  
WHERE AvailableDate = @MyDate and AvailableDates.Id <> @Id --dont need User 1 details to be returned

Can anyone help on this conundrum?
Question by:wilko100
    LVL 5

    Expert Comment

    The @MyDate is a single scalar value so it will only hold the last in the list of dates when used with the select in this way.
    You will need either a TABLE variable to hold these or a subquery, something along the lines of:

    select distinct * from userdetails
    join availdates on =
    where availdate in
      (select availdate from availdates where user_id = 1)
    and userid <> 1
    LVL 65

    Assisted Solution

    by:Jim Horn
    Copy-paste the below into your SSMS, Execute it to verify it meets your needs, then modify for your situation:

    IF OBJECT_ID('tempdb..#tmp') is not null
          DROP TABLE #tmp

    CREATE TABLE #tmp (userid int, dt date)

    INSERT INTO #tmp (userid, dt)
          (1, '1/1/2012'), (1, '2/1/2012'), (1, '3/1/2012'), (1, '4/1/2012'),
          (2, '2/1/2012'),
          (3, '2/1/2012'),
          (4, '3/1/2012'),       (4, '4/1/2012'),
          (5, '7/1/2012'),       (5, '8/1/2012'),      -- Users 5-7 do not match
          (6, '8/1/2012'),       (6, '9/1/2012'),
          (7, '9/1/2012'),       (7, '10/1/2012')

    -- I would like to return all users that match User 1 available dates
    SELECT u.userid, u.dt
    FROM (
          -- Get every non-User 1
          SELECT userid, dt
          FROM #tmp
          WHERE userid <> 1) u
    JOIN (
          -- Get all user id's from User 1
          SELECT userid, dt FROM #tmp WHERE userid = 1) u1 ON u.dt = u1.dt
    LVL 18

    Expert Comment

    are you saying that all available dates for user have to exist for user 1, if so then try

    SELECT *
    FROM UserDetails U1 WHERE 
    (SELECT count(*) FROM AvailableDates A2 WHERE A2.Id = U1.ID)
    (SELECT count(*) FROM AvailableDates A3 WHERE A3.Id = U1.ID AND A3.AvailableDate IN 
       (SELECT AvailableDate FROM AvailableDates A4 WHERE A4.ID = 1)
    U1.ID <> 1

    Open in new window

    LVL 18

    Accepted Solution

    or if any one or more matches will do

    SELECT *
    FROM UserDetails U1 WHERE 
    		SELECT 0 FROM AvailableDates A1 JOIN AvailableDates A2
    			ON A1.ID = 1 AND A2.ID = U1.ID
    			AND A1.AvailableDate  = A2.AvailableDate
    			AND U1.ID <> 1

    Open in new window

    LVL 31

    Expert Comment

    Can you show the relevant table structures and how the sample data appears in each?
    LVL 26

    Expert Comment

    Try this:

    		select * from AvailableDates 
    		where AvailableDate in 
    			(select AvailableDate from AvailableDates where id = 1)

    Open in new window


    Author Closing Comment

    You guys make it look so easy! Cheers, appreciated
    LVL 18

    Expert Comment

    thanks, good luck with your project.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

    734 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

    21 Experts available now in Live!

    Get 1:1 Help Now