CODEWITHSUNDEEP
sqlms-access
jon rios
jon rios

Reputation: 398

Return 2 records from Select Statement with different Where Clause

I wanted to return 2 separate records using 1 select statement with different WHERE clauses. I need assistance with the proper syntax or commands

my columns are: qDate, qTicker, qClose

SELECT
qClose (qDate = #5/15/2015#) AS FirstClose,
qClose (qDate = #5/10/2015#) AS SecondClose,
FROM Quotes
WHERE qTicker = "A";

here is the table structure

qDate, qTicker, qClose
5/15/2015, A, 45.00
5/14/2015, A, 43.50
5/10/2015, A, 42.00

I want something like this:

qTicker  FirstClose   SecondClose 
A        42.00        45.00

How can I achieve this?

Upvotes: 1

Views: 69

Answers (2)

LondonRob
LondonRob

Reputation: 78803

You're trying to get the value of qClose for a given qTicker on two different dates returned in the same row, right?

You'll need something similar to this (although I don't know your setup so it won't be this exactly):

SELECT first.qTicker, FirstClose, SecondClose,
(FirstClose - SecondClose) as CloseDifference
FROM
(
    SELECT qTicker, qClose as FirstClose FROM Quotes
    WHERE qDate = #5/15/2015#
) first
INNER JOIN
(
    SELECT qTicker, qClose as SecondClose FROM Quotes
    WHERE qDate = #5/10/2015#
) second
ON
first.qTicker = second.qTicker
WHERE
first.qTicker = "A"

I've even given you a difference column.

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

    Select
    qCl (qDate = #5/15/2015#) as FirstClose,
    qCl (qDate = #5/10/2015#) as SecondClose
    from Quotes
    WHERE --condition
    union all
    Select
    qCl (qDate = #5/15/2015#) as FirstClose,
    qCl (qDate = #5/10/2015#) as SecondClose
    from Quotes
    WHERE --condition

This may be what you need

Upvotes: 2

Related Questions