Stairway to DAX and Power BI - Level 20: Time Intelligence – Date Functions: ENDOFMONTH(), ENDOFQUARTER(), ENDOFYEAR() (2022-12-21)

SQLServerCentral Newsletter for December 21, 2022 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to DAX and Power BI - Level 20: Time Intelligence – Date Functions: ENDOFMONTH(), ENDOFQUARTER(), ENDOFYEAR()]( - [The Basics of Deleting Data from a SQL Server Table]( - [Maintaining a Utilities Schema in a Flyway Project]( - [From the SQL Server Central Blogs - Effectively Integrating FHIR Data from Azure Health Services]( - [From the SQL Server Central Blogs - T-SQL Tuesday #157 – Closing out 2022]( Question of the Day - [The SQL Server Upgrade Targets]( The Voice of the DBA  Daily Coping Tip Listen wholeheartedly to someone else, without judging 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. CPU Upgrades I expected [this to be an article from Glenn Berry](. He often tries to convince me to upgrade. I think so he can justify buying his own new hardware. In this case, however, this is a note from Tom's Hardware that it's a great time to upgrade your CPU. The article is a few weeks old, referencing [Cyber Monday]( deals, and it notes that CPU sales are at a 30-year low. This means there's a lot of supply and retailers are trying to clear out stock. I don't know how many of you find your CPU slow, or if you have access to change the CPU (often you can't in a laptop). If you have a desktop, then you might not have a motherboard that supports the latest CPUs. Glenn would say upgrade your motherboard, but as I've seen in the past, this sometimes means memory upgrades as well. Many of us work with server machines, but we aren't responsible for the hardware. We often can't even request different hardware. Glenn has [written about which CPUs work for SQL Server](, though not for a few years. Maybe this piece will get him to update that article. SQL Server 2022 is the latest version, and the price as increased. The performance per core is more important than ever, so choosing the best CPU could have a big impact on the ROI of your database server. Even if you use the cloud, often there are choices in hardware, and expressing your reasoning for one over the other to the groups responsible for infrastructure can help ensure your organization gets the most performance for their billing. And if you're the person specifying hardware, you definitely ought to understand the differences between the CPU choices and make the best choice for your budget. 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 20: Time Intelligence – Date Functions: ENDOFMONTH(), ENDOFQUARTER(), ENDOFYEAR()]( Steve Jones - SSC Editor from SQLServerCentral Business Intelligence Architect, Analysis Services Maestro, and author Bill Pearson introduces three DAX Time Intelligence functions related to the End of Periods: ENDOFMONTH(), ENDOFQUARTER(), and ENDOFYEAR(). He discusses the syntax, uses and operation of each, and then provides hands-on exposure to the function in Power BI. [External Article]( [The Basics of Deleting Data from a SQL Server Table]( Additional Articles from SimpleTalk n my last article “Updating SQL Server Data” I discussed using the UPDATE statement to change data in existing rows of a SQL Server table. In this article I will be demonstrating how to use the DELETE statement to remove rows from a SQL Server Table. [External Article]( [Maintaining a Utilities Schema in a Flyway Project]( Additional Articles from Redgate This article demonstrates how to manage these utilities from Flyway so that we can maintain and migrate them separately from the database objects. [Blog Post]( From the SQL Server Central Blogs - [Effectively Integrating FHIR Data from Azure Health Services]( DataOnWheels from DataOnWheels This blog is intended to be a follow up from the SQL Saturday 2022 in Oregon & SW Washington. In that session I presented an introduction to FHIR and... [Blog Post]( From the SQL Server Central Blogs - [T-SQL Tuesday #157 – Closing out 2022]( gbargsley from GarryBargsley This month’s T-SQL Tuesday is being hosted by ME. The subject I chose is “What are your year-end activities?” My post for T-SQL Tuesday #157 will outline some examples...   Question of the Day Today's question (by Steve Jones - SSC Editor):  The SQL Server Upgrade Targets Using the SQL Server Migration Assistant, when coming from Access, DB2, Oracle, MySQL, or SAP ASE, which versions of SQL Server can I target? I am ignoring Azure Synapse as a separate offering. 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) Recursive Triggers How do I allow direct recursive triggers to fire? With direct recursive triggers, I mean an AFTER trigger that fires because of a data modificati0n and inside the trigger, there is another data modification to the same table, which would cause the trigger to fire again. Answer: do nothing, this is the default setting Explanation: This is controlled by the server trigger recursion setting, which defaults to 1. So nothing needs to be done. Ref: Server trigger recursion Server Configuration Option - [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 - Development [A question about unique keys]( - I've been away from SQL Server for a number of years. Years ago what I am asking could not be done. I assume that it still can't be done, but rather than make an assumption I thought that I should ask. Below is a completely fictitious example, but it illustrates my question. Supposing that I […] SQL Server 2016 - Administration [SPN/KERBEROS issue]( - Hi everyone. I would like to kerberize SQL connections in the following situation and have not been able to: I have a single server with two SQL instances and only one IP (the server's). I can't configure more IPs due to infrastructure limitation: SERVER1.domain.com --> 10.10.10.1 I start each instance on a different port using […] SQL Server 2016 - Development and T-SQL [For Each Loop Query (SSIS)]( -  In above screenshot can anyone tell me how can I enter value of "Files" field as expression? [calculate 12 months of data from a given date from a user]( - I have a query that it seems to be off dont know what wrong but I need to show last 12 months of data based off a user input for a date. my query is this. My results show different in SSRS. /*Declare @Date as Date = '11/1/2022' Declare @prop as int = 8000 --*/SELECT […] [calculate 12 months of data from a given date from a user]( - I have a query that it seems to be off dont know what wrong but I need to show last 12 months of data based off a user input for a date. my query is this. My results show different in SSRS. /*Declare @Date as Date = '11/1/2022' Declare @prop as int = 8000 --*/ […] [calculate 12 months of data from a given date from a user]( - I have a query that it seems to be off dont know what wrong but I need to show last 12 months of data based off a user input for a date. my query is this. My results show different in SSRS. /*Declare @Date as Date = '11/1/2022' Declare @prop as int = 8000 --*/ […] Administration - SQL Server 2014 [Error on File]( - Hi Experts, I have a database with multiple files on multiple drives and one of the drive is restricted to a defined size. Now because the index maintenance job is failing with below error. How can I rectify this without increase the disk space.  Could not allocate space for object 'dbo.Table1'.'PK_Table1_ID' in database 'DB1' […] [how to check the table is being used(selecting/inserting/updating/deleting/locki]( - how to check the table is being used(selecting/inserting/updating/deleting/locking) by some sql statement? many thanks! SQL 2012 - General [SQL Server 2012 Auditing Question]( - I am currently trying to set up SQL Server audit specifications, but they are not showing with the following query: select distinct(eventid) from sys.fn_trace_geteventinfo(1) When I run the query it only shows defaults. However prior to installing KB2958429 (SP 2), the audit specifications manually selected showed up with the query. So, I installed KB3045321 (a […] Azure Data Factory [Upskill Azure Data Factory knowledge]( - Hey guys, I'm a DE with of 2 experience and I've been working with Azure Data Factory as the main ETL tool for 6 months now. However, I feel I need to gather more knowledge and assume more responsibilities inside my team but most of the senior professionals don't have much time to teach me. […] SQL Server 2022 - Development [Using SQL - Convert column to rows - not a straight forward pivot]( - Hello All, I have below sample table. Code is also included. create table Trade_test (portfoliocode nvarchar(10), tradedate varchar(10), tradeamount varchar(10) ); insert into Trade_test values('A','01012021','100') insert into Trade_test values('A','01012022','1000') insert into Trade_test values('B','01012018','100') insert into Trade_test values('C','01012019','100') I need to pivot the data to have below output. I have tried few things but not getting […] [What's the logic of the NOT IN clause?]( - I thought I understood the "NOT IN" logic very well until I run into a case where the presence of NULL values affects the result in a way I was not expecting. The setup is super simple: CREATE TABLE A1(ID1 INT); CREATE TABLE A2(ID2 INT); INSERT INTO A1(ID1) VALUES (1), (2), (NULL); INSERT INTO A2(ID2) […] [Struggle Bus - Varbinary(max) Performance Suggestions?]( - I have a very large table I am dealing with that has a varbinary(max) column.  I have a Order By that is extremely slow on this field and also I have a Convert as well to a NVARCHAR(MAX) but even without the convert it is slow.  How can I somehow increase the performance on this […] [I have to do a price increase on our parts. My question is history records.]( - For each part there must be a history record of price from to price to. Is there a better way to do this than a cursor? Use M2MDATA01 declare @part char(30) declare @rev char(3) Declare @price numeric(15,5) declare @newprice numeric(15,5) Declare @now datetime = current_timestamp declare @new varchar(max) declare @old varchar(max) ALTER TABLE [dbo].[inmastx] Disable […] [height_cm -> 167.60 centimeter need to convet height_FT column and Height_IN]( - height_cm -> 167.60 i have data in sql server column that data i need to insert HeightFT column feet and remain Inches should go into Height_In column.   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -