Help With Problem Using Over Partition By

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

SQL Server 2005 Forums

Transact-SQL (2005)

Help With Problem Using Over Partition By

Author

Topic

BobRoberts
Posting Yak Master

109 Posts

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

I need some help. I have a table called TESTTABLE that is represented below. What I want to do is put a "1" in the status of the rows of any EMPID/CHECKNO "combination" whose ACCOUNT does not contain the account of "00-777". The "combination" aspect is critical, and what I am having trouble with - I've tried using the "over by partition" construct, but am getting errors. Then, maybe there's another approach I'm not aware of.

EMPID|CHECKNO|ACCOUNT|STATUS
----------------------------
AAAAA|1111111|00-100|---0---
AAAAA|1111111|00-200|---0---
AAAAA|1111111|00-200|---0---
BBBBB|2222222|00-100|---0---
BBBBB|2222222|00-777|---0---
CCCCC|4444444|00-300|---0---
DDDDD|5555555|00-777|---0---

What the TESTTABLE should look like after the SQL script is run:

EMPID|CHECKNO|ACCOUNT|STATUS
----------------------------
AAAAA|1111111|00-100|---1---
AAAAA|1111111|00-200|---1---
AAAAA|1111111|00-200|---1---
BBBBB|2222222|00-100|---0---
BBBBB|2222222|00-777|---0---
CCCCC|4444444|00-300|---1---
DDDDD|5555555|00-777|---0---

Sachin.Nand

2937 Posts

Posted - 2010-04-14 : 13:56:04

[code]
declare @tbl as table(EMPID varchar(40), CHECKNO int, ACCOUNT varchar(15))
insert into @tbl
select 'AAAAA',1111111,'00-100' union all
select 'AAAAA',1111111,'00-200'union all
select 'AAAAA',1111111,'00-200'union all
select 'BBBBB',2222222,'00-777'union all
select 'BBBBB',2222222,'00-999'union all
select'CCCCC',4444444,'00-300'union all
select 'DDDDD',5555555,'00-777'

select * from @tbl

select *,isnull(max(case when account='00-777' then 0 end)over(partition by empid),1)as status from @tbl

[/code]

PBUH

BobRoberts
Posting Yak Master

109 Posts

Posted - 2010-04-14 : 14:17:22

This seems to work as a SELECT statement, but the purpose of this is to actually update the existing STATUS column in the TESTTABLE with the "status" that the script comes up with.

quote:

Originally posted by Idera


declare @tbl as table(EMPID varchar(40), CHECKNO int, ACCOUNT varchar(15))
insert into @tbl
select 'AAAAA',1111111,'00-100' union all
select 'AAAAA',1111111,'00-200'union all
select 'AAAAA',1111111,'00-200'union all
select 'BBBBB',2222222,'00-777'union all
select 'BBBBB',2222222,'00-999'union all
select'CCCCC',4444444,'00-300'union all
select 'DDDDD',5555555,'00-777'

select * from @tbl

select *,isnull(max(case when account='00-777' then 0 end)over(partition by empid),1)as status from @tbl

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-04-14 : 14:31:07

quote:
Originally posted by BobRoberts

This seems to work as a SELECT statement, but the purpose of this is to actually update the existing STATUS column in the TESTTABLE with the "status" that the script comes up with.

quote:
Originally posted by Idera

declare @tbl as table(EMPID varchar(40), CHECKNO int, ACCOUNT varchar(15))
insert into @tbl
select 'AAAAA',1111111,'00-100' union all
select 'AAAAA',1111111,'00-200'union all
select 'AAAAA',1111111,'00-200'union all
select 'BBBBB',2222222,'00-777'union all
select 'BBBBB',2222222,'00-999'union all
select'CCCCC',4444444,'00-300'union all
select 'DDDDD',5555555,'00-777'

select * from @tbl

select *,isnull(max(case when account='00-777' then 0 end)over(partition by empid),1)as status from @tbl

PBUH

Its quite easy.


declare @tbl as table(EMPID varchar(40), CHECKNO int, ACCOUNT varchar(40),STATUS int)
insert into @tbl
select 'AAAAA',1111111,'00-100',0 union all
select 'AAAAA',1111111,'00-200',0 union all
select 'AAAAA',1111111,'00-200',0 union all
select 'BBBBB',2222222,'00-100',0 union all
select 'BBBBB',2222222,'00-777',0 union all
select'CCCCC',4444444,'00-300',0 union all
select 'DDDDD',5555555,'00-777',0

--select *,isnull(max(case when account='00-777' then 0 end)over(partition by empid),1)as status from @tbl

update t1 set t1.status=(

			select top 1 isnull(max(case when account='00-777' then 0 end)over(partition by empid),1)as status from @tbl t2 where
			             t1.EMPID=t2.EMPID and t1.CHECKNO=t2.CHECKNO
			
) from @tbl t1 

select * from @tbl

PBUH

BobRoberts
Posting Yak Master

109 Posts

Posted - 2010-04-14 : 16:19:38

Thank you very much, Idera - that seems to be working fine. I did change the "partition by empid" to "partition by empid+checkno"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources