SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Report created in SSRS 2008 only pulling one record when a date range is selected


Report created in SSRS 2008 only pulling one record when a date range is selected

Author
Message
civilclerk1
civilclerk1
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 38
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"
doug.brown
doug.brown
SSChasing Mays
SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)

Group: General Forum Members
Points: 640 Visits: 13957
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.
civilclerk1
civilclerk1
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 38
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"
doug.brown
doug.brown
SSChasing Mays
SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)

Group: General Forum Members
Points: 640 Visits: 13957
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)


civilclerk1
civilclerk1
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 38
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"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search