Detailed Sales Report by Whs

[Former Member]

Hi Everyone,

We keep getting an error on this report and all I want to do is be able to view the Vendor CardNam that we bought the item from. Below is the query and under is the errors I am getting:

SELECT

T0.[CardCode], T0.[CardName], T0.[DocDate], T0.[DocTotal] AS 'TotalAmount', T1.[Dscription], T1.[Quantity], T0.[DocType], T1.[ItemCode], T2.[ItemName], T2.[ItmsGrpCod], T2.[CardCode],

T3.[ItmsGrpNam] AS ItemGroupName, T1.[WhsCode], T5.[WhsName], T0.[DocNum],

T1.[GrssProfit] AS 'GrossProfit', T1.[Price], T1.[StockPrice]

FROM

OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod INNER JOIN OWHS T5 ON T1.WhsCode = T5.WhsCode INNER JOIN

WHERET0.[DocType]='i'

UNION ALL

SELECT T6.[CardCode], T6.[CardName], T6.[DocDate], T6.[DocTotal], T7.[Dscription], T7.[Quantity] AS 'Quantity', T6.[DocType], T7.[ItemCode], T8.[ItemName], T8.[ItmsGrpCod],

T9.[ItmsGrpNam] AS 'ItemGroupName',T7.[WhsCode], T10.[WhsName], T6.[DocNum], T7.[GrssProfit] AS 'GrossProfit',T7.[Price], T7.[StockPrice]

FROM

ORIN T6  INNER JOIN RIN1 T7 ON T6.DocEntry = T7.DocEntry INNER JOIN OITM T8 ON T7.ItemCode = T8.ItemCode INNER JOIN OITB T9 ON T8.ItmsGrpCod = T9.ItmsGrpCod INNER JOIN OWHS T10 ON T7.WhsCode = T10.WhsCode INNER JOIN

1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '='.

2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near 'JOIN'.

3). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement 'Blanket Agr    18/05/2015  17:34:19  -1

1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'WHERE'.

2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near 'JOIN'.

3). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statem    18/05/2015  17:30:08  -1

Please help.

Sonia

[former_member184146]

Hi,

in the 2nd part of query you were missing the cardcode field and also the last part of join i.e. inner join was wrong , otherwise everything is fine .

Try the below query

SELECT distinct

T0.[CardCode], T0.[CardName], T0.[DocDate], T0.[DocTotal] AS 'TotalAmount', T1.[Dscription],

T1.[Quantity], T0.[DocType], T1.[ItemCode], T2.[ItemName], T2.[ItmsGrpCod], T2.[CardCode],

T3.[ItmsGrpNam] AS ItemGroupName, T1.[WhsCode], T5.[WhsName], T0.[DocNum],

T1.[GrssProfit] AS 'GrossProfit', T1.[Price], T1.[StockPrice]

FROM

OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2

ON T1.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod

INNER JOIN OWHS T5 ON T1.WhsCode = T5.WhsCode

WHERE T0.[DocType]='i'

UNION ALL

SELECT T6.[CardCode], T6.[CardName], T6.[DocDate], T6.[DocTotal] as 'TotalAmount', T7.[Dscription], T7.[Quantity],

T6.[DocType], T7.[ItemCode], T8.[ItemName], T8.[ItmsGrpCod],t6.CardCode,

T9.[ItmsGrpNam] AS 'ItemGroupName',T7.[WhsCode], T10.[WhsName], T6.[DocNum], T7.[GrssProfit] AS 'GrossProfit',T7.[Price], T7.[StockPrice]

FROM

ORIN T6  INNER JOIN RIN1 T7 ON T6.DocEntry = T7.DocEntry

INNER JOIN OITM T8 ON T7.ItemCode = T8.ItemCode

INNER JOIN OITB T9 ON T8.ItmsGrpCod = T9.ItmsGrpCod

INNER JOIN OWHS T10 ON T7.WhsCode = T10.WhsCode

where t6.DocType='i'

--Manish

[former_member212181]

Hi Sonia,

Please try below.

SELECT 'AR Invoice'[Type], T0.[CardCode][Customer Code], T0.[CardName][Customer Name]

  , T0.[DocDate], T0.[DocTotal] AS 'TotalAmount'

  , T1.[Dscription], T1.[Quantity], T0.[DocType]

  , T1.[ItemCode], T2.[ItemName], T2.[ItmsGrpCod]

  , T2.CardCode[Supplier Code], T6.CardName[Supplier Name]

  ,T3.[ItmsGrpNam] AS ItemGroupName

  , T1.[WhsCode], T5.[WhsName], T0.[DocNum]

  ,T1.[GrssProfit] AS 'GrossProfit', T1.[Price]

  ,T1.LineTotal- (T1.LineTotal*isnull(T0.DiscPrcnt,0)/100)[LineTotalLC]

  , T1.[StockPrice]

