May 6, 2011 at 7:53 am
Need some assistance with a subquery in an ISNULL....
What I am trying to accomplish is the following:
I am trying to list the cases with the status 'filed' but during the data migration from a previous case management system many of the dates where not pulled over, so I need to use the closed date but if there is not a status date or closed date I need to use the recieve date.
Using the following should accomplish this
SELECT ISNULL(ISNULL (status_date, closed_date), received_date).
To retrieve the status date for filed I use the following:
SELECT status_date
FROM #test a
WHERE a.case_status = 'filed' AND
(CASE WHEN EXISTS
(
SELECT 1
FROM #test b
WHERE(case_status = 'filed') and (a.case_sk = b.case_sk)
)
THEN 'yes'
ELSE 'no'
END = 'YES')
which works fine but when I try to wrap it with the first ISNULL I get an error Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." which is were I am stumped....
Here is the query and test data:
--=============================================================
-- temp table for test data
--========================================================
IF OBJECT_ID('TempDB..#test','u') IS NOT NULL
DROP TABLE #test
CREATE TABLE #test
(
case_sk int not null,
case_number varchar (10) null,
closed_date datetime null,
case_status varchar (15),
current_status varchar (1) null,
status_date datetime null,
receieved_date datetime null,
amount decimal(14,2) null
)
INSERT INTO #test (case_sk, case_number, closed_date, case_status, current_status, status_date, receieved_date, amount)
SELECT 1, '111111', '2010-03-16', 'treating', 'n', '2009-01-16', '2009-01-01', 5000.00 UNION ALL
SELECT 1, '111111', '2010-03-16', 'released', 'n', '2009-03-16', '2009-01-01', 5000.00 UNION ALL
SELECT 1, '111111', NULL, 'filed', 'y', NULL, '2009-01-01', 5000.00 UNION ALL
SELECT 2, '222222', '2011-03-16', 'treating', 'n', '2010-03-16', '2009-01-01', 25000.00 UNION ALL
SELECT 3, '222222', '2010-03-16', 'released', 'n', '2010-03-16', '2009-01-01', 25000.00 UNION ALL
SELECT 3, '222222', '2010-03-16', 'filed', 'y', NULL, '2009-01-01', 25000.00 UNION ALL
SELECT 4, '333333', '2010-06-16', 'treating', 'n', '2010-06-16', '2009-01-01', 10000.00 UNION ALL
SELECT 5, '333333', '2010-04-16', 'settled', 'y', '2010-04-16', '2009-01-01', 10000.00 UNION ALL
SELECT 5, '444444', '2010-04-16', 'filed', 'y', '2010-04-16', '2009-01-01', 10000.00 UNION ALL
SELECT 6, '555555', '2010-05-16', 'released', 'n', '2010-05-16', '2009-01-01', 15000.00 UNION ALL
SELECT 6, '555555', '2010-05-16', 'filed', 'y', '2010-05-16', '2009-01-01', 15000.00 UNION ALL
SELECT 7, '666666', '2010-02-16', 'released', 'y', '2010-02-16', '2009-01-01', 3000.00 UNION ALL
SELECT 8, '777777', '2010-01-25', 'released', 'y', '2010-01-25', '2009-01-01', 3000.00
--=============================================================
-- Query
--========================================================
SELECT ISNULL(
(SELECT status_date
FROM #test a
WHERE a.case_status = 'filed' AND
(CASE WHEN EXISTS
(
SELECT 1
FROM #test b
WHERE(case_status = 'filed') and (a.case_sk = b.case_sk)
)
THEN 'yes'
ELSE 'no'
END = 'YES')), #test.closed_date)
FROM #test
--DROP TABLE #test
Any help would be appreciated!
May 6, 2011 at 8:26 am
Paul Morris-1011726 (5/6/2011)
Need some assistance with a subquery in an ISNULL....What I am trying to accomplish is the following:
I am trying to list the cases with the status 'filed' but during the data migration from a previous case management system many of the dates where not pulled over, so I need to use the closed date but if there is not a status date or closed date I need to use the recieve date.
Using the following should accomplish this
SELECT ISNULL(ISNULL (status_date, closed_date), received_date).
To retrieve the status date for filed I use the following:
SELECT status_date
FROM #test a
WHERE a.case_status = 'filed' AND
(CASE WHEN EXISTS
(
SELECT 1
FROM #test b
WHERE(case_status = 'filed') and (a.case_sk = b.case_sk)
)
THEN 'yes'
ELSE 'no'
END = 'YES')
which works fine but when I try to wrap it with the first ISNULL I get an error Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." which is were I am stumped....
Here is the query and test data:
--=============================================================
-- temp table for test data
--========================================================
IF OBJECT_ID('TempDB..#test','u') IS NOT NULL
DROP TABLE #test
CREATE TABLE #test
(
case_sk int not null,
case_number varchar (10) null,
closed_date datetime null,
case_status varchar (15),
current_status varchar (1) null,
status_date datetime null,
receieved_date datetime null,
amount decimal(14,2) null
)
INSERT INTO #test (case_sk, case_number, closed_date, case_status, current_status, status_date, receieved_date, amount)
SELECT 1, '111111', '2010-03-16', 'treating', 'n', '2009-01-16', '2009-01-01', 5000.00 UNION ALL
SELECT 1, '111111', '2010-03-16', 'released', 'n', '2009-03-16', '2009-01-01', 5000.00 UNION ALL
SELECT 1, '111111', NULL, 'filed', 'y', NULL, '2009-01-01', 5000.00 UNION ALL
SELECT 2, '222222', '2011-03-16', 'treating', 'n', '2010-03-16', '2009-01-01', 25000.00 UNION ALL
SELECT 3, '222222', '2010-03-16', 'released', 'n', '2010-03-16', '2009-01-01', 25000.00 UNION ALL
SELECT 3, '222222', '2010-03-16', 'filed', 'y', NULL, '2009-01-01', 25000.00 UNION ALL
SELECT 4, '333333', '2010-06-16', 'treating', 'n', '2010-06-16', '2009-01-01', 10000.00 UNION ALL
SELECT 5, '333333', '2010-04-16', 'settled', 'y', '2010-04-16', '2009-01-01', 10000.00 UNION ALL
SELECT 5, '444444', '2010-04-16', 'filed', 'y', '2010-04-16', '2009-01-01', 10000.00 UNION ALL
SELECT 6, '555555', '2010-05-16', 'released', 'n', '2010-05-16', '2009-01-01', 15000.00 UNION ALL
SELECT 6, '555555', '2010-05-16', 'filed', 'y', '2010-05-16', '2009-01-01', 15000.00 UNION ALL
SELECT 7, '666666', '2010-02-16', 'released', 'y', '2010-02-16', '2009-01-01', 3000.00 UNION ALL
SELECT 8, '777777', '2010-01-25', 'released', 'y', '2010-01-25', '2009-01-01', 3000.00
--=============================================================
-- Query
--========================================================
SELECT ISNULL(
(SELECT status_date
FROM #test a
WHERE a.case_status = 'filed' AND
(CASE WHEN EXISTS
(
SELECT 1
FROM #test b
WHERE(case_status = 'filed') and (a.case_sk = b.case_sk)
)
THEN 'yes'
ELSE 'no'
END = 'YES')), #test.closed_date)
FROM #test
--DROP TABLE #test
Any help would be appreciated!
I would suggest that you use COALESCE which returns the first non-NULL value in a series of evaluations
SELECT COALESCE(status_date, closed_date, received_date)
If status_date is null, it will check closed_date. If that is null it will check received_date.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 6, 2011 at 8:28 am
Also, here's the BOL entry for COALESCE
http://msdn.microsoft.com/en-us/library/ms190349.aspx
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 6, 2011 at 8:45 am
Thanks for the quick response...
The problems is I need to only list the cases that have had a status of 'filed'. The query above looks at the history of the case to see if it has ever had a filed status. So I need to use the query above to list only those cases, then if the value is NULL for the status date, check for the closed date and if that is the NULL then use the recieved date.
May 6, 2011 at 9:04 am
Paul Morris-1011726 (5/6/2011)
Thanks for the quick response...The problems is I need to only list the cases that have had a status of 'filed'. The query above looks at the history of the case to see if it has ever had a filed status. So I need to use the query above to list only those cases, then if the value is NULL for the status date, check for the closed date and if that is the NULL then use the recieved date.
Do you want the dates just from the "filed" line or do you want dates from other statuses for the same case number? If you want the dates from other statuses with the same case number, how do you know which date you want if there are different dates for the same column on different statuses?
If you just want the date from the "filed" line, that's easy
SELECT COALESCE(status_date, closed_date, received_date) as TheDate, case_number
FROM #test
WHERE current_status = 'filed'
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 6, 2011 at 9:50 am
OK WOW... I was way over thinking that....
Thanks, that did the trick....
May 6, 2011 at 10:26 am
Paul Morris-1011726 (5/6/2011)
OK WOW... I was way over thinking that....Thanks, that did the trick....
Happens from time to time, sometimes you just need another set of eyes. 🙂
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply