working on it ...

Filters

Explore Public Snippets

Sort by

Found 9,514 snippets

    public by sjschmalfeld modified Feb 1, 2019  563  3  4  0

    SQL Duplicates

    ;WITH cte AS
    (SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2, col3, col4 ORDER BY (SELECT 0)) AS DuplicateRowNumber
        FROM table
    )
    SELECT * FROM cte WHERE DuplicateRowNumber > 1
    
    
    or----
    
    ;WITH cte as
    (SELECT PEOPLE.*, Loan.LoanID, ROW_NUMBER() OVER (PARTITION BY SocialSecurityNo ORDER BY SocialSecurityNo) AS DuplicateRowNumber
    FROM people INNER JOIN LOAN ON FK_PeopleID=PeopleID WHERE ISNULL(SocialSecurityNo,'') <> '' AND SocialSecurityNo <> '--' AND SocialSecurityNo <> '000-00-0000' AND loan.Status='active' )
    
    SELECT * INTO #TEMP99 FROM CTE WHERE cte.DuplicateRowNumber > 1
    
    SELECT *,b.SocialSecurityNo FROM dbo.Loan 
    	INNER JOIN (SELECT PeopleID,SocialSecurityNo FROM PEOPLE WHERE SocialSecurityNo	 
    							IN (SELECT SocialSecurityNo FROM #temp99)) b 
    	ON B.PeopleID=LOAN.FK_PeopleID
    	WHERE LOAN.Status='ACTIVE'
    
    DROP TABLE #TEMP99  
    
    
    -- Another way
    	--GET RID OF #NewAddressCandidates ADDESSES ALEADY IN THE ADDRESS TABLE
    	DELETE FROM #NewAddressCandidates WHERE EXISTS (
    	SELECT t.[Address1] 
    				,t.Address2
    				,t.City
    				,t.[State]
    				,SUBSTRING(t.Zip,1,5) AS ZIP, 
    				SUBSTRING(t.Zip,7,4) AS ZIP4 
    		FROM #NewAddressCandidates T 
    			INTERSECT  
    		SELECT [Address1] ,[Address2],[City],[State],[Zip],ZIP4 FROM dbo.[Address])				
    		
    		
    -- Another way
    	;WITH NODUP AS 
    		(SELECT  A.ADDRESS1,A.ADDRESS2,A.CITY,A.STATE,A.ZIP,A.ZIP4
    			  , row_number() OVER ( PARTITION BY  A.ADDRESS1,A.ADDRESS2,A.CITY,A.STATE,A.ZIP,A.ZIP4 ORDER BY  A.ADDRESS1,A.ADDRESS2,A.CITY,A.STATE,A.ZIP,A.ZIP4 ) AS nr
    		FROM    #NewAddressCandidates A)
    	DELETE FROM NODUP WHERE NR > 1;	
    	
    	SELECT * INTO  #UniqueNewAddresses  FROM #NewAddressCandidates WHERE EXISTS (
    	SELECT t.[Address1] 
    				,t.Address2
    				,t.City
    				,t.[State]
    				,SUBSTRING(t.Zip,1,5) AS ZIP, 
    				SUBSTRING(t.Zip,7,4) AS ZIP4 			
    		FROM #NewAddressCandidates T 
    			EXCEPT 
    		SELECT [Address1] ,[Address2],[City],[State],[Zip],ZIP4 FROM dbo.[Address])
    	
    	// Now to remove the duplicate rows, use the same table and use EXCEPT operator with an 
    	// empty result set returned by the same table	
    	// USING except operator	
    	SELECT col1,col2
      FROM DuplicateRcordTable
      EXCEPT
      SELECT col1,col2
      FROM DuplicateRcordTable WHERE 1=0
    																				

    public by DinhoPutz modified Jan 30, 2019  32  2  3  0

    Função para converter minutos para horas (SQL Oracle)

    PLSQL - SQL Oracle
    -- Criando nova função
    create or replace function converter (
        numero in  number 
    ) return varchar2
    is
        minutos varchar2(10);
    begin
        
    return 
    
    	case length 
        
         (case instr((numero / 60), '.')  
            when 2 then '0'  || substr((numero / 60), 1 ,1)
            when 3 then substr((numero / 60), 1 ,2)
        else to_char(numero / 60)
        end || '.' || lpad(mod(numero, 60), 2, 0))  
    	
    			when 4 then '0' || 
    			case instr((numero / 60), '.')  
    			when 2 then '0'  || substr((numero / 60), 1 ,1)
    			when 3 then substr((numero / 60), 1 ,2)
    		else to_char(numero / 60)
        end || '.' || lpad(mod(numero, 60), 2, 0) 
       
       else 
    		 case instr((numero / 60), '.')  
    			when 2 then '0'  || substr((numero / 60), 1 ,1)
    			when 3 then substr((numero / 60), 1 ,2)
    		else to_char(numero / 60)
        end || '.' || lpad(mod(numero, 60), 2, 0)   -- temos os minutos
       
       end ;
        
    end converter;
    /
    
    
    select  converter(valor), valor as valors from horario where  valor is not null

    public by DinhoPutz modified Jan 30, 2019  39  0  3  0

    Como obter o espaço em disco ocupado por cada tabela do Banco de Dados

    Como obter o espaço em disco ocupado por cada tabela do Banco de Dados O código abaixo retorno espaço em disco ocupado por cada tabela do banco de dados em em execução.
    SELECT
        t.NAME AS Entidade,
        p.rows AS Registros,
        SUM(a.total_pages) * 8 AS EspacoTotalKB,
        SUM(a.used_pages) * 8 AS EspacoUsadoKB,
        (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS EspacoNaoUsadoKB
    FROM
        sys.tables t
    INNER JOIN
        sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN
        sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN
        sys.allocation_units a ON p.partition_id = a.container_id
    LEFT OUTER JOIN
        sys.schemas s ON t.schema_id = s.schema_id
    WHERE
        t.NAME NOT LIKE 'dt%'
        AND t.is_ms_shipped = 0
        AND i.OBJECT_ID > 25
        AND P.rows > 0
    --     and t.NAME like 'PEC_pre%'
    GROUP BY
        t.Name, s.Name, p.Rows
    ORDER BY
        EspacoTotalKB DESC

    public by DinhoPutz modified Jan 30, 2019  39  3  3  0

    Verificar origem de eventos contábeis (Totvs RM)

    Verificar origem de eventos contábeis (Totvs RM), a partir de qual sistema foram gerados. Basicamente ele faz uma busca no banco de dados SQL para verificar onde foi feito o lançamento contábil.
    SELECT T.CODEVENTO, 
    T.NOME,
    C.NUMEROINTERNO,
    C.DESCRICAO,
    ISNULL(C.CODCONTA, 'Default'),
    C.CODFRM,
    -- Converter código para nomes
    Case when T.CODAPLIC = 'F' THEN 'Financeiro'
    	when T.CODAPLIC = 'T' THEN 'Nucleus'
    	when T.CODAPLIC = 'P' THEN 'Folha de Pagamento'
    	when T.CODAPLIC = 'D' THEN 'Fiscal'
    	when T.CODAPLIC = 'I' THEN 'Patrimônio'
    	when T.CODAPLIC = 'X' THEN 'SGI - Incorporação'
    	when T.CODAPLIC = 'S' THEN 'Classis - Educacional'
    	ELSE 'Desconhecido' END
    	
    	-- Tabela de eventos contábeis
    	FROM         TEVENTOSCONT T INNER
    	
    	-- Tabela de Itens do Evento Contábil
    	JOIN         TITMEVC C ON T.CODCOLIGADA = C.CODCOLIGADA
    	AND T.CODEVENTO = C.CODEVENTO;
    												

    public by DinhoPutz modified Aug 14, 2018  389  0  3  0

    Remover caracteres especiais e quebra-linhas de um resultado em uma tabela

    Substituir o texto, na verdade, por outra coisa, seja por nada ou por um espaço vazio, talvez. Ideal para resolver aqueles problema na exportação de um resultado para CSV para deve ser aberto no Excel e aquele maldito ponto e vírgula (;) acaba levando parte do texto para outra coluna. Aqui também removemos tabulações, quebralinhas e outros.
    SELECT
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(nomeDaColuna,CHAR(13) + Char(10) ,''), CHAR(10), ''), CHAR(13), ''), CHAR(9), ''),';','-'),'  ', ' ') as 'Um nome novo e legal' 
    FROM NOMEDATABELA

    public by MuhammadAkbar modified Nov 16, 2017  182  7  3  0

    How to See/Hack saved Passwoods in Chrome

    			Many of people do not know and they do not care about own browser privacy . alot of people when they Sign up on any site and Chrome ask for save Passwords and people save passwords . actually they Do not know where their Password saved and anyone can easilly access their passwords they have saved in Chrome browser.so Today i will tell you how you can See saved passwords in Chrome and you can See/Hack saved Passwoods in Chrome. its is so easy
    
    steps. so lets start Now See/Hack saved Passwoods in ChromeView-Saved-Passwords-Chrome
    
    
    Steps below to View saves password In chrome Brower
    
                                              1: Open Chrome Brower
    
    
                                         2: Then paste this on Chrome brower
    
    "chrome://settings/passwords"
    
    when you will enter in Chrome it will be show like this 
    
    11
    
     
    
    Now Click on Any Tab it will show like this
    
    11 (1)
    
    
    when you Click on Show Button it will show the Save password in chrome
    
    you can see all password save in Chrome like this i hope this toturial will help you to find Hidden password in chrome if you need any help you and Comment below i will reply instant about See/Hack saved Passwoods in Chrome.
    						
    					http://www.watchmoviesonlineclub.com	
    					https://www.apkcheap.com
    					https://apkmirrorapp.com

    public by sjschmalfeld modified Nov 8, 2017  282  1  4  0

    Getting Min or Max value without aggregates

    SQL Server Transact SQL
    // Enter here the actual content of the snippet.
    // Select max value without using MAX()
    
    //Version 1
    SELECT *
      FROM SO.dbo.MaxNoAgg mna1
    LEFT JOIN SO.dbo.MaxNoAgg mna2 ON (mna2.salary > mna1.salary)
    WHERE mna2.mna_id IS NULL;
    
    //version 2
    SELECT age
    FROM Owner o1
    WHERE NOT EXISTS (SELECT 1 FROM Owner o2 WHERE o2.age > o1.age)            						

    public by anand modified Sep 13, 2017  247  0  3  0

    LENGTH OF CHARACTER, ORDER BY AND LIMIT

    LENGTH OF CHARACTER, ORDER BY AND LIMIT: new_gist_file.sql
    SELECT CITY,LENGTH(CITY) FROM STATION ORDER BY CITY ASC LIMIT 1;
    
    
    SELECT * FROM CUSTOMERS
       ORDER BY (CASE ADDRESS
       WHEN 'DELHI' 	 THEN 1
       WHEN 'BHOPAL' 	 THEN 2
       WHEN 'KOTA' 	 THEN 3
       WHEN 'AHMADABAD' THEN 4
       WHEN 'MP' 	THEN 5
       ELSE 100 END) ASC, ADDRESS DESC;     ###ORDERING IN A PARTICULAR ORDER
       
       
    select * from nobel where winner like 'Sir%' order by yr desc,winner asc
    
    

    public by yourfriendcaspian modified Sep 2, 2017  308  0  3  0

    PostgreSQL - full-text search configuration

    PostgreSQL - full-text search configuration: pg_search_conf_unaccented.sql
    -- enable extensions
    -- full-text search on postgresql
    CREATE EXTENSION unaccent;
    
    -- languages supported
    CREATE TEXT SEARCH CONFIGURATION fr ( COPY = french );
    ALTER TEXT SEARCH CONFIGURATION fr ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, french_stem;
    
    CREATE TEXT SEARCH CONFIGURATION en ( COPY = english );
    ALTER TEXT SEARCH CONFIGURATION en ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, english_stem;
    
    CREATE TEXT SEARCH CONFIGURATION de ( COPY = german );
    ALTER TEXT SEARCH CONFIGURATION de ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, german_stem;
    
    CREATE TEXT SEARCH CONFIGURATION nl ( COPY = dutch );
    ALTER TEXT SEARCH CONFIGURATION nl ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, dutch_stem;
    
    CREATE TEXT SEARCH CONFIGURATION da ( COPY = danish );
    ALTER TEXT SEARCH CONFIGURATION da ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, danish_stem;
    
    CREATE TEXT SEARCH CONFIGURATION fi ( COPY = finnish );
    ALTER TEXT SEARCH CONFIGURATION fi ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, finnish_stem;
    
    CREATE TEXT SEARCH CONFIGURATION hu ( COPY = hungarian );
    ALTER TEXT SEARCH CONFIGURATION hu ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, hungarian_stem;
    
    CREATE TEXT SEARCH CONFIGURATION it ( COPY = italian );
    ALTER TEXT SEARCH CONFIGURATION it ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, italian_stem;
    
    CREATE TEXT SEARCH CONFIGURATION no ( COPY = norwegian );
    ALTER TEXT SEARCH CONFIGURATION no ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, norwegian_stem;
    
    CREATE TEXT SEARCH CONFIGURATION pt ( COPY = portuguese );
    ALTER TEXT SEARCH CONFIGURATION pt ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, portuguese_stem;
    
    CREATE TEXT SEARCH CONFIGURATION ro ( COPY = romanian );
    ALTER TEXT SEARCH CONFIGURATION ro ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, romanian_stem;
    
    CREATE TEXT SEARCH CONFIGURATION ru ( COPY = russian );
    ALTER TEXT SEARCH CONFIGURATION ru ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, russian_stem;
    
    CREATE TEXT SEARCH CONFIGURATION es ( COPY = spanish );
    ALTER TEXT SEARCH CONFIGURATION es ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, spanish_stem;
    
    CREATE TEXT SEARCH CONFIGURATION sv ( COPY = swedish );
    ALTER TEXT SEARCH CONFIGURATION sv ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, swedish_stem;
    
    CREATE TEXT SEARCH CONFIGURATION tr ( COPY = turkish );
    ALTER TEXT SEARCH CONFIGURATION tr ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, turkish_stem;
    
    CREATE TEXT SEARCH CONFIGURATION usimple ( COPY = simple );
    ALTER TEXT SEARCH CONFIGURATION usimple ALTER MAPPING
    FOR hword, hword_part, word WITH unaccent, simple;
    
    

    public by yourfriendcaspian modified Aug 29, 2017  189  0  2  0

    Create mysql user with specific permissions

    Create mysql user with specific permissions: mysql_grant.sql
    CREATE USER 'joe'@'%' IDENTIFIED BY 'xxxxxx';
    # ALL PRIVILEGES is everything except grant
    GRANT ALL PRIVILEGES ON APP_production_temp.* TO 'joe'@'%'; 
    GRANT SELECT ON APP_production.* TO 'joe'@'%';
    GRANT SELECT ON stats_production.* TO 'joe'@'%';
    
    
    • Public Snippets
    • Channels Snippets