SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SP273
Starting Member

14 Posts

Posted - 04/20/2011 :  19:24:57  Show Profile  Reply with Quote
select f,
Parent ,
d ,
s ,
M,
max(c),
P
FROM
(
select distinct hph.LoadID as f,
--hph.parent
Case when (substring (hph.Parent,1,2)IN ('D0','D1','D2','D3','D4','D5','D6','D7','D8','D9')) Then 'Direct Projects'
when (substring (hph.Parent,1,6)IN ('DRESVE')) Then 'Direct Projects'
When (substring (hph.Parent,1,2)IN ('F0','F1','F2','F3','F4','F5','F6','F7','F8','F9')) Then 'Fringe Projects'
When (substring (hph.Parent,1,2)IN ('M0','M1','M2','M3','M4','M5','M6','M7','M8','M9')) Then 'Marketing Projects'
When (substring (hph.Parent,1,2)IN ('H0','H1','H2','H3','H4','H5','H6','H7','H8','H9')) Then 'Overhead Projects'
When (substring (scp.Parent,1,6)IN ('F09500')) Then 'Overhead Projects'
When (substring (hph.Parent,1,2)IN ('P0','P1','P2','P3','P4','P5','P6','P7','P8','P9')) Then 'Proposal Projects'
ELSE hph.Parent END AS Parent,
LEFT(scp.child,6)as d,
hph.DataStorage as S,
hph.MemberValidForPlan1 as M,
hph.Plan1Aggregation as P,
hph.[Alias=Default]as c
from HS_Project_Hierarchy hph
left join oracle_Convert_Projects scp on Left(scp.child,6) = hph.child
where scp.DataStorage NOT IN ('ShareData')

UNION ALL

select distinct scp.LoadID as f,
--scp.parent,
Case when (substring (scp.Parent,1,2)IN ('D0','D1','D2','D3','D4','D5','D6','D7','D8','D9')) Then 'Direct Projects'
when (substring (scp.Parent,1,6)IN ('DRESVE')) Then 'Direct Projects'
When (substring (scp.Parent,1,2)IN ('F0','F1','F2','F3','F4','F5','F6','F7','F8','F9')) Then 'Fringe Projects'
When (substring (scp.Parent,1,2)IN ('M0','M1','M2','M3','M4','M5','M6','M7','M8','M9')) Then 'Marketing Projects'
When (substring (scp.Parent,1,2)IN ('H0','H1','H2','H3','H4','H5','H6','H7','H8','H9')) Then 'Overhead Projects'
When (substring (scp.Parent,1,2)IN ('H0','H1','H2','H3','H4','H5','H6','H7','H8','H9')) Then 'Overhead Projects'
When (substring (scp.Parent,1,6)IN ('F09500')) Then 'Overhead Projects'
When (substring (hph.Parent,1,2)IN ('P0','P1','P2','P3','P4','P5','P6','P7','P8','P9')) Then 'Proposal Projects'
ELSE scp.Parent END AS Parent,
LEFT(scp.child,6)as d,
scp.DataStorage as S,
scp.MemberValidForPlan1 as M,
scp.Plan1Aggregation as P,
SUBSTRING(hph.[Alias=Default],1,case len(hph.[Alias=Default]) when 0 then 99 else len(hph.[Alias=Default])-18 END) AS c
from oracle_Convert_Projects scp
left join HS_Project_Hierarchy hph on scp.child = hph.child
where scp.DataStorage NOT IN ('ShareData') AND LEFT (scp.child,6) <> 'F09500')t
group by
f,
Parent ,
d ,
s ,
M,
P

I have the above query & i m trying to remove the duplicate rows by using max in my outer query from the rows that get from my 2 inner queries. But when i run this query it still gives me duplicate rows. Can anybody know where i might be going wrong with the max statement or a way to modify this query so that i can have distinct rows.

Thanks!

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 04/20/2011 :  21:06:28  Show Profile  Reply with Quote
There should be/will be only one row for each unique combination of the values of the six columns: f, Parent, d, s, M, and P. Are you seeing results that contradict this?
Go to Top of Page

SP273
Starting Member

14 Posts

Posted - 04/20/2011 :  21:15:25  Show Profile  Reply with Quote
thanks for your reply:
below is an example of duplicate rows i see in my output
f Parent d s M c p
1 Direct Projects D4000 StoreData NULL hitech4000 +
1 Direct Projects D4000 StoreData 1 hitech4000 (hitech base) +

according to my understanding it should pick the second row as per my max on c in the query but it gives me 2 rows for same project.

Thanks
Go to Top of Page

SP273
Starting Member

14 Posts

Posted - 04/20/2011 :  21:19:47  Show Profile  Reply with Quote
Please discard my earlier post actually the problem was because of column M. I corrected it.Thanks for your help
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000