Stairway to DAX and Power BI - Level 21: Time Intelligence – Dates Functions: FIRSTDATE() and LASTDATE() (2023-03-01)

SQLServerCentral Newsletter for March 1, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to DAX and Power BI - Level 21: Time Intelligence – Dates Functions: FIRSTDATE() and LASTDATE()]( - [Join Redgate at SQL Bits, March 15-18]( - [Testing before coding: shifting farther left]( - [From the SQL Server Central Blogs - Metadata-driven pipelines in Azure Data Factory | Part 4 - Analytical Processing]( - [From the SQL Server Central Blogs - Writing a Hello World Go Container Web Application]( - [Transact-SQL: The Building Blocks to SQL Server Programming by Gregory A. Larsen]( Question of the Day - [Dropping Columns]( The Voice of the DBA  Daily Coping Tip Call a friend to catch up and really listen to them I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Stale Data Causes Security Issues Security has become better and better in many organizations. At the same time, hackers and malicious actors are doing a better and better job of finding new ways to attack systems. Some work to target specific individuals, often because of government or industrial espionage. Most of us aren't likely to deal with those issues, unless we work with (or are) someone that is very important in a particular situation. Instead, many of us deal with wider spread attacks that look to exploit vulnerabilities in technology or humans at scale, hoping to find the weak links. Lots of people I know have dealt with viruses in the past that shut down systems, and more recently, had to rebuild systems crippled by ransomware. Despite their best efforts, this often means lots of extra unexpected work, combined with the stress of falling behind on our commitments. We have plenty of other work to do. Windows has been vulnerable throughout its history, and it appears, lately through a data problem. There is a class of attacks that look to use approved, though old and unpatched, drivers as a vehicle for gaining a foothold inside a network. This was a problem (called the BYOVD issue) and Microsoft addressed this with a block list that was used to prevent the loading of vulnerable drivers. They updated this list through Windows Update. Except they didn't. In database terms, we had an eventually consistent set of data, which was being updated at Microsoft, but not being sent to client workstations. There were [over 3 years of the list not being updated](, despite assurances from Microsoft that Windows 10 PCs were protected. There are [instructions for manually updating your machine](. I don't envy this being a process I'd want to build. Getting data from security researchers or elsewhere, putting it in a database (I hope), then exporting this into a text format and getting that loaded into the Windows Update process, all while trying to ensure the process is secure along the way. That can't be easy inside a large company like Microsoft. At the same time, not noticing this wasn't working isn't excusable. Likely there were issues, but my guess is someone didn't want to admit a failure and get a bad annual review. Data sync issues are nothing new, and many of us struggle with these on a weekly basis. However, these are important issues. Replication can fill log files (and disks), broken ETL processes can cause execs to make poor decisions, and in the security space, not updating drivers and block lists leave us vulnerable. This situation isn't excusable and Microsoft ought to be ashamed. Some of these execs ought to lose bonuses, at the very least. It's also not excusable in our orgs. We ought to be sure we're patching on a regular basis and minimizing the attack surface area we present. It's the least we can do as IT professionals. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to DAX and Power BI]( [Stairway to DAX and Power BI - Level 21: Time Intelligence – Dates Functions: FIRSTDATE() and LASTDATE()]( Bill Pearson from SQLServerCentral Business Intelligence Architect, Analysis Services Maestro, and author Bill Pearson introduces two DAX Time Intelligence functions related to the Date: FIRSTDATE(), and LASTDATE(). He discusses the syntax, uses and operation of each function, and then provides hands-on exposure to it in Power BI. [External Article]( [Join Redgate at SQL Bits, March 15-18]( Additional Articles from Redgate From castles and dragons to sword fights and spells, this year’s SQL Bits will have it all. Make sure you don’t miss out on our great sessions, from Code Errors to Reliable Releases, to adding some PostgreSQL skills to your repertoire. Get 10% off registering with 10REDGATE [External Article]( [Testing before coding: shifting farther left]( Additional Articles from SimpleTalk A term I have only recently learned is "shift left testing." You can read more about this on Wikipedia here. The term was coined in 2001 and generally means testing earlier in the development lifecycle. Hence, shifting your testing left in the timeline. Just how left should you shift your testing, though? In my mind, so early, the rooster hasn't entirely fallen asleep yet. From the SQL Server Central Blogs - [Metadata-driven pipelines in Azure Data Factory | Part 4 - Analytical Processing]( Rayis Imayev from Data Adventures (2023-Feb-20) The previous posts covered the following areas of Metadata-driven pipelines in Azure Data Factory:Part 1 - Data CopyPart 2 - Feed ConfigurationPart 3 - Column MetadataThese 3 areas suggested that it is... [Blog Post]( From the SQL Server Central Blogs - [Writing a Hello World Go Container Web Application]( aen from Anthony Nocentino Blog In this blog post, I will show you how to build a hello world container-based web application in the go programming language. The reason I want to do this... [Transact-SQL: The Building Blocks to SQL Server Programming eBook by Gregory A. Larsen]( [Transact-SQL: The Building Blocks to SQL Server Programming by Gregory A. Larsen]( Greg Larsen from SQLServerCentral Transact SQL (TSQL) is the languaged used to query and update data stored in a SQL Server. This book, written by SQL Server Central and Simple Talk author Greg Larsen, will give developers an understanding of the basics of the TSQL language. Programmers will have the building blocks necessary to quickly and easily build applications that use SQL Server.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Dropping Columns I have a table in a SQL Server 2017 database that looks like this: CREATE TABLE ArticleSeries ( SeriesID INT NOT NULL, ArticleID INT NOT NULL, ArticleOrder TINYINT NOT NULL, ArticleAbstract VARCHAR(200), ArticlePublishDate DATE ) GO I have decided the ArticleAbstract and ArticlePublishDate can be removed as they are stored in another table. Can I delete them like this or do I need two batches? ALTER TABLE dbo.ArticleSeries DROP COLUMN ArticleAbstract, ArticlePublishDate GO Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) The Deleted Table Name I have built a DDL trigger to capture the drop of a table to prevent dropping a particular table. My trigger looks like this: CREATE TRIGGER NO_DROP_ImportantTable ON DATABASE FOR DROP_TABLE AS DECLARE @Table SYSNAME; SELECT @Table = ??? IF @Table = 'ReallyImportantTable' ROLLBACK; GO What should I put in the SELECT statement in place of the question marks? Answer: EVENTDATA ().value ('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'); Explanation: There are no inserted or deleted tables in a DDL trigger. Instead, data is captured and retrieved using the EVENTDATA() function. Ref: - DDL Triggers - - EVENTDATA() - [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [question on parallelism]( - Hi All, We have 16 CPU's, 256GB RAM Azure VM on which SQL Server 2017 is running. Our workload runs pretty well when MAXDOP = 1 and when we try to set the MAXDOP = 4 or MAXDOP = 8, we start seeing deadlocks all over and application team start complaining about slowness of some […] [Pagesplits question]( - Hi All, Does a DELETE operation cause PAGE Split? As far as I know we Page splits occur for only INSERT and UPDATE. monitoring for operation : LOP_DELETE_SPLIT Thanks, Sam SQL Server 2017 - Development [How to query performance baseline]( - Hi All, We don't have baselines in our env. I mean, no monitoring tool. We use sp_whoisactive every 5 mins. Currently, we are facing some query performance issues. Want to know what are the things to be considered or how to take query performance baselines? like rows counts, indexes, execution plans etc.. Also, suppose I […] [How to stop an XE after 2 hours automatically]( - Hi All, I want to start extended session via job and want it to stop automatically after 2 hours. How to accomplish it? ALTER EVENT SESSION ON SERVER STATE = START; GO ALTER EVENT SESSION ON SERVER STATE = STOP; GO Thanks, Sam [How to get the transaction mode of a spid.]( - Hi All, Which DMV will tell me the transaction mode of a SPID? I did check MSDN but couldn't get the answer. - Autocommit transaction - Implicit transaction - Explicit transaction - Batch-scoped transaction - Distributed transaction Regards, Sam Administration - SQL Server 2014 [Can't start SQL Server]( - I have a system with two disks in the C: drive and four disks in a RAID 5 setup for the D: drive. A few weeks back, one of the D: disks failed. No sweat, ordered another, plugged it in and let the RAID rebuild overnight. All seemed to be fine, I can log in […] SQL 2012 - General [SQL Server 2012 Standard Media/ISO]( - Does anyone have a copy of SQL Server 2012 Standard handy? I just need the ISO or DVD contents to install on a new server. I have the key already from the old server, but we don't have the media any longer. I checked online and can't seem to find a good place to download […] SQL Server 2019 - Administration [Upgrade to 2019 express from 2012 express check failed]( - Hello, I would like to upgrade my SQL Server 2012 Express instance (10.0.2100) to 2019 Express, but during the verification checks, I receive an error stating that Service Pack 2 is required to proceed. The issue is that I cannot find any SP2 for Express. Do I need to use the standard version instead?  […] SQL Server 2019 - Development [receiving duplicate key error when adding new records]( - I administer a database for a local chapter of a national group.  The National group does not provide local chapters access to a database but does give us a csv file which i then used to export to an Excel spreadsheet with all the members listed in it. I then imported that table into my […] [Find value of next row upon condition]( - In the following table,  I want to replace the zero in the column idPointsrv by the next row that is not a zero in this case replace all zero by 316770 Thanks in advance for your help [Using A CTE to exclude rows from the main Select statement]( - I have a cte that in short states SELECT mm.id1 mm.date ,mm.Datecompleted ,mn.number FROM table1 as mm left join table2 as m on mm.type = m.type left join table3 as mn on mm.id1 = mn.id1 where 1=1 and type = 29 and date is null and Completed = 1 I then have a select statement […] General [Informatica SQL generating basic statement]( - I'm newbie using Informatica Data Quality Analyst  and am unable to create a basic SQL statement. The Sql query is being created against a mapping specification of a table that was imported as a flat file originally. The statement is as follows: Select ColumnA, ColumnB FROM Table1 WHERE Table1.ColumnA = 'S' That SELECT.... FROM part […] Anything that is NOT about SQL! [Windows Server Alert When Service Doesn't Start - Not Working]( - I am trying to send an email when a Windows service doesn't start. What I did: I wrote a batch file that runs a short Powershell script to send an email. It works fine if I run the batch file by itself. I get an email. I have a service on my laptop set to […] Employers and Employees [Microsoft Sql Server DBA Salary - Full time vs Contract]( - I have 10 years of experience as regular full time employee as MSSQL DBA - unfortunately, with a small company with some 20+ on premise servers running about 100+ databases with no HA or clustering experience. The earlier roles before the DBA role, I was in tech support and web development. Comparing my salary to […] SQL Server 2022 - Development [Schedule a PowerBI dataset refresh on the "quarter" hour ???]( - Hi all, I know very little about Power BI, but I am hopeful that someone here has enough knowledge to know whether or not it is even possible to schedule a dataset refresh on the quarter-hour instead of merely on the half-hour or top of the hour.  I know that with SSRS, I can use […]   [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -