View and T-SQL different

[djj55]

Hello, SQL2008R2
If I create a view with the following code:

SELECT  
    C.Client, 
    AM.RefNum, 
    AM.GroupVal, 
    AM.MiscVal,
    COUNT(AM.RefNum) OVER (PARTITION BY AM.GroupVal, C.Client) AS ClientRelated, 
    cte.CNT - COUNT(AM.RefNum) OVER (PARTITION BY AM.GroupVal, C.Client) AS NonClientRelated, 
    cte.CNT AS Total
FROM MyTable1 AM 
INNER JOIN MyTable2 C ON AM.[CLIENT NAME] = C.ClientName 
INNER JOIN (SELECT GroupVal, COUNT(*) AS CNT
    FROM MyTable1 
    GROUP BY GroupVal
    HAVING COUNT(*) > 1) cte ON AM.GroupVal = cte.GroupVal
WHERE AM.GroupVal NOT LIKE '00000%' 
    AND AM.GroupVal NOT LIKE '99999%'

the run the view versus running the code I get different answers for ClientRelated/NonClientRelated for SOME RefNum's.
The view returns the correct values while the t-sql does not of the two RefNum's I am looking at one is correct on both the other is not correct for t-sql.

Any suggestions?





djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[djj55]

I found my error. I am actually adding a where clause that is causing the problem. Since I was adding the clause at the end it was limiting where it got the information. By putting the code within a select

select * from (code from above) where criteria

it works just fine.

Funny how I figure things out after I post. I have been working on this for a couple of days off and on.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[tcsbiz]

I have that happen, too, but, usually before I even post. I work out what I want to post in Word. I put it together so that it is logical and clear to the reader. The process of doing that makes the problem clearer to me and I can usually find the solution without posting.

Tom

 

[yelworcm]

There are a number of variations on that theme that I have seen, Tom. the best I have seen was one company with a cardboard cutout of the senior developer. All junior developers have to make their pitches to the cardboard cutout, before they could go to the senior developer himself with their questions. Sounds funny, but it cut the senior developer's interruptions by at least half.