sqlQuery 2Tables -Date - 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

SQL Server 2000 Forums

SQL Server Development (2000)

sqlQuery 2Tables -Date

Author

Topic

Meti
Starting Member

4 Posts

Posted - 2006-02-05 : 06:13:02

Hi!
I am a beginner in c# developing. The problem is that I don't have any idea how to write this sql query:
I have two tables the one is Kunden (its german and means clients) the other is Rechnung(invoice). In the table invoice is the invoiceNr and the Date and so on.

Now I want to find out which clients have not been in the local since a period.

strSql="SELECT * FROM  Kunden INNER JOIN Rechnung ON Kunden.KundenNr=Rechnung.KundenNr WHERE CDate(Rechnung.Datum) BETWEEN '"+strDate1+"'AND '"+strDate2+"'";

that is the code how i tried but i get here the clients who were in the last period present. (ps: this code don't work 100%)
how can i query wich people were not here in this period its the opposite of the upper code.

I hope anybody understand my english :)

thank you in advance
meti!

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-05 : 08:55:32

Hi meti,
Welcome to the SQLTeam

1. The way that you are writing the code is not a recommended way. You should use Stored procedures for better security and perfomance.

2. The way that you wrote the query is a bit incorrect as, the string strSQL is passed to the SQL server and that string contains a function CDate, which SQL Server couldn't understand.

3. When you want to get the answer quickly and, for us to understand question clearly, u may need to give some more data as table structures (at least the needed columns), sample data and the results that you expect.

Meti
Starting Member

4 Posts

Posted - 2006-02-05 : 09:37:49

thank u very much for helping
my tables are created in access.
Table1:
Kunden ( here are the dates of the clients)
Columns:KundenNr(primary key); Zuname(string); Vorname(string)
Table2:
Table2
Rechnung(informations about the invoices of the clients)
Columns:JournalNr(Primary Key); KundenNr(Integer - this is related to table Kunden);Datum(DateTime);

Thats the informatons....

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-05 : 09:48:41

You need to pass the dates in the format 'yyyymmdd hh:mm:ss.mmm'
the time is optional.
If you do that you don'need to convert the siored date.

strSql="SELECT * FROM Kunden k INNER JOIN Rechnung r ON k.KundenNr=r.KundenNr WHERE r.Datum BETWEEN '"+strDate1+"'AND '"+strDate2+"'";

As Srinika says it is better to create an sp an pass in the 2 dates.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Meti
Starting Member

4 Posts

Posted - 2006-02-05 : 09:55:24

the problem is how can I query the clients which are not present in this timeSpan.

(not between this time)?

ciao
meti

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-05 : 10:55:48

"select * from Kunden where KundenNr not in (SELECT * FROM Kunden k INNER JOIN Rechnung r ON k.KundenNr=r.KundenNr WHERE r.Datum BETWEEN '"+strDate1+"'AND '"+strDate2+"')";

or better
select * from Kunden k
left JOIN Rechnung r
ON k.KundenNr=r.KundenNr
and r.Datum BETWEEN @strDate1 AND @strDate2
where r.KundenNr is null

or maybe
select k.* from Kunden k
left JOIN (select distinct KundenNr from Rechnung where Datum BETWEEN @strDate1 AND @strDate2) r
ON k.KundenNr=r.KundenNr
where r.KundenNr is null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-06 : 04:38:21

>>"select * from Kunden where KundenNr not in (SELECT * FROM Kunden k INNER JOIN Rechnung r ON k.KundenNr=r.KundenNr WHERE r.Datum BETWEEN '"+strDate1+"'AND '"+strDate2+"')";

I hope, you meant this

"select * from Kunden where KundenNr not in (SELECT KundenNr FROM Kunden k INNER JOIN Rechnung r ON k.KundenNr=r.KundenNr WHERE r.Datum BETWEEN '"+strDate1+"'AND '"+strDate2+"')";

Madhivanan

Failing to plan is Planning to fail

Meti
Starting Member

4 Posts

Posted - 2006-02-06 : 05:18:53

yea thanks I mean this,
but there is an error.
"The Field KundenNr can relate to more Tables"

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-06 : 06:44:46

"select * from Kunden where KundenNr not in (SELECT k.KundenNr FROM Kunden k INNER JOIN Rechnung r ON k.KundenNr=r.KundenNr WHERE r.Datum BETWEEN '"+strDate1+"'AND '"+strDate2+"')";

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources