March 18, 2009 at 9:43 am
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
March 18, 2009 at 9:59 am
I'm not totally clear what you want but you can try this and let me know if it's what's needed
select col1, sum(col3)
from TempTable
where GroupCd is not null
group by col1
March 18, 2009 at 11:14 am
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.
March 18, 2009 at 1:41 pm
I got it resolved. I did a join with the subset of the results and created new column. and it worked for me.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply