Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81

    Unanswered: Group By trouble

    Select col1, col2, col3, col4
    from TempTable



    Col1 COl2 COL3 COL4 GroupCd\
    1/1/2001 Abc 20.00 xys G1
    1/1/2001 EFG 20.00 xys G1
    1/1/2001 Abc 20.00 xys NULL
    1/1/2001 EFG 20.00 xys NULL
    2/1/2001 npk 10.00 xys G1
    2/1/2001 Abc 30.00 xys NULL

    2/1/2001 Pkk 20.00 xys G1


    I need to get sum(col3)of similiar date Grouping by GroupCd
    Which is 40 for 1/1/2001 and 30 for 2/1/2001
    But I am to get my desired results. What query do I need to execute.

    Please help.

    Thankx

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by musman
    I need to get sum(col3)of similiar date Grouping by GroupCd
    Which is 40 for 1/1/2001 and 30 for 2/1/2001
    Huh?

    Your request seems vague to me. Are you excluding NULL values? How precise are your dates (day, minute, milliseond, etc)?

    -PatP

  3. #3
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81
    I think it would be better if I post actual string

    Select InstallNum, RecordWrittenDt, PolicyEffectiveDt, ActivityType, (Case TransactionDes When Null Then TransactionCd Else TransactionDes End) As TransactionCd, ActivityAmt, InstallBalanceAmt,ISNull(ReceiptTypeCd,'') as ReceiptTypeCd,ISNull(ReceiptId,'') as ReceiptId, IsNull(ReceiptTypeDes,'') as ReceiptTypeDes, @TotalRecords As TotalRecords, @FirstRec, @LastRec, TransactionGroupCd-- Added Receipt* fields for # 106707
    From #TempTable
    WHERE ID > @FirstRec AND ID < @LastRec --and TransactionGroupCd is not Null

    Table
    coming back is

    1 2010-07-03 22:08:56.000 2010-03-10 00:00:00.000 PREM Renewal 2021.10 2021.10 14 0 11 G1
    1 2010-07-03 22:08:57.000 2010-03-10 00:00:00.000 PREM Non Premium 1.00 2022.10 14 0 11 G1
    1 2010-07-03 22:09:02.000 2010-03-10 00:00:00.000 PREM NULL 4069.20 6091.30 14 0 11 NULL
    1 2010-07-03 22:09:04.000 2010-03-10 00:00:00.000 INV Notice 1 6091.30 6091.30 14 0 11 NULL
    1 2010-07-17 03:57:08.000 2010-03-10 00:00:00.000 INV NULL 6091.30 6091.30 14 0 11 NULL
    1 2010-08-05 23:36:36.000 2010-03-10 00:00:00.000 PREM Cancellation -452.00 5639.30 14 0 11 NULL
    1 2010-08-05 23:36:44.000 2010-03-10 00:00:00.000 BILL NULL 5639.30 5639.30 14 0 11 NULL
    1 2010-07-02 22:08:00.000 2009-09-10 00:00:00.000 PREM Renewal 1996.00 1996.00 14 0 11 G1
    1 2010-07-02 22:08:01.000 2009-09-10 00:00:00.000 PREM Non Premium 1.00 1997.00 14 0 11 G1
    1 2010-07-02 22:08:05.000 2009-09-10 00:00:00.000 PREM NULL 2072.20 4069.20 14 0 11 NULL

    and I need to Add 2021.10 +1.00 which is Renewal and Non Premium based on G1 for similar dates.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    Select PolicyEffectiveDt, Sum(ActivityAmt)
       FROM #TempTable
       WHERE  ID > @FirstRec
          AND ID < @LastRec 
    --    AND TransactionGroupCd IS NOT NULL
          AND 'G1' = TransactionGroupCd
       GROUP BY PolicyEffectiveDt
       ORDER BY PolicyEffectiveDt
    -PatP

  5. #5
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81
    got it.
    Thankx

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •