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
    mstopkey, Jun 21, 2007
    #2

  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
    FrankKalis, Jun 21, 2007
    #2

  3. mstopkey New Member

    Thanks for the help! I am trying it out now.
    mstopkey, Jun 21, 2007
    #2

  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


    mstopkey, Jun 21, 2007
    #2

  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


    mstopkey, Jun 21, 2007
    #2

Share This Page