Conditional sub-select


I have the original query in the code window below.

If the inner MIN select clause returns null, then I need to substitue the below conditional query instead.


******************************************************************************************************
Conditional query:
                              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                                                                    )
Original query:

SELECT s.value, new_startdate, new_enddate 
FROM new_worklocationbiometricscreenings w 
JOIN stringmap s 
ON s.attributevalue = w.new_biometricscreeningtype 
WHERE new_enddate=(
					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												  )
				  ) 
AND s.attributename = 'new_biometricscreeningtype'

Open in new window

sailing_12Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
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'


0
LowfatspreadCommented:
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'

Open in new window

0
sailing_12Author Commented:
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.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

LowfatspreadCommented:
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....
0
sailing_12Author Commented:
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.
0
sailing_12Author Commented:
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'

Open in new window

0
reb73Commented:
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'

Open in new window

0
reb73Commented:
Actually please ignore my post, my interpretation was incorrect!
0
LowfatspreadCommented:
ok sorry i had extra open brackets before min and max....

however i'm still not sure how ... the sub queries relate to the same "customer" as the outer query?
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'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sailing_12Author Commented:
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.
0
sailing_12Author Commented:
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.
0
reb73Commented:
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)

Open in new window

0
LowfatspreadCommented:
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'

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.