March 3, 2009 at 10:54 pm
I've got a table like this
EntityID | Property | Value | fromDeleted
-------------------------------------------------------------------
5555 | Active | 1 | 0
5555 | FilialName | asdf | 0
0233 | Active | 1 | 0
0233 | Active | 1 | 1
0233 | FilialName | xyz | 0
0233 | FilialName | XYZ | 1
I need to build this table according to "fromDeleted" column value. If it equals to 0, then column "Value" should be placed in "NewValue" column, if equals to 1, then in "OldValue" column. After that I need to group results by these criterias:
1) If OldValue = NewValue, then eclude it from resultset
2) If OldValue <> NewValue or either OldValue or NewValue equals to NULL, then include to resultset
I tried this:
SELECT EntityID, Property, MAX(CASE WHEN fromDeleted = 1 THEN Value END) AS OldValue,
MAX(CASE WHEN fromDeleted = 0 THEN Value END) AS NewValue
FROM temp
GROUP BY EntityID, Property
And I get such result:
EntityID | Property | OldValue | NewValue
-------------------------------------------------------------------
0233 | Active | 1 | 1
5555 | Active | NULL | 1
0233 | FilialName | XYZ | xyz
5555 | FilialName | NULL | asdf
so it does not exclude rows from result, where OldValue = NewValue. What can I do?
March 3, 2009 at 11:20 pm
Hello,
I think adding a Having clause to your Select statement would do the trick.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
March 3, 2009 at 11:28 pm
John Marsh (3/3/2009)
Hello,I think adding a Having clause to your Select statement would do the trick.
Regards,
John Marsh
Hi, thanks for your reply!
I tried this:
HAVING (MAX(Value) > MIN(Value))
but it removes the rows, where one of column values is NULL, but i need them. what else can i do? :blink:
March 3, 2009 at 11:41 pm
Hey Try This!
select * from
(
SELECT EntityID, Property, MAX(CASE WHEN fromDeleted = 1 THEN Value END) AS OldValue,
MAX(CASE WHEN fromDeleted = 0 THEN Value END) AS NewValue
FROM temp
--where CASE WHEN fromDeleted = 1 THEN Value END <> CASE WHEN fromDeleted = 0 THEN Value END
GROUP BY EntityID, Property
)s
where oldvalue <> newvalue or(oldvalue is null and newvalue is not null)
or(oldvalue is not null and newvalue is null)or(oldvalue is null and newvalue is null)
March 4, 2009 at 12:53 am
Ashok Suhag (3/3/2009)
Hey Try This!select * from
(
SELECT EntityID, Property, MAX(CASE WHEN fromDeleted = 1 THEN Value END) AS OldValue,
MAX(CASE WHEN fromDeleted = 0 THEN Value END) AS NewValue
FROM temp
--where CASE WHEN fromDeleted = 1 THEN Value END <> CASE WHEN fromDeleted = 0 THEN Value END
GROUP BY EntityID, Property
)s
where oldvalue <> newvalue or(oldvalue is null and newvalue is not null)
or(oldvalue is not null and newvalue is null)or(oldvalue is null and newvalue is null)
Hey, thank you very much )) That helped me ))
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply