COUNT DISTINCT VALUES in a Column

 COUNT DISTINCT VALUES in a Column

=SUM(1/COUNTIF(range, range))

=SUM(1/COUNTIF(B2:B71,B2:B71))

sp_wholock

 --Create Procedure WhoLock

--AS

SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#locksummary') IS NOT NULL

    DROP TABLE #locksummary;

IF OBJECT_ID('tempdb..#lock') IS NOT NULL

    DROP TABLE #lock;

CREATE TABLE #lock

(

    spid INT,

    dbid INT,

    objId INT,

    indId INT,

    Type CHAR(4),

    resource NCHAR(32),

    Mode CHAR(8),

    status CHAR(6)

);

INSERT INTO #lock

EXEC sp_lock;

IF OBJECT_ID('tempdb..#who') IS NOT NULL

    DROP TABLE #who;

CREATE TABLE #who

(

    spid INT,

    ecid INT,

    status CHAR(30),

    loginame CHAR(128),

    hostname CHAR(128),

    blk CHAR(5),

    dbname CHAR(128),

    cmd CHAR(16),

                   --

    request_id INT --Needed for SQL 2008 onwards

--

);

INSERT INTO #who

EXEC sp_who;

PRINT '-----------------------------------------';

PRINT 'Lock Summary for ' + @@servername + ' (excluding tempdb):';

PRINT '-----------------------------------------' + CHAR(10);

SELECT LEFT(loginame, 28) AS loginame,

       LEFT(DB_NAME(dbid), 128) AS DB,

       LEFT(OBJECT_NAME(objId), 30) AS object,

       MAX(Mode) AS [ToLevel],

       COUNT(*) AS [How Many],

       MAX(   CASE

                  WHEN Mode = 'X' THEN

                      cmd

                  ELSE

                      NULL

              END

          ) AS [Xclusive lock for command],

       l.spid,

       hostname

INTO #LockSummary

FROM #lock l

    JOIN #who w

        ON l.spid = w.spid

WHERE dbid != DB_ID('tempdb')

      AND l.status = 'GRANT'

GROUP BY dbid,

         objId,

         l.spid,

         hostname,

         loginame;

SELECT *

FROM #LockSummary

ORDER BY [ToLevel] DESC,

         [How Many] DESC,

         loginame,

         DB,

         object;

PRINT '--------';

PRINT 'Who is blocking:';

PRINT '--------' + CHAR(10);

SELECT p.spid,

       CONVERT(CHAR(12), d.name) db_name,

       program_name,

       p.loginame,

       CONVERT(CHAR(12), hostname) hostname,

       cmd,

       p.status,

       p.blocked,

       login_time,

       last_batch,

       p.spid

FROM master..sysprocesses p

    JOIN master..sysdatabases d

        ON p.dbid = d.dbid

WHERE EXISTS

(

    SELECT 1 FROM master..sysprocesses p2 WHERE p2.blocked = p.spid

);

PRINT '--------';

PRINT 'Details:';

PRINT '--------' + CHAR(10);

SELECT LEFT(loginame, 30) AS loginame,

       l.spid,

       LEFT(DB_NAME(dbid), 15) AS DB,

       LEFT(OBJECT_NAME(objId), 40) AS object,

       Mode,

       blk,

       l.status

FROM #lock l

    JOIN #who w

        ON l.spid = w.spid

WHERE dbid != DB_ID('tempdb')

      AND blk <> 0

ORDER BY Mode DESC,

         blk,

         loginame,

         dbid,

         objId,

         l.status;

Format Pivot Tables in Qlik

Format Pivot Tables in Qlik 

.cell.ng-scope.null-value {background-color:rgba(0,0,0,0); color: rgba(0,0,0,0); font-size:0px;}

.qv-pt .cell.bold { font-weight: 300; }

[tid="meta.rows"],[tid="meta.columns"] {display:none;}

 COUNT DISTINCT Values in a Range

= COUNTA ( UNIQUE ( B5:B14 ) )

https://www.howtoexcel.org/count-distinct-values/

Excel Advanced Filtering

 https://www.youtube.com/watch?v=ZoiwvdeJRaQ

Clean Shoes

It can get messy, but I've gotten out the toughest stains, in literally anything, even blood lmao. You mix baking soda, peroxide, and dish soap, and make sort of like a paste, scrub it in if you have to, and then pour vinegar over the stain and leave it for at least an hour, it should either go away or lighten tremendously. And then u repeat as necessary. But it may have to be applied slightly different for shoes.

Diff two files in SQL Server Management Studio

 Go to View -> Other Windows -> Command Window (Or Ctrl+Alt+A). In the Command Window type:

>Tools.DiffFiles <First file> <Second file> (If files are not in the Solution Explorer, get the local path from Windows Explorer)

Now in the new window you will see side-by-side differences and all buttons from "Compare Files" will work and will make sense.