cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Newbie

Former Member
0 Kudos

Hi, I am trying to create a report for a client, I need to get all invoices for some nominals, plus journal vouchers. then I am trying to pull it out by project.

I know I am asking a lot, but any pointers would be gratefully accepted. I have gotten only so far, the journal vouchers come, but not the project or the invoices.

Below is my attempt

SELECT T0.[AcctCode], T1.[Debit], T1.[Credit], T2.[RefDate], T3.[LineTotal], T4.[DocDate], T2.[BtfStatus], T5.[PrjCode]

FROM [dbo].[OACT]  T0 LEFT JOIN BTF1 T1 ON T0.[AcctCode] = T1.[Account]

LEFT JOIN OBTF T2 ON T1.[TransId] = T2.[BatchNum]

left JOIN INV1 T3 ON T0.[AcctCode] = T3.[AcctCode]

LEFT JOIN OINV T4 ON T3.[DocEntry] = T4.[DocEntry]

LEFT JOIN OPRJ T5 ON T2.Project = T5.PrjCode

where T0.[AcctCode] between '100000' AND '200000'

--and T5.PrjCode ='{?Project Code}'

order by T0.AcctCode

Thanks

Rory

Accepted Solutions (1)

Accepted Solutions (1)

luisdarui
Active Contributor
0 Kudos

Hi Rory,

There is a space for Business One topics, maybe they can help you with this.

And to be honest, trying to help you with a logic of the business would be really complicated. I also do not understand whether you're new to SQL Server or new to creating reports, but if you're new to SQL Server, Microsoft provide a lot of courses to get known to SQL Server and databases at http://www.microsoftvirtualacademy.com/

Best Regards,

Luis Darui

former_member206857
Active Participant
0 Kudos

Have that business request to an ABAPer to generate a report, don't do that at the sql level.

Answers (0)