Query with a twist having pivot requirement

Question

Query with a twist having pivot requirement

Hall of Fame

Points: 3069

October 12, 2022 at 5:26 am

Hello friends, i am trying to get one type of data which has some weird mappings and pivot requirement as well. Here is the sql to create sample data:

create table #temp_module
(module_id int, module_name varchar(50), metrics int, createdate datetime)

insert into #temp_module
select 1, 'account', 2, getdate() union all
select 2, 'inventory', 5, getdate()

create table #temp_metrics_hierarchy_mapping
(metrics int, hierarchy_id int)

insert into #temp_metrics_hierarchy_mapping
select 1, 1 union all
select 2, 1 union all
select 2, 2 union all
select 3, 1 union all
select 3, 5

create table #temp_approach
(cob_month date, approach_id int, report_id int, approach varchar(100), createdate datetime)

insert into #temp_approach
select '2022-09-01', 1, 1, 'Region', getdate() union all
select '2022-09-01', 3, 1, 'town 1', getdate() union all
select '2022-09-01', 8, 1, 'town 2', getdate() union all
select '2022-09-01', 1, 2, 'Region', getdate() union all
select '2022-09-01', 5, 2, 'city 1', getdate() union all
select '2022-10-01', 1, 1, 'Region', getdate() union all
select '2022-10-01', 3, 1, 'town 1', getdate() union all
select '2022-10-01', 8, 1, 'town 2', getdate() union all
select '2022-10-01', 9, 1, 'town 3', getdate() union all
select '2022-10-01', 1, 2, 'Region', getdate() union all
select '2022-10-01', 5, 2, 'city 1', getdate()

create table #temp_approach_hierarchy_mapping
(cob_month date, approach_id int, hierarchy_id int, hierarchygrouplist varchar(max), createdate datetime)

insert into #temp_approach_hierarchy_mapping
select '2022-09-01', 1, 1, 'region_south, region_south_west, region_east', getdate() union all
select '2022-09-01', 3, 1, 'region_west, region_south, region_south_west, region_east', getdate() union all
select '2022-09-01', 8, 1, 'region_south, region_south_west, region_north_east, region_north', getdate() union all
select '2022-09-01', 10, 1, 'region_north, region_north_west', getdate() union all
select '2022-10-01', 1, 1, 'region_south, region_south_west, region_east', getdate() union all
select '2022-10-01', 3, 1, 'region_west, region_south, region_south_west, region_east', getdate() union all
select '2022-10-01', 8, 1, 'region_south, region_south_west, region_north_east, region_north', getdate() union all
select '2022-10-01', 10, 1, 'region_north, region_north_west', getdate()


create table #fact
(module_id int, cob_month date, business varchar(50), hierarchygroup varchar(100), amt numeric(28,10), createdby varchar(50), createdate datetime)

insert into #fact
select 1, '2022-09-01', 'banking fees', 'region_east', 2058.1020, 'ax210', getdate() union all
select 1, '2022-09-01', 'banking fees', 'region_east', 8055.3385, 'ax210', getdate() union all
select 1, '2022-09-01', 'banking fees', 'region_east', 685.50, 'ax210', getdate() union all
select 1, '2022-09-01', 'banking fees', 'region_north_east', 12000.5825, 'ke105', getdate() union all
select 1, '2022-09-01', 'banking fees', 'region_north_west', 520.300, 'cj290', getdate() union all
select 1, '2022-09-01', 'banking fees', 'region_west', 782.3100, 'cj290', getdate() union all
select 1, '2022-09-01', 'banking fees', 'region_south_west', 1090.2535, 'ax210', getdate() union all
select 1, '2022-09-01', 'mortgage', 'region_north_east', 10580.3800, 'ax210', getdate() union all
select 1, '2022-09-01', 'mortgage', 'region_north_east', 25946.4860, 'ax210', getdate() union all
select 1, '2022-09-01', 'mortgage', 'region_east', 36080.1040, 'mk260', getdate() union all
select 1, '2022-09-01', 'mortgage', 'region_west', 12050.8720, 'da762', getdate() union all
select 1, '2022-09-01', 'mortgage', 'region_south_west', 36050.2200, 'da762', getdate() union all
select 1, '2022-09-01', 'green energy', 'region_east', 55.920, 'ge850', getdate()

Here are the values for a particular set of parameters for which i need to generate report:

declare @business varchar(50) = 'banking fees',
 @cob_date date = '2022-09-01',
 @module_id int = 1,
 @report_id int = 1


select * from #temp_module where module_id = @module_id
select * from #temp_metrics_hierarchy_mapping where metrics in(select metrics from #temp_module where module_id = @module_id)
select * from #temp_approach_hierarchy_mapping where hierarchy_id in(select hierarchy_id from #temp_metrics_hierarchy_mapping where metrics in(select metrics from #temp_module where module_id = 1)) and cob_month = @cob_date
select * from #temp_approach where cob_month = @cob_date and report_id = @report_id and approach_id in(select approach_id from #temp_approach_hierarchy_mapping where hierarchy_id in(select hierarchy_id from #temp_metrics_hierarchy_mapping where metrics in(select metrics from #temp_module where module_id = 1)) and cob_month = @cob_date)
select * from #fact where cob_month = @cob_date

I need the result in this format:

business,total,[Region],[town 1],[town 2]
banking fees,7,4,5,2
mortgage,5,2,3,3
green energy,1,1,1,0

Here, the approaches from table #temp_approach for these parameters are becoming columns and the number of approaches can vary month to month and for different set of parameters. Total is the total count of records for that business and rest counts are approach wise.

This topic was modified 2 years, 1 month ago by sqlenthu 89358.
This topic was modified 2 years, 1 month ago by sqlenthu 89358.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 1

I'm not really clear about how the data fits together, but this query matches your expected results.

This is for a fixed number of approaches, if you want this to be dynamic look here

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

select f.business,
       ca.total,
       count(case when ap.approach = 'Region' then ap.approach end) as Region,
       count(case when ap.approach = 'town 1' then ap.approach end) as [town 1],
       count(case when ap.approach = 'town 2' then ap.approach end) as [town 2]
from #temp_module md
inner join #temp_metrics_hierarchy_mapping mh on mh.metrics = md.metrics
inner join #temp_approach_hierarchy_mapping ah on ah.hierarchy_id = mh.hierarchy_id and ah.cob_month = @cob_date
inner join #temp_approach ap on ap.cob_month = ah.cob_month and ap.report_id = @report_id and ap.approach_id = ah.approach_id
inner join #fact f on f.cob_month = ah.cob_month
  and f.hierarchygroup in (select trim(value) from string_split(ah.hierarchygrouplist,','))
                  --and f.business = @business
cross apply (select count(*) from #fact f2 where f2.cob_month = f.cob_month and f2.business = f.business) ca(total)
where md.module_id = @module_id
group by f.business,ca.total
order by ca.total desc;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537