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

sum all records over 35 weeks Stored procedure or SSRS Expand / Collapse
Author
Message
Posted Friday, October 16, 2009 7:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 4, 2012 11:52 AM
Points: 26, Visits: 80
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


  Post Attachments 
untitled.bmp (10 views, 968.68 KB)
Post #804173
Posted Monday, October 19, 2009 7:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:02 PM
Points: 10,196, Visits: 13,127
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #805026
Posted Monday, October 19, 2009 8:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 4, 2012 11:52 AM
Points: 26, Visits: 80
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+')',
[IP SoT wait]=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
Post #805063
Posted Monday, October 19, 2009 8:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:02 PM
Points: 10,196, Visits: 13,127
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #805107
Posted Tuesday, October 20, 2009 4:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 4, 2012 11:52 AM
Points: 26, Visits: 80
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)
Post #805547
Posted Tuesday, October 20, 2009 1:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:02 PM
Points: 10,196, Visits: 13,127
In the code you have posted you are missing a comma (,) after COUNT(NHSNo) AS [patientvol].



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #806011
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse