------------------------------COGS-----------------------------------------------------
SELECT X.Cardname, SUM(X.Jan) JAN, SUM(X.FEB) FRB, SUM(X.MAR) MAR, SUM(X.APR) APR, SUM(X.MAY) MAY
, SUM(X.JUN) JUN, SUM(X.JUL) JUL, SUM(X.AUG) AUG, SUM(X.SEP) SEP, SUM(X.OCT) OCT
, SUM(X.NOV) NOV, SUM(X.DEC) DEC, SUM(X.GT) GT
FROM (
SELECT T0.Cardname
, CASE DATEPART(m,T0.DocDate) WHEN 1 THEN T0.CogsVal ELSE 0 END JAN
, CASE DATEPART(m,T0.DocDate) WHEN 2 THEN T0.CogsVal ELSE 0 END FEB
, CASE DATEPART(m,T0.DocDate) WHEN 3 THEN T0.CogsVal ELSE 0 END MAR
, CASE DATEPART(m,T0.DocDate) WHEN 4 THEN T0.CogsVal ELSE 0 END APR
, CASE DATEPART(m,T0.DocDate) WHEN 5 THEN T0.CogsVal ELSE 0 END MAY
, CASE DATEPART(m,T0.DocDate) WHEN 6 THEN T0.CogsVal ELSE 0 END JUN
, CASE DATEPART(m,T0.DocDate) WHEN 7 THEN T0.CogsVal ELSE 0 END JUL
, CASE DATEPART(m,T0.DocDate) WHEN 8 THEN T0.CogsVal ELSE 0 END AUG
, CASE DATEPART(m,T0.DocDate) WHEN 9 THEN T0.CogsVal ELSE 0 END SEP
, CASE DATEPART(m,T0.DocDate) WHEN 10 THEN T0.CogsVal ELSE 0 END OCT
, CASE DATEPART(m,T0.DocDate) WHEN 11 THEN T0.CogsVal ELSE 0 END NOV
, CASE DATEPART(m,T0.DocDate) WHEN 12 THEN T0.CogsVal ELSE 0 END DEC
, T0.CogsVal GT
FROM dbo.OINM T0
WHERE T0.DocDate BETWEEN [%0] AND [%1]) X
Group By X.Cardname
---------------------------------------Sale ----------------------------------------
SELECT X.CardName, SUM(X.Jan) JAN, SUM(X.FEB) FRB, SUM(X.MAR) MAR, SUM(X.APR) APR, SUM(X.MAY) MAY
, SUM(X.JUN) JUN, SUM(X.JUL) JUL, SUM(X.AUG) AUG, SUM(X.SEP) SEP, SUM(X.OCT) OCT
, SUM(X.NOV) NOV, SUM(X.DEC) DEC, SUM(X.GT) GT
FROM (
SELECT T0.CardName
, CASE DATEPART(m,T0.DocDate) WHEN 1 THEN T0.DocTotal ELSE 0 END JAN
, CASE DATEPART(m,T0.DocDate) WHEN 2 THEN T0.DocTotal ELSE 0 END FEB
, CASE DATEPART(m,T0.DocDate) WHEN 3 THEN T0.DocTotal ELSE 0 END MAR
, CASE DATEPART(m,T0.DocDate) WHEN 4 THEN T0.DocTotal ELSE 0 END APR
, CASE DATEPART(m,T0.DocDate) WHEN 5 THEN T0.DocTotal ELSE 0 END MAY
, CASE DATEPART(m,T0.DocDate) WHEN 6 THEN T0.DocTotal ELSE 0 END JUN
, CASE DATEPART(m,T0.DocDate) WHEN 7 THEN T0.DocTotal ELSE 0 END JUL
, CASE DATEPART(m,T0.DocDate) WHEN 8 THEN T0.DocTotal ELSE 0 END AUG
, CASE DATEPART(m,T0.DocDate) WHEN 9 THEN T0.DocTotal ELSE 0 END SEP
, CASE DATEPART(m,T0.DocDate) WHEN 10 THEN T0.DocTotal ELSE 0 END OCT
, CASE DATEPART(m,T0.DocDate) WHEN 11 THEN T0.DocTotal ELSE 0 END NOV
, CASE DATEPART(m,T0.DocDate) WHEN 12 THEN T0.DocTotal ELSE 0 END DEC
, T0.DocTotal GT
FROM dbo.OINV T0
WHERE T0.DocDate BETWEEN [%0] AND [%1]) X
Group By X.CardName
Hi,
Use Mr. Gordon's query and concept to create above report for only for Jan month.
If you need for particular year and for all months, use below query:
SELECT X.CardName, SUM(X.JanCOG) 'Jan-Cogs', SUM(X.JanINV) 'Jan-Inv',SUM(X.FebCOG) 'Feb-Cogs', SUM(X.FebINV) 'Feb-Inv',SUM(X.MarCOG) 'Mar-Cogs', SUM(X.MarINV) 'Mar-Inv', SUM(X.AprCOG) 'Apr-Cogs', SUM(X.AprINV) 'Apr-Inv', SUM(X.MayCOG) 'May-Cogs', SUM(X.MayINV) 'May-Inv',SUM(X.JunCOG) 'June-Cogs', SUM(X.JunINV) 'June-Inv',SUM(X.JulCOG) 'July-Cogs', SUM(X.JulINV) 'July-Inv',SUM(X.AugCOG) 'Aug-Cogs', SUM(X.AugINV) 'Aug-Inv',SUM(X.SepCOG) 'Set-Cogs', SUM(X.SepINV) 'Sep-Inv',SUM(X.OctCOG) 'Oct-Cogs', SUM(X.OctINV) 'Oct-Inv',SUM(X.NovCOG) 'Nov-Cogs', SUM(X.NovINV) 'VNov-Inv',SUM(X.DecCOG) 'Dec-Cogs', SUM(X.DecINV) 'Dec-Inv'
FROM
(SELECT CardName, [1] JanCOG, 0 JanINV,[2] FebCOG, 0 FEBINV,[3] MarCOG, 0 MARINV,[4] AprCOG, 0 AprINV,[5] MayCOG, 0 MayINV,[6] JunCOG, 0 JunINV,[7] JulCOG, 0 JulINV,[8] AugCOG, 0 AugINV,[9] SepCOG, 0 SepINV,[10] OctCOG, 0 OctINV,[11] NovCOG, 0 NovINV,[12] DecCOG,0 DecINV
FROM (SELECT T0.[CardName], T0.CogsVal,
MONTH(T0.DocDate) Month FROM OINM T0 where year(t0.docdate) = 2014
) S
PIVOT(SUM(CogsVal) for Month in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P
UNION ALL
SELECT CardName, 0,[1] JanINV, 0,[2] FebINV, 0,[3] MarINV, 0,[4] AprINV, 0,[5] MayINV, 0,[6] JunINV, 0,[7] JulINV, 0,[8] AugINV, 0,[9] SepINV, 0,[10] OctINV, 0,[11] NovINV, 0,[12] DecINV
FROM (SELECT T0.[CardName], T0.DocTotal,
MONTH(T0.DocDate) Month FROM OINV T0 where year(t0.docdate) = 2014
) S
PIVOT(SUM(DocTotal) FOR Month in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P) X
GROUP BY X.CardName
Thanks & Regards,
Nagarajan
Hi,
You can get COGS and doc total from OINM table itself without using OINV table.
But problem is, if you combine for all months (for COGS total and doc total) in single report, user may find difficult to read the report.
Please advice on this.
Thanks & Regards,
Nagarajan
Hi,
Try this:
SELECT X.CardName, SUM(X.JanCOG) 'Jan-Cogs', SUM(X.JanINV) 'Jan-Inv' FROM
(SELECT CardName, [1] JanCOG, 0 JanINV,[2] FebCOG, 0 FEBINV,[3] MarCOG, 0 MARINV,[4] AprCOG, 0 AprINV,[5] MayCOG, 0 MayINV,[6] JunCOG, 0 JunINV,[7] JulCOG, 0 JulINV,[8] AugCOG, 0 AugINV,[9] SepCOG, 0 SepINV,[10] OctCOG, 0 OctINV,[11] NovCOG, 0 NovINV,[12] DecCOG,0 DecINV
FROM (SELECT T0.[CardName], T0.CogsVal,
MONTH(T0.DocDate) Month FROM OINM T0
) S
PIVOT(SUM(CogsVal) for Month in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P
UNION ALL
SELECT CardName, 0,[1] JanINV, 0,[2] FebINV, 0,[3] MarINV, 0,[4] AprINV, 0,[5] MayINV, 0,[6] JunINV, 0,[7] JulINV, 0,[8] AugINV, 0,[9] SepINV, 0,[10] OctINV, 0,[11] NovINV, 0,[12] DecINV
FROM (SELECT T0.[CardName], T0.DocTotal,
MONTH(T0.DocDate) Month FROM OINV T0
) S
PIVOT(SUM(DocTotal) FOR Month in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P) X
GROUP BY X.CardName
I only entered Jan for an example. You can add all other months if the results are fine.
Thanks,
Gordon
Hi,
As per your request, we already grouped into single query. So please close this thread here with correct answer.
Do not mark correct answer for own reply.
If you further discussion on this thread, post new discussion with linking this thread.
Thanks & Regards,
Nagarajan
Add a comment