Link to home
Create AccountLog in
Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on

SQL Select on a Select with criteria

Hi

I have a number of queries that I have to select a date range on. The query below\
is SELECT [Transactions].* FROM [Transactions]) is one of these. What is wrong with my syntax below?


Select * From (SELECT [Transactions].* FROM [Transactions]) Where [Transaction Date] >'01 February 2013' AND [Transaction Date] <'26 May 2013'
Avatar of rpkhare
rpkhare
Flag of India image

Please try below as inner query:
SELECT * FROM [Transactions]
WHERE [TransactionDate] BETWEEN
'2013-02-01' AND '2013-05-26'

Open in new window

Avatar of Murray Brown

ASKER

Hi. Thanks but that doesn't help. I am trying to do a Select on a Select with criteria
ASKER CERTIFIED SOLUTION
Avatar of Murray Brown
Murray Brown
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I've requested that this question be closed as follows:

Accepted answer: 0 points for murbro's comment #a39197479

for the following reason:

worked it out myself
A valid and more better way was suggested for the inner query.
SELECT * FROM
(SELECT * FROM [Transactions]
WHERE [TransactionDate] BETWEEN
'2013-02-01' AND '2013-05-26') T

Open in new window

Avatar of Jim Horn
>I am trying to do a Select on a Select with criteria
Just for kicks and giggles, can you give us an English-only description of what you mean by 'Select on a Select'?
Reason I ask is because if we're talking only one table, and you're not doing anything funky such as filtering on sum values, rank orders, pivot/unpivot, or time series, then a simple select should meet most needs, as was in the first comment.
I would agree with Jim, you do not need the sub query in this instance.

And I would recommend using a syntax other than BETWEEN when working with dates.

Where datefield  >= '2013-02-01' AND datefield < '2013-05-27'

This will ensure that if your datefield contains a time component you include all records that are attributed to 2013-05-26, not just those where the date value is '2013-05-26 00:00:00'