Skip to Content
author's profile photo Former Member
Former Member

How to club the following COGS / Sale report, and add sale order value also to this report

------------------------------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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Best Answer
    Posted on Jul 10, 2014 at 01:21 AM

    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


    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 04, 2014 at 09:01 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 08, 2014 at 08:29 AM

    In this report I need to have two report for Sale / COGS, I want in one report

    JAN |Feb |

    Sale | COGS |Sale | COGS |

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 10, 2014 at 12:52 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 25, 2014 at 05:45 AM

    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
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.