Solved

getting the minum value of two select statements

Posted on 2014-03-07
4
260 Views
Last Modified: 2014-03-07
I have these two select statements joined with a union statement and they return the correct values.  I would like to select the minimum of the two values?

      (select MIN(wou.use_date)
      from web_OnlinePass_Use wou
      inner join web_top_id wti on wti.ind_id = wou.id
      inner join Name n on n.ID = wou.id
      where wti.top_ID = '16040' and (USE_DATE >= '12/07/2012' and USE_DATE <= '12/06/2013'))
      union
      (select MIN(wolu.olu_date)
      from web_Online_library_Use wolu
      inner join web_top_id wti on wti.ind_id = wolu.id
      inner join Name n on n.ID = wolu.id
      where wti.top_ID = '16040' and (olu_DATE >= '12/07/2012' and olu_DATE <= '12/06/2013'))


need the figure how to nest this union as part of another select min() statement?
0
Comment
Question by:Jeff_Kingston
  • 3
4 Comments
 
LVL 11

Expert Comment

by:Simone B
ID: 39912754
Try this:

SELECT MIN(X.DateValue) FROM (
      (select MIN(wou.use_date) AS DateValue
      from web_OnlinePass_Use wou 
      inner join web_top_id wti on wti.ind_id = wou.id
      inner join Name n on n.ID = wou.id
      where wti.top_ID = '16040' and (USE_DATE >= '12/07/2012' and USE_DATE <= '12/06/2013'))
      union
      (select MIN(wolu.olu_date) AS DateValue
      from web_Online_library_Use wolu 
      inner join web_top_id wti on wti.ind_id = wolu.id
      inner join Name n on n.ID = wolu.id
      where wti.top_ID = '16040' and (olu_DATE >= '12/07/2012' and olu_DATE <= '12/06/2013'))
) AS X

Open in new window

0
 
LVL 11

Accepted Solution

by:
Simone B earned 500 total points
ID: 39912770
You can also use a Common Table Expression:

(Edit sorry, forgot the brackets)

WITH CTE (DateValue) AS 
     ( (select MIN(wou.use_date) AS DateValue
      from web_OnlinePass_Use wou 
      inner join web_top_id wti on wti.ind_id = wou.id
      inner join Name n on n.ID = wou.id
      where wti.top_ID = '16040' and (USE_DATE >= '12/07/2012' and USE_DATE <= '12/06/2013'))
      union
      (select MIN(wolu.olu_date) AS DateValue
      from web_Online_library_Use wolu 
      inner join web_top_id wti on wti.ind_id = wolu.id
      inner join Name n on n.ID = wolu.id
      where wti.top_ID = '16040' and (olu_DATE >= '12/07/2012' and olu_DATE <= '12/06/2013')))
SELECT MIN( DateValue) FROM CTE

Open in new window

0
 

Author Closing Comment

by:Jeff_Kingston
ID: 39912774
Super thanks for the rapid reply.
0
 
LVL 11

Expert Comment

by:Simone B
ID: 39912776
You're welcome!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 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

18 Experts available now in Live!

Get 1:1 Help Now