December 16, 2010 at 11:50 pm
As Wayne has already suggested, NEVER use a code in PRODUCTION if you don't understand it completely. If you use such code without understanding you will find it difficult to maintain it in the future and will never learn. If you find it difficult to understand some parts feel free to ask us for the details.
The below code should solve your problem in SQL 2000. Its a small change to the query I already provided. Try to understand the same and let us know if you have any issues.
DECLARE @tblTemp TABLE
(
Patient_code int,
Patient_name varchar(50),
Invoice_code int,
Date_Visit date,
Date_Created date
)
INSERT @tblTemp
SELECT 20, 'David', 2, '2010-11-15', '2010-12-15' UNION ALL
SELECT 21, 'Anna', 3, '2010-12-15', '2010-12-15' UNION ALL
SELECT 21, 'Anna', 4, '2010-12-15', '2010-12-15' UNION ALL
SELECT 21, 'Anna', 5, '2010-12-15', '2010-12-16' UNION ALL
SELECT 22, 'Peter', 6, '2010-12-20', '2010-12-20'
SELECT Patient_Code, Patient_Name, tmp.Invoice_Code, Date_Created, Date_Visit,
CASE WHEN NOT EXISTS
(
SELECT *
FROM @tblTemp tmp_inn
WHERE tmp.Patient_Code = tmp_inn.Patient_Code
AND tmp.Invoice_Code > tmp_inn.Invoice_Code
) THEN 'New' ELSE 'Return'
END status
FROM @tblTemp tmp
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 17, 2010 at 1:19 am
Kingston, would u mind explaining me more about "tmp_inn" (maybe Temporary objects) ? Any link talks about it's usage.
Thanks.
December 17, 2010 at 1:38 am
This is called a Subquery in SQL Server.
http://msdn.microsoft.com/en-us/library/ms189575.aspx
Here we are selecting the first 5 columns( Patient_Code, Patient_Name, Invoice_Code, Date_Created, Date_Visit ) directly from the table @tblTemp as we normally do.
For the 6th column, we are joining each and every row from the table @tblTemp again with the same table @tblTemp( with an alias name of tmp_inn ) on the conditions specified( here, we are checking if the table does not have any other row with the same Patient_Code and an Invoice Code less than the Invoice Code of the row concerned ) in the Where Clause and if the condition is satisfied the status is returned as 'New' and if it is not satisfied the status is returned as 'Return'.
So, actually the term 'tmp_inn' is not a temporary object but it is the same table @tblTemp which has been referenced again with an alias name as tmp_inn to avoid confusion.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 19, 2010 at 8:51 pm
OK, thank Kingston, Subquery is not new to me, but Table variable. And I think the important thing here is to create 2 alias names to compare, good idea with me. Now I completely understand your solution.
Finally I want to appreciate all of you: Kingston, Lynn, Wayne. You guys are warm and straightforward. I sorry for making feeling that there's something lazy. I found out the spirit and promise to post responsibly!
Best regards.
December 19, 2010 at 10:56 pm
hoanglong88i (12/16/2010)
Hello everyone,My trouble is that I have objects stored as date type, not datetime:
Gosh... it seems to me that you've correctly identified the real problem... what keeps you from fixing it? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
December 20, 2010 at 12:51 am
Hm, might I take some advice from you, Jeff 🙂
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply