cancel
Showing results for 
Search instead for 
Did you mean: 

QUERY

former_member522789
Participant
0 Kudos

Hello

I have made the query which is not OK

I would like to have per document date the sum of total net amount for all invoices minus total net amount of credit notes and also total weight invoices less total weight creidt note. the link is the date but if tehre is not a creidt note raised that date the query takes only net total of invoices


SELECT T0.[DocDate], SUM((T0.DocTotal-T0.VATsum-T0.TotalExpns)-(T1.DocTotal-T1.VATsum-T1.TotalExpns)) as 'CA HT', SUM(T0.[Weight]-T1.[Weight]) AS POIDS FROM OINV T0 OUTER JOIN ORIN T1 ON T1.[DocDate]=T0.[DocDate] 
WHERE T0.[DocDate] >=[%0] and  T0.[DocDate] <=[%1] 
GROUP BY T0.[DocDate]

Accepted Solutions (1)

Accepted Solutions (1)

former_member583013
Active Contributor
0 Kudos

By making a change to the Query we could add the Credit Memo's but logically the result you are trying to get will not really be correct.

The reason is it is quite possible that a CM might not be on the same date as the Invoice so matching the two dates would be an incorrect way to join them. Also all CM which are not matching the dates of the Invoice would be excluded.

Let me know how you propose to handle this

Suda

former_member522789
Participant
0 Kudos

SImply the customer want to see the daily turnover

Invoice minus CM

All CM created outside the range date are excluded

We just need to have alll invoices reduced by the CM generated the same date

former_member583013
Active Contributor
0 Kudos

Please try

SELECT T0.DocDate, SUM(T0.DocTotal-T0.VATsum-T0.TotalExpns)-(SELECT SUM(T1.DocTotal-T1.VATsum-T1.TotalExpns) FROM [dbo\].[ORIN\] T1 WHERE T1.DocDate = T0.DocDate),

SUM(T0.Weight)-(SELECT SUM(T1.Weight) FROM [dbo\].[ORIN\] T1 WHERE T1.DocDate = T0.DocDate)

FROM [dbo\].[OINV\] T0

WHERE T0.DocDate >=[%0\] and T0.DocDate <=[%1\]

GROUP BY T0.DocDate

former_member522789
Participant
0 Kudos

Thank you for your help but unfortunately the variables does not give me any result (click on existing values)

I can enter the date manually but the results in the 2 other columns will be empty

former_member583013
Active Contributor
0 Kudos

I tested the query before I posted it and it works fine. Please make sure you are connected to the correct company database

Suda

former_member522789
Participant
0 Kudos

Sorry

I get existing values with my old querry which is giving me an error message after that I select the variable dates

When I copy your query then I want to select existing values I get the following message

'No matching records found'

former_member583013
Active Contributor
0 Kudos

'No matching records found' means there is no data. It does not mean any error.

I am not quite sure why? I have checked a few times and probably it is nothing to do with the query.

Suda

former_member522789
Participant
0 Kudos

Hello experts

I have still an issue with that query it is working when there is credit note (from ORIN) created the same day as invoices but when there is no CN the query is empty when I need just to see invoices....

This query is from Suda

SELECT T0.DocDate, SUM(T0.DocTotal-T0.VATsum-T0.TotalExpns)-(SELECT SUM(T1.DocTotal-T1.VATsum-T1.TotalExpns) FROM [dbo].[ORIN] T1 WHERE T1.DocDate = T0.DocDate), SUM(T0.Weight)-(SELECT SUM(T1.Weight) FROM [dbo].[ORIN] T1 WHERE T1.DocDate = T0.DocDate) FROM [dbo].[OINV] T0 WHERE T0.DocDate >=[%0] and T0.DocDate <=[%1] GROUP BY T0.DocDate

I have made my own query where I have the data correct when there is no CN created but then when there is CN the query is wrong the solution must be to combine the results of the 2 queries any idea how

SELECT T0.DocDate, SUM(T0.DocTotal-T0.VATsum-T0.TotalExpns) AS 'Factures HT', SUM(T0.Weight) AS 'Poids Total Factures', SUM(T1.DocTotal-T1.VATsum-T1.TotalExpns) AS 'Avoirs HT', SUM(T1.Weight) AS 'Poids Total Avoirs' FROM OINV T0 left outer join ORIN T1 on T0.DocDate=T1.DocDate WHERE T0.DocDate >=[%0] and T0.DocDate <=[%1]

GROUP BY T0.DocDate

former_member583013
Active Contributor
0 Kudos

What are you saying is wrong. You need to use a GROUP BY T0.DocDate

SELECT T0.DocDate, SUM(T0.DocTotal-T0.VATsum-T0.TotalExpns) AS 'Factures HT', SUM(T0.Weight) AS 'Poids Total Factures',

SUM(T1.DocTotal-T1.VATsum-T1.TotalExpns) AS 'Avoirs HT', SUM(T1.Weight) AS 'Poids Total Avoirs'

FROM OINV T0 left outer join ORIN T1 on T0.DocDate=T1.DocDate

WHERE T0.DocDate >='[%0\]' and T0.DocDate <='[%1\]'

GROUP BY T0.DocDate

Also, let me know how you want to view the data. It now shows as

Posting Date.........Factures HT....

Is this what you want?.

Former Member
0 Kudos

Hi,

Try this query:

select DocDate, Total=sum(oinv.doctotal)

from

(select oinv.docdate,

total=sum(oinv.doctotal)

from

oinv where docdate between %0 and %1

group by oinv.docdate

UNION ALL

select orin.docdate,

total=sum(orin.doctotal)*-1

from

orin where docdate between %0 and %1

group by orin.docdate

) BaseData

group by docdate

As you can see, the trick is to use a UNION ALL in an inner query to get the base results, one from Invoices and the other from Credit Notes and then calculate the sum in the outer query...I don't think you can get the results any other way.

Of course you will have to modify both of the inner queries to include the other fields you want, as well as the outer query to um them, but you should be able to get the idea from the above.

Answers (1)

Answers (1)

former_member583013
Active Contributor
0 Kudos

Per document date might not be possible as you are calculating the Sum of all Invoices.

Only if you Select the individual invoices would you be able to get the Invoice Date?

Let me know how you would like to present the data?

former_member522789
Participant
0 Kudos

Yes when I wrote the query only for OINV I have the result per date

when I want to combine ORIN with OINV I get an error

SELECT T0.[DocDate], SUM(T0.DocTotal-T0.VATsum-T0.TotalExpns) AS 'CA HT', SUM(T0.[Weight]) AS 'POIDS' FROM OINV T0

WHERE T0.[DocDate] >=[%0] and T0.[DocDate] <=[%1]

GROUP BY T0.[DocDate]