Conditional sub-select

SELECT s.value, new_startdate, new_enddate
FROM new_worklocationbiometricscreenings w
JOIN stringmap s
ON s.attributevalue = w.new_biometricscreeningtype
WHERE new_enddate=isnull (
                   (
                              SELECT MIN(new_enddate)
                              FROM new_worklocationbiometricscreenings
                              WHERE new_enddate >= CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(),120), 120)
                              AND deletionstatecode <> 2
                              AND new_biometricscreeningtype IN (1,3,4)
                              AND new_worklocationid = (      SELECT new_worklocationid
                                                                        FROM contact
                                                                        WHERE contactid = @contactid                                                                          )
                          ),
SELECT MAX(new_enddate)
                              FROM new_worklocationbiometricscreenings
                              WHERE new_enddate < CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(),120), 120)
                              AND deletionstatecode <> 2
                              AND new_biometricscreeningtype IN (1,3,4)
                              AND new_worklocationid = (      SELECT new_worklocationid
                                                                        FROM contact
                                                                        WHERE contactid = @contactid )
                          )
AND s.attributename = 'new_biometricscreeningtype'

no like this ...

just the one sub-select , but have a coalesce around the min/max  with embedded cases...


.... are you sure that you don't want this sub-query correlated to the outer select?

you appear to be retrieving data for any contact... in the outer select but are looking for matches
against a specific contact in the sub-select....

SELECT s.value, new_startdate, new_enddate 
  FROM new_worklocationbiometricscreenings w 
 Inner JOIN stringmap s 
   ON s.attributevalue = w.new_biometricscreeningtype 
WHERE new_enddate=(
					SELECT coalesce((MIN(case when new_enddate >= convert(varchar(10),getdate(),120)
					                         then new_enddate
					                         end) ,
					                 (max(case when new_enddate < convert(varchar(10),getdate(),120)
					                           then new_enddate
					                           end)
					               )              
					  FROM new_worklocationbiometricscreenings 
					 WHERE deletionstatecode <> 2 
					   AND new_biometricscreeningtype IN (1,3,4) 
					   AND new_worklocationid = (	SELECT new_worklocationid 
												FROM contact 
												WHERE contactid = @contactid )
				    ) 
AND s.attributename = 'new_biometricscreeningtype'

Select allOpen in new window

I'm getting syntax errors with both of these that I haven't been able to firgure out.

Ultimately what I need to get is:

based on new_enddate of the new_worklocationbiometricscreenings table, the ONE record that either:

1. Has the soonest endate equal or greater than today(), OR that failing,
2. Has the latest endate less than today()

AND matches all the other criteria: deletionstatecode, screeningtype, worklocation.

post the syntax errors....

please explain the actual scenario...

AND matches all the other criteria: deletionstatecode, screeningtype, worklocation.

it is hard to relate the above statement to your original query as what you've posted as an original working
code piece wouldn't seem to provide that ,,, unless your data/business process is doing something else
to ensure that the correct items are being matched....

Here are the errors:

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near ')'.

************************************************************************************************************

The original sub-query is returning the soonest future record (MIN(new_endate)) where:

1. new_endate >= today      [line 10]
2. deletionstatecode <> 2     [line 11]
3. new_biometricscreeningtype IN (1,3,4)       [line 12]
4. new_worklocationid = worklocationid from client's record in contact table    [line 13]

This seems to be working correctly for us.

What we need to add, is when there is no record that matches the above (MIN function returns null), instead get the most recent 'past' record (MAX(new_endate)), that still meets the other conditions - i.e.

1. new_endate < today
2. deletionstatecode <> 2
3. new_biometricscreeningtype IN (1,3,4)
4. new_worklocationid = worklocationid from client's record in contact table

**********************************************************************************************************

So, the way I see it, the sub-select should either be:

SELECT MIN(new_enddate) as dt
FROM new_worklocationbiometricscreenings
WHERE new_enddate >= CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(),120), 120)
AND deletionstatecode <> 2
AND new_biometricscreeningtype IN (1,3,4)
AND new_worklocationid = (SELECT new_worklocationid FROM contact WHERE contactid = @contactid)


<<<<  OR  >>>>

SELECT MAX(new_enddate)
FROM new_worklocationbiometricscreenings
WHERE new_enddate < CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(),120), 120)
AND deletionstatecode <> 2
AND new_biometricscreeningtype IN (1,3,4)
AND new_worklocationid = (SELECT new_worklocationid FROM contact WHERE contactid = @contactid)

Depending on whether or not the first one actually returns a value.

If there is a more concise or efficient way to code this, great... but ultimately I need the startdate, enddate, and translation of the screeningtype (from stringmap) of the one record in that is either the soonest future enddate, or that failing the most recent past enddate and meets the other three conditions, in either case.

Hope this helps clear it up.

This query below actually provides the correct results, but I am not entirely sure it is the best way to go about it.

SELECT s.value, new_startdate, new_enddate 
FROM new_worklocationbiometricscreenings w 
JOIN stringmap s 
ON s.attributevalue = w.new_biometricscreeningtype 
WHERE new_enddate=(
					SELECT MAX(dt) from 
					(
					SELECT MIN(new_enddate) as dt
					FROM new_worklocationbiometricscreenings 
					WHERE new_enddate >= CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(),120), 120) 
					AND deletionstatecode <> 2 
					AND new_biometricscreeningtype IN (1,3,4) 
					AND new_worklocationid = (SELECT new_worklocationid FROM contact WHERE contactid = '" + Request.QueryString["id"] + "' )
					UNION

					SELECT MAX(new_enddate) 
					FROM new_worklocationbiometricscreenings 
					WHERE new_enddate < CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(),120), 120) 
					AND deletionstatecode <> 2 
					AND new_biometricscreeningtype IN (1,3,4) 
					AND new_worklocationid = (SELECT new_worklocationid FROM contact WHERE contactid = '" + Request.QueryString["id"] + "')
					) x
				  ) 
AND s.attributename = 'new_biometricscreeningtype'

Select allOpen in new window

Your query appears to be a complicated version of essentially the following -

SELECT s.value, new_startdate, new_enddate 
FROM new_worklocationbiometricscreenings w 
JOIN stringmap s 
ON s.attributevalue = w.new_biometricscreeningtype 
WHERE new_enddate=(
			SELECT MAX(dt) as dt
			FROM new_worklocationbiometricscreenings 
			WHERE new_enddate >= CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(),120), 120) 
			AND new_enddate < CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(),120), 120)
			AND deletionstatecode <> 2 
			AND new_biometricscreeningtype IN (1,3,4) 
			AND new_worklocationid = (SELECT new_worklocationid FROM contact WHERE contactid = '" + Request.QueryString["id"] + "' )
				  ) 
AND s.attributename = 'new_biometricscreeningtype'

Select allOpen in new window

Actually please ignore my post, my interpretation was incorrect!

reb73 - I think your version will return no records because lines 8 & 9 will never be met - new_enddate cannot be >= AND < today at the same time.

This aside, you've removed the logic that gets only the one earliest future date, and the one most recent past date.

Lowfat - that seems to work and is more elegant than my solution. I will put it into our test environment and test it.

The outer query is not contact-specific, it is basically just getting the start and end dates and translating the value of the screeningtype of the screening event we've already identified in the sub-query.

Any given screening event is available to many clients based on their worklocation.

An alternative way in case you are on SQL Server 2005 or higher -

(Not 100% sure if this is more efficient than lowfatspread's solution, but the CTE picks the two nearest days to current date using a difference in seconds and automatically uses the minimum of these values which I think is what you are looking for. Do try this out and compare the execution plans vis-a-vis the other solution and let us know!)

;WITH CTE (dt, diff)
AS
(	SELECT Top 2 dt, abs(datediff(second, getdate(), dt)) as diff
	FROM new_worklocationbiometricscreenings 
	WHERE new_enddate >= CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(),120), 120) 
	AND new_enddate < CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(),120), 120)
	AND deletionstatecode <> 2 
	AND new_biometricscreeningtype IN (1,3,4) 
	AND new_worklocationid = (1) SELECT new_worklocationid FROM contact WHERE contactid = '" + Request.QueryString["id"] + "' )
	order by 2
)
SELECT s.value, new_startdate, new_enddate 
FROM new_worklocationbiometricscreenings w 
JOIN stringmap s 
ON s.attributevalue = w.new_biometricscreeningtype 
WHERE s.attributename = 'new_biometricscreeningtype'
and new_enddate=(select min(dt) from cte)

Select allOpen in new window

ok should work then ....

you may find that this works better since it "avoids" the NOT EQUALS test in the where clause...

SELECT s.value, new_startdate, new_enddate 
 
  FROM new_worklocationbiometricscreenings w 
 Inner JOIN stringmap s 
   ON s.attributevalue = w.new_biometricscreeningtype 
WHERE new_enddate=(
					SELECT coalesce( MIN(case deletionstatecode when 2 then null else case
					                         when new_enddate >= convert(char(8),getdate(),112)
					                         then new_enddate
					                         end end) ,
					                 max(case deletionstatecode when 2 then null else case
					                           when new_enddate < convert(char(8),getdate(),112)
					                           then new_enddate
					                           end end)
					               )              
					  FROM new_worklocationbiometricscreenings as b
					  Inner join contact as c
					   on b.new_worklocationid=c.new_worklocationid
					 WHERE new_biometricscreeningtype IN (1,3,4) 
					  and c.contactid = @contactid 
				    ) 
AND s.attributename = 'new_biometricscreeningtype'

Select allOpen in new window