March 17, 2017 at 12:04 pm
I have a query for sales by industry. The invoices and the credits are stored in 2 separate groups of tables. The query below gives me the correct numbers but it places the sales and the credits in the same column.
Declare @FDate DateTime, @TDate DateTime
Select @FDate = min(Q.Refdate) from OJDT Q where Q.Refdate >='20160101'
Select @TDate = max(R.Refdate) from OJDT R where R.Refdate <='20161231'
Select 'AR Invoice'[Type]
,C.IndustryC
,H.IndName
,SUM(B.LineTotal- (B.LineTotal*isnull(A.DiscPrcnt,0)/100))[Sales]
from OCRD C
Left Outer Join OINV A on A.CardCode = C.CardCode
Left Outer Join INV1 B on A.Docentry = B.DocEntry
Left Outer Join OOND H on C.IndustryC = H.IndCode
Where A.Canceled = 'N'
and A.DocDate >=@FDate and A.DocDate <=@TDate
Group by C.IndustryC
,H.IndName
Union All
Select 'AR Credit Note'[Type]
,C.IndustryC
,H.IndName
,Sum(-BB.LineTotal+ (BB.LineTotal*isnull(AA.DiscPrcnt,0)/100))[Credits]
from OCRD C
Left Outer Join ORIN AA on AA.CardCode = C.CardCode
Left Outer Join RIN1 BB on AA.Docentry = BB.DocEntry
Left Outer Join OOND H on C.IndustryC = H.IndCode
Where AA.Canceled = 'N'
and AA.DocDate >=@FDate and AA.DocDate <=@TDate
Group by C.IndustryC
,H.IndName
Order BY H.IndName
Here is a sample of the results of this query with correct figures.
Type IndustryC IndName Sales
AR Invoice 1 AD SPECIALTY 495345.54
AR Credit Note 1 AD SPECIALTY -8488.14
AR Invoice 25 AUTO DLR & SP 26454.00
AR Invoice 18 CAR RENTAL 499850.40
AR Credit Note 18 CAR RENTAL -2031.10
My desired result is to have the sales in one column and the credits in a separate column. Ultimately adding a third column that gives a total for the 2 columns (Sales - Credits)
Here is my attempt to get rid of the union which gives me the 2 columns I need but it does not return the correct figures
Declare @FDate DateTime, @TDate DateTime
Select @FDate = min(Q.Refdate) from OJDT Q where Q.Refdate >='20160101'
Select @TDate = max(R.Refdate) from OJDT R where R.Refdate <='20161231'
Select --'AR Invoice'[Type]
C.IndustryC
,H.IndName
,SUM(B.LineTotal- (B.LineTotal*isnull(A.DiscPrcnt,0)/100))[LineTotalSales]
,Sum(-BB.LineTotal+ (BB.LineTotal*isnull(AA.DiscPrcnt,0)/100))[LineTotalCredits]
from OCRD C
Left Outer Join OINV A on A.CardCode = C.CardCode
Left Outer Join INV1 B on A.Docentry = B.DocEntry
Left Outer Join ORIN AA on AA.CardCode = C.CardCode
Left Outer Join RIN1 BB on AA.Docentry = BB.DocEntry
Inner Join OOND H on C.IndustryC = H.IndCode
Where (A.Canceled = 'N'
and A.DocDate >=@FDate and A.DocDate <=@TDate)
or (AA.Canceled = 'N'
and AA.DocDate >=@FDate and AA.DocDate <=@TDate)
Group by C.IndustryC
,H.IndName
Order BY H.IndName
Here is a sample of the results with 2 columns but wrong figures
IndustryC IndName LineTotalSales LineTotalCredits
1 AD SPECIALTY 5480357.01 -9720525.20
25 AUTO DLR & SP 26454.00 NULL
18 CAR RENTAL 2377375.32 -3894480.16
3 CHAIN 56.06 NULL
4 DRUG 10616.55 -10160.64
My apologies for any breach of etiquette here it is my first time posting to a forum like this. Your help is greatly appreciated.
March 17, 2017 at 1:53 pm
Something like this might work. It's untested due to lack of sample data.Declare @FDate DateTime, @TDate DateTime Select @FDate = min(Q.Refdate) from OJDT Q where Q.Refdate >='20160101'
Select @TDate = max(R.Refdate) from OJDT R where R.Refdate <='20161231'
WITH cteInvoices AS(
Select
C.IndustryC
,SUM(B.LineTotal- (B.LineTotal*isnull(A.DiscPrcnt,0)/100))[Sales]
from OCRD C
Left Outer Join OINV A on A.CardCode = C.CardCode
Left Outer Join INV1 B on A.Docentry = B.DocEntry
Where A.Canceled = 'N'
and A.DocDate >=@FDate and A.DocDate <=@TDate
Group by C.IndustryC
)
, cteCreditNotes AS(
Select
C.IndustryC
,Sum(-BB.LineTotal+ (BB.LineTotal*isnull(AA.DiscPrcnt,0)/100))[Credits]
from OCRD C
Left Outer Join ORIN AA on AA.CardCode = C.CardCode
Left Outer Join RIN1 BB on AA.Docentry = BB.DocEntry
Where AA.Canceled = 'N'
and AA.DocDate >=@FDate and AA.DocDate <=@TDate
Group by C.IndustryC
)
SELECT i.IndustryC
,H.IndName
,i.Sales
,c.Credits
FROM cteInvoices i
LEFT OUTER JOIN cteCreditNotes c ON i.IndustryC = c.IndustryC
Left Outer Join OOND H on C.IndustryC = H.IndCode
Order BY H.IndName;
March 17, 2017 at 2:23 pm
This is great! Thank you!
I had to add a semi colon before the With clause and now it gives me correct figures in 2 columns. There is just one issue, it returns NULL for the industry name if the credits are NULL.
Example Results
IndustryC IndName Sales Credits
3 NULL 56.06 NULL
4 NULL 5629.25 NULL
5 NULL 244.48 NULL
19 NULL 9831.51 NULL
1 AD SPECIALTY 495345.54 -8488.14
18 CAR RENTAL 499850.40 -2031.10
6 FOREIGN 115421.90 -49.59
7 GOV/OTHER 72208.67 -202.00
Might you have a solution for that?
Once again thank you for all your help.
March 17, 2017 at 2:40 pm
Actually I figured it out. I had to change the last Join from c.IndustryC = H.IndCode to i.IndustryC = H.IndCode
FROM cteInvoices i
Left Outer JOIN cteCreditNotes c ON i.IndustryC = c.IndustryC
Left Outer Join OOND H on c.IndustryC = H.IndCode
Order BY H.IndName;
To
FROM cteInvoices i
Left Outer JOIN cteCreditNotes c ON i.IndustryC = c.IndustryC
Left Outer Join OOND H on i.IndustryC = H.IndCode
Order BY H.IndName;
Thanks again. I understand the temporary tables now
On to pivots .
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply