SSRS - SUM based on condition

SSRS - SUM based on condition

 

In this article , let us see an example on how to SUM based on condition in SSRS tablix report .

Consider below query is my report dataset :

DECLARE @tmp TABLE (Code VARCHAR(10),Level1 INT,Level2 INT)
INSERT @tmp SELECT 'CHN',5,10
INSERT @tmp SELECT 'KOL',5,15
INSERT @tmp SELECT 'IND',10,25
INSERT @tmp SELECT 'NY',5,10
INSERT @tmp SELECT 'TX',5,15
INSERT @tmp SELECT 'US',10,25
SELECT * FROM @tmp

Consider , I have tablix report as shown in below image :

 

In the above shown report , If we are required to SUM values in the Columns Level1 and Level2
, only for Code = "IND" and Code = "US" , then we need to write expression as shown below :

In the Total data row , under Level1 column expression:

=Sum(IIF(Fields!Code.Value="IND" OR Fields!Code.Value="US",Fields!Level1.Value,0))

In the Total data row , under Level2 column expression:

=Sum(IIF(Fields!Code.Value="IND" OR Fields!Code.Value="US",Fields!Level2.Value,0))

Reference : 
http://technet.microsoft.com/en-us/library/ms159134(v=sql.100).aspx

 

Transaction in Sql Server

               

In this article, I am sharing the concept of transaction in sql server and how it is implemented.
Transactions run as a group of commands or queries as single execution unit. It ensures either all the commands or queries will be executed or none of them.
Once transaction is opened, it needs to be either committed or rolled back using following commands.

 

·         Begin Tran: To start a transaction.

·         Commit Tran: To commit all the executed statements.

·         Rollback Tran: To rollback all the executed statements.

 

Let's create a table and see how transaction works with some examples

 

create table TableA( empid varchar(10), empname varchar(10))

Let's insert some rows in the table within the transaction and rollback the transaction

 

begin tran

    insert into TableA

    select 'EMP001', 'Sandeep' union all

    select 'EMP002', 'Abhay'    

rollback

select * from TableA

OUTPUT

Look at the output, we inserted 2 rows but there is no row in the table as we have rolled back the transaction.

 

Now lets run the same statements with commit.

 

begin tran

    insert into TableA

    select 'EMP001', 'Sandeep' union all

    select 'EMP002', 'Abhay'    

commit

select * from TableA

OUTPUT

Look at the output now, we run the same statements with commit and this time the rows are inserted

 

New Date and Time Functions in SQL Server 2012

New Date and Time Functions in SQL Server 2012

 

SQL Server 2012, code named Denali, has introduced some new DATE and TIME Functions. In this post, we will discuss on how these new functions are used.

Here is the list of New DATE and TIME Functions

 

-       DATEFROMPARTS

-       TIMEFROMPARTS

-       DATETIMEFROMPARTS

-       DATETIME2FROMPARTS

-       SMALLDATETIMEFROMPARTS

-       DATETIMEOFFSETFROMPARTS

-       EOMONTH

DATEFROMPARTS 

 

The DATEFROMPARTS function, returns a date value with the date part set to the specified year, specified month and the specified day, and the time portion set to the default as shown in the below query result.

 

DECLARE @YEAR  INT = 2012,
        @MONTH INT = 1,
        @DAY   INT = 1

SELECT DATEFROMPARTS (@YEAR, @MONTH, @DAY) AS [Result]
GO

Result
----------
2012-01-01 

 

TIMEFROMPARTS

 

The TIMEFROMPARTS function, returns a full time value as shown in the below query result.

It is important to note that the fractions argument actually depends on the precision argument.

 

For example:

 

When fractions have a value of 5 and precision has a value of 1, then the value of fractions represents 5/10 of a second.

 

·         When fractions have a value of 50 and precision has a value of 2, then the value of fractions represents 50/100 of a second.

