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,
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
Add comment