Question about SQL Query.

Question

0.00/5 (No votes)

See more:

Hi everybody,

I'm having a table with 3 fileds.

1 an ID 6 char
2 a ControlPoint 2 char (values are from A0 to A8)
3 a Datatime.

My question is about i want to get the ID's that are smaller to e.g. "A3" and a minimum month old.

I can get it by sepparate, ControlPoint or Datetime, but no both.
I have any option to get my query or that's impossible?

Thanks for your answers!!.

PD: I can't change any properties of the table. (Forbidden)

Moved by PIEBALDconsult

Imagine this table.

Table A

Field 1 ID Field 2 CP Field 3 Datetime
---------- ---------- ----------------
A00001     A0         10/10/2014


A10000     A0         10/10/2014
A10000     A1         11/10/2014
A10000     A2         11/11/2014
A10000     A3         11/11/2014
A10000     A4         15/12/2014
A10000     A5         17/12/2014

A20000     A0         10/10/2014
A20000     A1         12/10/2014
A20000     A2         13/11/2014
A20000     A3         14/11/2014
A20000     A4         15/12/2014
A20000     A5         17/12/2014
A20000     A6         17/12/2014

A30000     A0         10/10/2014
A30000     A1         12/10/2014
A30000     A2         01/11/2014

i want to get the ID's that are smaller to e.g. "A3" and the date between today 03/12/2014 and the field has to be minimum a month old.

the result of the query should be A00001 and A30000.

Sorry for my english. I know isn't clear.

Posted 2-Dec-14 13:27pm

skakimat23

Updated 2-Dec-14 14:17pm

PIEBALDconsult

v2

Add a Solution

Comments

syed shanu 2-Dec-14 19:47pm

Can you explain more clearly? It’s odd to understand the question.

syed shanu 2-Dec-14 19:49pm

Can you provide table create and with sample insert query. It will be easier to get what you want.

skakimat23 2-Dec-14 20:11pm

-- Content moved to question

PIEBALDconsult 2-Dec-14 20:18pm

Should be easy. What do you have so far?
And I certainly hope you are not storing dates as strings.

skakimat23 2-Dec-14 20:24pm

SELECT ID,count(CP) FROM A
where DATEDIFF(dd, Datetime, GetDate())>30
GROUP BY ID
HAVING count(CP)<4
order by ID;

But isn't work. In my real DB i'm getting more results than i expect.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

syed shanu · Answer 1 · 2014-12-02T16:01:00

Hi Check this query hope this will help you.

SQL

-- Create Table
Create Table A
(
ID  char(6),
CP char(2),
CPDate Datetime
)
--Sample data Insert
Insert into A Values('A00001',     'A0',         '10/10/2014')
 Insert into A Values('A10000',     'A0',         '10/10/2014')
  Insert into A Values('A10000',     'A1',         '10/11/2014')
 Insert into A Values('A10000',     'A2',         '11/11/2014')
  Insert into A Values('A10000',     'A3',         '11/11/2014')
 Insert into A Values('A10000',     'A4',         '12/15/2014')
 Insert into A Values('A10000',     'A5',         '12/17/2014')  
 Insert into A Values('A20000',     'A0',         '10/10/2014')
  Insert into A Values('A20000',     'A1',         '10/12/2014')
  Insert into A Values('A20000',     'A2',         '11/13/2014')
 Insert into A Values('A20000',     'A3',         '11/14/2014')
 Insert into A Values('A20000',     'A4',         '12/15/2014')
 Insert into A Values('A20000',     'A5',         '12/17/2014')
  Insert into A Values('A20000',     'A6',         '12/17/2014')  
 Insert into A Values('A30000',     'A0',         '10/10/2014')
  Insert into A Values('A30000',     'A1',         '10/12/2014')
  Insert into A Values('A30000',     'A2 ',         '11/01/2014')

-- Select Query
SELECT ID,count(CP) FROM A 
where CPDate between DateAdd(mm,-1,CPDate)
 and getdate()
GROUP BY ID
HAVING count(CP)<4 
order by ID;