Started learning how to work with variables in T-SQL. Doing first basic exercise to declare today’s date and then print it, failed mizerably.
I was using Toad for SQL because this is my day to day tool. Tried several other examples, searched online for this error… looks like “it works”, but it doesn’t for me?!
Opened Microsoft SQL Management Studio, pasted same example, hit F5. Boom, worked! Ok, then it’s something related to Toad for SQL.
This is not a bug and that is why things are not changing. This is original behavior from Toad for Oracle.
There are different execution needs depending on what you are doing.
When you have a script you might want to only execute the SQL you are working on. For this you use F9 and we figure out what the current statement is based on your cursor and only execute that.
Or when you have a script you might want to execute all in the script from top down. For this you use F5.
There is a third option which is to execute from the current statement to the end. For this there is a menu action but no hot key.
In the case of a SQL that contains a script variable you can’t execute the current statement as the variable must be declared in block of code that is being sent to the server. So in this case F5 is the only option.
Someone asked me the other day below thing: “On the same column I want a Range of dates not Duration between two dates So I have a Creation date and fulfillment date I need to come up with a column of weekly Range like for example 10/21 – 10/27 10/28 – 11/01 11/02 – 11/09 Something like this.”
The solution can be pretty simple and mostly done through Power Query’s GUI.
Add a date range first using either way you want.
Then under Add Column – Date, you can easy add “Start of Week” and “End of Week”.
Note that if your week starts on Sunday, you don’t need to provide the first day of the week. I need my week to start on Monday so I added 1, instead of 0 or nothing “Date.EndOfWeek([Date],1)“.
The only thing to do now is to merge these two columns and put a separator of your choice. This will give you a column with Week’s start and end dates in the long format.
If you want month and date (or only date) you need to extract month and then date separately from both your Start and End of Week columns. Then merge month and date from Start of the week and again from End of the week.
At this moment, you only need to merge these 2 last columns, remove all unneeded columns and that’s it.
Here’s the entire query I ended up with.
let
Query1 = let
Source = #date(2019,1,1),
Custom1 = List.Dates(Source, Number.From(DateTime.LocalNow())- Number.From(Source) ,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}})
in
#"Converted to Table",
#"Changed Type" = Table.TransformColumnTypes(Query1,{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Date", Order.Descending}}),
#"Inserted Start of Week" = Table.AddColumn(#"Sorted Rows", "Start of Week", each Date.StartOfWeek([Date],1), type date),
#"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date],1), type date),
#"Inserted Merged Column" = Table.AddColumn(#"Inserted End of Week", "Long range", each Text.Combine({Text.From([Start of Week], "en-US"), Text.From([End of Week], "en-US")}, " - "), type text),
#"Inserted Month" = Table.AddColumn(#"Inserted Merged Column", "Month", each Date.Month([Start of Week]), Int64.Type),
#"Inserted Day" = Table.AddColumn(#"Inserted Month", "Day", each Date.Day([Start of Week]), Int64.Type),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Day", {{"Month", type text}, {"Day", type text}}, "en-US"),{"Month", "Day"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Start"),
#"Inserted Month1" = Table.AddColumn(#"Merged Columns", "Month", each Date.Month([End of Week]), Int64.Type),
#"Inserted Day1" = Table.AddColumn(#"Inserted Month1", "Day", each Date.Day([End of Week]), Int64.Type),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Day1", {{"Month", type text}, {"Day", type text}}, "en-US"),{"Month", "Day"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"End"),
#"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Start", "End"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Short range"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns2",{"Start of Week", "End of Week"})
in
#"Removed Columns"
SELECT [ProductModel], COUNT(1) CNT
FROM [AdventureWorks2017].[Production].[vProductAndDescription]
GROUP BY [ProductModel]
ORDER BY CNT DESC
Count “if”:
SELECT [ProductModel], [Name],COUNT(1) CNT
FROM [AdventureWorks2017].[Production].[vProductAndDescription]
WHERE [ProductModel] LIKE '%Frame' AND [Name] LIKE '%52' --here's the IF statement
GROUP BY [ProductModel], [Name]
ORDER BY CNT DESC
Count duplicates:
SELECT [ProductModel], COUNT(1) CNT -- select a field you want to search for duplicates in
FROM [AdventureWorks2017].[Production].[vProductAndDescription]
GROUP BY [ProductModel] HAVING COUNT(1) > 1 -- if count is greater than 1, then it is a duplicate by that field
ORDER BY CNT DESC
Group count fields a tabular form:
SELECT [ProductModel], [Name], COUNT(1) CNT
FROM [AdventureWorks2017].[Production].[vProductAndDescription]
GROUP BY [ProductModel], [Name]
ORDER BY CNT DESC
Ok, I don’t want to make it a long post, so here’s the thing: one of the Data Profiling feature is “Column distribution”.
Column Distribution allows you to get a sense for the overall distribution of values within a column in your data previews, including the count of distinct values (total number of different values found in a given column) and unique values (total number of values that only appear once in a given column).
So, distinct and/or unique. For anyone else these 2 words may seem similar, but for Data Guys like us, these are 2 different things (just like empty and null 🙂 ). Still confused? Here’s a very simple example to clear things up:
Let’s say we have a small company with 10 employees using 10 laptops from different Manufacturers: HP, Dell, Apple and Lenovo. Distinct: We have laptops from 4 different Manufacturers (HP, Dell, Apple and Lenovo) aka “total number of different values”, regardless of how many of each we have. Unique: We have only 2 laptops that nobody else have in our company (one from Apple and another one from Lenovo) aka “total number of values that only appear once”.
Thinking legacy, here’s the same thing in Excel, using countif or countifs.
Trying to install .NET Framework 3.5 Features on a Windows Server 2016, either through GUI or PowerShell, returns below error (truncated):
“The request to add or remove features on the specified server failed.
Installation of one or more roles, role services, or features failed.
The source files could not be found.”
Cause:
.Net Framework 3.5 sources file are not pre-installed so we need to explicitly specify where “installer” can get the source files from.
Solution:
As per Microsoft’s official docs, there are 2 main solutions to make it work.
Through GUI (Server Manager), when asked during the wizard, you need to specify the path to “Media:\Sources\SxS” folder.
Through PowerShell, Install-WindowsFeature -Name Web-Net-Ext -source D:\Sources\SxS
Still, Microsoft says that even if this defined source is not found, installer should go to Windows Update to download required files. Let’s exclude those many possibilities that Windows Update is not accessible due to various reasons (no internet connection, GPOs disabling something etc.).
Not sure what’s different with calling DISM directly (since Server Manager and Powershell probably does the same), but below solution worked for me.
Run this from PowerShell (including tilde characters): DISM.exe /Online /Add-Capability /CapabilityName:NetFx3~~~~ /Source:D:\Sources\SxS where D was the drive with my mounted Server 2016 ISO.
When above command is done, run this: Add-WindowsCapability –Online -Name NetFx3~~~~ –Source D:\Sources\SxS
Read his blog post to see how he reached to below conclusion:
DateAdd and SamePeriodLastYear both work based on the DYNAMIC period in the filter context.
ParallelPeriod is working STATICALLY based on the interval selected in the parameter.
ParallelPeriod and DateAdd can go more than one interval back and forward, while SamePeriodLastYear only goes one year back.
DateAdd works on the interval of DAY, as well as month, quarter and year, but ParallelPeriod only works on month, quarter, and year.
Depends on the filter context you may get a different result from these functions. If you get the same result in a year level context, it doesn’t mean that all these functions are the same! Look more into the detailed context.
At this moment, you only need to merge these 2 last columns, remove all unneeded columns and that’s it.
