CODEWITHSUNDEEP
sqlt-sqlreporting-services
Christian Love
Christian Love

Reputation: 59

Is there a way to associate a column with a date range?

I'm working on a YTD gross wages report. I am wondering if there is a way to associate a column with a date. My idea is that someone can select a date range or even a single ending month, and only return the GROSWAGS_X that is for that range. (1 is Jan, 2 is Feb, etc.) For example: User selects 1/1/2020 as StartDate and 10/31/2020 as EndDate. This would bring GROSWAGS 1-10, but exclude 11 and 12. OR User selects October 2020. This would bring back 1-10, excluding 11 and 12 as well. The environment is MSSQL 2019, using SSRS to create the report. An example format of the table is below.

EMPID  GROSWAGS_1  GROSWAGS_2  GROSWAGS_3...
00001  2000.00     2000.00     2000.00

edit: I was able to get what I needed by adding a calculated column in SSRS.. something like this:

=IIf(Month(Parameters!EndDate.Value)=1,Fields!GROSWAGS_1.Value,
IIf(Month(Parameters!EndDate.Value)=2,Fields!GROSWAGS_1.Value+Fields!GROSWAGS_2.Value,
IIf(Month(Parameters!EndDate.Value)=3,Fields!GROSWAGS_1.Value+Fields!GROSWAGS_2.Value+Fields!GROSWAGS_3.Value,"error")))

Upvotes: 0

Views: 51

Answers (2)

Alan Schofield
Alan Schofield

Reputation: 21703

This is not ideal but assuming that your column names are static then you could unpivot the data like this

I've replicated your data structure based on what you provided and added some sample data

-- these DECLARES should be removed from the report's dataset query
-- They are only present for testing
-- Replace references to these with he names of the Report's parameters

DECLARE @StartDate Date = '2020-02-01'
DECLARE @EndDate Date = '2020-11-01'

DECLARE @t TABLE (EMPID int, GROSWAGS_1 DECIMAL(10,2),  GROSWAGS_2  DECIMAL(10,2), GROSWAGS_3 DECIMAL(10,2)
                , GROSWAGS_4 DECIMAL(10,2), GROSWAGS_5 DECIMAL(10,2), GROSWAGS_6 DECIMAL(10,2), GROSWAGS_7 DECIMAL(10,2)
                , GROSWAGS_8 DECIMAL(10,2), GROSWAGS_9 DECIMAL(10,2), GROSWAGS_10 DECIMAL(10,2), GROSWAGS_11 DECIMAL(10,2)
                , GROSWAGS_12 DECIMAL(10,2) )

INSERT INTO @t VALUES
(1, 1000, 1100, 1200, 1300, 1400, 1500, 1600, 1700, 1800, 1900, 2000, 2100),
(2, 1001, 1101, 1201, 1301, 1401, 1501, 1601, 1701, 1801, 1901, 2001, 2101),
(3, 1003, 1103, 1203, 1303, 1403, 1503, 1603, 1703, 1803, 1903, 2003, 2103)

SELECT * 
    FROM 
        (
        SELECT EmpID, CAST(REPLACE(Mnth, 'GROSWAGS_','') AS INT) AS MonthNumber, Wage
        FROM @t
        UNPIVOT
            (
                Wage 
                FOR Mnth IN (GROSWAGS_1,  GROSWAGS_2, GROSWAGS_3, GROSWAGS_4, GROSWAGS_5, GROSWAGS_6, GROSWAGS_7, GROSWAGS_8, GROSWAGS_9, GROSWAGS_10, GROSWAGS_11, GROSWAGS_12 )
            ) as up
        ) a 
    WHERE a.MonthNumber BETWEEN MONTH(@StartDate) AND MONTH(@EndDate)

The resulting output looks like this (top few rows shown only)

enter image description here

Once you have the data in this format then it will be easy to consume in your report.

NOTE: There is no logic to handle years as this was not visible in your sample data.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

No. You would use separate rows. Something like this:

EMPID    PERIOD_START   PERIOD END    GROSS_WAGES

Each column would be on a separate row. Voila! You have the ranges conveniently as columns in the data.

Upvotes: 1

Related Questions