Combining-Fields-with-Union

default

markdown

#Combining Multiple Event Based Fields with UNION

[[_TOC_]]

##About
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.

##Method 1

```sql
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
```

##Method 2

Additionally, an outer query may be used to further simplify the results of the UNION

```sql
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
```

Uploading file...

Header

Sidebar

![Izenda Logo](http://izenda.com/wp-content/uploads/2014/12/IzendaNewLogoBlueTR.png)

---

* [[Izenda 7 Series Documentation|https://www.izenda.com/docs/]]
* [[Release Notes]]
* [[Upgrade Best Practices|FAQ/Izenda-Update-Best-Practices]]
* [[FAQ]]
* [[Tutorials]]
* [[The Izenda API|/API/AdHocConfig]]
* [[The AdHocSettings class|/API/AdHocSettings]]
* [Developer Links and Guides](/Guides/Developer-Links-and-Guides)
* [[Known Issues]]

---

* <a href="http://www.izenda.com" rel="nofollow" target="_blank">Izenda Website</a>
* [Product Video](https://www.youtube.com/watch?v=X3-yWFq0w5A)
* <a href="http://www.izenda.com/blog" rel="nofollow" target="_blank">Izenda Blog</a>
* <a href="http://www.izenda.com/company/" rel="nofollow" target="_blank">About Us</a>
* <a href="http://www.izenda.com/contact-us/" rel="nofollow" target="_blank">Contact Us</a>

---
* [Guide To Report Design](/Guides/ReportDesign)
* [Making Custom Forms Video](http://www.youtube.com/watch?v=5b2axJlgdFs)

---

* [[Acknowledgements]]

Edit message:

Cancel

Editing Combining-Fields-with-Union

Header

Sidebar