Eliminating duplicates - 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

Eliminating duplicates

Author

Topic

reacha
Starting Member

49 Posts

Posted - 2010-10-04 : 11:16:09

In my query i have

pkgid name datetime
1 Area 2010-09-30 11:57:54.730
1 Split Call Center 2010-09-30 11:57:54.763

I NEED TO GET THE PKGID,NAME WHICH IS ASSOCIATED WITH THE NAX(DATETIME)

Output should be like this

1 Split Call Center 2010-09-30 11:57:54.763

reacha
Starting Member

49 Posts

Posted - 2010-10-04 : 11:47:21

SELECT PKGID as Emails_Camein_pkgid,EVENT_NAME,AUDITSTAMP
FROM AUDIT INNER JOIN
EVENT1 ON AUDIT.EVENTID = EVENT1.EVENTID AND AUDIT.PROCESSID = EVENT1.PROCESSID
WHERE (EVENT1.EVENTTYPE IN (4010,4011)) AND (AUDIT.PROCESSID IN ( 5))

and PKGID IN (
SELECT AUDIT.PKGID
FROM AUDIT INNER JOIN
EVENT1 ON AUDIT.EVENTID = EVENT1.EVENTID AND AUDIT.PROCESSID = EVENT1.PROCESSID
WHERE (AUDIT.PROCESSID IN (5)) AND (EVENT1.EVENTTYPE = 4019)
group by AUDIT.PKGID
having (max(AUDIT.AUDITSTAMP) >= CONVERT(VARCHAR(10),GETDATE()-4,111))
AND (max(AUDIT.AUDITSTAMP) < CONVERT(VARCHAR(10),GETDATE()-3,111) )
)
Group by AUDIT.PKGID, EVENT1.EVENT_NAME,AUDIT.AUDITSTAMP
HAVING (MAX(AUDIT.AUDITSTAMP) >= CONVERT(VARCHAR(10),GETDATE()-4,111))
AND (MAX(AUDIT.AUDITSTAMP) < CONVERT(VARCHAR(10),GETDATE()-3,111) )

In the above query the inner query is returning correct result
but the outer query is returning duplicates pkgid's

for each pkgid i need to get the eventname associated with max(auditstamp)

Please help me out!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-04 : 12:14:06

see scenario 2 below

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-10-04 : 13:55:53

quote:

In my query i have

pkgid name datetime
1 Area 2010-09-30 11:57:54.730
1 Split Call Center 2010-09-30 11:57:54.763

I NEED TO GET THE PKGID,NAME WHICH IS ASSOCIATED WITH THE NAX(DATETIME)

Output should be like this

1 Split Call Center 2010-09-30 11:57:54.763

Try this ...

declare @temp table(pkgid int, de varchar(100), date datetime)
insert into @temp values(1, 'Area', '2010-09-30 11:57:54.730')
insert into @temp values(2, 'Split Call Center', '2010-09-30 11:57:54.763')
insert into @temp values(2, 'Something', '2010-09-30 11:57:54.769')
insert into @temp values(1, 'Split Call Center', '2010-09-30 11:57:54.763')

;with temp as
(select *, rn = row_number() over(partition by pkgid order by date desc) from @temp)
select pkgid, de, date from temp where rn = 1 order by pkgid

-- resutlt
pkgid de date
1 Split Call Center 2010-09-30 11:57:54.763
2 Something 2010-09-30 11:57:54.770

If your version is earlier than 2005, assuming date is unique for each pkgid as your sample data shows, use this:

select * from @temp t1 where date in
(select max(date) from @temp t2 where t2.pkgid=t1.pkgid group by pkgid)
order by t1.pkgid

Performance is a bit worse than using row_number()

For this :

quote:

SELECT PKGID as Emails_Camein_pkgid,EVENT_NAME,AUDITSTAMP
FROM AUDIT INNER JOIN
EVENT1 ON AUDIT.EVENTID = EVENT1.EVENTID AND AUDIT.PROCESSID = EVENT1.PROCESSID
WHERE (EVENT1.EVENTTYPE IN (4010,4011)) AND (AUDIT.PROCESSID IN ( 5))

and PKGID IN (
SELECT AUDIT.PKGID
FROM AUDIT INNER JOIN
EVENT1 ON AUDIT.EVENTID = EVENT1.EVENTID AND AUDIT.PROCESSID = EVENT1.PROCESSID
WHERE (AUDIT.PROCESSID IN (5)) AND (EVENT1.EVENTTYPE = 4019)
group by AUDIT.PKGID
having (max(AUDIT.AUDITSTAMP) >= CONVERT(VARCHAR(10),GETDATE()-4,111))
AND (max(AUDIT.AUDITSTAMP) < CONVERT(VARCHAR(10),GETDATE()-3,111) )
)
Group by AUDIT.PKGID, EVENT1.EVENT_NAME,AUDIT.AUDITSTAMP
HAVING (MAX(AUDIT.AUDITSTAMP) >= CONVERT(VARCHAR(10),GETDATE()-4,111))
AND (MAX(AUDIT.AUDITSTAMP) < CONVERT(VARCHAR(10),GETDATE()-3,111) )

We need more info ...

reacha
Starting Member

49 Posts

Posted - 2010-10-04 : 15:10:39

Thanks For your Answer namman

Where this temp table gets stored

I need to write a select statement again for this

reacha
Starting Member

49 Posts

Posted - 2010-10-04 : 16:16:45

Sachin.Nand

2937 Posts

Posted - 2010-10-05 : 05:23:05

Remove the order by clause


with temp as
(select *, rn = row_number() over(partition by pkgid order by auditstamp desc) from @temp)
select pkgid as emails_camein,null as Emails_Completed_pkgid, event_name,processid, auditstamp from temp where rn = 1 
order by pkgid

PBUH

reacha
Starting Member

49 Posts

Posted - 2010-10-05 : 10:25:40

declare @temp table(pkgid int, eventname varchar(100),processid varchar(100), auditstamp datetime)
insert into @temp(pkgid,event_name,processid,auditstamp)

SELECT AUDIT.PKGID as Emails_Camein_pkgid,EVENT1.event_NAME,AUDIT.PROCESSID,AUDIT.AUDITSTAMP
FROM AUDIT INNER JOIN
EVENT1 ON AUDIT.EVENTID = EVENT1.EVENTID AND AUDIT.PROCESSID = EVENT1.PROCESSID
WHERE (EVENT.EVENTTYPE IN (4010)) AND (AUDIT.PROCESSID IN ( 5))
--and pkgid = '9903486'
and PKGID IN (
SELECT AUDIT.PKGID--,WF_EVENT.NAME,max(wf_auditlog.auditstamp)
FROM AUDIT INNER JOIN
EVENT1 ON AUDIT.EVENTID = EVENT1.EVENTID AND AUDIT.PROCESSID = EVENT1.PROCESSID
WHERE (AUDIT.PROCESSID IN (5)) AND (EVENT.EVENTTYPE = 4019)
--and pkgid = '9904874'
group by AUDIT.PKGID--,WF_EVENT.NAME
having (max(AUDIT.AUDITSTAMP) >= CONVERT(VARCHAR(10),GETDATE()-4,111))
AND (max(AUDIT.AUDITSTAMP) < CONVERT(VARCHAR(10),GETDATE()-3,111) )
)
Group by AUDIT.PKGID, EVENT1.EVENT_NAME,AUDIT.PROCESSID,AUDIT.AUDITSTAMP
HAVING (MAX(AUDIT.AUDITSTAMP) >= CONVERT(VARCHAR(10),GETDATE()-4,111))
AND (MAX(AUDIT.AUDITSTAMP) < CONVERT(VARCHAR(10),GETDATE()-3,111) )

;with temp as
(select *, rn = row_number() over(partition by pkgid order by auditstamp desc) from @temp)
select pkgid as emails_camein,null as Emails_Completed_pkgid, event_name,processid, auditstamp from temp where rn = 1

union

SELECT Null as Emails_Camein_pkgid,AUDIT.PKGID as Emails_Completed_pkgid, EVENT.EVENT_NAME, AUDIT.PROCESSID
FROM AUDIT INNER JOIN
EVENT1 ON AUDIT.EVENTID = EVENT1.EVENTID
AND (AUDIT.AUDITSTAMP >= CONVERT(VARCHAR(10),GETDATE()-1,111))
AND (AUDIT.AUDITSTAMP < CONVERT(VARCHAR(10),GETDATE(),111) )
AND AUDIT.PROCESSID = EVENT1.PROCESSID
WHERE (AUDIT.AUDITMSG = 'Event completed by user') AND (AUDIT.PROCESSID IN (3, 5)) AND (EVENT1.EVENTTYPE = 4010)

and exists (
SELECT AUDIT.PKGID, AUDIT.EVENTID, EVENT1.EVENTTYPE
FROM AUDIT INNER JOIN
EVENT1 ON AUDIT.RESULTEVENTID = WF_EVENT.EVENTID LEFT OUTER JOIN
QUEUE1 ON AUDIT.PKGID = QUEUE.PKGID
WHERE (QUEUE1.PKGID IS NULL) AND (EVENT1.EVENTTYPE = 4020)
AND (AUDIT.AUDITSTAMP >= CONVERT(VARCHAR(10),GETDATE()-1,111))
AND (AUDIT.AUDITSTAMP < CONVERT(VARCHAR(10),GETDATE(),111) )
)

union

declare @temp1 table(pkgid int, eventname varchar(100),processid varchar(100), auditstamp datetime)
insert into @temp1(pkgid,event_name,processid,auditstamp)

