Sql server - useful scripts

--Over operator

SELECT row_number() over(ORDER BY prod.productid) rownum, cat.CategoryName , prod.ProductName FROM Categories cat INNER JOIN products prod ON cat.CategoryID=prod.CategoryID

SELECT  ROW_NUMBER() over(PARTITION BY cat.categoryid ORDER BY prod.productid) ProductWiseRowNum,
row_number() over(ORDER BY (SELECT 1)) rownum, cat.CategoryName,prod.ProductName
FROM Categories cat INNER JOIN products prod ON cat.CategoryID=prod.CategoryID

SELECT dense_rank() over(ORDER BY cat.categoryid) CategoryWiseRowNum,cat.CategoryName,ROW_NUMBER() over(PARTITION BY cat.categoryid ORDER BY prod.productid) ProductWiseRowNum,prod.ProductName
FROM Categories cat INNER JOIN products prod ON cat.CategoryID=prod.CategoryID

SELECT prod.ProductName, sum(ord.Quantity*ord.UnitPrice) [TotalAmout]
FROM [Order Details] ord
INNER JOIN Products prod ON ord.ProductID=prod.ProductID
GROUP BY prod.ProductName 

--Running totals - Aggregation with Over Operator - 2012

SELECT productname,
       TotalAmout,
       sum([TotalAmout]) OVER(ORDER BY productname) AS RunningTotal
FROM
  (SELECT prod.ProductName,
          sum(ord.Quantity*ord.UnitPrice) [TotalAmout]
   FROM [Order Details] ord
   INNER JOIN Products prod ON ord.ProductID=prod.ProductID
   GROUP BY prod.ProductName) t

SELECT cat.CategoryName,
       productname,
       TotalAmout,
       sum([TotalAmout]) over(PARTITION BY cat.categoryid
                              ORDER BY productname) AS RunningTotal
FROM
  (SELECT prod.CategoryID,
          prod.ProductName,
          sum(ord.Quantity*ord.UnitPrice) [TotalAmout]
   FROM [Order Details] ord
   INNER JOIN Products prod ON ord.ProductID=prod.ProductID
   GROUP BY prod.ProductName,
            prod.CategoryID) t
INNER JOIN Categories cat ON t.CategoryID=cat.CategoryID

--Lead and Lag
SELECT cust.CustomerID,
       cust.CompanyName,
       ord.OrderDate,
       LEAD(ord.OrderDate) over(PARTITION BY cust.companyname
                                ORDER BY ord.orderdate) [NextOrderDate],
       LAG(ord.OrderDate) over(PARTITION BY cust.companyname
                                ORDER BY ord.orderdate) [PreviousOrderDate]
FROM orders ord
INNER JOIN Customers cust ON ord.CustomerID=cust.CustomerID 

WITH CTE AS
  (SELECT cust.CustomerID,
          cust.CompanyName,
          ord.OrderDate,
          LEAD(ord.OrderDate) over(PARTITION BY cust.companyname
                                   ORDER BY ord.orderdate) [NextOrderDate],
                              LAG(ord.OrderDate) over(PARTITION BY cust.companyname
                                                      ORDER BY ord.orderdate) [PreviousOrderDate]
   FROM orders ord
   INNER JOIN Customers cust ON ord.CustomerID=cust.CustomerID)
SELECT CustomerID,
       CompanyName,
       ISNULL(DATEDIFF(dd,OrderDate,isnull(NextOrderDate,OrderDate)),0) [NextDateDifference],
       ISNULL(DATEDIFF(dd,isnull(PreviousOrderDate,OrderDate),OrderDate),0) [PreviousDateDifference],
       OrderDate,
       NextOrderDate,
       PreviousOrderDate
FROM CTE
 --select DATEDIFF(dd,'1997-08-25 00:00:00.000','1997-10-03 00:00:00.000')

 --Paging
DECLARE @PageNumber int=3 DECLARE @PageSize int=10 DECLARE @StartPage int,@EndPage int
SET @StartPage=@PageNumber*@PageSize-@PageSize+1
SET @EndPage=@PageNumber*@PageSize

SELECT *
FROM
  (SELECT ROW_NUMBER() Over(ORDER BY ord.orderid) AS RowNum,*
   FROM orders ord) t
WHERE t.RowNum BETWEEN @StartPage AND @EndPage

SELECT @StartPage,@EndPage

DECLARE @PageNumber int=3 DECLARE @PageSize int=10 DECLARE @StartPage int,@EndPage int
SET @StartPage=@PageNumber*@PageSize-@PageSize+1
SET @EndPage=@PageNumber*@PageSize

SELECT *
FROM
  (SELECT ROW_NUMBER() Over(ORDER BY(SELECT 1)) AS RowNum,*
   FROM orders ord) t
