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