Skip to Content
0

Combining Queries for Sales Data B1

Dec 16, 2016 at 05:59 PM

30

avatar image

Hello There,

I recently took a course in SQL Queries but am COMPLETELY green. I am trying to extract sales data for our fiscal end. Unfortunately we have changed our invoicing process a couple of times and its becoming more complex than originally anticipated. I would like to combine the two queries below to include A/R Invoices and ARDPI in one report. I also need to include all A/R Credit Memos as negative numbers (omit C00000). Any help (with explanation) would be greatly appreciated!

Query 1 (A/R Inv):

SELECT T0.[DocEntry], T0.[ItemCode], T2.[ItemName], T0.[DocDate], T0.[Quantity], T0.[Price], (T0.[Quantity] * T0.[Price]) AS TOTAL, T1.[CardCode], T1.[CardName], T1.[Country], T1.[State1], T3.[descript] FROM INV1 T0 INNER JOIN OCRD T1 ON T0.[BaseCard] = T1.[CardCode] INNER JOIN OITM T2 ON T0.[ItemCode] = T2.[ItemCode] INNER JOIN OTER T3 ON T1.Territory = T3.territryID

Query 2 (ARDPI):

SELECT T0.[CardCode], T0.[CardName], T0.[Country], T0.[State1], T3.[territryID], T3.[descript], T1.[ItemCode], T1.[Quantity], T1.[Dscription], T1.[Price], T1.[DocDate], T2.[ItemName],T1.[DocEntry] FROM OCRD T0 INNER JOIN DPI1 T1 ON T0.[CardCode] = T1.[BaseCard] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] INNER JOIN OTER T3 ON T0.Territory = T3.territryID

SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers