SQL ROW COUNT AND CTE HELP PLEASE

Question

SQL ROW COUNT AND CTE HELP PLEASE

Ysa

Mr or Mrs. 500

Points: 553
More actions
June 10, 2019 at 2:32 pm

#3651857

Hello, I am new with this
I need help with this CTE, I need a report of all the Invoices paid, I am using 3 tables here but this CTE with ROW COUNT is taking the duplicates await. Usually you paid vendor twice a month or three times. so I need to see all records

Please advise how I can improve it.
;with Mycte as (
SELECT ap_invoice.a_vendor_alph_sort,gl_invoice.j_jnl_source, gl_invoice.j_ref1_vendor
, gl_ledger.a_segment_2 AS Department, gl_ledger.a_segment_3 AS Program, gl_ledger.a_segment_4 AS BATCH
, gl_ledger.a_org, gl_invoice.j_jnl_year_period,

gl_ledger.a_object AS Object, gl_ledger.a_project AS Project,

gl_ledger.s_segment1_desc AS [Fund Desc], gl_ledger.s_segment2_desc AS [Dept Desc],gl_ledger.s_segment3_desc AS [Prog Desc],

gl_ledger.s_segment4_desc AS [Function Desc], gl_ledger.s_segment5_desc AS [Location Desc], gl_ledger.a_object_desc AS [Object Desc],

gl_invoice.a_journal_number AS [Journal Num], gl_invoice.j_ref4_jnl_desc AS [Journal Desc],

gl_invoice.j_gross_amount AS [Gross Amount],

gl_invoice.j_line_comment AS Comments
, ap_invoice.a_check_number AS CheckNumber

, row_number() Over(partition byap_invoice.a_vendor_alph_sort,gl_invoice.j_jnl_source, gl_invoice.j_ref1_vendor
, gl_ledger.a_segment_2  order by ap_invoice.a_check_number desc ) RM

FROM gl_invoice INNER JOIN gl_ledger ON gl_invoice.a_object = gl_ledger.a_object
AND gl_invoice.a_project = gl_ledger.a_project AND gl_invoice.a_org = gl_ledger.a_org
INNER JOIN ap_invoice ON  cast(ap_invoice.a_vendor_number as varchar)=gl_invoice.j_ref1_vendor
and gl_invoice.a_org = ap_invoice.a_org  and gl_invoice.a_object = ap_invoice.a_object

WHERE      gl_ledger.a_org ='010020'
and gl_ledger.a_object ='514311' AND gl_invoice.j_jnl_year_period >=('201901')
)

select * from   Mycte
WHERE RM=1

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

We can't see your tables or your data, so I'm not sure how you expect us to be able to refine your unformatted SQL.

Please post some consumable code, along with sample data (in the form of INSERT statements) & desired results, and someone will post a working solution.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Lowell SSC Guru Points: 323518 More actions · Answer 2

look specifically at your row number function. if you are getting duplicates, identify the column that causes duplicates, and add it to the partition by of hte row_number() statement:

           ROW_NUMBER() OVER (PARTITION BY [ap_invoice].[a_vendor_alph_sort],
                                           [gl_invoice].[j_jnl_source],
                                           [gl_invoice].[j_ref1_vendor],
                                           [gl_ledger].[a_segment_2]
                              ORDER BY [ap_invoice].[a_check_number] DESC
                             ) AS [RM]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ysa Mr or Mrs. 500 Points: 553 More actions · Answer 3

Hello Thank you for replay, I changed to use RANK and it works but know the issue I have is that is no bringing the null values

Attachments:

You must be logged in to view attached files.

Ysa Mr or Mrs. 500 Points: 553 More actions · Answer 4

Hello

here are the tables, my code didnt work because is not bring the unposted invoice

Thank you

Attachments:

You must be logged in to view attached files.