FROM OINV T0

  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

  INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

  INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod

  INNER JOIN OWHS T5 ON T1.WhsCode = T5.WhsCode

  Left Outer Join OCRD T6 On T2.CardCode = T6.CardCode

WHERE T0.[DocType]='I' and T0.DocDate>=[%0] and T0.DocDate<=[%1]

UNION ALL----AR CreditNote

SELECT 'AR Credit Note'[Type], T0.[CardCode][Customer Code], T0.[CardName][Customer Name]

  , T0.[DocDate], T0.[DocTotal] AS 'TotalAmount'

  , T1.[Dscription], T1.[Quantity], T0.[DocType]

  , T1.[ItemCode], T2.[ItemName], T2.[ItmsGrpCod]

  , T2.CardCode[Supplier Code], T6.CardName[Supplier Name]

  ,T3.[ItmsGrpNam] AS ItemGroupName

  , T1.[WhsCode], T5.[WhsName], T0.[DocNum]

  ,-T1.[GrssProfit] AS 'GrossProfit', T1.[Price]

  ,-T1.LineTotal+ (T1.LineTotal*isnull(T0.DiscPrcnt,0)/100)[LineTotal]

  , T1.[StockPrice]

FROM ORIN T0

  INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry

  INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

  INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod

  INNER JOIN OWHS T5 ON T1.WhsCode = T5.WhsCode

  Left Outer Join OCRD T6 On T2.CardCode = T6.CardCode

WHERE T0.[DocType]='I' and T0.DocDate>=[%0] and T0.DocDate<=[%1]

Order By T0.[DocDate]

Thanks

Unnikrishnan

Message was edited by: Unnikrishnan Balan

[Former Member]

This one is great I get to select the date range and it accounts for the credit notes that I didnt think about on the last one.

[former_member212181]

Hi Sonia,

If you got the answer, please close the thread by marking Correct /  helpful answer.

Thanks

Unnikrishnan

[Former Member]

Hi!

Hope this helps.

SELECT

T0.[CardCode], T0.[CardName], T0.[DocDate],

T0.[DocTotal] AS 'TotalAmount', T1.[Dscription], T1.[Quantity],

T0.[DocType], T1.[ItemCode], T2.[ItemName],

T2.[ItmsGrpCod], T3.[ItmsGrpNam] AS ItemGroupName, T1.[WhsCode],

T5.[WhsName], T0.[DocNum], T1.[GrssProfit] AS 'GrossProfit',

T1.[Price], T1.[StockPrice]

FROM OINV T0

INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod

INNER JOIN OWHS T5 ON T1.WhsCode = T5.WhsCode

WHERE T0.[DocType]='i'

UNION ALL

SELECT

T0.[CardCode], T0.[CardName], T0.[DocDate],

(T0.[DocTotal]*-1) AS 'TotalAmount', T1.[Dscription], (T1.[Quantity]*-1) Quantity,

T0.[DocType], T1.[ItemCode], T2.[ItemName],

T2.[ItmsGrpCod], T3.[ItmsGrpNam] AS ItemGroupName, T1.[WhsCode],

T5.[WhsName], T0.[DocNum], (T1.[GrssProfit]*-1) AS 'GrossProfit',

(T1.[Price]*-1) Price, T1.[StockPrice]

FROM ORIN T0

INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod

INNER JOIN OWHS T5 ON T1.WhsCode = T5.WhsCode

WHERE T0.[DocType]='i'

Regards,

[Former Member]

Thank you, it was missing some of the details.

[kothandaraman_nagarajan]

Hi,

Try this:

SELECT

T0.[CardCode], T0.[CardName], T0.[DocDate], T0.[DocTotal] AS 'TotalAmount', T1.[Dscription], T1.[Quantity], T0.[DocType], T1.[ItemCode], T2.[ItemName], T2.[ItmsGrpCod], T2.[CardCode],

T3.[ItmsGrpNam] AS ItemGroupName, T1.[WhsCode], T5.[WhsName], T0.[DocNum],

T1.[GrssProfit] AS 'GrossProfit', T1.[Price], T1.[StockPrice]

FROM

OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod INNER JOIN OWHS T5 ON T1.WhsCode = T5.WhsCode

WHERE T0.[DocType]='i'

UNION ALL

SELECT

T0.[CardCode], T0.[CardName], T0.[DocDate], T0.[DocTotal] AS 'TotalAmount', T1.[Dscription], T1.[Quantity], T0.[DocType], T1.[ItemCode], T2.[ItemName], T2.[ItmsGrpCod], T2.[CardCode],

T3.[ItmsGrpNam] AS ItemGroupName, T1.[WhsCode], T5.[WhsName], T0.[DocNum],

T1.[GrssProfit] AS 'GrossProfit', T1.[Price], T1.[StockPrice]

FROM

ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod INNER JOIN OWHS T5 ON T1.WhsCode = T5.WhsCode

WHERE T0.[DocType]='i'

Thanks.

[Former Member]

Thank you this works but it was missing the name I added it.