Recursive Creativity LLC

RECURSIVE CREATIVITY LLC

SQL SERVER, SQL


SQL SERVER


KEYBOARD SHORTCUTS

Microsoft SQL Server Management Studio (SSMS)

Last Updated: August 15, 2021 Top of Page


SQL


EXCEL @ SQL

Content here is specific to SQL used for querying data used in Excel workbooks, VBA and Add-ins.


PASSENGERS

File available per link below contains numerous .sql files that can be opened, then copied and pasted into SQL Server Mangement 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, Wiliam; Wick, Helen; Wick, John

    2. List of names by ID

      ID           Names
      AA12345 Newby, Michael; Newby, Wiliam
      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, Wiliam
      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