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
DateCompleted | GtrReference | SourceWarehouse | TargetWarehouse | DateCreated | EntryType | ControlAccount | InitialValue | Operator | FirstDesc | SecondDesc |
---|---|---|---|---|---|---|---|---|---|---|
2 | 2023-02-17 00:00:00.000 | PI2302027 | W | 01 | 2023-02-15 00:00:00.000 | W | 1610 | 1441.40 | ahsiao | IN TRANSIT WAREHOUSE |
723 | 2023-02-17 00:00:00.000 | MDSITA-220506 | W | 01 | 2022-05-10 00:00:00.000 | W | 1610 | 22239.24 | ahsiao | IN TRANSIT WAREHOUSE |
724 | 2023-02-17 00:00:00.000 | MDSITA-220506 | W | 01 | 2022-05-10 00:00:00.000 | W | 1610 | 22239.24 | ahsiao | IN TRANSIT WAREHOUSE |
725 | 2023-02-17 00:00:00.000 | MDSITA-220506 | W | 01 | 2022-05-10 00:00:00.000 | W | 1610 | 22239.24 | ahsiao | IN TRANSIT WAREHOUSE |
726 | 2023-02-17 00:00:00.000 | MDSITA-220506 | W | 01 | 2022-05-10 00:00:00.000 | W | 1610 | 22239.24 | ahsiao | IN 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
RN | Complete | Line | DateMonth | DateYear | GtrReference | SourceWarehouse | TargetWarehouse | DateCreated | ExpectedDueDate | EntryType | ControlAccount | InitialValue | Operator | FirstDesc | SecondDesc | Movement | DateCompleted |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Y | 1 | 2 | 2023 | PI2302027 | W | 01 | 2023-02-15 00:00:00.000 | 2023-02-22 00:00:00.000 | W | 1610 | 1441.40 | ahsiao | IN TRANSIT WAREHOUSE | MAIN WAREHOUSE | I | 2023-02-17 00:00:00.000 |
1 | Y | 1 | 5 | 2022 | MDSITA-220506 | W | 01 | 2022-05-10 00:00:00.000 | 2022-05-13 00:00:00.000 | W | 1610 | 22239.24 | ahsiao | IN TRANSIT WAREHOUSE | MAIN WAREHOUSE | I | 2023-02-17 00:00:00.000 |
2 | Y | 2 | 5 | 2022 | MDSITA-220506 | W | 01 | 2022-05-10 00:00:00.000 | 2022-05-13 00:00:00.000 | W | 1610 | 22239.24 | ahsiao | IN TRANSIT WAREHOUSE | MAIN WAREHOUSE | I | 2023-02-17 00:00:00.000 |
3 | Y | 3 | 5 | 2022 | MDSITA-220506 | W | 01 | 2022-05-10 00:00:00.000 | 2022-05-13 00:00:00.000 | W | 1610 | 22239.24 | ahsiao | IN TRANSIT WAREHOUSE | MAIN WAREHOUSE | I | 2023-02-17 00:00:00.000 |
4 | Y | 4 | 5 | 2022 | MDSITA-220506 | W | 01 | 2022-05-10 00:00:00.000 | 2022-05-13 00:00:00.000 | W | 1610 | 22239.24 | ahsiao | IN TRANSIT WAREHOUSE | MAIN WAREHOUSE | I | 2023-02-17 00:00:00.000 |
5 | Y | 5 | 5 | 2022 | MDSITA-220506 | W | 01 | 2022-05-10 00:00:00.000 | 2022-05-13 00:00:00.000 | W | 1610 | 22239.24 | ahsiao | IN TRANSIT WAREHOUSE | MAIN WAREHOUSE | I | 2023-02-17 00:00:00.000 |
6 | Y | 6 | 5 | 2022 | MDSITA-220506 | W | 01 | 2022-05-10 00:00:00.000 | 2022-05-13 00:00:00.000 | W | 1610 | 22239.24 | ahsiao | IN TRANSIT WAREHOUSE | MAIN WAREHOUSE | I | 2023-02-17 00:00:00.000 |
7 | Y | 7 | 5 | 2022 | MDSITA-220506 | W | 01 | 2022-05-10 00:00:00.000 | 2022-05-13 00:00:00.000 | W | 1610 | 22239.24 | ahsiao | IN TRANSIT WAREHOUSE | MAIN WAREHOUSE | I | 2023-02-17 00:00:00.000 |
8 | Y | 8 | 5 | 2022 | MDSITA-220506 | W | 01 | 2022-05-10 00:00:00.000 | 2022-05-13 00:00:00.000 | W | 1610 | 22239.24 | ahsiao | IN TRANSIT WAREHOUSE | MAIN WAREHOUSE | I | 2023-02-17 00:00:00.000 |
9 | Y | 9 | 2 | 2023 | MDSITA-220506 | W | 01 | 2022-05-10 00:00:00.000 | 2022-05-13 00:00:00.000 | W | 1610 | 22239.24 | ahsiao | IN TRANSIT WAREHOUSE | MAIN WAREHOUSE | I | 2023-02-17 00:00:00.000 |
What am i doing wrong in my main query?
1条答案
按热度按时间fruv7luv1#
row_number()
creates the sequence for the inner query. But all ofWHERE
clause criteria are applied in the outer query. This filters the rows after the sequence was created, leaving the gaps as shown.