SQL Server Performance

Query For 2 Seperate Month Results

Discussion in 'SQL Server Reporting Services' started by mstopkey, Jun 21, 2007.

  1. mstopkey New Member

    I am building a report that is currently running correctly for cuurent month results with the following query.

    SELECT DISTINCT
    Customer.Street1 AS 'Prop#',
    Customer.Name AS 'Prop_Name',
    COUNT(master.number) AS '#_Of_Accts',
    SUM(master.original1) AS '$_Listed',
    SUM(payhistory.paid1) AS '$_Collected'
    FROM Customer
    LEFT OUTER JOIN payhistory ON payhistory.customer=Customer.customer
    INNER JOIN master ON master.number=payhistory.number
    WHERE (Customer.customer in ('@allowedcustomers'))
    AND (DATEDIFF(MM, master.received, GETDATE()) = 0)
    GROUP BY Customer.Street1, Customer.Name

    What I need to do is duplicate columns '#_Of_Accts', '$_Listed', and '$_Collected' for the previous month: (DATEDIFF(MM, master.received, GETDATE()) = 1)

    Any help would be greatly appreciated.
    Mark Stopkey
  2. FrankKalis Moderator

    You should be able to use a CASE expression like
    < pseudocode >
    SUM(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 0 THEN master.original1 ELSE 0 END) AS $_listed_this_month,
    SUM(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 1 THEN master.original1 ELSE 0 END) AS $_listed_previous_month
    </pseudocode>
    You would of courde need to adjust your WHERE clause.

    Another way would be to use a derived table in which you retrieve the data for the previous month and JOIN this derived table to the one above.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de
  3. mstopkey New Member

    Thanks for the help! I am trying it out now.
  4. mstopkey New Member

    The results of SUM master.original1 works great but COUNT master.number gets me the same results for both dates. Here is the query.

    SELECT DISTINCT
    Customer.Street1 AS 'Prop#',
    Customer.Name AS 'Prop_Name',
    COUNT(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 5 THEN master.number ELSE 0 END) AS '#_Accts_This_Month',
    SUM(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 0 THEN master.original1 ELSE 0 END) AS '$_Listed_This_Month',
    COUNT(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 1 THEN master.number ELSE 0 END) AS '#_Accts_Prior_Month',
    SUM(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 1 THEN master.original1 ELSE 0 END) AS '$_Listed_Prior_Month',
    SUM(payhistory.paid1) AS '$_Collected'
    FROM Customer
    LEFT OUTER JOIN payhistory ON payhistory.customer=Customer.customer
    INNER JOIN master ON master.number=payhistory.number
    WHERE (Customer.customer in ('@allowedcustomers'))
    GROUP BY Customer.Street1, Customer.Name


  5. mstopkey New Member

    OOPS. In the reply, I pasted in test numbers by mistake. Here is the actual query.

    SELECT DISTINCT
    Customer.Street1 AS 'Prop#',
    Customer.Name AS 'Prop_Name',
    COUNT(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 0 THEN master.number ELSE 0 END) AS '#_Accts_This_Month',
    SUM(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 0 THEN master.original1 ELSE 0 END) AS '$_Listed_This_Month',
    COUNT(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 1 THEN master.number ELSE 0 END) AS '#_Accts_Prior_Month',
    SUM(CASE WHEN DATEDIFF(MM, master.received, GETDATE()) = 1 THEN master.original1 ELSE 0 END) AS '$_Listed_Prior_Month',
    SUM(payhistory.paid1) AS '$_Collected'
    FROM Customer
    LEFT OUTER JOIN payhistory ON payhistory.customer=Customer.customer
    INNER JOIN master ON master.number=payhistory.number
    WHERE customer.customer IN (@allowedcustomers)
    GROUP BY Customer.Street1, Customer.Name


Share This Page