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
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,
Thanks Kennedy for adding Discount. Exactly what i wanted.
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]
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