Dynamically UNION tables with similar names using SQL

-




We know it's not a good sign of database design to have multiple tables for the same data, but that's our case here anyway.
Suppose we have this database where revenue tables are stored in multiple tables and we want to dynamically union these tables without writing a long select every time.

This basically creates a variable that stores the text of the union all select, which gets the name of the tables dynamically from the sys tables
then trim the last union all
to make sure everything is alright you can print your query before executing it.
and here everything seems alright.


Replace PRINT with EXEC to execute your query and you are good to go.



Comments

Post a Comment

Popular posts from this blog

Integrating Python in Power BI With An Example Of Data Wrangling & Data Visualization

-
Image
  With a mix of Power BI & Excel's versatility and coding experience, You can easily wrangle any kind of data and get the most out of it. Let's see how we can use python in two processes (Data Wrangling & Data Visualization) inside Power BI Make sure that anaconda and python3 installed on your pc. Our Data set The dataset I'm using for this article is a Kaggle dataset that I've recently worked on you can check it out here what we are going to do is to transform some of my work in this notebook to power bi. 2. Data wrangling The data wrangling process in power bi is based inside the query editor After loading your data click on the transform tab then run a python script then you will need to import your libraries and run your code for me, the dataset only needed to replace some values and to get the date and time  for more information check out my Kaggle notebook You will get this after running your code this is the new dataset after running your code  so all you
Read more

REDUCE Your Measures With Calculation Groups In Power BI

-
Image
  You will need an external tool Tabular Editor for this. If you already don't have it, then please install it first. You can visit the Tabular Editor website to download the latest installer. Calculation groups are a new feature in DAX, inspired by a similar feature available in MDX known as calculated members. Calculation groups are easy to use; however, designing a model with calculation groups correctly can be challenging when you create multiple calculation groups or when you use calculation items in measures. Let's see first why calculation groups are useful: Imagine we have these measures: Sales Amount   := SUMX ( Sales , Sales[Quantity] * Sales[Net Price] ) Total Cost     := SUMX ( Sales , Sales[Quantity] * Sales[Unit Cost] ) Margin         := [Sales Amount] - [Total Cost] Sales Quantity := SUM ( Sales[Quantity] ) And we want to calculate YTD, MTd ...etc for these measures: YTD Sales Amount := CALCULATE (      [Sales Amount] ,      DATESYTD ( 
Read more

شرح الأداة الرائعة Tabular Editor للـ Power BI

-
Image
  مميزات أداة Tabular Editor للـ Power BI : يمكنك نسخ الـ Measures من تقرير لتقرير اخر بكل سهولة يمكنك عمل Calculation groups والتي ستقلل عدد الـ Measures في التقرير يمكنك عمل Folders لتقسيم الـ Measures بشكل واضح يمكنك عمل مئات الـ Measures بسهولة بدون ان يحتاج التقرير للتحميل عند كل مرة والذي سيوفر عليك وقت كبير! أولا لتحميل الاداة من Github اضغط  هنا  نسخ الـ Measures من تقرير لتقرير اخر بكل سهولة: يمكن نسخ جميع الـ Measures الخاصة بك من تقرير لاخر بشرط ان يكون التقريران بهما نفس البيانات لمعرفة الطريقة من هنا .  عمل Calculation groups والتي ستقلل عدد الـ Measures في التقرير: الـ Calculation groups هي طريقة لتقليل عدد الـ Measures فمثلا اذا كان عندك Measures لحساب قيم معينة وتريد ان تحسب كل القيم Year to Date أو Month To date وهكذا … بدلا من ان تنسخ كل هذه الـ Measures وتطبق عليها فلتر DATESMTD أو DATESYTD يمكن عمل calculation item والذي بدوره سيطبق هذا الفلتر على كل  الـ Measures  لمعرفة الطريقة من  هنا . عمل Folders لتقسيم الـ Measures بشكل واضح: يمكنك تقسم جميع الـ Measures الخاص
Read more