SQL One Liners

Snowflake

List row count for views in a schema

This creates a command you subsequently execute

select listagg(xx, ' union all ')
from (
    select 'select count(*) c, \'' || x || '\' v from ' || x as xx
    from (
        select TABLE_CATALOG ||'.'|| TABLE_SCHEMA ||'."'||TABLE_NAME||'"' x
        from IHC.INFORMATION_SCHEMA.VIEWS
        where table_schema='APPROVALPLUS'
    )
);

Convert date from known format

select to_date('3/4/2013', 'dd/mm/yyyy');

MS SQL

List row count for tables in a schema

SELECT o.NAME,
  i.rowcnt
FROM sysindexes AS i
  INNER JOIN sysobjects AS o ON i.id = o.id
WHERE i.indid < 2  AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
ORDER BY o.NAME

Consecutive Days

SELECT
    ID,
    REASON_ID,
    START_DATE,
    DATEADD(day, - ROW_NUMBER() OVER (PARTITION BY ID, REASON_ID ORDER BY ID, REASON_ID, START_DATE), START_DATE ) AS PREV_START_DATE
FROM    
    LOGS

This produces rows

Row  ID   Reason  Start Date Prev Start Date
--------------------------------------------
1    3546 2       2023-06-30 2023-06-29
2    1869 2       2023-06-23 2023-06-22
3    1869 2       2023-06-26 2023-06-24
4    1869 2       2023-06-27 2023-06-24
5    1869 2       2023-06-28 2023-06-24

Now the row with consecutive dates have the same Prev Start Date. We can count PREV_START_DATE to understand the number of consecutive days, max date contains the start of range and min date contains the end of range.

SELECT 
    ID,  
    REASON_ID,
    MAX(START_DATE) AS START_DATE,
    MIN(START_DATE) AS END_DATE,
    COUNT(PREV_START_DATE) AS CONTINUOUS_DAYS
FROM (
    SELECT
       ID,
       REASON_ID,
       START_DATE,
       DATEADD(day, - ROW_NUMBER() OVER (PARTITION BY ID, REASON_ID ORDER BY ID, REASON_ID, START_DATE), START_DATE ) AS PREV_START_DATE
    FROM    
       LOGS
)
GROUP BY
    ID,  
    REASON_ID,
    PREV_START_DATE;