Solved

Conditional sub-select

Posted on 2011-02-28
13
289 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
  • 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How to calculate iops? 12 28
Report Builder 9 31
Haw to apply join on 2 tables with this scenario 4 10
Square brackets 4 0
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

747 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

11 Experts available now in Live!

Get 1:1 Help Now