SELECT AUDIT.PKGID as Emails_Camein_pkgid,EVENT1.event_NAME,AUDIT.PROCESSID,AUDIT.AUDITSTAMP
FROM AUDIT INNER JOIN
EVENT1 ON AUDIT.EVENTID = EVENT1.EVENTID AND AUDIT.PROCESSID = EVENT1.PROCESSID
WHERE (EVENT.EVENTTYPE IN (4010)) AND (AUDIT.PROCESSID IN ( 5))
--and pkgid = '9903486'
and PKGID IN (
SELECT AUDIT.PKGID--,WF_EVENT.NAME,max(wf_auditlog.auditstamp)
FROM AUDIT INNER JOIN
EVENT1 ON AUDIT.EVENTID = EVENT1.EVENTID AND AUDIT.PROCESSID = EVENT1.PROCESSID
WHERE (AUDIT.PROCESSID IN (5)) AND (EVENT.EVENTTYPE = 4019)
--and pkgid = '9904874'
group by AUDIT.PKGID--,WF_EVENT.NAME
having (max(AUDIT.AUDITSTAMP) < CONVERT(VARCHAR(10),GETDATE()-1,111) )
)
Group by AUDIT.PKGID, EVENT1.EVENT_NAME,AUDIT.PROCESSID,AUDIT.AUDITSTAMP
HAVING (MAX(AUDIT.AUDITSTAMP) < CONVERT(VARCHAR(10),GETDATE()-1,111) )

;with temp1 as
(select *, rn = row_number() over(partition by pkgid order by auditstamp desc) from @temp1)
select pkgid as emails_camein,null as Emails_Completed_pkgid, event_name,processid, auditstamp from temp1 where rn = 1

getting the error message as

Msg 156, Level 15, State 1, Line 61
Incorrect syntax near the keyword 'declare'.

Please help me out

Thanks,
reacha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-05 : 12:14:06

you need to do the declaration and insert for table variable before union step. In union you will just include select query to retrieve data from table variable.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

reacha
Starting Member

49 Posts

Posted - 2010-10-05 : 12:50:19

The Link did not work for me.

Can anyone help me out with this issue

Thanks,
reacha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-05 : 12:59:37

you cant mix these CTE (with thingy) and inserts in between the union. you need to do them first and then use union all

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

reacha
Starting Member

49 Posts

Posted - 2010-10-06 : 10:55:53

This query to eliminate duplicates is working only for one day.

Can anyone please help me out with this

declare @temp table(pkgid int, eventname varchar(100),processid varchar(100), auditstamp datetime)
insert into @temp(pkgid,eventname,processid,auditstamp)

SELECT AUDIT.PKGID ,EVENT1.EVENTNAME,AUDIT.PROCESSID,AUDIT.AUDITSTAMP
FROM AUDIT INNER JOIN
EVENT1 ON AUDIT.EVENTID = EVENT1.EVENTID AND AUDIT.PROCESSID = EVENT1.PROCESSID
WHERE (EVENT1.EVENTTYPE IN (4010,4011)) AND (AUDIT.PROCESSNAME = 'MCR Auth')
AND (AUDIT.AUDITSTAMP > = CONVERT(DATETIME,'2010-10-01 00:00:00', 102))
AND (AUDIT.AUDITSTAMP < CONVERT(DATETIME,'2010-10-02 00:00:00', 102))
--and pkgid = '9903486'
and PKGID IN (
SELECT AUDIT.PKGID--,WF_EVENT.NAME,max(wf_auditlog.auditstamp)
FROM AUDIT INNER JOIN
EVENT1 ON AUDIT.EVENTID = EVENT1.EVENTID AND AUDIT.PROCESSID = EVENT.PROCESSID
WHERE (AUDIT.PROCESSNAME = 'MCR Auth') AND (EVENT1.EVENTTYPE = 4019)
AND (AUDIT.AUDITSTAMP > = CONVERT(DATETIME,'2010-10-01 00:00:00', 102))
AND (AUDIT.AUDITSTAMP < CONVERT(DATETIME,'2010-10-02 00:00:00', 102))
group by AUDIT.PKGID--,WF_EVENT.NAME
)
Group by AUDIT.PKGID,EVENT1.EVENTNAME,AUDIT.PROCESSID,AUDIT.AUDITSTAMP

;with temp as
(select *, rn = row_number() over(partition by pkgid order by auditstamp desc) from @temp)
select pkgid as Emails_Camein_pkgid ,null as Emails_Completed_pkgid,null as email_queue_pkgid, eventname,processid,auditstamp from temp where rn = 1 ORDER BY PKGID

Thanks,
reacha

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-06 : 11:07:11

quote:
Originally posted by reacha

AND (AUDIT.AUDITSTAMP > = CONVERT(DATETIME,'2010-10-01 00:00:00', 102))
AND (AUDIT.AUDITSTAMP < CONVERT(DATETIME,'2010-10-02 00:00:00', 102))

May be because you have specified date for 2010-10-01 to 2010-10-02.

reacha
Starting Member

49 Posts

Posted - 2010-10-06 : 12:34:10

i am trying to run 3 to 4 days like below

AND (AUDIT.AUDITSTAMP > = CONVERT(DATETIME,'2010-10-01 00:00:00', 102))
AND (AUDIT.AUDITSTAMP < CONVERT(DATETIME,'2010-10-05 00:00:00', 102))

but not getting correct values

if i try to run for 1 day the values are correct

Thanks,
Reacha

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources