Search the Column on the DB (SQL Server)

          To find a particular column from all the tables available on the particular  DB, We can use the following sql query :

SELECT tab.name AS table_name,

col.name AS column_name

FROM sys.tables AS tab

INNER JOIN sys.columns col ON tab.object_id  = col.object_id

WHERE col.name LIKE '%SalesDoc%'  -- Column Name

To Find Duplicate Rows using Common Table Expressions and perform DML operations

Handling the Duplicates using CTE and Row_Number()  - (ie, To Delete or Update the Duplicates from SQL server Tables Using CTE, Row_Number() ) 

            Just to select the duplicate rows from a table, we can use the following kind of simple query. The below mentioned query will return duplicated rows based on the Product_Id :

                     

SELECT Product_id,

      COUNT(Product_id) AS NumOccurrences

FROM InventoryDetail

      GROUP BY Product_id

      HAVING ( COUNT(Product_id) > 1 )

If you want to Delete or Update the duplicate, we can use the following sample which will use the CTE (Common Table Expression) , Row_Number function and the MDL statement (Delete/ Update).

            

                      

WITH CTE (Product_id,Time_Id, DuplicateCount)

AS

(    

      SELECT Product_id,Time_Id,

                  ROW_NUMBER() OVER(PARTITION BY Product_id,Time_Id ORDER BY Product_id) AS DuplicateCount

      FROM InventoryDetail

)

DELETE

      FROM CTE

WHERE DuplicateCount > 1

SQL : To calculate the Time difference between the consecutive incidents(Time between incidents)

            If you want to calcualte the time difference between the first record and the next record - we can use the following SQL Query.

EX : Data

          In the above sample to calculate the Time difference between the First incident's closed Time and the next incident's Start Time (we can call it as a time between incidents)

SQL :



WITH CTE
                  as (

SELECT IncidentNumber, IncidentOpenDate, IncidentClosedDate,
               ( SELECT MAX(T1.IncidentClosedDate)
      FROM Trn_Incidents T1
      WHERE T1.IncidentClosedDate < T2.IncidentOpenDate
    ) AS Previous_IncidentEndTime
 FROM Trn_Incidents T2
                    )

SELECT IncidentNumber, IncidentOpenDate, IncidentClosedDate ,  DATEDIFF(MI,Previous_IncidentEndTime,IncidentOpenDate) AS IdleTime
FROM CTE

The Result would be :

Now we got the idle time between the incident sequence..!!

SQL server : Usage of "in" clause in "If" condition

                    I had a situation to pass multi-value parameter into SSRS data set and execute a query if the multi value parameter contains a value -'ABC' else execute another one query  (to put it in simple form) ..So i tried using the following syntax:


If @Param1 in ('ABC')  
Select  a,b from Table1


else 
select b,c from Table1
End



 we got surprised when the above syntax didn't work as expected..Then we identified the trick over the SQL syntax :

The IN clause should be in reverse order when it has been used in IF Conditions:


If @Param1 'ABC' in (@Param1)


Select  a,b from Table1


else 
select b,c from Table1
End

Then it started working fine..!