working on it ...

Filters

Explore Public Snippets

Sort by

Found 8,855 snippets

    public by 04pallav modified Sep 13, 2017  93  2  1  0

    hrbi schema

    hrbi schema: new_gist_file_0.sql
    This table contains the start date for diferent employees.
    
    Each row represents a unique employee and when he joined.
    
    empl_id       
    empl_user_id   --PALLANAN
    dd_dt -- starting date of each employee 
    job_lvl_nm -- Level of job  I am level 4
    reports_to_level3_login = 'REYNOLM'    # director Hr filter Mike Reynold's group
    person_org_nm
    dept_org_level1
    loc_cntry_nm  ---  Name of the country where employe is working 'USA'
    person_org_nm ---- has only three values Employee, NULL contingent worker.
    
    
    count
    3566985
    ############################################################################
    hrbi_monthly_snapshot
    
    
    For each month this table contains a snapshot for an employee. A particular employe can get promoted so data changes. Historical is preserved.
    can be used to get Active headcount
    
    ##########################################################################
    hrbi_wfm_terms
    
    termination data similar to start date data.
    
    
    #Reynoldson Org STARTS, HC, TERMS query, can easily switch out the reports to for other groups,
    SELECT c.month, c.year, c.job_lvl_nm, starts, active_hc, terms  FROM
    (SELECT a.month, a.year, a.job_lvl_nm, starts, active_hc FROM
    ((SELECT date_part('month', dd_dt) as month
          ,date_part('year', dd_dt) as year
          , count(*) as starts
          , job_lvl_nm
           FROM hrbi.hrbi_wfm_starts  WHERE reports_to_level3_login = 'REYNOLM' GROUP BY  month, year, job_lvl_nm) a
    JOIN
    (SELECT date_part('month', dd_dt) as month
          ,date_part('year', dd_dt) as year, count(*) as active_hc, job_lvl_nm FROM hrbi.hrbi_monthly_snapshot
           WHERE reports_to_level3_login = 'REYNOLM' GROUP BY  month, year, job_lvl_nm) b
    ON a.month = b.month AND a.job_lvl_nm = b.job_lvl_nm AND a.year = b.year)) c
    
    JOIN (SELECT date_part('month', dd_dt) as month
          ,date_part('year', dd_dt) as year
          ,job_lvl_nm
          , count(*) as terms
           FROM hrbi.hrbi_wfm_terms  WHERE reports_to_level3_login = 'REYNOLM' GROUP BY  month, year, job_lvl_nm) d
     ON c.month = d.month AND c.job_lvl_nm = d.job_lvl_nm AND c.year = d.year
     
     
     
     ############################################################## Fulfillment centers starts, terms, active headcount
     
     
     SELECT c.month, c.year, c.country, starts, active_hc, terms FROM (SELECT a.month, a.year, a.country, starts, active_hc FROM (SELECT date_part('month', dd_dt) as month
          ,date_part('year', dd_dt) as year
          ,loc_cntry_nm as country
          , count(*) as starts
           FROM hrbi.hrbi_wfm_starts  WHERE country = 'USA'  AND person_org_nm = 'Employee' AND job_lvl_nm IN ('1', '2', '3') AND dept_org_level1 = 'Ops & Cust Svc' GROUP BY country, month, year ) a
          JOIN
          (SELECT date_part('month', dd_dt) as month
          ,date_part('year', dd_dt) as year
     ,loc_cntry_nm as country
     , count(*) as active_hc
     FROM hrbi.hrbi_monthly_snapshot WHERE country = 'USA'  AND person_org_nm = 'Employee' AND job_lvl_nm IN ('1', '2', '3') AND dept_org_level1 = 'Ops & Cust Svc' GROUP BY country, month, year) b
      ON a.month = b.month AND a.country = b.country AND a.year = b.year) c
      JOIN (SELECT date_part('month', dd_dt) as month
          ,date_part('year', dd_dt) as year
          ,loc_cntry_nm as country
          , count(*) as terms
           FROM hrbi.hrbi_wfm_terms  WHERE country = 'USA'  AND person_org_nm = 'Employee' AND job_lvl_nm IN ('1', '2', '3') AND dept_org_level1 = 'Ops & Cust Svc' GROUP BY country, month, year) d
      ON c.month = d.month AND c.country = d.country AND c.year = d.year
    
    
    
    
    
    
    
    
    
    
    
    
    

    public by yourfriendcaspian modified Sep 2, 2017  46  0  1  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  18  0  1  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 by cwilli34 modified Feb 21, 2017  45  0  1  0

    Set SQL database to single user mode

    Set SQL database to single user mode: database_single_user.sql
    USE master;
    GO
    ALTER DATABASE AdventureWorks2012
    SET SINGLE_USER
    WITH ROLLBACK IMMEDIATE;
    GO
    ALTER DATABASE AdventureWorks2012
    SET READ_ONLY;
    GO
    ALTER DATABASE AdventureWorks2012
    SET MULTI_USER;
    GO
    
    

    public by bobby modified Jan 10, 2017  156963  2  4  0

    Returns Descriptions for all active Magento products

    Returns Descriptions for all active Magento products: Magento-active-product-descriptions.sql
    SELECT 
        e.entity_id,
        e.sku,
        eav.value AS 'description',
        IF(at_status.value_id > 0,
            at_status.value,
            at_status_default.value) AS `status`
    FROM
        catalog_product_entity e
            JOIN
        catalog_product_entity_text eav ON e.entity_id = eav.entity_id
            JOIN
        eav_attribute ea ON eav.attribute_id = ea.attribute_id
            INNER JOIN
        `catalog_product_entity_int` AS `at_status_default` ON (`at_status_default`.`entity_id` = `e`.`entity_id`)
            AND (`at_status_default`.`attribute_id` = '89')
            AND `at_status_default`.`store_id` = 0
            LEFT JOIN
        `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`)
            AND (`at_status`.`attribute_id` = '89')
            AND (`at_status`.`store_id` = 1)
    WHERE
        ea.attribute_code = 'description'
            AND IF(at_status.value_id > 0,
            at_status.value,
            at_status_default.value) = 1
    LIMIT 9999
    
                

    public by JulioTrujillo modified Nov 15, 2016  7529  0  4  0

    Anonimous block

    SQL
    cl scr
    set lin 200
    SET SERVEROUTPUT ON
    SET TERMOUT ON
    
    DECLARE 
    
    BEGIN
    
    END;
    /

    public by sjschmalfeld modified Oct 31, 2016  298383  1  4  1

    Kill Connections to a Database SQL Server

    sql
    // KILL CONNECTIONS TO A DATABASE
    
    USE [master]
    GO  
    
    DECLARE @kill varchar(8000) = '';  
    SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  
    FROM sys.dm_exec_sessions
    WHERE database_id  = db_id('MyDB')
    
    EXEC(@kill);

    public by TristinDavis modified Aug 16, 2016  894  0  3  0

    SQL Server View missing indexes

    SQL Server View missing indexes: missing_indexes.sql
    -- Credit to https://basitaalishan.com/2013/03/13/find-missing-indexes-using-sql-servers-index-related-dmvs/
    SELECT CAST(SERVERPROPERTY('ServerName') AS [nvarchar](256)) AS [SQLServer]
        ,db.[database_id] AS [DatabaseID]
        ,db.[name] AS [DatabaseName]
        ,id.[object_id] AS [ObjectID]
        ,id.[statement] AS [FullyQualifiedObjectName]
        ,id.[equality_columns] AS [EqualityColumns]
        ,id.[inequality_columns] AS [InEqualityColumns]
        ,id.[included_columns] AS [IncludedColumns]
        ,gs.[unique_compiles] AS [UniqueCompiles]
        ,gs.[user_seeks] AS [UserSeeks]
        ,gs.[user_scans] AS [UserScans]
        ,gs.[last_user_seek] AS [LastUserSeekTime]
        ,gs.[last_user_scan] AS [LastUserScanTime]
        ,gs.[avg_total_user_cost] AS [AvgTotalUserCost]
        ,gs.[avg_user_impact] AS [AvgUserImpact]
        ,gs.[system_seeks] AS [SystemSeeks]
        ,gs.[system_scans] AS [SystemScans]
        ,gs.[last_system_seek] AS [LastSystemSeekTime]
        ,gs.[last_system_scan] AS [LastSystemScanTime]
        ,gs.[avg_total_system_cost] AS [AvgTotalSystemCost]
        ,gs.[avg_system_impact] AS [AvgSystemImpact]
        ,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]
        ,'CREATE INDEX [Missing_IXNC_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE
            WHEN id.[equality_columns] IS NOT NULL
                AND id.[inequality_columns] IS NOT NULL
                THEN '_'
            ELSE ''
            END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE
            WHEN id.[equality_columns] IS NOT NULL
                AND id.[inequality_columns] IS NOT NULL
                THEN ','
            ELSE ''
            END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex]
        ,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]
    FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)
    INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK)
        ON gs.[group_handle] = ig.[index_group_handle]
    INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK)
        ON ig.[index_handle] = id.[index_handle]
    INNER JOIN [sys].[databases] db WITH (NOLOCK)
        ON db.[database_id] = id.[database_id]
    WHERE db.[name] LIKE 'ClassOnlineAssessmentDev' -- Remove this to see for entire instance
    ORDER BY [IndexAdvantage] DESC
    OPTION (RECOMPILE);
    
    

    public by bobby modified Apr 5, 2016  441  2  3  0

    Magento - Grab Orphaned Enabled Products

    Magento - Grab Orphaned Enabled Products : magento-orphaned-enabled-products.sql
    SELECT 
        *
    FROM
        catalog_product_entity a
            LEFT JOIN
        catalog_product_entity_int ei ON ei.entity_id = a.entity_id
            LEFT JOIN
        catalog_category_product cp ON cp.`product_id` = a.entity_id
            LEFT JOIN
        catalog_product_relation cpr ON cpr.child_id = a.entity_id
    WHERE
        cp.product_id IS NULL
            AND cpr.parent_id IS NULL
            AND a.type_id != 'configurable'
            AND ei.attribute_id = 89
            AND ei.value = 1
            /*
            AND attribute_id = (SELECT 
                attribute_id
            FROM
                `eav_attribute`
            WHERE
                `attribute_code` LIKE 'status')
            AND `ei`.`value` = 1
            */
    
    

    public by lauhin modified Mar 24, 2016  2541  1  5  0

    Find tables where certain columns exists

    if you need to find all tables where a column name exists, you can search that with this query
    //this query will give you all table names in a database where certain column names exists.
    select distinct table_name
        from information_schema.columns
        where column_name in ('columnA','ColumnB')
            and table_schema = 'YourDatabase';            
    • Public Snippets
    • Channels Snippets