CODEWITHSUNDEEP
sqlsql-serverssrs-2008bids
NebDaMin
NebDaMin

Reputation: 658

SQL Server Reporting Services chart query. How to get rid of unions

I'm in ssrs and need to make a chart that compares 2 years of data on a single chart. Since charts in ssrs can only use columns of data for values and not rows I'm forced to write a very long query with lots of unions. I need the 12 months for the horizontal axis and I cant create a new table or any user defined functions. everything has to be in one query. Is there an easier way to accomplish this task? 

SELECT
'01' AS 'MONTH',
(SELECT COUNT(*) FROM Order JOIN Car ON Order.Car_ID = Car.ID JOIN CarType ON Car.CarType_ID = CarType.ID WHERE @Year = YEAR(Order.OrderDate) AND (@GPI IS NULL OR @GPI = CarType.GPI) AND (@CarType_ID IS NULL OR @CarType_ID = CarType.ID) AND '01' = MONTH(Order.OrderDate)) AS 'Orders1',
(SELECT COUNT(*) FROM Order JOIN Car ON Order.Car_ID = Car.ID JOIN CarType ON Car.CarType_ID = CarType.ID WHERE @Year -1 = YEAR(Order.OrderDate) AND (@GPI IS NULL OR @GPI = CarType.GPI) AND (@CarType_ID IS NULL OR @CarType_ID = CarType.ID) AND '01' = MONTH(Order.OrderDate)) AS 'Orders2'
UNION 
SELECT
'02' AS 'MONTH',
(SELECT COUNT(*) FROM Order JOIN Car ON Order.Car_ID = Car.ID JOIN CarType ON Car.CarType_ID = CarType.ID WHERE @Year = YEAR(Order.OrderDate) AND (@GPI IS NULL OR @GPI = CarType.GPI) AND (@CarType_ID IS NULL OR @CarType_ID = CarType.ID) AND '02' = MONTH(Order.OrderDate)) AS 'Orders1',
(SELECT COUNT(*) FROM Order JOIN Car ON Order.Car_ID = Car.ID JOIN CarType ON Car.CarType_ID = CarType.ID WHERE @Year -1 = YEAR(Order.OrderDate) AND (@GPI IS NULL OR @GPI = CarType.GPI) AND (@CarType_ID IS NULL OR @CarType_ID = CarType.ID) AND '02' = MONTH(Order.OrderDate)) AS 'Orders2'

--It goes on for 12 months

Upvotes: 1

Views: 445

Answers (2)

user359040
user359040

Reputation:

Try:

select MONTH(Order.OrderDate) as [MONTH],
       COUNT(CASE WHEN @Year = YEAR(Order.OrderDate) THEN 1 END) as [Orders1],
       COUNT(CASE WHEN @Year-1 = YEAR(Order.OrderDate) THEN 1 END) as [Orders2]
from Order 
JOIN Car ON Order.Car_ID = Car.ID 
JOIN CarType ONCar.CarType_ID = CarType.ID 
WHERE (@GPI IS NULL OR @GPI = CarType.GPI) AND 
      (@CarType_ID IS NULL OR @CarType_ID = CarType.ID)
group by MONTH(Order.OrderDate)

Upvotes: 3

Taryn
Taryn

Reputation: 247680

You can do with this a CURSOR in a stored procedure and loop through each of the month 1-12, you would just which out the SELECT statement with your SELECT statement.

CREATE PROCEDURE [dbo].[test]
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #temp
    (
        [Month]     int
        , Orders1   int
        , Orders2   int
    )

    DECLARE @MonthCount int
    SET @MonthCount = 1         

    WHILE @MonthCount <= 12
        BEGIN
        INSERT INTO #temp
        SELECT @MonthCount, (@MonthCount + 1) as Orders1, (@MonthCount + 2) as Orders2      

        SET @MonthCount = @MonthCount + 1
        END

    SELECT [Month], Orders1, Orders2
    FROM #temp

    DROP TABLE #temp
END

This loads the data into a temp table that is dropped once the procedure has completed.

Upvotes: 0

Related Questions