Sales Analysis Comparison 9.0 PL10

[Former Member]

Please advise how to create this query properly by to/from (period)dates?  Having a problem, for some reason, adding units and %s.

Stores(a property in BP)  Total Units     Total Sales     Total Units LY(same period)     Total Sales LY(same period)     %Units    %Sales

I have:

DECLARE @DateFrom AS DATE

DECLARE @DateTo AS DATE

SELECT @DateFrom=DocDate FROM OINV TO WHERE T0.DocDate='[%0]'

SELECT @DateTo=DocDate FROM OINV T1 WHERE T1.DocDate='[%1]'

CREATE TABLE #temp

(CardCode NVARCHAR(15),CardName NVARCHAR(100),CurrentYear DECIMAL(18,2),Prior Year DECIMAL (18,2))

INSERT INTO #temp(CardCode,CardName,CurrentYear,PriorYear)

SELECT CardCode,CardName,SUM(DocTotal),0

FROM OINV

WHERE DocDate BETWEEN @DateFrom AND @DateTo

GROUP BY CardCode,CardName

UNION

SELECT CardCode,CardName,SUM(DocTotal*-1),0

FROM ORIN

WHERE DocDate BETWEEN @DateFrom AND @DateTo

GROUP BY CardCode,CardName

UNION

SELECT CardCode,CardName,0,SUM(DocTotal)

FROM OINV

WHERE DocDate BETWEEN DATEADD(YEAR,-1,@DateFrom)AND DATEADD(YEAR,-1@DateTo)

GROUP By CardCode,CardName

UNION

SELECT CardCode,CardName,0,SUM(DocTotal*-1)

FROM ORIN

WHERE DocDate BETWEEN DATEADD(YEAR,-1@DateFrom)AND DATEADD(YEAR,-1@DateTo)

GROUP BY CardCode,CardName

SELECT CardCode,CardName,SUM(CurrentYear)AS'CurrentYear',SUM(PriorYear)AS'PriorYear' FROM #temp

GROUP BY CardCode,CardName ORDER BY CardCode,CardName

DROP TABLE #temp

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this:

DECLARE @DateFrom AS DATE

DECLARE @DateTo AS DATE

SELECT @DateFrom=DocDate FROM OINV T0 WHERE T0.DocDate='[%0]'

SELECT @DateTo=DocDate FROM OINV T1 WHERE T1.DocDate='[%1]'

CREATE TABLE #temp

(CardCode NVARCHAR(15),CardName NVARCHAR(100),CurrentYear DECIMAL(18,2),Totalunit NVARCHAR (15),Totalunits NVARCHAR(100),  salesPrior Year DECIMAL (18,2))

INSERT INTO #temp(CardCode,CardName,totalunit, totalsales,CurrentYear,PriorYear)

SELECT CardCode,CardName,SUM(T1.[LineTotal]),0,sum(T1.[Quantity])

FROM OINV t0 inner join inv1 t1 on t0.docentry = t1.docentry

WHERE T0.DocDate BETWEEN @DateFrom AND @DateTo

GROUP BY CardCode,CardName

UNION

SELECT CardCode,CardName,SUM(T1.[LineTotal]),0,sum(T1.[Quantity])

FROM ORIN T0 inner join RIN1 t1 on t0.docentry = t1.docentry

WHERE t0.DocDate BETWEEN @DateFrom AND @DateTo

GROUP BY CardCode,CardName

UNION

SELECT CardCode,CardName,0,SUM(T1.[LineTotal),,sum(T1.[Quantity])

FROM OINV t0 inner join inv1 t1 on t0.docentry = t1.docentry

WHERE t0.DocDate BETWEEN DATEADD(YEAR,-1,@DateFrom)AND DATEADD(YEAR,-1@DateTo)

GROUP By CardCode,CardName

UNION

SELECT CardCode,CardName,0,SUM(T1.[LineTotal),,sum(T1.[Quantity])

FROM ORIN T0 inner join RIN1 t1 on t0.docentry = t1.docentry

WHERE T0.DocDate BETWEEN DATEADD(YEAR,-1@DateFrom)AND DATEADD(YEAR,-1@DateTo)

GROUP BY CardCode,CardName

SELECT CardCode,CardName,Totalunit, Totalsales,SUM(CurrentYear)AS'CurrentYear',SUM(PriorYear)AS'PriorYear' FROM #temp

GROUP BY CardCode,CardName ORDER BY CardCode,CardName

DROP TABLE #temp

Let me know the query result.

Thanks & Regards,

Nagarajan

[former_member186095]

Hi Dan,

What's wrong with your query ? Could you post the print screen of the query execution result ?

Rgds,
Jimmy