sum all records over 35 weeks Stored procedure or SSRS

  • jbon007

    Mr or Mrs. 500

    Points: 542

    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: 184373

    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

  • jbon007

    Mr or Mrs. 500

    Points: 542

    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: 184373

    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

  • jbon007

    Mr or Mrs. 500

    Points: 542

    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: 184373

    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 6 (of 6 total)

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