• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • Last Modified:

SQL Query using mulitple dates

Hi

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

SELECT *
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?
0
wilko100
Asked:
wilko100
2 Solutions
 
mvdeveloperCommented:
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 userdetails.id = availdates.id
where availdate in
  (select availdate from availdates where user_id = 1)
and userid <> 1
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
GO

CREATE TABLE #tmp (userid int, dt date)

INSERT INTO #tmp (userid, dt)
VALUES
      (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
0
 
deightonCommented:
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)
)
AND
U1.ID <> 1

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
deightonCommented:
or if any one or more matches will do

SELECT *
FROM UserDetails U1 WHERE 
    EXISTS(
		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

0
 
awking00Commented:
Can you show the relevant table structures and how the sample data appears in each?
0
 
ZberteocCommented:
Try this:

SELECT 
	*
FROM 
	UserDetails
where
	exists
	( 
		select * from AvailableDates 
		where AvailableDate in 
			(select AvailableDate from AvailableDates where id = 1)
	)

Open in new window

0
 
wilko100Author Commented:
You guys make it look so easy! Cheers, appreciated
0
 
deightonCommented:
thanks, good luck with your project.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now