Category Archives: Scripts

How to get Quarterly Data in Sql Server

Scripts, Sql Server, , , , , , , , ,

This article demonstrate how to get quarterly data in Sql Server in different formats as shown in the below image. Here Sales table data is presented in two different quarterly aggregated sales data formats.

Quarterly Sales Data
[ALSO READ] How to get Yearly data in Sql Server

Let us create a Temporary Sales table and insert 1000 Sample Sales Records With Random past 0-1000 days as the sales date by using the below script.

--Create Temporary Sales Table
CREATE TABLE #Sales
(SalesId INT IDENTITY(1,1), SalesDate DateTime)
GO
--Populate 1000 Sample Sales Records With 
--Random past 0-1000 days as sales date
INSERT INTO #Sales(SalesDate)
VALUES(DATEADD(dd, - CONVERT(INT, (1000+1)*RAND()),GETDATE()))
GO 1000

Demo 1: Getting Quarterly Sales Data

SELECT DATEPART(YEAR,SalesDate) [Year],
 DATEPART(QUARTER,SalesDate) [Quarter], COUNT(1) [Sales Count]
FROM #Sales
GROUP BY DATEPART(YEAR,SalesDate),DATEPART(QUARTER,SalesDate)
ORDER BY 1,2

RESULT:
Quarterly Data

Demo 2: Getting Quarterly Sales Data using PIVOT

SELECT Year, QPivot.[1] As Q1, QPivot.[2] As Q2, 
 QPivot.[3] As Q3, QPivot.[4] As Q4
FROM (SELECT YEAR(SalesDate) [Year],
	 DATEPART(QUARTER, SalesDate) [Quarter], 
         COUNT(1) [Sales Count]
      FROM #Sales
      GROUP BY YEAR(SalesDate), 
         DATEPART(QUARTER,SalesDate)) AS QuarterlyData
PIVOT( SUM([Sales Count])   
        FOR QUARTER IN ([1],[2],[3],[4])) AS QPivot

RESULT:
Quarterly Sales Data Using PIVOT

[ALSO READ]:
How to get Yearly data in Sql Server
How to get Monthly Data in Sql Server
How to get Daily Data in Sql Server
How to get Hourly data in Sql Server

PRINT/SELECT Statement messages within WHILE LOOP or BATCH of statement is not displayed immediately after it’s execution- Sql Server

Scripts, Sql Server, Tips/Tricks, , , , , ,

Are you facing the problem where the PRINT/SELECT statements messages are not being displayed like the one’s explained in the below two scenario’s? Let us go through these scenario’s and also see how to solve them.

[ALSO READ] WHILE loop in Sql Server

PROBLEM: PRINT/SELECT Statement values within a WHILE LOOP are not displayed when they are executed instead they are displayed at the end of the WHILE loop.

SET NOCOUNT ON
GO
DECLARE @iteration AS INT 
SET @iteration = 1
WHILE(@iteration<=10)
BEGIN
	SELECT 'Start of Iteration ' + CAST(@iteration AS VARCHAR)
	WAITFOR DELAY '00:00:01'
	PRINT 'End Of Iteration ' + CAST(@iteration AS VARCHAR)
	SET @iteration = @iteration + 1	
END

In the above script we are going through 10 iteration and each iteration has delay of 1 second. And during each iteration we are displaying messages using PRINT and SELECT statements. Here if we observe the execution of the above script, all the PRINT/SELECT statement messages are displayed at the end of loop i.e. after 10 seconds instead of it being displayed during each iteration (i.e. when it is executed).

As shown in the below image even after 8 seconds of execution of the above script not even a single message’s of the PRINT/SELECT are displayed.
PRINT_AND_SELECT_MESSAGES_ARE_NOT_DISPLAYED

SOLUTION: The reason why the PRINT/SELECT Statement Messages within a batch are not displayed is because Sql Server BUFFERS the output.

Below are the two way of solving this issue. Here I am using the RAISERROR WITH NOWAIT, and severity level is 10 which is sufficient enough for the Sql Server to consider it as a message instead of it being an error. RAISERROR WITH NOWAIT statement flushes all the buffered messages.

