Query Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query Problem

Author

Topic

Lady Gragor
Starting Member

2 Posts

Posted - 2009-08-10 : 02:47:45

Hi,

Could someone please tell me how to avoid duplicate amounts in the subtotal field of the following SQL statement. I would like it to display the subtotal only once for the last entry for each CAT with the same value and otherwise leave the subtotal field blank.

SELECT qry_Claim.Field1, qry_Claim.Field2, qry_Claim.WBS, qry_Claim.CAT, qry_Claim.[CAT Description], qry_Claim.Date, qry_Claim.Vendor, qry_Claim.Description, qry_Claim.Units, qry_Claim.Amount, qry_Totals.SumOfField12 AS Subtotal
FROM qry_Claim LEFT JOIN qry_Totals ON qry_Claim.CAT = qry_Totals.Field6;

Thanks in advance

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-10 : 02:54:42

Can you post some sample data and expected result?

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-10 : 02:55:07

do u have any date column in ur table qry_Totals ?
if yes then use row_number funciton

Lady Gragor
Starting Member

2 Posts

Posted - 2009-08-10 : 07:21:34

Sample data of claim query

Field1 Field2 WBS CAT CAT Description Date Vendor Units
1234 2345 1-01-01 007 a description 24/06/09 ABCD 1
1234 2345 1-01-01 007 a description 30/06/09 ABCD 3

Field12
$128.00
$700.00

Every line of the file has a CAT number. The number of lines containing the same CAT number is unknown.

Sample data of totals query

Field1 Field3 Field6 SumOfField12
1234 1-01-01 007 $828.00
1234 1-01-01 024 $700.52
1234 1-01-01 051 $254.82
1234 1-01-01 061 $112.56

I would like it to appear as follows
(summarised here)

Field1 Field3 Field6 Field 12 SumOfField12
1234 1-01-01 007 $128.00
1234 1-01-01 007 $700.00 $828.00
1234 1-01-01 061 $112.56
1234 1-01-01 061 $112.56
1234 1-01-01 061 $256.90 $482.02

I hope this makes sense

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources