Skip to Content
0
Jun 07, 2020 at 05:01 PM

AR Invoice Wise Sale Summary without Warehouse Name

55 Views

Hi,

I am trying to create a query for AR Invoice wise Sales summary with GP from the each warehouse. And I tried adding to OWHS to this query format but unsuccessful. Can anyone help to add the Warehouse Code & Warehouse Name to fix in my bellow reference.

Thanks.

Mohamamd Firoz

SELECT

T0.docentry AS "S.No", T0.DocNum AS "A/R",T0.[Comments],T0.[DocStatus] AS "Status", CONVERT(VARCHAR(2),MONTH(T0.DocDate)) as Month, DatePart(week, T0.DocDate) [Week], T0.[DocDate] AS "Date", T0.[DocTime] AS "Time", T5.[USER_CODE] AS 'User ID', T5.[U_NAME], T3.[SlpCode] AS "E. ID", T3.[SlpName] AS "Sales Team", T2.[GroupName] AS "Trade", T1.[CardCode], T1.[CardName], (ISNULL(T0.GrosProfit,0))/(ISNULL(T0.DocTotal,1) )*100 As "% GP", T0.[GrosProfit],T0.DocTotal AS "Gross Sales +IVA", ( T0.DocTotal - T0.VatSum) As "Net Sales -IVA" , T0.[VatSum] AS "IVA", T0.[DiscSum] AS "Disc.", T0.[DiscPrcnt] AS "Disc. %", T3.Commission AS "% Of Commission", ( (T0.DocTotal - T0.VatSum)*( T3.Commission/100)) As "Team Commissions",T4.[TrnspCode] AS "D. ID", T4.[TrnspName] AS "Driver Name", T0.[Volume],T0.[Weight],T0.[Address], T0.[Address2], T0.[NumAtCard]

FROM

[dbo].[OINV] T0

INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode

INNER JOIN OCRG T2 ON T1.GroupCode = T2.GroupCode

INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode

LEFT OUTER JOIN OSHP T4 ON T0.TrnspCode = T4.TrnspCode

INNER JOIN OUSR T5 ON T0.UserSign = T5.USERID

WHERE

T0.DocDate >= '[%1]' AND T0.DocDate <= '[%2]'