How to add totals by group

  • Hi,

    I have a query below that I think is close to what I need, but I need to add a column(s) that has the

    Number of APPROVED claims

    Number of ENTERED claims

    Number of PENDING claims

    As of now, I am not sure how to get the totals, grouped by the batch.

    DECLARE@CLAIMS int

    DECLARE @from_date_entereddate

    DECLARE @to_date_entereddate

    ----------------------------

    SET @from_date_entered = GETDATE()

    SET @to_date_entered= GETDATE()

    --SET @from_date_entered = N'03/08/2016'

    --SET @to_date_entered= N'03/14/2016'

    ----------------------------

    SELECT@CLAIMS = batchtype_id

    FROMbatchtype

    WHEREbatchtype_ud = 'CLAIMS'

    DECLARE @begin smalldatetime

    SELECT @begin = CONVERT(smalldatetime, @from_date_entered)

    DECLARE @end smalldatetime

    SELECT @end = DATEADD (dd,1,CONVERT(smalldatetime, @to_date_entered))

    DECLARE @begind smalldatetime

    SELECT @begind = CONVERT(smalldatetime, @from_date_entered)

    DECLARE @endd smalldatetime

    SELECT @endd = CONVERT(smalldatetime, @to_date_entered)

    DECLARE @CompanyName varchar(100)

    SELECT @CompanyName = environment_info.company_name

    FROMenvironment_info

    IF OBJECT_ID('tempdb..#rpt_data') IS NOT NULL

    begin

    drop table #rpt_data

    end

    CREATE TABLE #rpt_data (CompanyNamevarchar(100) NULL,

    Batchchar(3)NULL,

    UserNamevarchar(35)NULL,

    SQLUserNamevarchar (25)NULL,

    claim_form_type_idintnull, --altered 11/25/02

    Claim_type varchar (10) NULL,

    status_typevarchar(15)NULL,

    eachintNULL,

    member_lastvarchar(35)NULL,

    fromdatesmalldatetimeNULL,

    todatesmalldatetimeNULL)

    INSERT INTO #rpt_data (CompanyName,

    Batch,

    UserName, /*This field is named batch_sql_user_name in BALI */

    SQLUserName,

    claim_form_type_id,--altered 11/25/02

    Claim_type,

    status_type,

    each,

    member_last,

    fromdate,

    todate )

    /***************************************************

    GetBatch where length is 3

    select * from claim_status

    ***/

    SELECT DISTINCT@CompanyName,

    substring(batch_user.batch, 1, 3) as batch_init,

    batch_user.batch as user_name,

    batch_user.batch_sql_user_name,

    claim_form_type_id,--altered 11/25/02

    null,--altered 11/25/02

    claim_status.claim_status_ud,

    claim.claim_id,

    claim.member_last_name as member_last,

    @begind,

    @endd

    FROM batch_user

    inner join batchtype

    on batch_user.batchtype_id = batchtype.batchtype_id,

    claim

    inner join claim_status

    on claim.claim_status_id = claim_status.claim_status_id

    WHERElen(batch_user.batch) = 3

    and substring(claim.claim_ud, 9, 3) = batch_user.batch

    and (claim.date_created < @end AND claim.date_created >= @begin)

    --and batchtype.batchtype_id = @CLAIMS--altered 11/25/02

    GROUP BY claim.claim_form_type_id, batch_user.batch_sql_user_name, batch_user.batch, claim_status.claim_status_ud, claim.member_last_name, claim.claim_id

    /***************************************************

    GetBatch where length is 2

    ***/

    INSERT INTO #rpt_data (CompanyName,

    Batch,

    UserName, /*This field is named batch_sql_user_name in BALI */

    SQLUserName,

    claim_form_type_id,

    Claim_type,

    status_type,

    each,

    member_last,

    fromdate,

    todate )

    SELECT DISTINCT @CompanyName,

    substring(batch_user.batch, 1, 2) as batch_init,

    batch_user.batch as user_name,

    batch_user.batch_sql_user_name,

    claim_form_type_id,

    null,--altered

    claim_status.claim_status_ud,--altered

    claim.claim_id,

    claim.member_last_name as member_last,

    @begind,

    @endd

    FROM batch_user

    inner join batchtype

    on batch_user.batchtype_id = batchtype.batchtype_id,

    claim

    inner join claim_status

    on claim.claim_status_id = claim_status.claim_status_id

    WHERElen(batch_user.batch) = 2

    and substring(claim.claim_ud, 9, 2) = batch_user.batch

    and (claim.date_created < @end AND claim.date_created >= @begin)

    --and batchtype.batchtype_id = @CLAIMS--altered 11/25/02

    GROUP BY claim.claim_form_type_id, batch_user.batch_sql_user_name, batch_user.batch, claim_status.claim_status_ud, claim.member_last_name,claim.claim_id

    /***************************************************

    GetBatch where length is 1

    ***/

    INSERT INTO #rpt_data (CompanyName,

    Batch,

    UserName, /*This field is named batch_sql_user_name in BALI */

    SQLUserName,

    claim_form_type_id,

    Claim_type,

    status_type,

    each,

    member_last,

    fromdate,

    todate )

    SELECT DISTINCT@CompanyName,

    substring(batch_user.batch, 1, 1) as batch_init,

    batch_user.batch as user_name,

    batch_user.batch_sql_user_name,

    null,--altered

    claim_status.claim_status_ud,--altered

    claim_status.claim_status_ud,

    claim.claim_id,

    claim.member_last_name as member_last,

    @begind,

    @endd

    FROM batch_user

    inner join batchtype

    on batch_user.batchtype_id = batchtype.batchtype_id,

    claim

    inner join claim_status

    on claim.claim_status_id = claim_status.claim_status_id

    WHERElen(batch_user.batch) = 1

    and substring(claim.claim_ud, 9, 1) = batch_user.batch

    and (claim.date_created < @end AND claim.date_created >= @begin)

    --and batchtype.batchtype_id = @CLAIMS--altered 11/25/02

    GROUP BY claim.claim_form_type_id, batch_user.batch_sql_user_name, batch_user.batch, claim_status.claim_status_ud, claim.member_last_name,claim.claim_id

    update #rpt_data

    set claim_type = 'HCFA'

    where (claim_form_type_id = 2 or claim_form_type_id is null)

    update #rpt_data

    set claim_type = 'UB92'

    where (claim_form_type_id = 1)

    SELECT * FROM #rpt_data

  • How about you give us one query, not ~200 lines of code, along with create table statements and some sample data?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • krypto69 (3/15/2016)


    Hi,

    I have a query below that I think is close to what I need, but I need to add a column(s) that has the

    Number of APPROVED claims

    Number of ENTERED claims

    Number of PENDING claims

    The standard pattern for this type of requirement is

    SUM(CASE WHEN Status = 'Approved' THEN 1 ELSE 0 END) AS NumberApproved

    You can probably figure out where to add that to your code. If not, then do as Gail suggests and post a repro script that is as short as possible and contains all the ingredients that we can run it in our playpen database.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • I tend to do what Hugo has suggested. Use a CASE to only return values when some criteria is met, then sum those.

  • For a single column, you could use COUNT(*) OVER(PARTITION BY Status)

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 5 posts - 1 through 4 (of 4 total)

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