export without duplicates under certain criteria

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

export without duplicates under certain criteria

Author

Topic

eftychia
Starting Member

5 Posts

Posted - 2014-03-20 : 16:10:17

Hello,
If I have a table (lets name it table1) contains the columns: Customer Name, Creation date, Call status and I want to create a query for the last call status per Account name how I am going to do that via sql? I tried :
SELECT DISTINCT customer name, creation date, call status FROM table1

and I got less rows but there where still duplicates (customer name) since the call status was different..
any ideas??

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-20 : 16:14:12

You can use row_number function

SELECT
	CustomerName, CreationDate,CallStatus
FROM
(
	SELECT
		CustomerName, CreationDate,CallStatus,
		ROW_NUMBER() OVER (PARTITION BY CustomerName ORDER BY CreationDate DESC) AS RN
	FROM
		Table1
)s WHERE RN = 1;

eftychia
Starting Member

5 Posts

Posted - 2014-03-20 : 16:24:18

Thanks for the answer! I will try it tomorrow at work .. Haven t heard of row_number function..
I will let you know if it works. Thanks James

eftychia
Starting Member

5 Posts

Posted - 2014-03-21 : 14:16:07

It didn t work... :(

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-21 : 14:50:01

Please explain, what do you mean by it didn't work?

better if you provide sample data (in the form of Insert statements) for the specified structure (table1) and the desired output. e.g.
declare @table1 table ....
insert into @table1
values .....
....
....

and what should be the desired output based on your given data?

Cheers
MIK

eftychia
Starting Member

5 Posts

Posted - 2014-03-21 : 16:33:34

The tool I am using is the siebel (7.8)analytics where you can export data via standard reports. Every report has sql code. The standard code for the report I want to change is the following:

SELECT "- Campaign".Name saw_0, Response."Created By" saw_1, Response."Creation Date" saw_2, Response."Response Type" saw_3, Account."VAT registration number (AFM)" saw_4, Asset.MSISDN saw_5, "- Campaign Load Wave"."Wave #" saw_6, "- Campaign Load Wave"."Load Date Time" saw_7 FROM "Campaign Responses_segmentation"

I gave a simplier example above and I adjust James answer in the original:

SELECT
"- Campaign".Name saw_0, Response."Created By" saw_1, Response."Creation Date" saw_2, Response."Response Type" saw_3, Account."VAT registration number (AFM)" saw_4, Asset.MSISDN saw_5, "- Campaign Load Wave"."Wave #" saw_6, "- Campaign Load Wave"."Load Date Time" saw_7
FROM
(
SELECT
"- Campaign".Name saw_0, Response."Created By" saw_1, Response."Creation Date" saw_2, Response."Response Type" saw_3, Account."VAT registration number (AFM)" saw_4, Asset.MSISDN saw_5, "- Campaign Load Wave"."Wave #" saw_6, "- Campaign Load Wave"."Load Date Time" saw_7,
ROW_NUMBER() OVER (PARTITION BY Account."VAT registration number (AFM)" saw_4 ORDER BY Response."Creation Date" saw_2 DESC) AS RN
FROM
"Campaign Responses_segmentation"
)s WHERE RN = 1;

but I got an error where it couldn t find 2nd SELECT . I don t know, maybe is the tool I am using...

The original report gives you all the entries in the campaign per Vat registration number (customer), for example in the vat registration number 11111 we have 4 attempts in 4 different date/time (response.creation date and 4 response types (from earlier to later: no reply, no reply, no reply, accepted)-4 rows in the export- . So I want to export only the last one (the accepted) and avoid the all the previous.

Everything I have tried results in an error.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-21 : 17:03:12

Please post sample data in a consumable format with expected results. This will help us to help you. Here are some link to help you provide that:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

eftychia
Starting Member

5 Posts

Posted - 2014-03-21 : 17:51:50

Lamprey I read the links and the only thing I understood is that I put it all wrong... I have no idea how to do these things...
..sorry.. I thought that someone would just need the code and a description of the output in order to put an extra command or something.
Thank you though (and Mik 2008, James K)for trying to help me.

Monib
Starting Member

11 Posts

Posted - 2014-03-24 : 03:07:27

Hi,

SELECT
CustomerName, CreationDate,CallStatus
FROM TABLE1
where CallStatus
in
(
SELECT
max(CallStatus)
FROM
Table1
)

Monib

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources