sql server - In T-SQL What is the best way to handle groups within groups?

Question

This issue is from a booking system, the task is to add a flag to the table and also produce total number of visitors.

ItineraryID	BookingID	Type	NumberOfPeople	*Flag
1001	211	Entry Fee	2	F
1001	212	Camping Fee	2	T
1002	361	Entry Fee	4	T
1003	388	Entry Fee	2	F
1003	389	Entry Fee	2	F
1003	390	Camping Fee	2	T
1003	391	Camping Fee	2	T
1005	401	Camping Fee	2	T

Answer 1

Consider this:

WITH tots AS(
  SELECT 
    itineraryID, 
    SUM(CASE WHEN Type = 'Entry Fee' THEN NumberOfPeople END) as E,
    SUM(CASE WHEN Type = 'Camping Fee' THEN NumberOfPeople END) as C
  FROM
    t
  GROUP BY itineraryID
)

If we join this back to our table (SELECT * FROM t JOIN tots ON t.itineraryID = tots.itineraryID) then we can use the E and C values per row to work some things out:

• If E or C is 0 then mark T ("If an itinerary only have entry or camping, then mark T")
• If E = C and it's a Camping row then mark 'T'
• If E = C and it's an Entry row mark 'F'

After this logic is done in a SELECT CASE WHEN, you just need to convert it to an UPDATE JOIN where you modify t (UPDATE t SET flag = CASE WHEN ... FROM t JOIN tots ...)

Or you can make a new table with the result of the select (or you can make a view this it and just query it and it will work out the T/F dynamically each time)

---

NB: Your example data didn't seem to consider what happens if 2 entry and 4 camping are bought.. But it's easy to extend the logic