PASSENGERS

File available per link below contains numerous .sql files that can be opened, then copied and pasted into SQL Server Management Studio (SSMS).

File contents:

• Create Passengers database
• Create Passenger table
• Insert sample data into Passenger table
• Create Stored Procedures
  1. List of names in single cell
     
     Appleseed, Jane; Appleseed, Johnny; Jordan, Michael; Newby, Michael; Newby, William; Wick, Helen; Wick, John
     
     
  2. List of names by ID
     
     ID           Names
     AA12345 Newby, Michael; Newby, William
     AA12346 Appleseed, Jane; Appleseed, Johnny
     AA12348 Jordan, Michael
     AA12349 Wick, Helen; Wick, John
     
     
     
  3. List of names by Names
     
     ID           Names
     AA12346 Appleseed, Jane; Appleseed, Johnny
     AA12348 Jordan, Michael
     AA12345 Newby, Michael; Newby, William
     AA12349 Wick, Helen; Wick, John
     

There are endless possibilities for the first stored procedure above. One of my favorite uses is for Excel Add-ins for metadata. For example, want to know:

• all Accounts that roll up to a Parent Account?
• all Business Units that roll up to an Enode \ Entity \ Parent?
• everybody that reports to a given manager?
• all Accounts that are mapped to Financial Statement Line Item (FSLI) for GAAP? for IFRS?

The stored procedure uses STUFF, FOR XML PATH. However, in the Miscellaneous Queries file is alternative code using COALESCE.

My metadata Add-ins use the following nomenclature for Excel Functions: System_Known_Unknown.
For example, =HFM_Account_MembersBase("Account") would return all base level Members that roll up to a given Parent Account in HFM (Hyperion Financial Management).
Another example, =HFM_Entity_MembersBase("Entity") would return all base level Members that roll up to a given Parent Entity in HFM.

Passengers

Last Updated: September 18, 2021 Top of Page

BILLING DATA

Last Updated: September 18, 2021 Top of Page