sum all records over 35 weeks Stored procedure or SSRS

  • jbon007

    SSC-Addicted

    Points: 492

    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

  • Jack Corbett

    SSC Guru

    Points: 184296

    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 Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • jbon007

    SSC-Addicted

    Points: 492

    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+')',

    172.16.23.120=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

  • Jack Corbett

    SSC Guru

    Points: 184296

    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 Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

  • jbon007

    SSC-Addicted

    Points: 492

    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)

  • Jack Corbett

    SSC Guru

    Points: 184296

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


    Jack Corbett Consultant Straight Path Solutions Dont let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance Problems[/url]Crosstabs and Pivots or How to turn rows into columns Part 1[/url][url url=http://www.sqlservercent

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply