sum all records over 35 weeks Stored procedure or SSRS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Testing Center » SQL Server Security Skills » sum all records over 35 weeks Stored...

sum all records over 35 weeks Stored procedure or SSRS

Rate Topic

Display Mode

Topic Options

Author

Message

jbon007

Posted Friday, October 16, 2009 7:48 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 04, 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

Jack Corbett

Posted Monday, October 19, 2009 7:38 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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

jbon007

Posted Monday, October 19, 2009 8:14 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 04, 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

Jack Corbett

Posted Monday, October 19, 2009 8:54 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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?

Post #805107

jbon007

Posted Tuesday, October 20, 2009 4:06 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 04, 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

Jack Corbett

Posted Tuesday, October 20, 2009 1:00 PM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

In the code you have posted you are missing a comma (,) after COUNT(NHSNo) AS [patientvol].

Post #806011

« Prev Topic | Next Topic »

Permissions