Solved

SQL Select on a Select with criteria

Posted on 2013-05-26
10
247 Views
Last Modified: 2013-05-31
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'
0
Comment
Question by:murbro
[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
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 8

Expert Comment

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

Open in new window

0
 

Author Comment

by:murbro
ID: 39197478
Hi. Thanks but that doesn't help. I am trying to do a Select on a Select with criteria
0
 

Accepted Solution

by:
murbro earned 0 total points
ID: 39197479
Worked it out myself

Select * From (SELECT [Transactions].* FROM [Transactions]) As oAlias Where [Transaction Date] >'01 February 2013' AND [Transaction Date] <'26 May 2013'
0
Independent Software Vendors: 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!

 

Author Comment

by:murbro
ID: 39197496
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
0
 
LVL 8

Expert Comment

by:rpkhare
ID: 39197497
A valid and more better way was suggested for the inner query.
0
 
LVL 8

Expert Comment

by:rpkhare
ID: 39197498
SELECT * FROM
(SELECT * FROM [Transactions]
WHERE [TransactionDate] BETWEEN
'2013-02-01' AND '2013-05-26') T

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39197679
>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'?
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39197680
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39197803
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'
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

739 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