ROW COUNT OVER using AND/WHERE affect No of Rows

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 2008 Forums

Transact-SQL (2008)

ROW COUNT OVER using AND/WHERE affect No of Rows

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2015-01-23 : 04:03:23

Hi all

I have this query:

SELECT DISTINCT
      table1.userID,
      table2.UniqueID,
      table3.entrydate,
      table2.entrytime,
      table4.changedatestart,
      table2.changetimestart,
      table5.changedateend,
      table2.changetimeend,
      table6.leavedate,
      table2.leavetime,
      table7.nationality_ID,
      table8.reg_code
      FROM
      table1 INNER JOIN table2 ON (table1.t1_KEY=table2.t2_KEY)
       RIGHT OUTER JOIN DATETIMETABLE  table4 ON (change_start_date_alias.DATEKEY=table2.change_date_start_KEY)
       RIGHT OUTER JOIN DATETIMETABLE  table5 ON (change_end_date_alias.DATEKEY=table2.change_date_end_KEY)
       RIGHT OUTER JOIN DATETIMETABLE  table3 ON (entry_date_alias.DATEKEY=table2.ENTRY_DATE_KEY)
       RIGHT OUTER JOIN DATETIMETABLE  table6 ON (leave_date_alias.DATEKEY=table2.LEAVE_DATE_KEY)
       RIGHT OUTER JOIN table7 ON (table7.nat_KEY=table2.nat_KEY)
       INNER JOIN table8 ON (table8.reg_DEPT_KEY=table2.reg_DEPT_KEY)
       RIGHT OUTER JOIN dbo.table9 ON (table9.leave_KEY=table2.leave_KEY)
    INNER JOIN
        (SELECT 
        table1.userID UID,
        ROW_NUMBER() OVER (PARTITION BY table1.userID ORDER BY table3.entrydate ASC) as Seq,
        table2.UniqueID "Unique_ID",
        table3.entrydate "entry_date",
        table2.entrytime "entry_time",
        table4.changedatestart "change_start_Date",
        table2.changetimestart "change Start Time",
        table5.changedateend "change End Date",
        table2.changetimeend "change End Time",
        table6.leavedate "leave Date",
        table2.leavetime "leave time",
        table7.nationality_ID "Nationality ID",
        table8.reg_code "Registration Code"
        FROM
        table1 INNER JOIN table2 ON (table1.t1_KEY=table2.t2_KEY)
        RIGHT OUTER JOIN DATETIMETABLE  table4 ON (change_start_date_alias.DATEKEY=table2.change_date_start_KEY)
        RIGHT OUTER JOIN DATETIMETABLE  table5 ON (change_end_date_alias.DATEKEY=table2.change_date_end_KEY)
        RIGHT OUTER JOIN DATETIMETABLE  table3 ON (entry_date_alias.DATEKEY=table2.ENTRY_DATE_KEY)
        RIGHT OUTER JOIN DATETIMETABLE  table6 ON (leave_date_alias.DATEKEY=table2.LEAVE_DATE_KEY)
        RIGHT OUTER JOIN table7 ON (table7.nat_KEY=table2.nat_KEY)
        INNER JOIN table8 ON (table8.reg_DEPT_KEY=table2.reg_DEPT_KEY)
        RIGHT OUTER JOIN dbo.table9 ON (table9.leave_KEY=table2.leave_KEY)
        WHERE table3.entrydate  BETWEEN  '20131201'  AND  '20140531'
        AND table8.reg_DESC  In  ( 'Value1','Value2','Value3','Value4','Value5'  )
        AND table9.leave_CODE IN ('11','15','16','22','25','27','54','57','66')
        )b
    ON b.UID = table1.userID
    AND b.Unique_ID <> table2.UniqueID
    --AND b.Seq = 1
    AND b.[Registration Code] = table8.reg_code

In the final conditional statement if I change the bold AND to WHERE it makes no difference to the row count returned but when I include the condition for the Seq = 1 and then change the AND/WHERE I get slightly different number of results.

With the WHERE condition I get 35806 results
With the AND condition I get 36762 results
A difference of 956 extra rows with the AND

I am using the ROW_NUMBER() OVER statement in sub query to only return first earliest date record for a certain user ID (within certain time period) and then using outer query I am returning subsequent records for that same user ID.

The final conditional statement is used to match user ID but exclude same record ID (unique ID) from sub-query hence the <> Unique ID, and final condition (reg code) is just to match same subsequent registration dept records. I want subsequent records for that same person who went to same reg dept.

So I'm stumped about this Seq = 1. I am not understanding why it is affecting the records returned when I change the AND/WHERE. I have ruled out any outer joins on the tables in the conditional statement; I know that would affect the row count returned when using the AND V the WHERE statement.

Any thoughts as to why the Seq = 1 is affecting the row count when change AND/WHERE would be most welcome.

Many thanks

G

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-23 : 06:36:00

All the other tables having right join so in and condition it consider as part of join.
in where clause it will consider as inner join so that the difference is coming

Regards
Viggneshwar A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources