on 06-19-2008 2:51 PM
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]
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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?.
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.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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]
User | Count |
---|---|
88 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.