Skip to Content
0

Daily Sales query

Nov 07, 2016 at 10:06 AM

91

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Kennedy T Nov 08, 2016 at 07:05 AM
0
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,
Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks Kennedy for adding Discount. Exactly what i wanted.

0
Gonzalo Gomez Nov 07, 2016 at 03:38 PM
0

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]

Share
10 |10000 characters needed characters left characters exceeded
Tichaona Gaza Nov 07, 2016 at 04:30 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded