Posted by Erland Sommarskog on 06/07/06 07:06

Alexander Kuznetsov (AK_TIREDOFSPAM@hotmail.COM) writes:
> one more question: what if there is only one row with negative amount?
>
> INSERT INTO t1 VALUES (11, '20060101', '2321323', -100.00)
>
> and there is no corresponding row with positive amount? Nothing in the
> posted DDL prevents you from that. In fact, originally I was
> considering the query posted by Hugo, but realized it would return that
> single row with negative amount and assumed it incorrect. It looks like
> there might be no base for my assumption.

Having watched the thread from aside, I think the real problem is that
the data model needs improvement. I would add a bit column "isbalanceentry"
or some such. And of course add a primary key. (InvoiceNumber,
isbalanceentry) looks like a candidate.

Better get the data model in order, before looking at smart queries.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Create project/stored procedure for SQLCRL
• Prev in forum: Re: Challenge: Can you optimize this?
• Thread view: Re: Challenge: Can you optimize this?

[Reply to this message]