Think Inside The Box

If you are using Packages and coupons, you probably have found that it can be difficult to see what is inside the package at a glance. This bit of sql will compress a package to a 2D dataset for easy viewing. Note - this will expand a show list to a list of events attached, but it will not do calculations on time-limitations. It only lists time limitations.

**updated 9/14/11 - old code had incorrect link from limitsetslink to packageitems.


declare @packageid int
set @packageid = 4

declare @item_type as table (code int, descr varchar(99))
insert @item_type (code, descr)
select 1,'Event'
union select 3,'Merchandise'
union select 4,'Series'
union select 5,'Memberships'
union select 6,'Events'
union select 7,'Shows'

declare @limittype as table (code int, descr varchar(99), source varchar(99))

insert @limittype (code, descr, source)
select 9,'Pricetype', 'pricetypes'
union select 10,'Subs Price Type', 'subscriptiontype'
union select 2,'Shows', 'shows'
union select 3,'Halls', 'halls'
union select 7,'Merchandise', 'merchandiseitems'
union select 8,'Price Levels', 'tariff'
union select 14,'Time Limitations', 'timelimitations'
union select 4,'Areas', 'areas'
union select 6,'Series', 'serieses'


declare @benefit_type table (code int, descr varchar(99))
insert @benefit_type (code, descr)
select 0,'No Benefit (qualifier)'
union select 1,'Nominal'
union select 2,'Percent'
union select 3,'Free Item'

declare @timelimitcat as table (code int, descr varchar(99))
insert @timelimitcat (code, descr)
select 1, 'Dates+Hours'
union select 2,'Days of Week + Hours'
union select 3,'Hours'

--select * from @item_type
--select * from @limittype
--select * from @benefit_type
--select * from @timelimitcat

SELECT
pgi_code,
pgi_BasketItemType,
isnull(it.descr ,'?') as item_type_descr,
pgi_MinItemsCount, pgi_MaxItemsCount,
pgi_BenefitType,
isnull(bt.descr,'?') as benefit_type_descr,
pgi_BenefitValue,
0 as pgi_PriceTypeForCommission,
'' as pricetype_for_commission,
lsl_LimitType,
isnull(limittype.descr,'?') as limit_type_descr,
lsl_LimitID,
lsl_AvailabilityType,
replicate(' ',50) as limit_descr,
isnull(eveventdate,'1/1/1900') as evdate
into #pkg
FROM
PKG_PackagesItems PKG_PackagesItems left outer JOIN
LimitSetsLink ON pgi_limitset = lsl_LimitSetID
left outer join @limittype as limittype on limittype.code = lsl_LimitType
left outer join @benefit_type as bt on bt.code = pgi_BenefitType
left outer join @item_type as it on it.code = pgi_BasketItemType
left outer join events on evshow = lsl_LimitID and lsl_limittype = 2
WHERE (pgi_Package = @packageid)
order by pgi_Package, pgi_code

update #pkg set limit_descr = 'All' where lsl_limittype in (10,9,2,3,7,8,4,6) and lsl_limitID = 0

update #pkg
set limit_descr = pctdescr
from #pkg
inner join PriceType ON lsl_limitID = pctCode
where lsl_limittype = 9

update #pkg
set limit_descr = sbtdescr
from #pkg
inner join subscriptiontype ON lsl_limitID = sbtCode
where lsl_limittype = 10

update #pkg
set limit_descr = left(shdescr,50)
from #pkg
inner join shows ON lsl_limitID = shcode
where lsl_limittype = 2

update #pkg
set limit_descr = left(haname,50)
from #pkg
inner join halls ON lsl_limitID = hacode
where lsl_limittype = 3

update #pkg
set limit_descr = left(srdescr,50)
from #pkg
inner join serieses ON lsl_limitID = srcode
where lsl_limittype = 6


update #pkg
set limit_descr = left(mitdescr,50)
from #pkg
inner join merchandiseitems ON lsl_limitID = mitcode
where lsl_limittype = 7

update #pkg
set limit_descr = left(tadescr,50)
from #pkg
inner join tariff ON lsl_limitID = tacode
where lsl_limittype = 8

update #pkg
set limit_descr =
case when lsl_availabilitytype = 1 then 'Available for: ' else 'Not Available for: ' end
+ left(ltg_description,50)
from #pkg
inner join LimitTimeGroup ON lsl_limitID = ltg_recordid
where lsl_limittype = 14


select * from #pkg
order by pgi_code, limit_type_descr, evdate, limit_descr

drop table #pkg