Grouping Data Problem

Hello all

I am using SQL Server 2000. I have a table of over 1 million accounting transactions. I need to be able to remove all items that have contra items.

e.g

debit �G100 - credit �G100 - debit of �G125 ( I only want to see the debit of �G125)

I can achieve this in MS Access by grouping the key fields, suming the value fields and using the First() or Last() command for columns that I need to display but not group.

How can I achieve this in SQL?

All help appreciated.

hi how does your table look like?

can you please post the schema

|||

I am still a little new to SQL so am not entirely sure what the 'schema' is so here's the code from the create table command: -

Please note that I am importing data from an old DB3 file so don't have a lot of control over the data structure.

(
[KEY] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[POL_IDX] [nvarchar] (12) COLLATE Latin1_General_CI_AS NULL ,
[ITEM] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
[AGCY] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[BRCH] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[DEPT] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[MONTH] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[AMT] [float] NULL ,
[TBAL] [float] NULL ,
[DESC] [nvarchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[EDATE] [nvarchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[INPUTDATE] [nvarchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[FLAG] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[TRANS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[BILL] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CPAID] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[STATE] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[BCO] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[ICO] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[MCO] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[COM_P] [float] NULL ,
[PR] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[PR_P] [float] NULL ,
[PR2] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[PR_P2] [float] NULL ,
[BR_P] [float] NULL ,
[TYPE] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[POL] [nvarchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[OINT] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[LOANNUM] [nvarchar] (11) COLLATE Latin1_General_CI_AS NULL ,
[PRT] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CLOSED] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[OP_ID] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[CSR] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[JOURNAL] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[DOCTYPE] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[REFER] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[PRINTED] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CODE] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[PC] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[BIN] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[SPLIT_PLAN] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[PR3] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[PR_P3] [float] NULL ,
[CO_AMT] [float] NULL ,
[PR_AMT1] [float] NULL ,
[PR_AMT2] [float] NULL ,
[PR_AMT3] [float] NULL ,
[BR_AMT] [float] NULL ,
[PPAID] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[PFLAG] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CO_TYPE] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[PR_TYPE1] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[PR_TYPE2] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[PR_TYPE3] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CPAID_MO] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[ADJUST] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[STATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[TYPEGROUP] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[REFER1] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[NOTE] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CONGLOM] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[SKIP] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[EXTRA] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL
)

|||I have now managed to achived this by using the same approach as I did with Access, however used the min,max options

Grouping Data Problem

Hello all

I am using SQL Server 2000. I have a table of over 1 million accounting transactions. I need to be able to remove all items that have contra items.

e.g

debit �G100 - credit �G100 - debit of �G125 ( I only want to see the debit of �G125)

I can achieve this in MS Access by grouping the key fields, suming the value fields and using the First() or Last() command for columns that I need to display but not group.

How can I achieve this in SQL?

All help appreciated.

hi how does your table look like?

can you please post the schema

|||

I am still a little new to SQL so am not entirely sure what the 'schema' is so here's the code from the create table command: -

Please note that I am importing data from an old DB3 file so don't have a lot of control over the data structure.

(
[KEY] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[POL_IDX] [nvarchar] (12) COLLATE Latin1_General_CI_AS NULL ,
[ITEM] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
[AGCY] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[BRCH] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[DEPT] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[MONTH] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[AMT] [float] NULL ,
[TBAL] [float] NULL ,
[DESC] [nvarchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[EDATE] [nvarchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[INPUTDATE] [nvarchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[FLAG] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[TRANS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[BILL] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CPAID] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[STATE] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[BCO] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[ICO] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[MCO] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[COM_P] [float] NULL ,
[PR] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[PR_P] [float] NULL ,
[PR2] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[PR_P2] [float] NULL ,
[BR_P] [float] NULL ,
[TYPE] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[POL] [nvarchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[OINT] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[LOANNUM] [nvarchar] (11) COLLATE Latin1_General_CI_AS NULL ,
[PRT] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CLOSED] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[OP_ID] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[CSR] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[JOURNAL] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[DOCTYPE] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[REFER] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[PRINTED] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CODE] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[PC] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[BIN] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[SPLIT_PLAN] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[PR3] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[PR_P3] [float] NULL ,
[CO_AMT] [float] NULL ,
[PR_AMT1] [float] NULL ,
[PR_AMT2] [float] NULL ,
[PR_AMT3] [float] NULL ,
[BR_AMT] [float] NULL ,
[PPAID] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[PFLAG] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CO_TYPE] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[PR_TYPE1] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[PR_TYPE2] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[PR_TYPE3] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CPAID_MO] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[ADJUST] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[STATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[TYPEGROUP] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[REFER1] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[NOTE] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CONGLOM] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[SKIP] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[EXTRA] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL
)

|||I have now managed to achived this by using the same approach as I did with Access, however used the min,max options

Group summary totals Help please

Hi,

I am pretty new to Crystal 8.5 and am having problems using formulas on a group summary field.

The database is > 1 million records and I am trying to group a field (for quicker report generation). The field is the time (in seconds) numeric and I want to be able to manipulate this field and get it into hhhh:mm:ss format.

In this application, I want to add up all the seconds for each person in a department, then give a total for each person however in hh:mm:ss format.

Eg Person Table

Name Duration(in seconds)

Peter 50
John 10
Peter 45
John 22
John 80
Peter 100

The Crystal generated SQL will generate

Peter 195
John 112

This is straight forward, however, I now need to convert this to hh:mm:ss format to give me

Peter 00:03:15
John 00:01:52

I can do the above if I look at each individual record (ie no grouping) and use formulas on the Duratin field, however, because of the size of the database, I want to group the records and then manipulate the SUM (Person."Duration") field.

Does anybody have a solution?

(I hope my explanation of my problem is clear ...)

Thanking you in advance.

TheodoreOK Guys,

Solved the problem.

Thanks anyway.

Theodore|||Theo,

I have similar problem, any chance of posting the solution

Regards

Steve Dyson|||Why do you not just use the same formula you used for individual records, to convert the summary? The field would be sum of duration.|||My problem is that i have the following:

Planned and unplanned items.

I can group and Sum them that is the easy bit.

Because this is going to be a sub report, i need these totals to appear in the Page header as i only want to see the Totals

Steve