Filter Record based on month and Year column

Transact-SQL
1

I have a table

FY REG Year Month TU TSP TES TEGM TMS TMGM TTGMA STATUS
FY2021 Region 1 2021 4 17 3.19 2.01 5.39 0.10 5.40 0.28 A
FY2021 Region 1 2021 5 13 2.39 1.51 5.39 0.07 5.40 0.21 A
FY2021 Region 1 2021 6 17 3.19 2.01 5.39 0.10 5.40 0.28 A
FY2021 Region 1 2021 7 15 2.79 1.76 5.39 0.08 5.40 0.25 A
FY2021 Region 1 2021 8 19 3.59 2.26 5.39 0.11 5.40 0.32 A
FY2021 Region 1 2021 9 17 3.19 2.01 5.39 0.10 5.40 0.28 A
FY2021 Region 1 2021 10 19 3.59 2.26 5.39 0.11 5.40 0.32 A
FY2021 Region 1 2021 11 19 3.59 2.26 5.39 0.11 5.40 0.32 A
FY2021 Region 1 2021 12 19 3.59 2.26 5.39 0.11 5.40 0.32 A
FY2021 Region 1 2022 1 17 3.19 2.01 5.39 0.10 5.40 0.28 A
FY2021 Region 1 2022 2 17 3.19 2.01 5.39 0.10 5.40 0.28 A
FY2021 Region 1 2022 3 19 3.59 2.26 5.39 0.11 5.40 0.32 A

Here i want to filter the record based on fiscal year, in this case fiscal year start from April to march.
i want to sum up columns |TU|TSP|TES|TEGM|TMS|TMGM|TTGMA| based on month 4 and year 2021 to month 3 year 2022 . should be group by |FY|REG|STATUS|

Please help me.

2

this is the idea

... combine the year month and take 1st if the month

year = 2021 and month = 4
so first of month = get it in this format = 01042021 = convert to date

and do between 01042021 and 01032022

SELECT fy,
       reg,
       status,
       Sum(tu),
       Sum(tsp),
       Sum(ttes),
       Sum(ttegm),
       Sum(ttms),
       Sum(ttmgm),
       Sum(tttgma)
FROM   table
WHERE  year + month BETWEEN 202104 AND 202203
GROUP  BY fy,
          reg,
          status
3

hi

please see here ..

please click here for drop create sample data 
drop table sample_data 
go 

create table sample_data 
(
	FY	varchar(10) 
	, REG varchar(10)
	, Year int 
	, Month int 
	, TU int 
	, TSP decimal(5,2) 
	, TES decimal(5,2) 
	, TEGM decimal(5,2)
	, TMS decimal(5,2) 
	, TMGM decimal(5,2)	
	, TTGMA	decimal(5,2)
	, STATUS varchar(1)
)
insert into sample_data select 'FY2021','Region 1',	2021,	4 ,	17	, 3.19 ,	2.01 ,	5.39 ,	0.10,	5.40,	0.28 ,'A'



insert into sample_data select 'FY2021','Region 1',2021,5	,13	,2.39,1.51,5.39,0.07,5.40,0.21,'A'
insert into sample_data select 'FY2021','Region 1',2021,6	,17	,3.19,2.01,5.39,0.10,5.40,0.28,'A'
insert into sample_data select 'FY2021','Region 1',2021,7	,15	,2.79,1.76,5.39,0.08,5.40,0.25,'A'
insert into sample_data select 'FY2021','Region 1',2021,8	,19	,3.59,2.26,5.39,0.11,5.40,0.32,'A'
insert into sample_data select 'FY2021','Region 1',2021,9	,17	,3.19,2.01,5.39,0.10,5.40,0.28,'A'
insert into sample_data select 'FY2021','Region 1',2021,10	,19	,3.59,2.26,5.39,0.11,5.40,0.32,'A'
insert into sample_data select 'FY2021','Region 1',2021,11	,19	,3.59,2.26,5.39,0.11,5.40,0.32,'A'
insert into sample_data select 'FY2021','Region 1',2021,12	,19	,3.59,2.26,5.39,0.11,5.40,0.32,'A'
insert into sample_data select 'FY2021','Region 1',2022,1	,17	,3.19,2.01,5.39,0.10,5.40,0.28,'A'
insert into sample_data select 'FY2021','Region 1',2022,2	,17	,3.19,2.01,5.39,0.10,5.40,0.28,'A'
insert into sample_data select 'FY2021','Region 1',2022,3	,19	,3.59,2.26,5.39,0.11,5.40,0.32,'A'

select * from sample_data go

SELECT 
  * 	
FROM 
   sample_data
WHERE 
   cast(cast(year as varchar)  + cast(format (month, '0#') as varchar) as int ) > cast ( '202108' as int)

-- only thing to remember is in the filter condition give month as double digit

-- 2021 3 becomes 202103
-- 2021 12 stays 202112

image
image932×601 17.7 KB

4

Help us help you. As recommended in the past please provide the data in proper ddl and dml

Declare @sample table()

Insert into @sample

5

You should never perform functions on table columns, that can force a table scan(s) at run time. Instead do the calcs on the parameter values used to control the query.

DECLARE @month_count smallint
DECLARE @start_month tinyint
DECLARE @start_year smallint

SET @start_year = 2021
SET @start_month = 4
SET @month_count = 12

;WITH cte_year_months AS (
    SELECT 
        end_year = YEAR(end_date),
        end_month = MONTH(end_date)
    FROM (
        SELECT DATEADD(MONTH, @month_count, CAST(@start_year * 10000 + (@start_month - 1) * 100 + 01 AS varchar(8))) AS end_date
    ) AS end_date_calc
)
SELECT 
    FY, REG, STATUS,
    SUM(TU) AS TU,
    SUM(TSP) AS TSP--,
    --...
FROM sample_data 
CROSS JOIN cte_year_months
WHERE 
    ((year = @start_year AND month >= @start_month) OR year > @start_year AND month <= end_month)
GROUP BY FY, REG, STATUS
ORDER BY FY, REG, STATUS
6

Thanks for you reply. Only problem is parameter for date in coming full like 2021-03-01 from form datepicker, how i will convert date to yyyyMM. I need to used it in filter.

7

hi

i know only about T-SQL microsoft sql server

from datepicker .. to convert to yyyyMM you will have to think in other ways ..

what i mean
... is there anything in the software .. which you are working that will do that
.. if NOT then can you use something else that will do that for you
.. if NOT THAT also .. is there any work around

this is very general advice
Sorry .. i know only Transact -SQL Microsoft SQL Server

8

No reason to do all that work - the fiscal year is already calculated for you, so all you need to search for is the FY column equal to FY2021.

9

Heh... man... I agree! Looking at the table and the presence of the FY column, I couldn't figure out why there was any issue with just doing the sums on the desired columns and a WHERE clause on the FY column for a single hear or a GROUP BY on the FY column for sums by year for all years.

10

I took the approach that at some point they might want to include only part of fy(s); for example, if for any reason they need to list a calendar year.

Thus, I provided code that can handle any month range. Actually, in general I prefer code that is flexible enough to handle any reasonably expected range of conditions in the future rather than later someone having to rewrite the code to get that.

11

Thanks all of you for suggestion and help.

12

I totally agree with that. With that, I'll also say the design of this table is actually making sucking sounds. The REG column shouldn't contain a display name. The FY column should not exist in this table and it certainly should not contain a display name that has "FY" in the data. The Year and month column should not exist in this table. Only a DATE column containing the first of the month is needed to replace the FY, Year, and Month columns. All the fiscal year stuff should be available in (perhaps) a Calendar table (or very well written iTVF) that will support both Calendar and Fiscal Year aggregation and reporting.

Ah, but this table is actually a "reporting" table that should be in a "data warehouse" if it's going to exist at all and was designed to support reporting by Fiscal Year. It could also be used to support Calendar year reporting but it was designed to support Fiscal Year reporting. There's nothing wrong with writing code that specifically makes use of that functionality. The whole reason for the creation of such tables is to be able to easily make high performance reporting code. Recalculating what is already available in the table will only slow things down.

If you're not going to use the table for what it was designed to do and write code instead, then you need to get rid of the table altogether. It's just dead weight when you do otherwise.

13

Interesting, doesn't that disagree with what you posted earlier?
Heh... man... I agree! Looking at the table and the presence of the FY column, I couldn't figure out why there was any issue with just doing the sums on the desired columns and a WHERE clause on the FY column for a single [y]ear or a GROUP BY on the FY column for sums by year for all years.

And, yes, the table design is not normalized, although denormalizing the FY seems not unreasonable here. And of course REG should be encoded to a number, not varchar, but it may be in the real table and OP just chose to show name rather than region_id.

14

My apologies... I agree that it sounds that way. What I was truly agreeing with is that the FY, Year, and Month column are unnecessary to doing the things that people want to do with the data. They're just formatted columns for output or BI or whatever and I'd be hard-pressed to allow such a duplication of such denormalized data. Yeah... people that do BI and the like seem to like it so they can just do pivots and the like but lordy.

What I was talking about in the post that you've mentioned is actually doing it the right way and I agree with that and so sorry for the confusion. What I was getting at was if the table has to stay the way it is, then 1) do what the BI folks would expect to see to make it easier for them to maintain and 2) having 2 sources of the truth seriously rubs me the wrong way because of some nasty issues I've had to contend with in the past.

So, you're correct in the code you've written and I actually do support that. I'd also get after folks and tell then to drop the bloody denormalization for purposes of display.

Yep... there are pros and cons for both methods and a combination of methods and leave it at that with an apology to you, Scott.

1 Like