Solved

Conditional sub-select

Posted on 2011-02-28
13
295 Views
Last Modified: 2012-05-11

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

0
Comment
Question by:sailing_12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 34999136
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34999535
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
 

Author Comment

by:sailing_12
ID: 35000123
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35001767
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
 

Author Comment

by:sailing_12
ID: 35008483
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
 

Author Comment

by:sailing_12
ID: 35008853
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
 
LVL 25

Expert Comment

by:reb73
ID: 35008930
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
 
LVL 25

Expert Comment

by:reb73
ID: 35008957
Actually please ignore my post, my interpretation was incorrect!
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 350 total points
ID: 35009007
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
 

Author Comment

by:sailing_12
ID: 35009035
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
 

Author Comment

by:sailing_12
ID: 35009300
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
 
LVL 25

Expert Comment

by:reb73
ID: 35009407
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35009520
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

724 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