Video

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.

It wasnt a comment about your video. it was a comment about pivot itself..... thank you for the reply

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 think it could work, you can't remove NULLs though, only replace.

You're welcome!

Thanks so much.

Glad I could help.

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.

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.

Hi

​@@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 ?

It would depend on what you want the output to look like, it sounds like you'd need a SUM(CASE ... as a starting point.

Thanks so much.

I'm starting to think all the Microsoft documentation is written by AI xD. It's so overly complicated.

Glad I could help.

Thanks so much.

No problem.

Thanks so much.

Thank you for watching

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.

It's in the description.

@@BeardedDevData thanks, do you have video for windows functions with code pls

Sure, I have done a follow along video: czcams.com/video/lBcDSsgp0RU/video.html

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.

Thank you for your reply

Exec sp_helptext @object='procedure name',@columnname=billamount How use procedure?

I would recommend using a script activity.

I would recommend using a script activity.

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.

I understand where you are coming from but because we are grouping data we need to use an aggregate function otherwise the query would fail.

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.

Fantastic, glad I could help.

Unfortunately you wouldn't be able to run this code within a View because it's a dynamic query, you need to use a Stored Procedure.

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.

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.

You can create a window function in views but to see the table and columns being used you'd have to look at the definition of the view

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.

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.