Post reply

sql count

  • May 3, 2011 at 3:11 am

    #235627

    ScheduleDate,ModaltyID are the fields of my dataset

    based on the month in scheduleDate, i want to count modalityID is it possible

    i want to write expression in rdlc for this.. give any suggestions

  • May 3, 2011 at 5:38 am

    #1318977

    Are you looking like this

    declare @month table(ScheduleDate date,ModaltyID int)

    insert into @month

    select '01/01/2011',1 union all

    select '01/15/2011',1 union all

    select '01/28/2011',1 union all

    select '02/10/2011',6 union all

    select '03/15/2011',7 union all

    select '03/28/2011',5 union all

    select '04/18/2011',1 union all

    select '04/14/2011',5 union all

    select '04/26/2011',2

    --Select * from @month

    select COUNT(ModaltyID) as ModaltyID,MONTH(ScheduleDate) as Months from @month

    group by MONTH(ScheduleDate)

    Thanks
    Parthi

  • May 3, 2011 at 5:53 am

    #1318986

    am not able to changa stored procedure ya i want to write code in rdlc expression only

  • May 3, 2011 at 5:56 am

    #1318987

    usharani.t (5/3/2011)

    am not able to changa stored procedure ya i want to write code in rdlc expression only

    Can you post Sp?

    Thanks
    Parthi

  • May 3, 2011 at 6:13 am

    #1318999

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE PROCEDURE [pr_PatientDetails]

    AS

    BEGin

    SELECT PatientDetails.PatientID, PatientDetails.MRN, PatientDetails.Code, PatientDetails.Chart#, PatientDetails.FullName, PatientDetails.FirstName, PatientDetails.Mi,

    PatientDetails.LastName, PatientDetails.Suffix, PatientDetails.DOB, PatientDetails.Age, PatientDetails.SSN, PatientDetails.[Marital-Status],

    PatientDetails.[Driver-Licence], PatientDetails.[Driver-Licence-State], PatientDetails.RefProvider, PatientDetails.Provider, PatientDetails.Employer,

    PatientDetails.[Employer-Status], PatientDetails.School, PatientDetails.[ConInfo1-Address-1], PatientDetails.[ConInfo1-Address-2], PatientDetails.[ConInfo1-City],

    PatientDetails.[ConInfo1-State], PatientDetails.[ConInfo1-ZipFileID], PatientDetails.[ConInfo1-ZipCode], PatientDetails.[ConInfo1-HomePhone],

    PatientDetails.[ConInfo1-Fax], PatientDetails.[ConInfo1-Mobile], PatientDetails.[ConInfo1-WorkPh], PatientDetails.[ConInfo1-Email],

    PatientDetails.[ConInfo1-HomePage], PatientDetails.[ConInfo2-Address-1], PatientDetails.[ConInfo2-Address-2], PatientDetails.[ConInfo2-City],

    PatientDetails.[ConInfo2-State], PatientDetails.[ConInfo2-ZipFileID], PatientDetails.[ConInfo2-ZipCode], PatientDetails.[ConInfo2-HomePhone],

    PatientDetails.[ConInfo2-Fax], PatientDetails.[ConInfo2-Mobile], PatientDetails.[ConInfo2-WorkPh], PatientDetails.[ConInfo2-EMail],

    PatientDetails.[ConInfo2-HomePage], PatientDetails.[Student-Status], PatientDetails.Gender, PatientDetails.IsActive, PatientDetails.IsGuarantor,

    PatientDetails.UnApplied, PatientDetails.PatientBalance

    FROM PatientDetails

    END

    GO

  • May 3, 2011 at 6:30 am

    #1319004

    usharani.t (5/3/2011)

    ScheduleDate,ModaltyID are the fields of my dataset

    based on the month in scheduleDate, i want to count modalityID is it possible

    i want to write expression in rdlc for this.. give any suggestions

    Hi, you can do something like this, if you can relate the month to the current month:

    1. Add a count column in the report.

    2. Add an expression like, if Month(scheduleDate) == currentmonth (or currentmonth - 1 etc), ModaltyID, else 0

    3. Add a Total at the footer for the count column.

    Let me know if that helps.

    - arjun

    https://sqlroadie.com/

  • May 3, 2011 at 10:38 pm

    #1319523

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE PROCEDURE [pr_PatientDetails]

    AS

    BEGin

    SELECT PatientDetails.PatientID, PatientDetails.MRN, PatientDetails.Code, PatientDetails.Chart#, PatientDetails.FullName, PatientDetails.FirstName, PatientDetails.Mi,

    PatientDetails.LastName, PatientDetails.Suffix, PatientDetails.DOB, PatientDetails.Age, PatientDetails.SSN, PatientDetails.[Marital-Status],

    PatientDetails.[Driver-Licence], PatientDetails.[Driver-Licence-State], PatientDetails.RefProvider, PatientDetails.Provider, PatientDetails.Employer,

    PatientDetails.[Employer-Status], PatientDetails.School, PatientDetails.[ConInfo1-Address-1], PatientDetails.[ConInfo1-Address-2], PatientDetails.[ConInfo1-City],

    PatientDetails.[ConInfo1-State], PatientDetails.[ConInfo1-ZipFileID], PatientDetails.[ConInfo1-ZipCode], PatientDetails.[ConInfo1-HomePhone],

    PatientDetails.[ConInfo1-Fax], PatientDetails.[ConInfo1-Mobile], PatientDetails.[ConInfo1-WorkPh], PatientDetails.[ConInfo1-Email],

    PatientDetails.[ConInfo1-HomePage], PatientDetails.[ConInfo2-Address-1], PatientDetails.[ConInfo2-Address-2], PatientDetails.[ConInfo2-City],

    PatientDetails.[ConInfo2-State], PatientDetails.[ConInfo2-ZipFileID], PatientDetails.[ConInfo2-ZipCode], PatientDetails.[ConInfo2-HomePhone],

    PatientDetails.[ConInfo2-Fax], PatientDetails.[ConInfo2-Mobile], PatientDetails.[ConInfo2-WorkPh], PatientDetails.[ConInfo2-EMail],

    PatientDetails.[ConInfo2-HomePage], PatientDetails.[Student-Status], PatientDetails.Gender, PatientDetails.IsActive, PatientDetails.IsGuarantor,

    PatientDetails.UnApplied, PatientDetails.PatientBalance

    FROM PatientDetails

    END

    GO

    this is the stored Procedure i want to take sum of patientBalance based on month i want to write expression for that

  • May 3, 2011 at 11:28 pm

    #1319531

    usharani.t (5/3/2011)

    i want to write expression in rdlc for this.. give any suggestions

    Huh? Please check what you said. Do you want to do this (finding count of modalityID based on month) in SP or in rdlc? If you want to do it in rdlc, refer to my previous post.

    If you want to do it in SP, refer to the code below.

    declare @tblMonthBasedCount table(ModalityID int, ScheduleDate smalldatetime)

    insert into @tblMonthBasedCount

    values

    (10, '1 Mar 2010'),

    (20, '15 Mar 2010'),

    (30, '1 May 2010'),

    (40, '15 May 2010'),

    (50, '1 Jul 2010'),

    (60, '15 Jul 2010')

    select

    (case when month(ScheduleDate) = '3' then ModalityID else 0 end) [Count], ScheduleDate

    from @tblMonthBasedCount

    https://sqlroadie.com/

  • May 3, 2011 at 11:31 pm

    #1319534

    This works but some Problems in count.. the count values for alll months is displayed in any one month....

    Am not able to modify SP...

  • May 3, 2011 at 11:32 pm

    #1319535

    This works but some Problems in count.. the count values for alll months is displayed in any one month....

    Am not able to modify SP...

  • May 4, 2011 at 1:42 am

    #1319573

    usharani.t (5/3/2011)

    Am not able to modify SP...

    So obviously, you have to do it at the report side.

    the count values for alll months is displayed in any one month....

    Even after many posts, you have not clearly stated what you want to do. And I do not understand what you mean by - count values for all months are displayed in any one month.

    Give sample data and desired output. I'm afraid I may not be able to help you otherwise.

    - arjun

    https://sqlroadie.com/

  • May 4, 2011 at 3:22 am

    #1319606

    Month 2007 2008 2009

    Jan 0 1 0

    Feb 0 0 0

    Mar 0 4 1

    Apr 1 1 0

    May 0 0 0

    June 1 0 0

    July 1 0 0

    Aug 1 0 0

    This is the Required Format

    But now am getting the result as

    Month 2007 2008 2009

    Jan 0 6 0

    Feb 0 0 0

    Mar 0 0 1

    Apr 4 0 0

    May 0 0 0

    June 0 0 0

    July 0 0 0

    Aug 0 0 0

  • May 4, 2011 at 5:39 am

    #1319655

    Hi Usha, this information is not sufficient.

    As of now, you have mentioned -

    1. The output you need.

    2. The output you are getting.

    3. The SP which acts as the source for the report.

    The SP is of no use as you cannot modify it. Please post sample data (i repeat, sample data), ie, the dataset/datatable given by the SP. We will be able to help you once we have that.

    Please provide all sufficient information at the beginning in your future posts. This will help you in getting a solution sooner.

    - arjun

    https://sqlroadie.com/

  • May 4, 2011 at 5:53 am

    #1319667

    Dataset

    dsRefDetails_SelByRef

    RefPhyName

    RefPhyAddress1

    RefPhyAddress2

    RefPhyOfficePh

    RefPhyOfficeFax

    ModalityId

    ModalityName

    ScheduleDate

    THis is my dataSet

  • May 4, 2011 at 5:55 am

    #1319669

    Dataset

    dsRefDetails_SelByRef

    RefPhyName

    RefPhyAddress1

    RefPhyAddress2

    RefPhyOfficePh

    RefPhyOfficeFax

    ModalityId

    ModalityName

    ScheduleDate

    THis is my dataSet

Viewing 15 posts - 1 through 15 (of 16 total)

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