CODEWITHSUNDEEP
sql-servert-sql
lpfy
lpfy

Reputation: 134

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

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

The last column is what I am going to create, and have no good way to design the SQL query.

When an itinerary issued, the visitors paid for Entry Fee and/or Camping Fee. If Both camping and entry paid, then we should count "number of people" from the row of camping fee (mark T). If an itinerary only have entry or camping, then mark T

Further explanation:

  1. Booking system had some bugs, so visitors may pay camping fee only and not buy entry ticket, e.g. 1005
  2. Booking system has the ability to make group purchase and indicate visitor info separately. e.g. 1003: two couples made one transaction, paid for both entry and camping

For ItineraryID 1001, total no. of people is 2, for 1003 total no. of people is 4. then For the above example table, to produce a total number of visitors, SUM(case when Type='Camping Fee' then NumberOfPeople else 0 end) OVER (PARTITION BY ItineraryID, Type) should be ok, just wondering is there any other robust way to do it?

And I am stuck at the flag column creation, the real table has over a million rows...

Upvotes: 0

Views: 59

Answers (1)

Caius Jard
Caius Jard

Reputation: 74660

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

Upvotes: 2

Related Questions