This documentation is for the legacy Izenda 6 product. Documentation for the new Izenda 7 product can be found at https://www.izenda.com/docs/
Event-based or time-series data is often spread across multiple fields or data sources which makes it difficult to report on. The UNION operator may be used to combine these fields into one and create an artificial categorical field which defines the type of event it is.
CREATE VIEW EventView AS SELECT OrderDate AS Date, 'Order' AS Event, ShipCountry AS Country FROM Orders UNION SELECT ShippedDate AS Date, 'Shipment' AS Event, ShipCountry AS Country FROM Orders UNION SELECT HireDate AS Date, 'Hire' AS Event, Country AS Country FROM Employees
Additionally, an outer query may be used to further simplify the results of the UNION
CREATE VIEW EventView AS SELECT DatePart(m,Date) as Month, DatePart(yyyy,Date) as YEAR, * FROM ( SELECT OrderDate AS Date, 'Order' AS Event, ShipCountry AS Country FROM Orders UNION SELECT ShippedDate AS Date, 'Shipment' AS Event, ShipCountry AS Country FROM Orders UNION SELECT HireDate AS Date, 'Hire' AS Event, Country AS Country FROM Employees ) AS IQ
Last edited by Joseph Adams, 2014-04-24 18:12:22