Hi, I am BeardedDev, a Data Engineer specialising in Microsoft technologies with a true passion for data and learning. I have experience across a variety of sectors and enjoy helping people develop and solve problems. On my channel I create tutorials focusing on T-SQL, Data Engineering, Data Analysis and Business Intelligence across SQL Server; SSIS, SSRS and SSAS and Azure; Azure SQL Database, Azure Data Factory, Azure Data Lake Storage, Azure Synapse Analytics and Azure Databricks.
Appreciate that, that's why I have separate videos on dynamic pivoting. You will find though even writing a basic query you will need to know the data.
Hi BeardedDev, Many thanks for this video. It's my first one, I'm only starting with Azure multiverse - via your videos. I see some difference in the functions available nowadays, I guess they have changed in the last couple of years... Most of it is not so important, of course. However, I did get desperately stuck at the step of authentication when connecting to SQL server on my SSMS [Error 18456]. After trying all possible trouble-shooting hints all over the internet, I (purely accidentally) discovered that my authentication method shuld be 'Entra MFI'. I was logged into my Azure account with my usual Microsoft account credentials...probably for that reason [how bad is this idea, btw?]. As far, as I understand, this whole Entra thing is new and slightly differs from the previous options. This might be worse some update on this video in the future or might be just useful info for some other viewers. 🙃 Once I overcame my authentication challenge, the rest went very smoothely - cause your video is really good! Thank you!!!
Hi, a lot does change now at rapid pace in the cloud, UIs can change quite frequently. Entra used to be Active Directory, that's always best practice, the reason I use SQL Server Authentication which is still supported is because I'm working on a personal subscription, I don't have Entra setup but great call its something I should call out. Great work on starting with Azure, it's lots of fun.
I really appreciate the amount of effort you put into this tutorial. Your hard work is not lost on me. Your explanation are clear and examples are easy to follow along with. Thanks again and God bless🙏🏽🙏🏽
This solution breaks down if there are not any sales for a day: it would grab sales from a neighboring day instead of counting the missing day as zero. How would you normalize the time series data to account for that?
The solution here is about running totals or averages rather than replacing values. Let's say we start our data on 01/01/2024 and sales were 0, on 02/01/2024 sales were 100. If we have a running total on 02/01/2024 it would be 100 taking into account the previous days sales of 0 and the current days of 100. We might want to do this if say we work for a company that has peaks of sales on weekends, we might want a rolling total of the last 7 days rather than peaks every weekend. All we have really done is change the scope of the dates considered in that particular column, this is easily communicated through column names such as rolling7daytotal. We can also add a description if we use a tool to output the data that allows us to, hopefully that helps.
If my table contains customer details having birthdate with other information Then my requirement is that I need birth date wise data (consider only 1 month in this case) with birthdate count column as Total Like Below Column name :--- '2013-03-01' having value 4 (4 is count of customer w.r.t. birthdate) '2013-03-02' having value 3 '2013-03-03' having value 5 Remaining column name :- '2013-03-04' to '2013-03-31' having value 0 or null Last column name :- birthdate_count=3 having count as per above (because three columns have value greater than 0 and remaining have 0 value)
This sounds more of a simple aggregation, first you'd need to manipulate the birthdate to get a consistent date for each month then group by it with a count.
@@BeardedDevData CREATE TABLE yourtable ([role] varchar(17), [familyname] varchar(8), [givenname] varchar(5), [skill] int, [level] varchar(15), [id] int, [date] datetime) ; INSERT INTO yourtable ([role], [familyname], [givenname], [skill], [level], [id], [date]) VALUES ('Vision Supervisor', 'Unsworth', 'Simon', 10, 'Telegenic Staff', 664, '2013-03-27 00:00:00'), ('Vision Superv', 'Unsworth', 'Simon', 10, 'Telegenic Staff', 664, '2013-03-07 00:00:00'), ('Vision Supervisor', 'Unsworth', 'Simon', 10, 'Telegenic Staff', 664, '2013-03-27 00:00:00'), ('Vision Supervor', 'Unsworth', 'Simon', 10, 'Telegenic Staff', 664, '2013-03-01 00:00:00') ; select date, (case when date is null then count(distinct date) else count(*) end ) daywisetotal from yourtable where date between '2013-03-01' and '2013-03-31' group by cube (date) having count(*)>0 ; => Output (this is the output what I required) date daywisetotal ----------------------- ------------ 2013-03-01 00:00:00.000 1 2013-03-07 00:00:00.000 1 2013-03-27 00:00:00.000 2 NULL 3 Need same output in pivot Like Total '2013-03-01' '2013-03-07' '2013-03-27' Row1 :- 3 1 1 2 But I am getting output in more than 1 row . Can please help me for getting same output in pivot ?
CREATE TABLE yourtable ([role] varchar(17), [familyname] varchar(8), [givenname] varchar(5), [skill] int, [level] varchar(15), [id] int, [date] datetime) ; INSERT INTO yourtable ([role], [familyname], [givenname], [skill], [level], [id], [date]) VALUES ('Vision Supervisor', 'Unsworth', 'Simon', 10, 'Telegenic Staff', 664, '2013-03-27 00:00:00'), ('Vision Superv', 'Unsworth', 'Simon', 10, 'Telegenic Staff', 664, '2013-03-07 00:00:00'), ('Vision Supervisor', 'Unsworth', 'Simon', 10, 'Telegenic Staff', 664, '2013-03-27 00:00:00'), ('Vision Supervor', 'Unsworth', 'Simon', 10, 'Telegenic Staff', 664, '2013-03-01 00:00:00') ; select date, (case when date is null then count(distinct date) else count(*) end ) daywisetotal from yourtable where date between '2013-03-01' and '2013-03-31' group by cube (date) having count(*)>0 ; => Output (this is the output what I required) date daywisetotal ----------------------- ------------ 2013-03-01 00:00:00.000 1 2013-03-07 00:00:00.000 1 2013-03-27 00:00:00.000 2 NULL 3 Need same output in pivot Like Total '2013-03-01' '2013-03-07' '2013-03-27' Row1 :- 3 1 1 2 But I am getting output in more than 1 row . Can please help me for getting same output in pivot ?
I need some help if in your example instead of product if its date (like monthly date '2012-03-01', '2012-03-02','2012-03-03','2012-03-04', so on.. upto 2012-03-31' ) and instead of customer total column i required monthly day count with respect to day value means if march month having total 31 day in that 25 day having value greater than 0 and remaining 6 day have null or 0 value then monthly day count should be 25 So please help in this case
Ditto all that. Truly the best format so a person can understand the terms as well as what is happening. As he builds the pivot table when he writes over the <words that define the functionality> you can understand what is happening. He uses terms like the spreader. Describing what is going to spread out or fan out the columns of data that will be displayed. Good job .....really good job.
Hi there, I’m new to ssis and connection manager- provider shows “Native OLE DB/SQL Server Native Client RDA 11.0” and giving me failure. If you could do a video about it that would be great.
Hey, that video and the explanation is absolutely great! Thanks a lot! Although everything works just fine I don’t understand why do we create the @query variable as a string :?
We need to use a valid data type for our parameter, in this case we are building the query, something of variable length that contains text. NVARCHAR(MAX) is overkill in this scenario.
How to find column name in proc Example goodsvalue+taxamount billamount i find the billamount in proc give the output text=goodsvalue+taxamount Any possible?
You can search anything as long as its in the definition of the stored procedure but remember you are just searching text. Can just pass in the column name, can even search expressions such as goodsvalue+taxamount but this won't return any stored procedures where the expression is written as goodsvalue + taxamount. Hopefully that provides some clarity.
great tutorial I like your style. But the table you worked on is not attached so I am giving it 6/10, thumb down. Please upload the table. Like now the video is below modern standards because it's hard to follow what you talked about.
Appreciate that, the idea behind was that if I show you how to do something and you can apply it with your data then you've learnt something, if I give you the data and you follow what I'm doing all you've done is follow a tutorial. I understand people might like to test things out first though so will take that on board.
It would depend on your acceptable range of collisions, certainly checksum is out the window, you'd have to use hashbytes with an algorithm that generates at least a 64-bit value. I've just had a look online for some numbers, found some details that say at 609 million rows you have a 1 in 100 chance of a collision, that is different inputs returning the same value, this may be acceptable as the alternative is column by column comparison to get 100% results. If this is a one off activity you could also use hashing then handle any collisions separately.
I have tried so many things in excel to get data to look like this, so i could load it into power bi, and with small amount of data excel can do the trick, but my man you just saved my ass at work. Thank you so much for this. :)
Hi BeardedDev, I do have a challenge that I am facing. Let say you are creating a view on top of multiple schemas dbo and dbo2, dbo2 has some auxiliary fields in new table, that in the future will become a single schema in dbo. How would you change data in that sort of view where insert is needed for both tables? Lets say I don't want to use triggers on the view and schema dbo cannot be changed. Will really be interested to know your answer. Thanks.
Think we should throw updating through a View out of the window for that scenario, this video was to show it can be done but it's very rare that it should be done. Creating the View on top of multiple tables is great but to run multiple inserts especially if the tables are related you need to use a stored procedure to control logic and add error handling.
Can you think of a scenario where you might use Unpivot Only Selected Columns in Power BI? I thought about it a lot, but I cannot come up with a real life scenario where this would be helpul. Anyway, you did a great job in explaining the differences. Thank you!
That's a really good question, I think there can be some scenarios but it all depends on what can happen to the underlying data, in the example use case I had somebody could add a subcategory to the file. Another case could be where we have a file per year and months as the headers, we would only want to unpivot the months in this case.
Wonderful video. Is there a way to created window functions in “Views” - where you can see the tables and fields being used? All while still performing a window function
The first thing you need to consider is do you want to truncate all tables or one at a time, this is important to consider because if you get any failures you will be left with blank tables, there are a couple of approaches to copying multiple datasets, the first is multiple copy activities, these can either be dependent in a chain or ran in parallel, the second option is metadata driven Pipelines where you can store the tables to copy within a table then call that table and look through.
Why we need to give columns in Group by clause which are using case statements? Select c1, c2='x' sum(values in c3) sum(values in c4) end as 'z' from table A where c5='Y' and c6='w' group by c1, ( Do we need to use c2 here ) c1, Z column (I need Z values grouped by only c1 column )
I think you're asking why we need to use the Customers table, that's because the discount depends on the CustomerType. If you can clarify the question a bit more, I can help.
Good question, with set operators they are used for a comparison between sets or to combine sets, as an example, we might have a table representing employees and another representing customers, we might want a set of employees who are also customers. In terms of joins, we need more information than we have in the existing table, following on from the previous example, we may want to left join customers to employees to see when an employee is a customer what their employee number is. In terms of set operators, we aren't adding any more columns but are adding rows if combining, in terms of joins we are typically adding more columns and maybe more rows depending on the data we join. Hope that helps.