Skip to Content

Daily Sales query

Can anyone help me in writing a query that shows daily totals sales as well as VAT and Discount. And if its possible to split invoice an invoice plus payment. Looking forward to your replies.

Regards

Tichaona

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Nov 08, 2016 at 07:05 AM
    DECLARE @Fromdate  AS DATETIME
    DECLARE @Todate    AS DATETIME
    SET @FROMDATE = /*SELECT MIN(Docdate) FROM OINV T1 WHERE T1.DocDate >= */ '[%0]'
    SET @TODATE = /*SELECT MAX(Docdate) FROM OINV T1 WHERE T1.DocDate <= */ '[%1]'
    SELECT DISC AS 'Description',
           SUM(SALESV) AS 'TOTAL INCLUSIVE',
           SUM(RETURNSV) AS 'CASH RETURNS',
           SUM(SALESV) -SUM(RETURNSV) AS 'NET SALES',
           SUM(VATV) AS 'VAT',
           SUM(sdisc -rdisc) AS NetDiscount
    FROM   (
               SELECT 'TOTAL SALES' [DISC],
                      (SUM(T1.[GTotal])) [SalesV],
                      0 [ReturnsV],
                      (SUM(T1.[GTotal]) -SUM(T1.[VatSum])) [TSalesV],
                      0 [TReturnsV],
                      (SUM(T1.[VatSum])) [VATV],
                      SUM(t0.DiscSum) AS SDISC,
                      0 AS Rdisc
               FROM   OINV T0
                      INNER JOIN INV1 T1
                           ON  T0.DocEntry = T1.DocEntry
                      INNER JOIN OCRD T2
                           ON  T0.CardCode = T2.CardCode
               WHERE  T0.[DocDate] >= @FromDate
                      AND T0.[DocDate] <= @ToDate
               
               UNION ALL
               
               SELECT 'TOTAL SALES' [DISC],
                      0[SalesV],
                      (SUM(T1.[GTotal]) -SUM(T1.[VatSum])) [ReturnsV],
                      0[TSalesV],
                      (SUM(T1.[GTotal]) -SUM(T1.[VatSum])) [TReturnsV],
                      0[VATV],
                      0,
                      SUM(t0.DiscSum) AS Rdisc
               FROM   ORIN T0
                      INNER JOIN RIN1 T1
                           ON  T0.DocEntry = T1.DocEntry
                      INNER JOIN OCRD T2
                           ON  T0.CardCode = T2.CardCode
               WHERE  T0.[DocDate] >= @FromDate
                      AND T0.[DocDate] <= @ToDate
           )S
    GROUP BY
           DISC
    
    
    Regards,
    Add comment
    10|10000 characters needed characters exceeded

  • Nov 07, 2016 at 03:38 PM

    try this one and modificate to your requirements:

    SELECT T0.[DocNum], T0.[CardCode], T0.[CardName], T0.[VatSum], T0.[DiscSum], T0.[DocTotal], T0.[PaidToDate] FROM OINV T0 WHERE T0.[DocDate] =[%0]

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 07, 2016 at 04:30 PM

    Thanks for responding Gonzalo. But thats not quite what i'm looking for. Can you have a look at what i have done so far and tell me how to include discount.

    Declare @Fromdate as datetime

    Declare @Todate as datetime

    SET @FROMDATE = /*SELECT MIN(Docdate) FROM OINV T1 WHERE T1.DocDate >= */ '[%0]'

    SET @TODATE = /*SELECT MAX(Docdate) FROM OINV T1 WHERE T1.DocDate <= */ '[%1]'

    SET @FROMDATE = /*SELECT MIN(Docdate) FROM ORIN T1 WHERE T1.DocDate >= */ '[%0]'

    SET @TODATE = /*SELECT MAX(Docdate) FROM ORIN T1 WHERE T1.DocDate <= */ '[%1]'

    SELECT DISC AS 'Description', SUM(SALESV) as 'TOTAL INCLUSIVE', SUM(RETURNSV) as 'CASH RETURNS', SUM(SALESV)-SUM(RETURNSV) as 'NET SALES', SUM(VATV) as 'VAT'

    FROM

    (SELECT

    'TOTAL SALES' [DISC], (sum(T1.[GTotal])) [SalesV], 0 [ReturnsV], (sum(T1.[GTotal])-sum(T1.[VatSum])) [TSalesV], 0 [TReturnsV], (sum(T1.[VatSum])) [VATV]

    FROM

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

    INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode

    WHERE T0.[DocDate] >=@FromDate AND T0.[DocDate] <=@ToDate

    UNION ALL

    SELECT 'TOTAL SALES' [DISC], 0[SalesV], (sum(T1.[GTotal])-sum(T1.[VatSum])) [ReturnsV], 0[TSalesV], (sum(T1.[GTotal])-sum(T1.[VatSum])) [TReturnsV], 0[VATV]

    FROM

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

    INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode

    WHERE T0.[DocDate] >=@FromDate AND T0.[DocDate] <=@ToDate)S

    GROUP BY DISC

    Add comment
    10|10000 characters needed characters exceeded