on 2022 Oct 30 8:53 AM
Hi Mohamad,
Check this query if it helps, sure it will need some modification:
SELECT P."Code", P."CName",
[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."Itemcode" as Code, T0."Dscription" as CName, T0."Quantity" as Qty,
MONTH(T0."docdate") as month
FROM dbo.inv1 T0 Inner Join OINV T1 ON T0."DocEntry"=T1."DocEntry"
WHERE Year(T1."docdate") = 2022
UNION
SELECT (T0."ItemCode") as Code, T0."Dscription" as CName, -1 * T0."Quantity" as Qty,
MONTH(T0."docdate") as month
FROM dbo.RIN1 T0 Inner Join ORIN T1 ON T0."DocEntry"=T1."DocEntry"
WHERE Year(T1."docdate") = 2022) S
PIVOT (SUM(Qty) FOR month IN
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P
Thank you,
Aziz
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
94 | |
9 | |
8 | |
8 | |
6 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.