Create Simple Query. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Create Simple Query.

Author

Topic

ladak
Starting Member

13 Posts

Posted - 2010-03-31 : 07:37:31

Hi

I have a table with the following fields.
1. Date
2. CarRegNumber (nvarchar) 10
3. InvoiceAmount (Numeric)

what i want when user enter date like 31.03.2010 the query searches on 31.03.2010 for CarRegNumber, provide list of all CarRegNumber that never comes before. It means that the car arrived on 31.03.2010 are our new customers and they never arrived before 31.03.2010

Thanks

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-31 : 07:43:10

Something like this:


SELECT <fields>
FROM <table>
WHERE Date >= '2010-03-31'

There are 10 types of people in the world, those that understand binary, and those that don't.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-31 : 07:53:58

If you store time also along with date, you may need to trim time part before making comparison. Also using ISO format (yyyymmdd) is better.

where dateadd(day, 0, datediff(day, 0, date)) >= '20100331'

Harsh Athalye
http://www.letsgeek.net/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-31 : 08:09:39

quote:
Originally posted by harsh_athalye

If you store time also along with date, you may need to trim time part before making comparison. Also using ISO format (yyyymmdd) is better.

where dateadd(day, 0, datediff(day, 0, date)) >= '20100331'

Harsh Athalye
http://www.letsgeek.net/

I believe that
where date >= '20100331'
is already enough.

No, you're never too old to Yak'n'Roll if you're too young to die.

ladak
Starting Member

13 Posts

Posted - 2010-03-31 : 09:01:41

Hi there,

the query you provided me is not sufficient, it brings all records where car arrived date >= 31.03.2010, but it will not check that the car has already arrived 2 months ago, i want list of cars which arrived on 31.03.2010 but never visited our showroom before 31.03.2010, it means they are our new customer of 31.03.2010

Thanks
Ladak

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-31 : 09:18:23

select * from table as t1
where date >= '20100331'
and not exists(select * from table as t2 where t2.CarRegNumber = t1.CarRegNumber and t2.date < '20100331')

No, you're never too old to Yak'n'Roll if you're too young to die.

ladak
Starting Member

13 Posts

Posted - 2010-04-01 : 07:17:55

Thanks dear, query works perfectly, Thanks for your support.
Ladak

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-01 : 07:25:51

welcome