Note this works fine for the first 500 RAISERROR WITH NOWAIT statement’s only. After that SQL Server starts flushing 50 messages at a time.

SET NOCOUNT ON
GO
DECLARE @iteration AS INT,@message as VARCHAR(100)
SET @iteration = 1
WHILE(@iteration<=10)
BEGIN
	SET @message =  'Start of Iteration ' + CAST(@iteration AS VARCHAR)
	RAISERROR( @message, 10,1) WITH NOWAIT
	WAITFOR DELAY '00:00:01'
	SET @message =  'End Of Iteration ' + CAST(@iteration AS VARCHAR)
	RAISERROR( @message, 10,1) WITH NOWAIT
	SET @iteration+=1
END

————-OR————-

SET NOCOUNT ON
GO
DECLARE @iteration AS INT
SET @iteration = 1
WHILE(@iteration<=10)
BEGIN
	SELECT 'Start of Iteration ' + CAST(@iteration AS VARCHAR)
	WAITFOR DELAY '00:00:01'
	PRINT 'End Of Iteration ' + CAST(@iteration AS VARCHAR)
	SET @iteration+=1	

	RAISERROR('',10,1) WITH NOWAIT
END

RESULT: Below image demonstrates that the messages are displayed as and when the corresponding statement are executed:

PRINT_AND_SELECT_MESSAGES_ARE_NOT_DISPLAYED_SOLUTION

PROBLEM: PRINT/SELECT Statement values within a BATCH of statements are not displayed when they are executed instead they are displayed at the end of the execution of the BATCH.

PRINT 'PRINT MESSAGE'
SELECT 'SELECT MESSAGE'

WAITFOR DELAY '00:00:10'
GO

As shown in the below image even after 8 seconds of execution of the above script the PRINT/SELECT messages are not displayed.

PRINT_AND_SELECT_MESSAGES_ARE_NOT_DISPLAYED_1

SOLUTION:

PRINT 'PRINT MESSAGE'
 SELECT 'SELECT MESSAGE'
 RAISERROR('',10,1) WITH NOWAIT
 WAITFOR DELAY '00:00:10'
 GO

————-OR————-

RAISERROR('PRINT MESSAGE',10,1) WITH NOWAIT
RAISERROR('SELECT MESSAGE',10,1) WITH NOWAIT
WAITFOR DELAY '00:00:10'
GO

[ALSO READ] Looping through table records in Sql Server

How to Check if a String Contains a Substring in it in Sql Server

Scripts, Sql Server, , , ,

We can use the CHARINDEX() function to check whether a String contains a Substring in it. Name of this function is little confusing as name sounds something to do with character, but it basically returns the starting position of matched Substring in the main String. If it is not found then this function returns value 0.

Alternative to CHARINDEX() is using the LIKE predicate. Example 2 Demonstrates this.

Let us understand this with examples
Example 1: Using CHARINDEX() function

DECLARE @ExpressionToSearch VARCHAR(50) 
SET @ExpressionToSearch = 'Basavaraj Prabhu Biradar'
--Check whether @ExpressionToSearch contains the substring 
--'Prabhu' in it
IF  CHARINDEX('Prabhu', @ExpressionToSearch ) > 0 
	PRINT 'Yes it Contains'
ELSE
	PRINT 'It doesn''t Contain'

RESULT:
Substring_Within_String_Using_CHARINDEX

Example 2: Using LIKE Predicate

DECLARE @ExpressionToSearch VARCHAR(50)
DECLARE  @ExpressionToFind VARCHAR(50)
SET @ExpressionToSearch = 'Basavaraj Prabhu Biradar'
SET @ExpressionToFind = 'Prabhu'

IF @ExpressionToSearch LIKE '%' + @ExpressionToFind + '%'
    PRINT 'Yes it Contains'
ELSE
    PRINT 'It doesn''t Contain'

RESULT:
Substring_Within_String_Using_LIKE

You may like to read the other popular articles: