SQL Server Row Number Subquery Not Iterating Properly

js81xvg6  于 2023-02-18  发布在  Perl
关注(0)|答案(1)|浏览(148)

I have this query which generates the below report

select
RN,
DateCompleted,
GtrReference,
SourceWarehouse,
TargetWarehouse,
DateCreated,
EntryType,
ControlAccount,
InitialValue,
Operator,
FirstDesc,
SecondDesc
from
(
    select
    row_number() over(partition by a.GtrReference order by a.GtrReference desc) as RN,
    --concat(d.TransfCompYear, case when len(d.TransfCompPeriod) = 1 then '0' end, d.TransfCompPeriod) as DateCompleted,
    a.Complete,
    d.Line,
  d.TransfCompPeriod as DateMonth,
  d.TransfCompYear as DateYear,
  a.GtrReference as GtrReference,
  a.SourceWarehouse as SourceWarehouse, 
  max(a.TargetWarehouse) over (partition by a.GtrReference) as TargetWarehouse, 
  a.DateCreated as DateCreated,
  COALESCE(d.ExpectedDueDate, d.ExpectedDueDate) as ExpectedDueDate,
  a.EntryType as EntryType, 
  a.ControlAccount as ControlAccount, 
  max(a.InitialValue) over (partition by a.GtrReference) as InitialValue,
  a.Operator as Operator,
  b.Description as FirstDesc, 
  c.Description as SecondDesc,
  e.TransactionTyp as Movement,
  e.TransactionDate as DateCompleted
FROM 
    [GtrMaster] a  
    LEFT JOIN [InvWhControl] b  
     ON (a.SourceWarehouse = b.Warehouse) 
    LEFT JOIN [InvWhControl] c  
     ON (a.TargetWarehouse = c.Warehouse) 
    LEFT JOIN [GtrDetail] d  
     ON (a.GtrReference = d.GtrReference) 
    LEFT JOIN [GtrTransactions] e
     ON (a.GtrReference = e.GtrReference) 
) as i
    WHERE ( i.EntryType = 'W' OR i.EntryType = 'S' ) 
    AND i.Complete = 'Y' AND i.GtrReference <> '' 
     --and i.Line = '1'
     and i.DateCompleted >= DATEADD(hh,0,dateadd(DAY, datediff(day, +90, getdate()),0))
     --and InitialValue = '4732.53'
     and Movement = 'I'
    -- and RN = '1'
    ORDER BY i.DateCompleted desc
DateCompletedGtrReferenceSourceWarehouseTargetWarehouseDateCreatedEntryTypeControlAccountInitialValueOperatorFirstDescSecondDesc
22023-02-17 00:00:00.000PI2302027W012023-02-15 00:00:00.000W16101441.40ahsiaoIN TRANSIT WAREHOUSE
7232023-02-17 00:00:00.000MDSITA-220506W012022-05-10 00:00:00.000W161022239.24ahsiaoIN TRANSIT WAREHOUSE
7242023-02-17 00:00:00.000MDSITA-220506W012022-05-10 00:00:00.000W161022239.24ahsiaoIN TRANSIT WAREHOUSE
7252023-02-17 00:00:00.000MDSITA-220506W012022-05-10 00:00:00.000W161022239.24ahsiaoIN TRANSIT WAREHOUSE
7262023-02-17 00:00:00.000MDSITA-220506W012022-05-10 00:00:00.000W161022239.24ahsiaoIN TRANSIT WAREHOUSE

For some reason, I'm getting 2, 723, 724, 725 etc for row number instead of iteration.. But when I run the query outside the subquery, i get the correct row numbers.. so

select
    row_number() over(partition by a.GtrReference order by a.GtrReference) as RN,
    --concat(d.TransfCompYear, case when len(d.TransfCompPeriod) = 1 then '0' end, d.TransfCompPeriod) as DateCompleted,
    a.Complete,
    d.Line,
  d.TransfCompPeriod as DateMonth,
  d.TransfCompYear as DateYear,
  a.GtrReference as GtrReference,
  a.SourceWarehouse as SourceWarehouse, 
  max(a.TargetWarehouse) over (partition by a.GtrReference) as TargetWarehouse, 
  a.DateCreated as DateCreated,
  COALESCE(d.ExpectedDueDate, d.ExpectedDueDate) as ExpectedDueDate,
  a.EntryType as EntryType, 
  a.ControlAccount as ControlAccount, 
  max(a.InitialValue) over (partition by a.GtrReference) as InitialValue,
  a.Operator as Operator,
  b.Description as FirstDesc, 
  c.Description as SecondDesc,
  e.TransactionTyp as Movement,
  e.TransactionDate as DateCompleted
FROM 
    [GtrMaster] a  
    LEFT JOIN [InvWhControl] b  
     ON (a.SourceWarehouse = b.Warehouse) 
    LEFT JOIN [InvWhControl] c  
     ON (a.TargetWarehouse = c.Warehouse) 
    LEFT JOIN [GtrDetail] d  
     ON (a.GtrReference = d.GtrReference) 
    LEFT JOIN [GtrTransactions] e
     ON (a.GtrReference = e.GtrReference) 
    WHERE ( a.EntryType = 'W' OR a.EntryType = 'S' ) 
     AND a.Complete = 'Y' AND a.GtrReference <> '' 
     --and i.Line = '1'
     and e.TransactionDate >= DATEADD(hh,0,dateadd(DAY, datediff(day, +90, getdate()),0))
     --and InitialValue = '4732.53'
     and e.TransactionTyp = 'I'
    ORDER BY e.TransactionDate desc
RNCompleteLineDateMonthDateYearGtrReferenceSourceWarehouseTargetWarehouseDateCreatedExpectedDueDateEntryTypeControlAccountInitialValueOperatorFirstDescSecondDescMovementDateCompleted
1Y122023PI2302027W012023-02-15 00:00:00.0002023-02-22 00:00:00.000W16101441.40ahsiaoIN TRANSIT WAREHOUSEMAIN WAREHOUSEI2023-02-17 00:00:00.000
1Y152022MDSITA-220506W012022-05-10 00:00:00.0002022-05-13 00:00:00.000W161022239.24ahsiaoIN TRANSIT WAREHOUSEMAIN WAREHOUSEI2023-02-17 00:00:00.000
2Y252022MDSITA-220506W012022-05-10 00:00:00.0002022-05-13 00:00:00.000W161022239.24ahsiaoIN TRANSIT WAREHOUSEMAIN WAREHOUSEI2023-02-17 00:00:00.000
3Y352022MDSITA-220506W012022-05-10 00:00:00.0002022-05-13 00:00:00.000W161022239.24ahsiaoIN TRANSIT WAREHOUSEMAIN WAREHOUSEI2023-02-17 00:00:00.000
4Y452022MDSITA-220506W012022-05-10 00:00:00.0002022-05-13 00:00:00.000W161022239.24ahsiaoIN TRANSIT WAREHOUSEMAIN WAREHOUSEI2023-02-17 00:00:00.000
5Y552022MDSITA-220506W012022-05-10 00:00:00.0002022-05-13 00:00:00.000W161022239.24ahsiaoIN TRANSIT WAREHOUSEMAIN WAREHOUSEI2023-02-17 00:00:00.000
6Y652022MDSITA-220506W012022-05-10 00:00:00.0002022-05-13 00:00:00.000W161022239.24ahsiaoIN TRANSIT WAREHOUSEMAIN WAREHOUSEI2023-02-17 00:00:00.000
7Y752022MDSITA-220506W012022-05-10 00:00:00.0002022-05-13 00:00:00.000W161022239.24ahsiaoIN TRANSIT WAREHOUSEMAIN WAREHOUSEI2023-02-17 00:00:00.000
8Y852022MDSITA-220506W012022-05-10 00:00:00.0002022-05-13 00:00:00.000W161022239.24ahsiaoIN TRANSIT WAREHOUSEMAIN WAREHOUSEI2023-02-17 00:00:00.000
9Y922023MDSITA-220506W012022-05-10 00:00:00.0002022-05-13 00:00:00.000W161022239.24ahsiaoIN TRANSIT WAREHOUSEMAIN WAREHOUSEI2023-02-17 00:00:00.000

What am i doing wrong in my main query?

fruv7luv

fruv7luv1#

row_number() creates the sequence for the inner query. But all of WHERE clause criteria are applied in the outer query. This filters the rows after the sequence was created, leaving the gaps as shown.

相关问题