Daily sales report with last selling price

[Former Member]

Dear All,

how to query daily sales record with last selling price as following:

cust. code , item , unit price, last selling price to this cust. within 3 month , highest selling price of this item with 3months

I'm new to both SAP and SQL, Please help! Many Thanks!!

Wing

[Former Member · Accepted solution]

Hi,

Try this........

SELECT P.CardCode,P.CardName,P.ItemCode,
[1] as [Jan],
 [2] as [Feb],
 [3] as [Mar],
  [4] as [Apr],
 [5] as [May],
 [6] as [Jun],
 [7] as [Jul],
 [8] as [Aug],
 [9] as [Sep],
 [10] as [Oct],
 [11] as [Nov],
 [12] as [Dec]
 
FROM (SELECT (T0.CardCode),T0.CardName,t1.ItemCode,T1.price,MONTH(T1.Docdate)as month
FROM dbo.OINV  T0
inner join INV1 T1 ON T0.DocEntry=T1.DocEntry
inner join OITM t2 on T1.ItemCode=t2.ItemCode  
WHERE T1.DocDate>='[%0]' and T1.DocDate<='[%1]' and T1.ITEMCODE='[%2]' 
and T0.CardCode='[%3]'
) S
  PIVOT  (max(price) FOR [month] IN 
([4],[5],[6],[7],[8],[9],[10],[11],[12],[1],[2],[3])) P
ORDER BY P.[CardCode] 

Check this link for SQL tutorials.........

http://beginner-sql-tutorial.com/sql-subquery.htm

http://w3schools.com/sql/default.asp

[Former Member · Accepted solution]

Thx Priyalakshmanan's reply! However it is not a daily sale record

I need to list out all daily sales(invoice) record, and use last selling price and highest selling to check salesman's selling strategy.

the exact report should be

cust A / item 1/ selling price/ last selling price of item 1 to cust A (check 3 months sale record only)/highest selling price of item1 to any cust. (check 3 months sale record only)

cust B / item 2/ selling price/ last selling price of item 2 to cust B (check 3 months sale record only)/highest selling price of item1 to any cust. (check 3 months sale record only)

cust C/ item 3/ selling price/ last selling price of item 3 to cust C (check 3 months sale record only)/highest selling price of item1 to any cust. (check 3 months sale record only)

Do I need to create temporary table of "last selling price of all item and all cust." and temporary table of highest selling price of all item" ? Then join the daily sales query?

PLease help

[Former Member · Accepted solution]

Hi,

Please response and close your previous posting first.

You may try:

SELECT DISTINCT T0.CardCode,T1.ItemCode,T1.Price, (SELECT MAX(T2.Price) FROM INV1 T2 JOIN OINV T3 ON T3.DocEntry=T2.DocEntry WHERE T2.ItemCode= T1.ItemCode and T3.CardCode=T0.CardCode AND DateDiff(d,T3.DocDate,GetDate()) Between 1 and 90 GROUP BY ItemCode HAVING MIN(DateDiff(d,T3.DocDate,GetDate())) BETWEEN 1 AND 90) 'Latest', (SELECT Max(T4.Price) FROM INV1 T4 JOIN OINV T5 ON T5.DocEntry=T4.DocEntry WHERE T4.ItemCode= T1.ItemCode and DateDiff(d,T5.DocDate,GetDate()) Between 1 and 90) 'highest'

FROM OINV T0

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

WHERE DateDiff(d,T0.DocDate,GetDate())=0

Thanks,

Gordon

[Former Member · Accepted solution]

Thx Gordon!!

Is it "ALWAYS" suggest to use sub query instead of creating temporary table and then join them together?

Wing