December 16, 2010 at 2:17 am
Hello everyone,
My trouble is that I have objects stored as date type, not datetime:
Column name | Type
Patient_code int
Patient_name varchar(50)
Invoice_code int
Date_Visit date //date that patient comes to the clinic
Date_Created date //date that patient's profile created in dbo.patient
If Date_Visit != Date_Created, of course the patient's status is: RETURN (to the clinic)
But if Date_Visit = Date_Created, there're 2 cases:
1. The patient just came once a time in that day -> He's a NEW patient.
2. The patient came many times in that day (morning, afternoon)... When he firstly came to the clinic in the morning, he's NEW, but in the afternoon, he's RETURN.
Patient_Code Patient_Name Invoice_Code Date_Created Date_Visit status
------------ ------------ ------------ ------------ ---------- ------
20 David 2 2010-11-15 2010-12-15 Return
21 Anna 3 2010-12-15 2010-12-15 New
21 Anna 4 2010-12-15 2010-12-15 New
21 Anna 5 2010-12-15 2010-12-16 Return
22 Peter 6 2010-12-20 2010-12-20 New
Here is my TSQL:
SELECT Patient_Code,Patient_Name,Invoice_Code,Date_Created,Date_Visit,
CASE WHEN Date_Created=Date_Visit
THEN 'New' ELSE 'Return'
END AS status
FROM tblTemp
You can see that with Invoice_Code=4, Anna's status must be RETURN.
I try hard to put some more conditions if Date_Created=Date_Visit but it not works 🙁
I Appreciate for any help!
December 16, 2010 at 2:32 am
Double post,
All replies to http://www.sqlservercentral.com/Forums/Topic1035686-391-1.aspx please
December 16, 2010 at 3:00 am
This should do it for you
DECLARE@tblTemp TABLE
(
Patient_code int,
Patient_name varchar(50),
Invoice_code int,
Date_Visit date,
Date_Created date
)
INSERT @tblTemp
SELECT20, 'David', 2, '2010-11-15', '2010-12-15' UNION ALL
SELECT21, 'Anna', 3, '2010-12-15', '2010-12-15' UNION ALL
SELECT21, 'Anna', 4, '2010-12-15', '2010-12-15' UNION ALL
SELECT21, 'Anna', 5, '2010-12-15', '2010-12-16' UNION ALL
SELECT22, 'Peter', 6, '2010-12-20', '2010-12-20'
SELECTPatient_Code, Patient_Name, tmp.Invoice_Code, Date_Created, Date_Visit,
CASE WHEN EXISTS(
SELECT*
FROM@tblTemp tmp_inn
WHEREtmp.Patient_Code = tmp_inn.Patient_Code
ANDtmp.Invoice_Code != tmp_inn.Invoice_Code
) THEN 'Return' ELSE 'New'
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 16, 2010 at 6:27 pm
Thank Kingston so much but the problem's still there. Now with your solution then the first time Anna comes to the clinic, she's RETURN while she must be NEW.
Patient_Code Patient_Name Invoice_Code Date_Created Date_Visit status
------------ ------------ ------------ ------------ ---------- ------
20 David 2 2010-12-15 2010-11-15 New
21 Anna 3 2010-12-15 2010-12-15 Return
21 Anna 4 2010-12-15 2010-12-15 Return
21 Anna 5 2010-12-16 2010-12-15 Return
22 Peter 6 2010-12-20 2010-12-20 New
December 16, 2010 at 6:48 pm
Using Kingston's fine sample code (hoanglong88i - please see the first link in my signature for how / why you need to do this), this seems to be producing the proper results:
-- See how you start off by actually creating a table and then
-- inserting the data into it? Your doing this makes it a lot easier
-- for all of us volunteers to help you. So, help us help you.
-- See http://www.sqlservercentral.com/articles/Best+Practices/61537/
-- for more details on how to do all of this.
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';
WITH CTE AS
(
-- assign row number in order of the invoice code.
-- restarting numbering for each patient code
SELECT Patient_code,
Patient_name,
Invoice_code,
Date_Visit,
Date_Created,
RN = row_number() OVER (PARTITION BY Patient_code ORDER BY Invoice_code)
FROM @tblTemp
)
SELECT Patient_code,
Patient_name,
Invoice_code,
Date_Visit,
Date_Created,
-- do the date_visit check first, since it might be the first row returned for this range
-- next check if RN = 1 - if so, then it's new
-- all others are Returns.
[Status] = CASE WHEN Date_Visit <> Date_Created THEN 'Return'
WHEN RN = 1 THEN 'New'
ELSE 'Return' END
FROM CTE
ORDER BY Patient_code, Invoice_code;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 16, 2010 at 6:57 pm
Kingston Dhasian (12/16/2010)
This should do it for you
DECLARE@tblTemp TABLE(
Patient_code int,
Patient_name varchar(50),
Invoice_code int,
Date_Visit date,
Date_Created date
)
INSERT @tblTemp
SELECT20, 'David', 2, '2010-11-15', '2010-12-15' UNION ALL
SELECT21, 'Anna', 3, '2010-12-15', '2010-12-15' UNION ALL
SELECT21, 'Anna', 4, '2010-12-15', '2010-12-15' UNION ALL
SELECT21, 'Anna', 5, '2010-12-15', '2010-12-16' UNION ALL
SELECT22, 'Peter', 6, '2010-12-20', '2010-12-20'
SELECTPatient_Code, Patient_Name, tmp.Invoice_Code, Date_Created, Date_Visit,
CASE WHEN EXISTS(
SELECT*
FROM@tblTemp tmp_inn
WHEREtmp.Patient_Code = tmp_inn.Patient_Code
ANDtmp.Invoice_Code != tmp_inn.Invoice_Code
) THEN 'Return' ELSE 'New'
END status
FROM@tblTemp tmp
Check out the code posted on the other thread.
Thanks to Kingston for the base code.
December 16, 2010 at 6:59 pm
WayneS (12/16/2010)
Using Kingston's fine sample code (hoanglong88i - please see the first link in my signature for how / why you need to do this), this seems to be producing the proper results:
-- See how you start off by actually creating a table and then-- inserting the data into it? Your doing this makes it a lot easier
-- for all of us volunteers to help you. So, help us help you.
-- See http://www.sqlservercentral.com/articles/Best+Practices/61537/
-- for more details on how to do all of this.
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';
WITH CTE AS
(
-- assign row number in order of the invoice code.
-- restarting numbering for each patient code
SELECT Patient_code,
Patient_name,
Invoice_code,
Date_Visit,
Date_Created,
RN = row_number() OVER (PARTITION BY Patient_code ORDER BY Invoice_code)
FROM @tblTemp
)
SELECT Patient_code,
Patient_name,
Invoice_code,
Date_Visit,
Date_Created,
-- do the date_visit check first, since it might be the first row returned for this range
-- next check if RN = 1 - if so, then it's new
-- all others are Returns.
[Status] = CASE WHEN Date_Visit <> Date_Created THEN 'Return'
WHEN RN = 1 THEN 'New'
ELSE 'Return' END
FROM CTE
ORDER BY Patient_code, Invoice_code;
Written slightly different, but we came to the same conclusion!
December 16, 2010 at 7:11 pm
Thank Lynn but your solution still has problem. Now when Anna returned to the clinic on the other day, her status is New while it must be Return.
20 David 2 2010-11-15 2010-12-15 New
21 Anna 3 2010-12-15 2010-12-15 New
21 Anna 4 2010-12-15 2010-12-15 Return
21 Anna 5 2010-12-15 2010-12-16 New
22 Peter 6 2010-12-20 2010-12-20 New
You can understand it's like Virgin. It just happens for one time 😀
PS: I visited your link, Lynn. Thank for instructing ^^
December 16, 2010 at 7:23 pm
hoanglong88i (12/16/2010)
Thank Lynn but your solution still has problem. Now when Anna returned to the clinic on the other day, her status is New while it must be Return.
20 David 2 2010-11-15 2010-12-15 New21 Anna 3 2010-12-15 2010-12-15 New
21 Anna 4 2010-12-15 2010-12-15 Return
21 Anna 5 2010-12-15 2010-12-16 New
22 Peter 6 2010-12-20 2010-12-20 New
You can understand it's like Virgin. It just happens for one time 😀
PS: I visited your link, Lynn. Thank for instructing ^^
You are correct. The fix is simple, so how would you correct my code?
December 16, 2010 at 8:28 pm
Yes, Lynn, it works. But just on my Laptop. On my office's desktop, there's a Error Message:
'row_number' is not a recognized function name.
Both my laptop and office's desk are installed SQL Server 2008 SP1 :ermm:
---
OMG, I find out our CMS databse is SQL 2000.
Is ROW_NUMBER() function not available in SQL 2000 ?
So maybe I must wirte a ROW_NUMBER() function for SQL 2000 ?
December 16, 2010 at 8:46 pm
There is still a problem with my code based on your previous post. How would you correct my code? I have already identified the necessary fix.
December 16, 2010 at 8:58 pm
Really? I tried your code and it works correctly. With just my 5 records, I dont find the error.
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';
WITH CTE AS
(
-- assign row number in order of the invoice code.
-- restarting numbering for each patient code
SELECT Patient_code,
Patient_name,
Invoice_code,
Date_Visit,
Date_Created,
RN = row_number() OVER (PARTITION BY Patient_code ORDER BY Invoice_code)
FROM @tblTemp
)
SELECT Patient_code,
Patient_name,
Invoice_code,
Date_Visit,
Date_Created,
-- do the date_visit check first, since it might be the first row returned for this range
-- next check if RN = 1 - if so, then it's new
-- all others are Returns.
[Status] = CASE WHEN Date_Visit <> Date_Created THEN 'Return'
WHEN RN = 1 THEN 'New'
ELSE 'Return' END
FROM CTE
ORDER BY Patient_code, Invoice_code;
It's still need fixing?
December 16, 2010 at 9:18 pm
hoanglong88i (12/16/2010)
Yes, Lynn, it works. But just on my Laptop. On my office's desktop, there's a Error Message:'row_number' is not a recognized function name.
Both my laptop and office's desk are installed SQL Server 2008 SP1 :ermm:
---
OMG, I find out our CMS databse is SQL 2000.
Is ROW_NUMBER() function not available in SQL 2000 ?
So maybe I must wirte a ROW_NUMBER() function for SQL 2000 ?
Correct, ROW_NUMBER() is NOT available for SQL 2000. Good luck in writing your own, and having it be a good performing piece of code. I know that I wouldn't try doing that.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 16, 2010 at 9:23 pm
hoanglong88i (12/16/2010)
Really? I tried your code and it works correctly. With just my 5 records, I dont find the error.
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';
WITH CTE AS
(
-- assign row number in order of the invoice code.
-- restarting numbering for each patient code
SELECT Patient_code,
Patient_name,
Invoice_code,
Date_Visit,
Date_Created,
RN = row_number() OVER (PARTITION BY Patient_code ORDER BY Invoice_code)
FROM @tblTemp
)
SELECT Patient_code,
Patient_name,
Invoice_code,
Date_Visit,
Date_Created,
-- do the date_visit check first, since it might be the first row returned for this range
-- next check if RN = 1 - if so, then it's new
-- all others are Returns.
[Status] = CASE WHEN Date_Visit <> Date_Created THEN 'Return'
WHEN RN = 1 THEN 'New'
ELSE 'Return' END
FROM CTE
ORDER BY Patient_code, Invoice_code;
It's still need fixing?
That's actually the code that I posted, not what Lynn posted. Which code are you having issues with?
If you're on SQL 2000, you'd be better off figuring out what needs to be fixed with his code. There's a difference between helping you, and doing it for you - and since you're not paying us to do it for you then you need to spend a little bit of time/effort into figuring out what needs to be altered in his code to make it work - his code will run on SQL 2000 (which means that it will definitely be a LOT easier to modify this code than writing you own ROW_NUMBER() function!)
Edit: BTW, do you understand what this code is doing? Can you explain it to someone else? If not, DO NOT USE IT in production until you do! If you need help understanding what it's doing, feel free to ask us.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 16, 2010 at 10:23 pm
Yes, Wayne, thank for your sharing.
I also think that to look another way to solve it rather than writing ROW_FUNCTION() in SQL 2000 (not feasible with my little skill).
I know that the problem is as VISIT<>CREATED, so we need to get the firsrt INVOICE CODE of that patient because the INVOICE CODE is increment, and the first (also smallest) indicates NEW status.
Before I knew your solution (I'm new to ROW_NUMBER() function), I made the second condition like this:
select Patient_Code,Patient_Name,Invoice_Code,Date_Created,Date_Visit,
case when Date_Created=Date_Visit and COUNT(Patient_Code)=1
then 'New' else 'Return'
end as status
from tblDiary
group by Patient_Code,Patient_Name,Invoice_Code,Date_Created,Date_Visit
BTW, I'm an apprentice, and this's just one of my challengeS, making reports not for commerce 🙂
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply