receipts report - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

receipts report

Author

Topic

kuku sql
Starting Member

6 Posts

Posted - 2012-10-28 : 10:46:22

I am preparing a receipts report .
When the Invoice is not linked to the receipt, the receipt does not appear in the report. its Important to me that these receipts will be in the report. How do I get them to appear?
The following query:

SELECT T0.DocNum, T0.DocDate,
T1.DocNum, T2.DocDate , t2.docduedate, T1.sumapplied, t0.slpcode,
datediff(day, T0.DocDate, T2.DocDate)
- case when MONTH(T0.docdate)= 2 then 28-day(T0.docdate)
WHEN MONTH(T0.docdate) IN (4, 6, 9, 11) then 30-day(T0.docdate)
else 31-day(T0.docdate) end as '?????' ,
datediff(day, T0.DocDate, T2.DocdueDate)
- case when MONTH(T0.docdate)= 2 then 28-day(T0.docdate)
WHEN MONTH(T0.docdate) IN (4, 6, 9, 11) then 30-day(T0.docdate)
else 31-day(T0.docdate) end as '??????'
FROM OINV T0 inner JOIN RCT2 T1 ON T0.DocEntry = T1.DocEntry
inner JOIN ORCT T2 ON T0.ReceiptNum = T2.DocNum
WHERE T0.[SlpCode] =[%0] AND T2.[DocDate] >=[%1] AND T2.[DocDate] <=[%2] AND (T2.JrnlMemo != N'?????')

Can anyone help me?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-28 : 12:05:07

When you use INNER JOIN only rows that match based on the join condition are returned. If you want to preserve rows in one table even if there is no matching row in another table, you should use LEFT (or RIGHT join as appropriate). In your case, I would change WHERE clause of the query like shown below; If that does not do what you are expecting it to do, can you post some sample data?

....
FROM   OINV T0
       RIGHT JOIN RCT2 T1
            ON  T0.DocEntry = T1.DocEntry AND T0.[SlpCode] = [%0]
       RIGHT JOIN ORCT T2
            ON  T0.ReceiptNum = T2.DocNum
WHERE  T2.[DocDate] >= [%1]
       AND T2.[DocDate] <= [%2]
       AND (T2.JrnlMemo != N'?????')

kuku sql
Starting Member

6 Posts

Posted - 2012-10-28 : 14:01:02

Thanks for trying but it did not work.
in Most of the receipts, there are invoices linked to receipt, so we can know wich invoices where paid.
When there is no linked invoices to the receipt, the receipt does not appear at the report at all. i need a full receipt report. I need the missing receipts in the report with doctotal (t2.doctotal).
Can you try again?
Thank you in advance

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-28 : 20:28:06

From the information you posted, it is difficult for me (or someone else) to figure out which table has receipts and the kind of report you are looking for. I see that there is a Receipt number in OINV and one in ORCT. Based on the abbreviated names, I am guessing that OINV is the invoice table and ORCT is the receipt table.

If that is true, what you want to do is something like shown below, but more likely than not, this would not give you exactly what you are looking for because I am working from incomplete information based on educated guesses.

Also, I don't know what the %1, %2 etc. are. I am assuming that they are place holders in a client program that will get replaced with actual values.

If you can post some sample data and the DDL for the tables, someone on the forum would be able to give you precise answers. This article describes how to get the DDL's, how to post etc: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

SELECT T0.DocNum,
       T0.DocDate,
       T1.DocNum,
       T2.DocDate,
       t2.docduedate,
       T1.sumapplied,
       t0.slpcode,
       DATEDIFF(DAY, T0.DocDate, T2.DocDate)
       - CASE 
              WHEN MONTH(T0.docdate) = 2 THEN 28 -DAY(T0.docdate)
              WHEN MONTH(T0.docdate) IN (4, 6, 9, 11) THEN 30 -DAY(T0.docdate)
              ELSE 31 -DAY(T0.docdate)
         END AS '?????',
       DATEDIFF(DAY, T0.DocDate, T2.DocdueDate)
       - CASE 
              WHEN MONTH(T0.docdate) = 2 THEN 28 -DAY(T0.docdate)
              WHEN MONTH(T0.docdate) IN (4, 6, 9, 11) THEN 30 -DAY(T0.docdate)
              ELSE 31 -DAY(T0.docdate)
         END AS '??????'
FROM
      ORCT T2
      LEFT JOIN OINV T0
         ON T0.ReceiptNum = T2.DocNum AND T0.[SlpCode] = [%0]
      LEFT JOIN RCT2 T1
         ON T0.DocEntry = T1.DocEntry
WHERE
      T2.[DocDate] >= [%1]
       AND T2.[DocDate] <= [%2]
       AND (T2.JrnlMemo != N'?????');

waterduck
Aged Yak Warrior

982 Posts

Posted - 2012-10-28 : 20:33:33

[CODE]
SELECT
T0.DocNum
, T0.DocDate
, T1.DocNum
, T2.DocDate
, t2.docduedate
, T1.sumapplied
, t0.slpcode
, datediff(day, T0.DocDate, T2.DocDate) - case when MONTH(T0.docdate)= 2 then 28-day(T0.docdate)
WHEN MONTH(T0.docdate) IN (4, 6, 9, 11) then 30-day(T0.docdate)
else 31-day(T0.docdate) end as '?????'
, datediff(day, T0.DocDate, T2.DocdueDate) - case when MONTH(T0.docdate)= 2 then 28-day(T0.docdate)
WHEN MONTH(T0.docdate) IN (4, 6, 9, 11) then 30-day(T0.docdate)
else 31-day(T0.docdate) end as '??????'

FROM ORCT T2
LEFT JOIN OINV T0 ON T2.DocNum = T0.ReceiptNum
LEFT JOIN RCT2 T1 ON T1.DocEntry = T0.DocEntry
WHERE T0.[SlpCode] =[%0]
AND T2.[DocDate] >=[%1]
AND T2.[DocDate] <=[%2]
AND (T2.JrnlMemo != N'?????')

[/CODE]

kuku sql
Starting Member

6 Posts

Posted - 2012-10-29 : 04:35:28

Thank you for your efforts, I'm still stuck.
I'll try to explain better:

The purpose of the report: calculation of commission due to the sales agent (t0.slpcode) for a particular month (T2.[DocDate] >=[%1]
AND T2.[DocDate] <=[%2]).

The fee is determined by the quality of the collection from the customers.
To do this I calculated the number of days elapsed from the date of invoice
Until payment.
The report works on three tables:
oinv- Invoices table
orct- receipts table
rct2- Reception rows table, in each row appears one invoice .
For example:
we Received payment from a client. the person who produces the receipt selects the invoices paid on this payment and link them to the receipt.
When we get a genneral payment without invoices linked to the receipt (rct2 is empty) the receipt does not appear in my report. I want these receipts to appear with the total (t2.doctotal).
I'm sorry if my explanations are not good enough, I'm new at, and English is not my native language, I try to be as clear as I can.
Thank you all

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources