urgent plzzzz

Question

Post reply

urgent plzzzz

Chinna

SSCertifiable

Points: 5408
More actions
April 23, 2008 at 9:36 am

#186830

Hi..
i have two flat files which i have already imported to the DB.Now based on the table to which i imported i need to makes some calculations on that and create a new table but the problem is that i have got duplicates of rows in the imported table as it comes from two files ....which i dont want it to be ....So
table A
col1 col2 col3 col4 col5 col6 col7
abc 123 salsa xyz 2.5 2.0 0.0----from flat file 1
def 163 alsa fre 3.5 0.0 2.4----from flat file 1
abc 123 salsa xyz 2.5 2.0 0.0---from flat file 2
dfh 323 asla dhs 4.3 2.7 0.0 ---from flat file 2
abc 143 salsa xbz 3.0 2.5 0.0 --- form flat file 2
here col6 is value_submitted... col7 is the value_corrected ....so i jus need the sum of files
where col1 ='abc' but here i get
as 6.5 but as i jus need to het
total col6 = 2+2.5 = 4.5 as the other row number 3 is a duplicate of row number 1 so i jus dont want it to be included again and can i know how to do that ..
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman

Viewing 7 posts - 1 through 6 (of 6 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

here is one way, might not be the most efficent but will work

declare @Tmp as Table

(col1 char(10),col2 char(10),col3 char(10),col4 char(10)

,col5 char(10),col6 int, col7 int)

insert into @Tmp

select distinct col1,col2,col3, col4, col5, col6, col7

from tableA

select col1, sum(col6)

from @Tmp

group by col1

i hope you have used col1 etc.. as an example, and not it the actual database:)

Chinna SSCertifiable Points: 5408 More actions · Answer 2

But if i have used some joins what would be the query let me post the query which would be easier for you ....

select km_ndc,custno,custnm,wholesaler_dea,wholesaler_name,null,null,

cd.sourcefile,null,qtrstartdate,qtrenddate,

case when sum(qty_corrected) =0 then sum(Qty_submitted)

else

sum(qty_corrected)

end,

CASE WHEN (extended_amount_corrected)

+ (qty_corrected)

+ (cost_corrected)

+ (contract_price_corrected) = 0

THEN SUM(extended_amount_submitted)

ELSE SUM(extended_amount_corrected)

END,null

from dbo.ABC_DEF_SHIP S

inner join dbo.XYZ_header ch

on ch.wholesaler_dea = s.primary_dea_number

inner join dbo.Chargeback_detail Cd

on ch.chargeback_number = cd.chargeback_number

inner join dbo.SHIP_A S1

on S.ship_to_number=s1.custno

inner join kms_ABC.dbo.qtrdate q

ON Invoice_date_submitted >= QTRSTARTDATE

and Invoice_date_submitted <= QTRENDDATE

group by km_ndc,custno,custnm,wholesaler_dea,wholesaler_name,qtrstartdate,qtrenddate,cd.sourcefile,qty_corrected,extended_amount_corrected,cost_corrected,contract_price_corrected

order by 1,2,3,4,5,6

THIS IS MY QUERY I HAVE JOINED EVEN OTHER DATABASES SO CAN I KNOW HOW CAN I ELIMINATE THOSE NOW....

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman

steveb. SSC-Forever Points: 46733 More actions · Answer 3

I'm sorry i thought you had an issue with duplicates in your table.

It is hard to work out what is happening with your query without seeing the table defs and some sample data.

If you post them it will make it easier to offer help.

Bob Monahon Ten Centuries Points: 1282 More actions · Answer 4

Hello,

You can eliminate duplicates in Table A with SQL statement:

SELECT col1, col2, col3, col4, col5, col6, col7

FROM TableA

GROUP BY col1, col2, col3, col4, col5, col6, col7

This will return all unique rows in the table; exact duplicates in TableA will be grouped together into a single row in the result set.

You can use this select statement in the FROM clause of another query, similar to this example (Joining Table1 to TableA_unique)

SELECT able, baker, etc.

FROM Table1,

(

SELECT col1, col2, col3, col4, col5, col6, col7

FROM TableA

GROUP BY col1, col2, col3, col4, col5, col6, col7

) AS TableA_unique

WHERE Table1.something = TableA_unique.col1

etc.

Regards,

Bob Monahon

Chinna SSCertifiable Points: 5408 More actions · Answer 5

USE [KMS_GSK]

GO

/****** Object: Table [dbo].[GSK_MAINFRAMES_CHRGBACK_CUSTOMER_TRANSACTON] Script Date: 04/23/2008 12:29:41 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[GSK_MAINFRAMES_CHRGBACK_CUSTOMER_TRANSACTON](

[NDC] [varchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CustomerID] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[CustomerDescription] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Whlslr_Dea] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Whlslr_Name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[System] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Tablename] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SourceFile] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[TransactionType] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[StartDate] [datetime] NOT NULL,

[EndDate] [datetime] NOT NULL,

[TotalDollars] [float] NOT NULL,

[TotalQuantity] [float] NOT NULL,

[UnitQuantity] [float] NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

this is my new table into which i have to insert the claculated values from diff tables ...so now

i have to import into this table using taht query ...

insert into [GSK_MAINFRAMES_CHRGBACK_CUSTOMER_TRANSACTON]