locked
How to add the ammount of sales RRS feed

  • Question

  • Hi there i need to get the total amount of sales per customer per month.

    So  the customer makes a sale today then another sale in 2 weeks time, at the end of the month i need to add this to sales and disply  as one.

    same as in exel

    SUM B1+B7...


    thanks
    Monday, August 3, 2009 3:22 PM

Answers

  • SELECT cust_nbr, start_date, end_date, SUM(cost) FROM orders AS a INNER JOIN report_calendar AS b ON a.order_date BETWEEN b.start_date AND b.end_date GROUP BY cust_nbr, start_date, end_date;

    Let's test performance, shall we?

    SET NOCOUNT ON
    SET STATISTICS IO ON
    
    -- Prepare sample data
    CREATE TABLE	#Sales
    		(
    			CustomerID INT,
    			SalesDate DATETIME,
    			Amount MONEY
    		)
    
    CREATE CLUSTERED INDEX IX_Sales ON #Sales (CustomerID, SalesDate, Amount)
    
    INSERT	#Sales
    SELECT	ABS(CHECKSUM(NEWID())) % 20,
    	ABS(CHECKSUM(NEWID())) % 1000 + 39000,
    	ABS(CHECKSUM(NEWID())) % 1000
    FROM	master..spt_values
    WHERE	Type = 'P'
    
    CREATE TABLE	#Calendar
    		(
    			FromDate DATETIME,
    			ToDate DATETIME,
    			MonthNam VARCHAR(20)
    		)
    
    CREATE CLUSTERED INDEX IX_Calendar ON #Calendar (FromDate, ToDate, MonthNam)
    
    INSERT	#Calendar
    SELECT	DATEADD(MONTH, Number, '20050101'),
    	DATEADD(MONTH, Number, '20050131'),
    	LEFT(STUFF(CONVERT(VARCHAR(20), DATEADD(MONTH, Number, '20050101'), 100), 5, 3, ''), 9)
    FROM	master..spt_values
    WHERE	Type = 'P'
    
    PRINT	'Testing starts here...'
    
    PRINT	'-- Peso & Plamen'
    -- Peso & Plamen
    SELECT		CustomerID,
    		DATEADD(MONTH, DATEDIFF(MONTH, 0, SalesDate), 0),
    		SUM(Amount) AS Amount
    FROM		#Sales
    GROUP BY	CustomerID,
    		DATEADD(MONTH, DATEDIFF(MONTH, 0, SalesDate), 0)
    ORDER BY	CustomerID,
    		DATEADD(MONTH, DATEDIFF(MONTH, 0, SalesDate), 0)
    
    PRINT	'-- sqlguruu'
    -- sqlguruu
    SELECT		s.CustomerID,
    		c.FromDate,
    		c.ToDate,
    		SUM(s.Amount) AS Amount
    FROM		#Sales AS s
    INNER JOIN	#Calendar AS c ON s.SalesDate BETWEEN c.FromDate AND c.ToDate
    GROUP BY	s.CustomerID,
    		c.FromDate,
    		c.ToDate
    ORDER BY	s.CustomerID,
    		c.FromDate,
    		c.ToDate
    
    SET STATISTICS IO OFF
    
    DROP TABLE	#Sales,
    		#Calendar
    
    

    Here is the statistics for the two suggestions.

    -- Peso & Plamen
    Table '#Sales'. Scan count 1, logical reads 10, physical reads 0.
    
    -- sqlguruu
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0.
    Table '#Calendar'. Scan count 2048, logical reads 4096.
    Table '#Sales'. Scan count 1, logical reads 10, physical reads 0.
    And what about the execution plans?

    -- Peso & Plamen
    
      |--Compute Scalar(DEFINE:([Expr1005]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011] END))
           |--Stream Aggregate(GROUP BY:([tempdb].[dbo].[#Sales].[CustomerID], [Expr1004]) DEFINE:([Expr1010]=COUNT_BIG([tempdb].[dbo].[#Sales].[Amount]), [Expr1011]=SUM([tempdb].[dbo].[#Sales].[Amount])))
                |--Sort(ORDER BY:([tempdb].[dbo].[#Sales].[CustomerID] ASC, [Expr1004] ASC))
                     |--Compute Scalar(DEFINE:([Expr1004]=dateadd(month,datediff(month,'1900-01-01 00:00:00.000',[tempdb].[dbo].[#Sales].[SalesDate]),'1900-01-01 00:00:00.000')))
                          |--Table Scan(OBJECT:([tempdb].[dbo].[#Sales]))
    
    -- sqlguruu
      |--Compute Scalar(DEFINE:([Expr1006]=CASE WHEN [Expr1011]=(0) THEN NULL ELSE [Expr1012] END))
           |--Stream Aggregate(GROUP BY:([s].[CustomerID], [c].[FromDate], [c].[ToDate]) DEFINE:([Expr1011]=COUNT_BIG([tempdb].[dbo].[#Sales].[Amount] as [s].[Amount]), [Expr1012]=SUM([tempdb].[dbo].[#Sales].[Amount] as [s].[Amount])))
                |--Sort(ORDER BY:([s].[CustomerID] ASC, [c].[FromDate] ASC, [c].[ToDate] ASC))
                     |--Nested Loops(Inner Join, WHERE:([tempdb].[dbo].[#Sales].[SalesDate] as [s].[SalesDate]>=[tempdb].[dbo].[#Calendar].[FromDate] as [c].[FromDate] AND [tempdb].[dbo].[#Sales].[SalesDate] as [s].[SalesDate]<=[tempdb].[dbo].[#Calendar].[ToDate] as [c].[ToDate]))
                          |--Table Scan(OBJECT:([tempdb].[dbo].[#Calendar] AS [c]))
                          |--Table Scan(OBJECT:([tempdb].[dbo].[#Sales] AS [s]))
    • Marked as answer by Zongqing Li Monday, August 10, 2009 8:24 AM
    Monday, August 3, 2009 6:56 PM

All replies

  • Maybe something like this:
     
    SELECT customer, YEAR(sale_date), MONTH(sale_date), SUM(amount) AS total_sales
    FROM Sales
    GROUP BY customer, YEAR(sale_date), MONTH(sale_date);



    Plamen Ratchev
    Monday, August 3, 2009 3:24 PM
  • SELECT cust_nbr, start_date, end_date, SUM(cost) FROM orders AS a INNER JOIN report_calendar AS b ON a.order_date BETWEEN b.start_date AND b.end_date GROUP BY cust_nbr, start_date, end_date;
    Monday, August 3, 2009 3:29 PM