·         When fractions have a value of 500 and precision has a value of 3, then the value of fractions represents 500/1000 of a second.

 

DECLARE @HOUR    INT = 11,
        @MINUTE  INT = 59,
        @SECONDS INT = 59

SELECT TIMEFROMPARTS (@HOUR, @MINUTE, @SECONDS, 500, 3) AS [Result]
GO

Result
------------
11:59:59.500 

 

DATETIMEFROMPARTS

 

The DATETIMEFROMPARTS function,  returns a full datetime value as shown in the below query result.

 

DECLARE @YEAR         INT = 2012,
        @MONTH        INT = 1,
        @DAY          INT = 9,
        @HOUR         INT = 11,
        @MINUTE       INT = 59,
        @SECONDS      INT = 59,
        @MILLISECONDS INT = 0

SELECT DATETIMEFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS,
       @MILLISECONDS)
       AS [Result]
GO

Result
-----------------------
2012-01-01 11:59:59.000 

DATETIME2FROMPARTS

 

The DATETIME2FROMPARTS function, returns a full datetime2 value as shown in the below query result.

 

DECLARE @YEAR    INT = 2012,
        @MONTH   INT = 1,
        @DAY     INT = 1,
        @HOUR    INT = 11,
        @MINUTE  INT = 59,
        @SECONDS INT = 59

SELECT  
  DATETIME2FROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, 500, 3)
  AS [Result]
GO

Result
-----------------------
2012-01-01 11:59:59.500 

 

SMALLDATETIMEFROMPARTS

 

The SMALLDATETIMEFROMPARTS function, which is available in SQL Server 2012, returns a full smalldatetime value as shown in the below query result.

 

DECLARE @YEAR   INT = 2012,
        @MONTH  INT = 1,
        @DAY    INT = 1,
        @HOUR   INT = 11,
        @MINUTE INT = 59

SELECT SMALLDATETIMEFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE) AS [Result]
GO

Result
-------------------
2012-01-01 11:59:00 

 

DATETIMEOFFSETFROMPARTS

 

The DATETIMEOFFSETFROMPARTS function, returns a full datetimeoffset data type as shown in the below query result. The OFFSET argument is basically used to represent the time zone offset value hour and minutes.

 

DECLARE @YEAR    INT = 2012,
        @MONTH   INT = 1,
        @DAY     INT = 1,
        @HOUR    INT = 11,
        @MINUTE  INT = 59,
        @SECONDS INT = 59

SELECT DATETIMEOFFSETFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS,
       500, 5, 30, 3) AS [Result]
GO

Result
------------------------------
2012-01-01 11:59:59.500 +05:30 

 

EOMONTH

 

The EOMONTH function, calculates the last date of the month based on the date which is passed as an input parameter.

 

DECLARE @STARTDATE DATETIME = GETDATE()

SELECT EOMONTH (@STARTDATE) AS [Last Date of Month]
GO

Last Date of Month
-----------------------
2012-01-31 00:00:00.000 

 

TSQL script - CTE to remove duplicate rows

TSQL script - CTE to remove duplicate rows

// Creating Table and Inserting Data //

CREATE TABLE #Table (C1 INT,C2 VARCHAR(10))

INSERT INTO #Table VALUES (1,'SQL Server')

INSERT INTO #Table VALUES (1,'SQL Server')

INSERT INTO #Table VALUES (2,'Oracle')

// Retrieving Data //

SELECT * FROM #Table


-- Delete Duplicate rows --
  
;WITH Delete_Duplicate_Row_cte

AS (SELECT ROW_NUMBER()OVER(PARTITION BY C1, C2 ORDER BY C1,C2) ROW_NUM,*

FROM #Table )

DELETE FROM Delete_Duplicate_Row_cte WHERE ROW_NUM > 1

-- Retrivieng Records after deleting duplicate rows --

SELECT * FROM #Table

 -- Drop the table --
DROP TABLE #Table