Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Report created in SSRS 2008 only pulling one record when a date range is selected Expand / Collapse
Author
Message
Posted Wednesday, February 26, 2014 11:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 13, 2014 2:32 PM
Points: 8, 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"
Post #1545515
Posted Wednesday, February 26, 2014 12:52 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:07 PM
Points: 190, Visits: 9,335
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.
Post #1545553
Posted Wednesday, February 26, 2014 2:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 13, 2014 2:32 PM
Points: 8, 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"
Post #1545587
Posted Wednesday, February 26, 2014 3:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:07 PM
Points: 190, Visits: 9,335
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)

Post #1545609
Posted Thursday, February 27, 2014 10:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 13, 2014 2:32 PM
Points: 8, 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"
Post #1546005
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse