multiple UNIONs to same Function [Resolved]

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

multiple UNIONs to same Function [Resolved]

Author

Topic

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-02-20 : 10:44:39

I have a function this forum has helped me make with calculated columns.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=118916&SearchTerms=pivot
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=118654

I am pivotting these from columns into rows under columns: RoomCount and Occupancy.
I have done the Pivotting also using suggestions from this forum.

This is where I may need help.
I have used UNION to join diffferent SELECTs from the same function to show the different pivotted columns under a single column.

I want to check if there is a better way to join different pivotted columns from a function rather than calling it multiple times, e.g. using CTE

Here is a sample of how I use UNION multiple times on a single function (In my full version I union 12 seperate calls to the same function)

Note: the inputs to the function are the same each time - just the select is different.

Select * from (

SELECT [Year],[day],[Time],
HourlyTypeRoomCount as RoomCount,
TypeHourlyOccupancy as Occupancy,
[type],oDay,oTime
FROM [dbo].[fn_A] ('0708',0,1000,'abc','xyz')

UNION

SELECT [Year],[day],[Time],
HourlyAll_TypeRoomCount as RoomCount,
AllTypeHourlyOccupancy as Occupancy,
'All_Type' as [type],oDay,oTime
FROM [dbo].[fn_A] ('0708',0,1000,'abc','xyz')

UNION

SELECT [Year],'Weekly',[Time],
WeeklyHourlyAll_TypeRoomCount as RoomCount,
AllTypeWeeklyHourlyOccupancy as Occupancy,
'All_Type' as [type],'8' as oDay,oTime
FROM [dbo].[fn_A] ('0708',0,1000,'abc','xyz')

) as Sample

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 11:23:15

what does fn_A return? how does three batch differ?

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-02-20 : 11:50:08

It's the
[.....] as RoomCount,
[.....] as Occupancy

which varies in the each of the function select statements. These are calculated columns. fn_A does all the calculations using OVER(PARTITION BY ... to work out different Occupancies for different times of day and week.
I am UNION'ing these so that I have:

Time | Occupancy
10:00 | 10%
AM | 50%
Daily | 33%
etc

where each row relates to a different column in the function.

What I want to know is there a way to do a Select * from fn_A and then refer to these results rather than doing multiple calls to the function to retrieve a slightly different set of columns.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-20 : 12:04:57

yup. i guess what you need is just to cross tab the results from function. can you post what will be resultset from function (may be 5 lines) and then what you want as output from it

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-02-23 : 05:26:55

My function has the following columns:

select [Year],[day],[TimeSlot],
DailyAll_TypeRoomCount, DailyTypeRoomCount,HourlyTypeRoomCount,WeeklyHourlyTypeRoomCount,
WeeklyHourlyAll_TypeRoomCount,HourlyAll_TypeRoomCount,
WeeklyAM_PM_All_TypeRoomCount,WeeklyAM_PM_TypeRoomCount,
AM_PM_All_TypeRoomCount, AM_PM_TypeRoomCount,
TypeHourlyOccupancy,AllTypeHourlyOccupancy, TypeDailyOccupancy,
AllTypeDailyOccupancy,TypeWeeklyHourlyOccupancy,AllTypeWeeklyHourlyOccupancy,
TypeWeeklyDailyOccupancy, AllTypeWeeklyDailyOccupancy,
All_TypeWeeklyAM_PMOccupancy,
All_TypeAM_PMOccupancy,TypeAM_PMOccupancy,TypeWeeklyAM_PMOccupancy,
[type], oDay,oTime
from fn_A ('0708', 0, 1000,'test','101')

The following columns are pivotted into a RoomCount column:
DailyAll_TypeRoomCount, DailyTypeRoomCount,HourlyTypeRoomCount,WeeklyHourlyTypeRoomCount,
WeeklyHourlyAll_TypeRoomCount,HourlyAll_TypeRoomCount,
WeeklyAM_PM_All_TypeRoomCount,WeeklyAM_PM_TypeRoomCount,
AM_PM_All_TypeRoomCount, AM_PM_TypeRoomCount,

and the following are pivotted into an Occupancy column:
TypeHourlyOccupancy,AllTypeHourlyOccupancy, TypeDailyOccupancy,
AllTypeDailyOccupancy,TypeWeeklyHourlyOccupancy,AllTypeWeeklyHourlyOccupancy,
TypeWeeklyDailyOccupancy, AllTypeWeeklyDailyOccupancy,
All_TypeWeeklyAM_PMOccupancy,
All_TypeAM_PMOccupancy,TypeAM_PMOccupancy,TypeWeeklyAM_PMOccupancy,

So the final column list displayed is: [Year],[day],TimeSlot,RoomCount,Occupancy,[type],oDay,oTime

Here is full Query using 12 UNIONs:

Select * from (
--Hourly Type
SELECT [Year],[day],TimeSlot,
HourlyTypeRoomCount as RoomCount,
TypeHourlyOccupancy as Occupancy,
[type],oDay,oTime
FROM [dbo].[fn_A] (@Year,@MinCapacity,@MaxCapacity,@SC,@BC)

--Hourly All_Type
UNION

SELECT [Year],[day],TimeSlot,
HourlyAll_TypeRoomCount as RoomCount,
AllTypeHourlyOccupancy as Occupancy,
'All_Type' as [type],oDay,oTime
FROM [dbo].[fn_A] (@Year,@MinCapacity,@MaxCapacity,@SC,@BC)

--Weekly Hourly All_Type
UNION

SELECT [Year],'Weekly',TimeSlot,
WeeklyHourlyAll_TypeRoomCount as RoomCount,
AllTypeWeeklyHourlyOccupancy as Occupancy,
'All_Type' as [type],'8' as oDay,oTime
FROM [dbo].[fn_A] (@Year,@MinCapacity,@MaxCapacity,@SC,@BC)

--Weekly Hourly Type
UNION

SELECT [Year],'Weekly',TimeSlot,
WeeklyHourlyTypeRoomCount as RoomCount,
TypeWeeklyHourlyOccupancy as Occupancy,
[type],'8' as oDay,oTime
FROM [dbo].[fn_A] (@Year,@MinCapacity,@MaxCapacity,@SC,@BC)

--Weekly AM/PM Type
UNION

SELECT [Year],'Weekly',CASE
when TimeSlot in ('9:00','10:00','11:00','12:00') then 'AM'
when TimeSlot in ('13:00','14:00','15:00','16:00','17:00','18:00') then 'PM'
END AS TimeSlot,
WeeklyAM_PM_TypeRoomCount as RoomCount,
TypeWeeklyAM_PMOccupancy as Occupancy,
[type],'8' as oDay,
CASE
when TimeSlot in ('9:00','10:00','11:00','12:00') then 'x'
when TimeSlot in ('13:00','14:00','15:00','16:00') then 'y'
ELSE Null end as oTime
FROM [dbo].[fn_A] (@Year,@MinCapacity,@MaxCapacity,@SC,@BC)

--Weekly AM/PM All_Type
UNION

SELECT [Year],'Weekly',CASE
when TimeSlot in ('9:00','10:00','11:00','12:00') then 'AM'
when TimeSlot in ('13:00','14:00','15:00','16:00') then 'PM'
END AS TimeSlot,
WeeklyAM_PM_All_TypeRoomCount as RoomCount,
All_TypeWeeklyAM_PMOccupancy as Occupancy,
'All_Type','8' as oDay,
CASE
when TimeSlot in ('9:00','10:00','11:00','12:00') then 'x'
when TimeSlot in ('13:00','14:00','15:00','16:00') then 'y'
ELSE Null end as oTime
FROM [dbo].[fn_A] (@Year,@MinCapacity,@MaxCapacity,@SC,@BC)

--Daily Type
UNION

SELECT [Year],[day],'Daily' AS TimeSlot,
DailyTypeRoomCount as RoomCount,
TypeDailyOccupancy as Occupancy,
[type],oDay,'z' as oTime
FROM [dbo].[fn_A] (@Year,@MinCapacity,@MaxCapacity,@SC,@BC)

--Daily all_Type
UNION

SELECT [Year],[day],'Daily' AS TimeSlot,
DailyAll_TypeRoomCount as RoomCount,
AllTypeDailyOccupancy,
[type],oDay,'z' as oTime
FROM [dbo].[fn_A] (@Year,@MinCapacity,@MaxCapacity,@SC,@BC)

--Weekly Daily Type
UNION

SELECT [Year],'Weekly' as [day],'Daily' AS TimeSlot,
DailyTypeRoomCount as RoomCount,
TypeWeeklyDailyOccupancy as Occupancy,
[type],'8' as oDay,'z' as oTime
FROM [dbo].[fn_A] (@Year,@MinCapacity,@MaxCapacity,@SC,@BC)

--Weekly Daily all_Type
UNION

SELECT [Year],'Weekly' as [day],'Daily' AS TimeSlot,
DailyAll_TypeRoomCount as RoomCount,
AllTypeWeeklyDailyOccupancy as Occupancy,
[type],'8' as oDay,'z' as oTime
FROM [dbo].[fn_A] (@Year,@MinCapacity,@MaxCapacity,@SC,@BC)

--AM/PM Type
UNION

SELECT [Year],[day],CASE
when TimeSlot in ('9:00','10:00','11:00','12:00') then 'AM'
when TimeSlot in ('13:00','14:00','15:00','16:00') then 'PM'
END AS TimeSlot,
AM_PM_TypeRoomCount as RoomCount,
TypeAM_PMOccupancy as Occupancy,
[type],oDay,
CASE
when TimeSlot in ('9:00','10:00','11:00','12:00') then 'x'
when TimeSlot in ('13:00','14:00','15:00','16:00') then 'y'
ELSE Null end as oTime
FROM [dbo].[fn_A] (@Year,@MinCapacity,@MaxCapacity,@SC,@BC)

--AM/PM All_Type
UNION

SELECT [Year],[day],CASE
when TimeSlot in ('9:00','10:00','11:00','12:00') then 'AM'
when TimeSlot in ('13:00','14:00','15:00','16:00') then 'PM'
END AS TimeSlot,
AM_PM_All_TypeRoomCount as RoomCount,
All_TypeAM_PMOccupancy as Occupancy,
'All_Type',oDay,
CASE
when TimeSlot in ('9:00','10:00','11:00','12:00') then 'x'
when TimeSlot in ('13:00','14:00','15:00','16:00') then 'y'
ELSE Null end as oTime
FROM [dbo].[fn_A] (@Year,@MinCapacity,@MaxCapacity,@SC,@BC)

) as M

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-02-26 : 07:59:49

Found Solution using UNPIVOT and CASE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources