February 26, 2014 at 11:17 am
I have a report that I have created that is supposed to pull cases from a date range based on the date_received filed however even though the filter has been created to point to this field in the dataset, it only pulls one record regardless of the dates searched. I am posting the code here to see if anyone sees a problem that I can't. The report is supposed to pull ALL cases in that date range regardless of whether the values in the last name, first_name, role_sk, create_date, create_user_id, or comments fields exist. It is supposed to pull the info specific to the values stated for those fields but if they are not present then those fields should be blank YET it should still pull the case style and date_received.
SELECT date_received,
(SELECT TOP (1) create_user_id
FROM comments AS t4
WHERE (case_sk = t1.case_sk) AND (comment_type_sk = 4322)) AS create_user_id,
(SELECT TOP (1) create_date
FROM comments AS t4
WHERE (case_sk = t1.case_sk) AND (comment_type_sk = 4322)) AS create_date,
(SELECT TOP (1) CAST(comments AS varchar(MAX)) AS Expr1
FROM comments AS t4
WHERE (case_sk = t1.case_sk) AND (comment_type_sk = 4322)) AS comments, CASE WHEN ISNULL
((SELECT TOP 1 last_name
FROM case_parties t2 INNER JOIN
legal_entity t3 ON t2.entity_sk = t3.legal_entity_sk AND t2.role_sk = 3936
WHERE t2.case_sk = t1.case_sk), '') <> '' THEN
(SELECT TOP 1 last_name
FROM case_parties t2 INNER JOIN
legal_entity t3 ON t2.entity_sk = t3.legal_entity_sk AND t2.role_sk = 3936
WHERE t2.case_sk = t1.case_sk) WHEN ISNULL
((SELECT TOP 1 last_name
FROM case_parties t2 INNER JOIN
legal_entity t3 ON t2.entity_sk = t3.legal_entity_sk AND t2.role_sk = 4310
WHERE t2.case_sk = t1.case_sk), '') <> '' THEN
(SELECT TOP 1 last_name
FROM case_parties t2 INNER JOIN
legal_entity t3 ON t2.entity_sk = t3.legal_entity_sk AND t2.role_sk = 4310
WHERE t2.case_sk = t1.case_sk) WHEN ISNULL
((SELECT TOP 1 last_name
FROM case_parties t2 INNER JOIN
legal_entity t3 ON t2.entity_sk = t3.legal_entity_sk AND t2.role_sk = 4216
WHERE t2.case_sk = t1.case_sk), '') <> '' THEN
(SELECT TOP 1 last_name
FROM case_parties t2 INNER JOIN
legal_entity t3 ON t2.entity_sk = t3.legal_entity_sk AND t2.role_sk = 4216
WHERE t2.case_sk = t1.case_sk) ELSE '' END AS last_name, CASE WHEN ISNULL
((SELECT TOP 1 first_name
FROM case_parties t2 INNER JOIN
legal_entity t3 ON t2.entity_sk = t3.legal_entity_sk AND t2.role_sk = 3936
WHERE t2.case_sk = t1.case_sk), '') <> '' THEN
(SELECT TOP 1 first_name
FROM case_parties t2 INNER JOIN
legal_entity t3 ON t2.entity_sk = t3.legal_entity_sk AND t2.role_sk = 3936
WHERE t2.case_sk = t1.case_sk) WHEN ISNULL
((SELECT TOP 1 first_name
FROM case_parties t2 INNER JOIN
legal_entity t3 ON t2.entity_sk = t3.legal_entity_sk AND t2.role_sk = 4310
WHERE t2.case_sk = t1.case_sk), '') <> '' THEN
(SELECT TOP 1 first_name
FROM case_parties t2 INNER JOIN
legal_entity t3 ON t2.entity_sk = t3.legal_entity_sk AND t2.role_sk = 4310
WHERE t2.case_sk = t1.case_sk) WHEN ISNULL
((SELECT TOP 1 first_name
FROM case_parties t2 INNER JOIN
legal_entity t3 ON t2.entity_sk = t3.legal_entity_sk AND t2.role_sk = 4216
WHERE t2.case_sk = t1.case_sk), '') <> '' THEN
(SELECT TOP 1 first_name
FROM case_parties t2 INNER JOIN
legal_entity t3 ON t2.entity_sk = t3.legal_entity_sk AND t2.role_sk = 4216
WHERE t2.case_sk = t1.case_sk) ELSE '' END AS first_name, CASE WHEN ISNULL
((SELECT TOP 1 last_name
FROM case_parties t2 INNER JOIN
legal_entity t3 ON t2.entity_sk = t3.legal_entity_sk AND t2.role_sk = 3936
WHERE t2.case_sk = t1.case_sk), '') <> '' THEN 'Lead Attorney' WHEN ISNULL
((SELECT TOP 1 last_name
FROM case_parties t2 INNER JOIN
legal_entity t3 ON t2.entity_sk = t3.legal_entity_sk AND t2.role_sk = 4310
WHERE t2.case_sk = t1.case_sk), '') <> '' THEN 'Bankruptcy Mgr' WHEN ISNULL
((SELECT TOP 1 last_name
FROM case_parties t2 INNER JOIN
legal_entity t3 ON t2.entity_sk = t3.legal_entity_sk AND t2.role_sk = 4216
WHERE t2.case_sk = t1.case_sk), '') <> '' THEN 'Partner in Charge' ELSE '' END AS staff_role, style
FROM cases AS t1
WHERE (date_received BETWEEN @StartDate AND @EndDate)
"Cakes"
February 26, 2014 at 12:52 pm
Do you think it could have something to do with the 18 "SELECT TOP (1)" statements? 🙂 The function of a "SELECT TOP (1)" is to return only one record, so it doesn't surprise me that's the result you're getting.
February 26, 2014 at 2:21 pm
Ugh! That makes sense! I am teaching myself SQL and I had already written the code when a guy from our software developer told me I should do it that way instead of how I was doing it. My way would have been the right way then! So much time lost. Thank you. I will make some changes and let you know if that corrects it. Couldn't I just take out the TOP 1 verbiage?
UPDATE: I took out the TOP1 on all select statements and I am still only getting one record. Any ideas?
"Cakes"
February 26, 2014 at 3:03 pm
You can start with taking out the TOP 1's and see where that gets you. You might still have some restructuring to do, though. I would try to move most of the (SELECT FROM WHERE) clauses in the select statement to the FROM, so your select can be greatly simplified. This is a rough start of what I'm suggesting:
SELECT
t4.date_received
,t4.create_user_id
,t4.create_date
,CAST(t4.comments AS varchar(MAX)) AS comments
, CASE WHEN t2.role_sk = 3936 AND ISNULL(last_name, '') <> ''
THEN last_name
WHEN t2.role_sk = 4310 AND ISNULL(last_name, '') <> ''
THEN last_name
WHEN t2.role_sk = 4216 AND ISNULL(last_name, '') <> ''
THEN last_name
ELSE '' END AS last_name
, etc...
FROM
cases AS t1
INNER JOIN case_parties t2 on t1.case_sk = t2.case_sk
INNER JOIN legal_entity t3 ON t2.entity_sk = t3.legal_entity_sk
INNER JOIN comments AS t4 ON t1.case_sk = t4.case_sk
WHERE
(date_received BETWEEN @StartDate AND @EndDate)
February 27, 2014 at 10:35 am
Doug,
Removing the Top1's did not change a thing. I personally despise the way he wrote this code. It's nothing but confusing as heck! I will try to reconstruct it in a simpler manner but the problem unfortunately still remains. Aesthetics will be taken care of but that's it. 🙁
Dawn
"Cakes"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply