cancel
Showing results for 
Search instead for 
Did you mean: 

Query - Sales

Former Member
0 Kudos

Hi Experts,

Has anyone created a Query for Previous Day Sales and Month To Date Sales? One condition is not to include the weekends when the previous day sales are generated. Ex: if the report is generated for Monday, the previous day sales figures should come from Friday and not Sunday. Below is how the query should look,like:

SalespersonSales prev dayGM% Sales MTDGM%
Employee A$1,485.65 28.40% $16,637.83 30.87%
Employee B$1,897.50 29.58% $41,898.53 32.06%

Thanks in Advance,

Maria

Accepted Solutions (0)

Answers (1)

Answers (1)

Johan_H
Active Contributor
0 Kudos

Hi Maria,

How about this:

DECLARE @D AS DATETIME = CAST('2016/6/16' AS DATETIME)

SELECT sp.SlpName

      ,(ISNULL(Y.DocTotal, 0)) AS PreviousBusinessDay

   ,CAST(((ISNULL(Y.GrosProfit, 0)) / (ISNULL(Y.DocTotal, 1))) * 100 AS MONEY) AS 'GM%'

   ,SUM(ISNULL(MTD.DocTotal, 0)) AS MTD

   ,CAST((SUM(ISNULL(MTD.GrosProfit, 0)) / SUM(ISNULL(MTD.DocTotal, 1))) * 100 AS MONEY) AS 'GM%'

FROM OSLP sp

LEFT OUTER JOIN

(SELECT h1.DocDate

      ,h1.SlpCode

      ,SUM((h1.DocTotal - h1.DiscSum) - h1.VatSum) AS DocTotal

   ,SUM(h1.GrosProfit) AS GrosProfit

FROM OINV h1

WHERE h1.DocDate = CASE

                   WHEN DATENAME(WEEKDAY, DATEADD(DAY, -1, @D)) = 'Sunday' THEN DATEADD(DAY, -3, @D)

    WHEN DATENAME(WEEKDAY, DATEADD(DAY, -1, @D)) = 'Saturday' THEN DATEADD(DAY, -2, @D)

                   ELSE DATEADD(DAY, -1, @D)

   END

GROUP BY h1.DocDate

        ,h1.SlpCode) Y ON sp.SlpCode = Y.SlpCode

LEFT OUTER JOIN

(SELECT h2.DocDate

      ,h2.SlpCode

      ,SUM((h2.DocTotal - h2.DiscSum) - h2.VatSum) AS DocTotal

   ,SUM(h2.GrosProfit) AS GrosProfit

FROM OINV h2

WHERE h2.DocDate BETWEEN CAST(CAST(YEAR(@D) AS NVARCHAR) + '/' + CAST(MONTH(@D) AS NVARCHAR) + '/' + '01' AS DATETIME)

              AND CASE

                   WHEN DATENAME(WEEKDAY, DATEADD(DAY, -1, @D)) = 'Sunday' THEN DATEADD(DAY, -3, @D)

    WHEN DATENAME(WEEKDAY, DATEADD(DAY, -1, @D)) = 'Saturday' THEN DATEADD(DAY, -2, @D)

                   ELSE DATEADD(DAY, -1, @D)

   END

GROUP BY h2.DocDate

        ,h2.SlpCode) MTD ON sp.SlpCode = MTD.SlpCode

GROUP BY sp.SlpName

      ,(ISNULL(Y.DocTotal, 0))

   ,CAST(((ISNULL(Y.GrosProfit, 0)) / (ISNULL(Y.DocTotal, 1))) * 100 AS MONEY)

HAVING SUM(ISNULL(MTD.DocTotal, 0)) > 0

ORDER BY sp.SlpName

Regards,

Johan