WHERE t.RowNum BETWEEN @StartPage AND @EndPage

SELECT *
   FROM orders ord
 ORDER BY ord.orderid
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY

DECLARE @PageNumber int=3 DECLARE @PageSize int=10 DECLARE @StartPage int,@EndPage int
SET @StartPage=@PageNumber*@PageSize-@PageSize+1
SET @EndPage=@PageNumber*@PageSize
SELECT *
   FROM orders ord
 ORDER BY ord.orderid
OFFSET @PageNumber*@PageSize-@PageSize ROWS
FETCH NEXT @PageNumber*@PageSize ROWS ONLY

--First_value and Last_Value
SELECT cust.CustomerID,
       cust.CompanyName,
       ord.OrderDate,
       First_Value(ord.OrderDate) over(PARTITION BY cust.companyname
                                ORDER BY (select 1)) [FirstOrderDate],
       Last_Value(ord.OrderDate) over(PARTITION BY cust.companyname
                                ORDER BY (select 1)) [LastOrderDate]
FROM orders ord
INNER JOIN Customers cust ON ord.CustomerID=cust.CustomerID 

WITH CTE AS
  (SELECT cust.CustomerID,
          cust.CompanyName,
          ord.OrderDate,
          LEAD(ord.OrderDate) over(PARTITION BY cust.companyname
                                   ORDER BY ord.orderdate) [NextOrderDate],
                              LAG(ord.OrderDate) over(PARTITION BY cust.companyname
                                                      ORDER BY ord.orderdate) [PreviousOrderDate]
   FROM orders ord
   INNER JOIN Customers cust ON ord.CustomerID=cust.CustomerID)
,
CTE2
AS(
SELECT CustomerID,CompanyName,AVG(NextDateDifference) [AvgDiff]
,IIF(AVG(NextDateDifference)<30,'1',IIF(AVG(NextDateDifference)>=30 and AVG(NextDateDifference)<90,'2',
IIF(AVG(NextDateDifference)>=90,'3','4'))) AS [Tag]
 FROM (SELECT CustomerID,
       CompanyName,
       ISNULL(DATEDIFF(dd,OrderDate,isnull(NextOrderDate,OrderDate)),0) [NextDateDifference],
       ISNULL(DATEDIFF(dd,isnull(PreviousOrderDate,OrderDate),OrderDate),0) [PreviousDateDifference],
       OrderDate,
       NextOrderDate,
       PreviousOrderDate FROM CTE) t
GROUP BY CustomerID,CompanyName
)
SELECT customerid,CompanyName,AvgDiff,
CHOOSE([Tag],'Important','Recommended','Normal','Ignore') [Flag]
 from CTE2

--iif and choose
 select iif(1=2,'a','b')
 select choose(3,'hello','friend','ghost')
 declare @var1 varchar(10),@var2 varchar(10)
 set @var1='Best'
 set @var2=null
 select @var1 + ' ' + @var2,concat(@var1,' ',@var2)

 --Complex Match Join
 SELECT * INTO #CITY FROM
(
SELECT 1 AS ID,'ind'  CITY
UNION ALL
SELECT 2 AS ID,'aus'  CITY
UNION ALL
SELECT 3 AS ID,'sri'  CITY
UNION ALL
SELECT 4 AS ID,'Eng'  CITY
)VW

select * from #CITY

SELECT c.id fromid,c.city fromcity,c1.id toid,c1.city tocity
INTO #team
FROM #CITY c
INNER JOIN #CITY c1 ON c.id <> c1.id

select * from #team

SELECT c.* FROM #team c
INNER JOIN #team c1 ON c.fromid = c1.toid AND c.toid = c1.fromid AND c1.fromid <=c1.toid
ORDER BY c.tocity

drop table #CITY
drop table #team

--Second Highest Salary
declare @n int;
set @n=2;
Select e1.*
from Employee e1 where (@n-1)=(Select COUNT(distinct salary) from Employee e2 where e2.Salary>e1.Salary);

--Second Highest Salary Department Wise
;WITH DepartmentWiseSalary AS
(
    SELECT    *,DENSE_RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) AS RowNum
    FROM    Employee
)
SELECT    *
FROM    DepartmentWiseSalary
WHERE    RowNum = 2;

--Getting All Level Child
;WITH cteEmployee (EmpId, FirstName, LastName, ManagerID, Level)
AS
(
    SELECT    EmpId, FirstName, LastName, ManagerID, 0 AS Level
    FROM    Employee
    WHERE    ManagerID = 2
   
    UNION ALL
   
    SELECT    e.EmpId, e.FirstName, e.LastName, e.ManagerID, Level + 1
    FROM    Employee e
            INNER JOIN cteEmployee AS d ON e.ManagerID = d.EmpId
)
SELECT    EmpId, FirstName, LastName, ManagerID,Level
FROM    cteEmployee;

--Finding Duplicates Record
WITH CTE
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY (Select 1)) [RowNum],* FROM EMPLOYEE
)
SELECT * FROM CTE c1
WHERE
1<(SELECT count(c2.RowNum) FROM CTE c2 WHERE c1.EmpId=c2.EmpId)

--PIVOT Count of employee joining as per YEAR
SELECT    *
FROM  
        (
          SELECT    Department,
                    YEAR(DOJ) AS [Year],
                    COUNT(EmpId) AS [EmployeeCount]
          FROM        Employee
          GROUP BY    Department, YEAR(DOJ)
        ) TT
        PIVOT
        (
              SUM([EmployeeCount])
              FOR [Year] IN ([2006],[2007],[2008],[2009],[2010],[2011])
        ) PT

--Count SPACE
DECLARE     @strName VARCHAR(1000)
SET        @strName = ' White Space is where the world and all distraction falls away '
PRINT        (LEN(@strName) - LEN(REPLACE(@strName, ' ', '')))
PRINT        (DATALENGTH(@strName) - DATALENGTH(REPLACE(@strName, ' ', '')))

--Optimize Below Query
SELECT        *
FROM        Student AS S
WHERE        DOB IN
            (
                SELECT        MAX(DOB)
                FROM        Student sp
                WHERE        YEAR(S.DOB) = YEAR(sp.DOB)
                GROUP BY    YEAR(sp.DOB)
            )
ORDER BY    DOB

--Optimized Query
WITH CTE
AS
(
SELECT        YEAR(DOB) [Year],max(DOB) [DOB]
                FROM        Student sp
                GROUP BY YEAR(DOB)
)
SELECT        *
FROM        Student AS S
join CTE ON        s.DOB =CTE.DOB

Cache Implementations in C# .NET

One of the most commonly used patterns in software development is Caching. It’s a simple, but a very effective concept. The idea is to reuse operation results. When performing a heavy operation, we will save the result in our cache container. The next time that we need that result, we will pull it from the cache container, instead of performing the heavy operation again.

For example, to get a person’s Avatar you might need a trip to the database. Instead of performing that trip every time, we will save that Avatar in the cache, pulling it from memory every time you need it.

Caching works great for data that changes infrequently. Or even better, never changes. Data that constantly changes, like the current machine’s time shouldn’t be cached or you will get wrong results.

In-process Cache, Persistant in-process Cache, and Distributed Cache

There are 3 types of caches:

• In-Memory Cache is used for when you want to implement cache in a single process. When the process dies, the cache dies with it. If you’re running the same process on several servers, you will have a separate cache for each server.
• Persistent in-process Cache is when you back up your cache outside of process memory. It might be in a file, or in a database. This is more difficult, but if your process is restarted, the cache is not lost. Best used when getting the cached item is expansive, and your process tends to restart a lot.
• Distributed Cache is when you want to have shared cache for several machines. Usually, it will be several servers. With a distributed cache, it is stored in an external service. This means if one server saved a cache item, other servers can use it as well. Services like Redis are great for this.

in-process cache:

https://www.nuget.org/packages/Microsoft.Extensions.Caching.Memory/

Microsoft.Extensions.Caching.Memory

public class WaitToFinishMemoryCache<TItem>

{

    private MemoryCache _cache = new MemoryCache(new MemoryCacheOptions());

    private ConcurrentDictionary<object, SemaphoreSlim> _locks = new ConcurrentDictionary<object, SemaphoreSlim>();

    public async Task<TItem> GetOrCreate(object key, Func<Task<TItem>> createItem)

    {

        TItem cacheEntry;

        if (!_cache.TryGetValue(key, out cacheEntry))// Look for cache key.

        {

            SemaphoreSlim mylock = _locks.GetOrAdd(key, k => new SemaphoreSlim(1, 1));

            await mylock.WaitAsync();

            try

            {

                if (!_cache.TryGetValue(key, out cacheEntry))

                {

                    // Key not in cache, so get data.

                    cacheEntry = await createItem();

                    _cache.Set(key, cacheEntry);

                }

            }

            finally

            {

                mylock.Release();

            }

        }

        return cacheEntry;

    }

}

Usage:

var _avatarCache = new WaitToFinishMemoryCache<byte[]>();

// ...

var myAvatar =

await _avatarCache.GetOrCreate(userId, async () => await _database.GetAvatar(userId));

With this, when trying to get an item, if the same item is in the middle of being created by another thread, you will wait for the other to finish first. Then, you will get the already cached item created by the other thread.