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.
| Author |
Topic |
|
bendertez
Yak Posting Veteran
94 Posts |
 Posted - 2011-04-07 : 12:51:48
|
| HiI have a View RPT_MPN_Serials which i'm trying to incorporate in a report using T-SQL. I am using the row_number() over(partition) function to get my data out in the appropriate format. All seemed okay until I noticed a couple of accounts where the data seemed to be duplicating in the T2. fields (see below).If I add a condition to the bottom of the query specifying the account i.e. and cust_ref = 'B1234', the output is exactly how it should be, however if I leave it to return all rows then one or two accounts have the T1. data duplicated in T2. fields......I can't understand why. Surely when i'm specifying an account it should return the same information as to when I select all....??Does anyone have any ideas why it could do this.....it's baffling the heck out of me....!!I have placed my code below.Report Query:WITH temp as(select Distinct cust_ref, Met_id, MPN, met_ref1,met_ref2,met_ref3,met_ref4,row_number() over(partition by cust_ref order by met_id asc) as RowNumFROM RPT_MPN_Serials)SELECT distinct LEFT(T1.Cust_Ref,9) as Cust_Ref, T1.MPN as MPN1, T1.Met_ref1 as MPN1_Ref1, T1.Met_Ref2 as MPN1_Ref2, T1.Met_Ref3 as MPN1_Ref3, T1.Met_Ref4 as MPN1_Ref4, T2.MPN as MPN2, T2.Met_ref1 as MPN2_Ref1, T2.Met_Ref2 as MPN2_Ref2, T2.Met_Ref3 as MPN2_Ref3, T2.Met_Ref4 as MPN2_Ref4Left Outer Join temp as T2 ON T1.cust_ref= T2.cust_ref AND T2.RowNum = 2 Left Outer Join temp as T3 ON T1.cust_ref= T3.cust_ref AND T3.RowNum = 3 Left Outer Join temp as T4 ON T1.cust_ref= T4.cust_ref AND T4.RowNum = 4 WHERE T1.RowNum = 1 |
|
|
|
|
|
|
|
This is killing me....!!