October 16, 2009 at 7:48 am
Dear All,
I have stored procedure which shows records by week numbers and amongst everything else. When i place it in ssrs i display this in a chart by week number and patient volumes.
However i i would like to display the chart upto 35 weeks and anything above sum all the records? is that doable in ssrs or do i need to state that in my stored procedure?
regards
Farouq
October 19, 2009 at 7:38 am
Without seeing the data returned I don't know that I can give definitive answer, but I would venture to say that you can probably do it in either place. If you are not going to be doing any drill-down on the data over 35 weeks I'd recommend doing it in the procedure. It might look something like this:
SELECT
COUNT(events) AS number_of_events,
CASE
WHEN number_of_weeks > 35 THEN 'OVER 35'
ELSE number_of_weeks
END AS number_of_weeks
FROM
event_table
GROUP BY
CASE
WHEN number_of_weeks > 35 THEN 'OVER 35'
ELSE number_of_weeks
END
You might have to do some tweaking to make sure you have the ordering you want.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 19, 2009 at 8:14 am
Hi thankyou for replying how would i be able to apply this to the example if you given me?
USE [InformationServices]
GO
/****** Object: StoredProcedure [dbo].[p18WeekPTLAdmitted] Script Date: 10/19/2009 14:53:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[p18WeekPTLAdmitted]
@status char(20),
@spec NVARCHAR(Max) as set dateformat dmy
if @status = 'ALL'
begin
select
[Hospital number]=HospNo,[NHS number]=NHSNo,[Name]=[Name],[Account number]=SpellNo,
[Referral date]=ReferDt,[Specialty code]=SpecCd,[Specialty]=Spec,[Current status]=[Event],
[Last contact date]=EventDt,[Last treatment status code]=LastTreatmentStatusCd,
[Last treatment status]=Treatment,[Consulant code]=ConsCd,[Consultant]=Cons,
[Date to be given TCI date by]=dateadd(Week,-41,BreachDt),[TCI date]=TCIDt,
[18 week breach date]=BreachDt,[Expected op]=Description+' ('+ExpOpCd+')',
127.0.0.1=IPSOTLoW,[18 week Length of wait]=(datediff(Week,ReferDt,getdate())-PLoW),
[Comments]=case when TCIDt is null then 'No TCI date' else null end,
[Wait list link error]=case when LoWdiag='888' then 'Wait list record without link' else null end,
TreatWeekNo,
[Treat in the week ending]=case when [Event]<>'WaitListSOT' then TreatWeek else null end,
[Booked assumed treatment date]=TCIDt,
[Action needed]=case when BreachDt<TCIDt and Feb='x' then 'breached booking - move' else null end,
[Site]=Site,[Wait list comment]=WLComment,[Wait list priority]=WLPriority,[26 week breach date]=[26WBreachDt],
[OPCS code]=ExpOpCd,[Age]=Age,[PCT code]=PCTCdhttp://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gif
from [18weekptl]
--where Spec in(SELECT Spec FROM dbo.Split3(@spec,','))
WHERE Spec in (select * from dbo.fnSplit3(@spec, ','))
and [Event] in ('WaitTimeOngoingWL','WaitListSOT')
and ClockPause<>'Yes'
order by [18 week Length of wait]
--case when TreatWeekNo=0 then 100 else TreatWeekNo end
end
regards
F
October 19, 2009 at 8:54 am
I'm going to recommend that you read the first article linked in my signature as I don't have any idea how the code you have posted ties to the question you have asked. For example, where is the week number?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 20, 2009 at 4:06 am
I've tried to simply it...but get an error saying Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'CASE'.
SELECT COUNT(NHSNo) AS [patientvol]
CASE WHEN DATEDIFF(Week, ReferDt, GETDATE()) - PLoW > 35 THEN '35+' else '' End
FROM [18WeekPTL]
WHERE (Spec = 'ophthalmology') AND (Event IN ('WaitTimeOngoingWL', 'WaitListSOT')) AND (ClockPause <> 'Yes') AND (DATEDIFF(Week, ReferDt, GETDATE()) - PLoW > 35)
October 20, 2009 at 1:00 pm
In the code you have posted you are missing a comma (,) after COUNT(NHSNo) AS [patientvol].
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy