Query problem in "where" clause.

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)

Query problem in "where" clause.

Author

Topic

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2006-03-27 : 16:05:07

I have a query that needs to locate the last invoice we sent to a client. If this last invoice dates back as far as 12/31/2004 or later, then include them in the report.

I can locate the final invoice for each client however my problem is that I can't setup my where clause to only include 2004 or later dates. When I do, my query simply ignores this line and includes more recent ones anyway.

Here's my code:
------------------------------------
select max(invoiceID) as invoiceID, clientname, (select max(invoiceDate) from tbilling where billclientid=clientid) as invoiceDate
from tbilling
inner join tclients on clientid = billclientid
where billdate < '1/1/2005'
group by clientid, clientname
order by clientname
---------------------------------------

Any idea what I'm doing wrong with my "where" clause?

Thanks in advance.

-Goalie35

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-27 : 16:26:03

Happy to see some sample data and expected results

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2006-03-27 : 17:02:19

"Happy to see some sample data and expected results"

Here's what I'm currently receiving:

invoiceID------ClientName------------------InvoiceDate
--------------------------------------------------------------
54753-----------First Client--------------2006-03-02 00:00:00.000
55076-----------Second Client-------------2005-12-14 00:00:00.000
37374-----------Third Client--------------2003-03-31 00:00:00.000
52638-----------Fourth Client-------------2005-07-19 00:00:00.000
55648-----------Fifth Client--------------2006-01-25 00:00:00.000
25125-----------Sixth Client--------------1999-06-25 00:00:00.000
---------------------------------------------------------------------

As you can see, the dates exceed 2004. What I need is a result set that looks like the following:

invoiceID------ClientName------------------InvoiceDate
--------------------------------------------------------------
37374-----------Third Client--------------2003-03-31 00:00:00.000
25125-----------Sixth Client--------------1999-06-25 00:00:00.000

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-27 : 21:09:58

This is the results u get & results u expect!! Its good.
Can u put the data (to get the above results)?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-27 : 21:40:55

try this

where billdate < '2005-01-01'

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources