SQL Hell

Reporting Stored Procedure

with org(ID, Name, TypeID, ParentID, UserID, sort) as
(
  select
	la.ID, la.Name, la.TypeID, la.ParentID, la.UserID, convert(varchar(8000), la.Name)  
	from (
		select LocationID as ID, LocationTypeID as TypeID, Name as Name, ParentLocationID as ParentID, 0 as UserID
		from Location

		union all

		select 0 as ID,
			CASE WHEN RoleID = 1 THEN 7
			WHEN RoleID = 2 THEN 8
			WHEN RoleID = 3 THEN 9
			WHEN RoleID = 4 THEN 10
			WHEN RoleID = 5 THEN 11
			END as TypeID,
			FirstName + ' ' + LastName as Name,
			LocationID as ParentID,
			u.UserID
		from [User] u
		inner join UserLocation ul on ul.UserID = u.UserID
	) as la
	where la.ParentID = 1
	and la.ID = 4709
 
	union all
 
	select
	lb.ID, lb.Name, lb.TypeID, lb.ParentID, lb.UserID, convert(varchar(8000), sort + ' : ' + lb.Name)
	from (
		select LocationID as ID, LocationTypeID as TypeID, Name as Name, ParentLocationID as ParentID, 0 as UserID
		from Location

		union all

		select 0 as ID,
			CASE WHEN RoleID = 1 THEN 7
			WHEN RoleID = 2 THEN 8
			WHEN RoleID = 3 THEN 9
			WHEN RoleID = 4 THEN 10
			WHEN RoleID = 5 THEN 11
			END as TypeID,
			FirstName + ' ' + LastName as Name,
			LocationID as ParentID,
			u.UserID
		from [User] u
		inner join UserLocation ul on ul.UserID = u.UserID
	) as lb
	join org on org.ID = lb.ParentID
)

select Distributor, Account, Location, PAX, [StickerBucks Default Total], [StickerBucks GrossUp Total], [RO/SPIFF Total], Total = [StickerBucks Default Total] + [StickerBucks GrossUp Total] + [RO/SPIFF Total]
from
(
	select
	ID,
	CASE WHEN TypeID = 5 or TypeID = 3 THEN Name ELSE '' END as Distributor,
	CASE WHEN TypeID = 4 or TypeID = 2 THEN Name ELSE '' END as Account,
	CASE WHEN TypeID = 1 THEN Name ELSE '' END as Location,
	CASE WHEN TypeID = 7 or TypeID = 8 or TypeID = 9 or TypeID = 10 or TypeID = 11 THEN Name ELSE '' END as PAX,
	'StickerBucks Default Total' = CASE WHEN g.TypeID = 1 THEN
								ISNULL((SELECT SUM(ISNULL(sq.Quantity, 0) * (sq.Value - (sq.Value - 1)))
								FROM Claim c
								JOIN StickerQuantity sq ON c.ClaimID = sq.ClaimID
								JOIN Sticker s ON sq.StickerID = s.StickerID											
								WHERE c.LocationID = g.ID
								and s.StickerTypeID = '1'), 0)
							WHEN g.ID > 0 and g.TypeID > 1 THEN
								ISNULL((SELECT SUM(ISNULL(sq.Quantity, 0) * (sq.Value - (sq.Value - 1)))
								FROM Claim c
								JOIN StickerQuantity sq ON c.ClaimID = sq.ClaimID
								JOIN LocationTree lt ON c.LocationID = lt.ChildLocationID
								JOIN Sticker s ON sq.StickerID = s.StickerID
								WHERE lt.LocationID = g.ID
								and s.StickerTypeID = '1'), 0)
							WHEN g.ID = 0 and g.TypeID = 11 THEN
								ISNULL((SELECT SUM(ISNULL(sq.Quantity, 0) * (sq.Value - (sq.Value - 1)))
								FROM Claim c
								JOIN StickerQuantity sq ON c.ClaimID = sq.ClaimID
								JOIN Sticker s ON sq.StickerID = s.StickerID
								WHERE c.UserID = g.UserID
								and s.StickerTypeID = '1'), 0) 									
							ELSE
								0
							END,
	'StickerBucks GrossUp Total' = CASE WHEN g.TypeID = 1 THEN
								ISNULL((SELECT SUM(ISNULL(sq.Quantity, 0) * ISNULL(sq.Value - 1, 0))
								FROM Claim c
								JOIN StickerQuantity sq ON c.ClaimID = sq.ClaimID
								JOIN Sticker s ON sq.StickerID = s.StickerID
								WHERE c.LocationID = g.ID
								and s.StickerTypeID = '1'), 0)
							WHEN g.ID > 0 and g.TypeID > 1 THEN
								ISNULL((SELECT SUM(ISNULL(sq.Quantity, 0) * ISNULL(sq.Value - 1, 0))
								FROM Claim c
								JOIN StickerQuantity sq ON c.ClaimID = sq.ClaimID
								JOIN LocationTree lt ON c.LocationID = lt.ChildLocationID
								JOIN Sticker s ON sq.StickerID = s.StickerID
								WHERE lt.LocationID = g.ID
								and s.StickerTypeID = '1'), 0)
							WHEN g.ID = 0 and g.TypeID = 11 THEN
								ISNULL((SELECT SUM(ISNULL(sq.Quantity, 0) * ISNULL(sq.Value - 1, 0))
								FROM Claim c
								JOIN StickerQuantity sq ON c.ClaimID = sq.ClaimID
								JOIN Sticker s ON sq.StickerID = s.StickerID
								WHERE c.UserID = g.UserID
								and s.StickerTypeID = '1'), 0)
							ELSE
								0
							END,
	'RO/SPIFF Total' = CASE WHEN g.TypeID = 1 THEN
								ISNULL((SELECT SUM(ISNULL(sq.Quantity, 0) * ISNULL(sq.Value, 0))
								FROM Claim c
								JOIN StickerQuantity sq ON c.ClaimID = sq.ClaimID
								JOIN Sticker s ON sq.StickerID = s.StickerID								
								WHERE c.LocationID = g.ID
								and s.StickerTypeID = '2'), 0)
							WHEN g.ID > 0 and g.TypeID > 1 THEN
								ISNULL((SELECT SUM(ISNULL(sq.Quantity, 0) * ISNULL(sq.Value, 0))
								FROM Claim c
								JOIN StickerQuantity sq ON c.ClaimID = sq.ClaimID
								JOIN LocationTree lt ON c.LocationID = lt.ChildLocationID
								JOIN Sticker s ON sq.StickerID = s.StickerID
								WHERE lt.LocationID = g.ID
								and s.StickerTypeID = '2'), 0)
							WHEN g.ID = 0 and g.TypeID = 11 THEN
								ISNULL((SELECT SUM(ISNULL(sq.Quantity, 0) * ISNULL(sq.Value, 0))
								FROM Claim c
								JOIN StickerQuantity sq ON c.ClaimID = sq.ClaimID
								JOIN Sticker s ON sq.StickerID = s.StickerID
								WHERE c.UserID = g.UserID
								and s.StickerTypeID = '2'), 0)
							ELSE
								0
							END,
	sort
	from org g
	where TypeID NOT IN (7,8,9,10)
) as ss 
order by sort;