How many days of data do I have in the table?

  • The table has TimeStamp column and 10  of other columns with data.

    And I do speak TSQL but sometimes get stumbled over something that is supposed to be simple and obvious. solutionwise.

    This may be one of such cases.

    QUESTION: How do I answer the question via a Query that will show:

    * How many days of data do we have ? (for the entire rane: between the MIN(TimeStamp) and MAX(TimeStamp)

    * Counts of rows by each day (Meaning not just  @TotalRows/24 but by each calendar date).

    Likes to play Chess

  • You're joking, right? This is database 101 stuff.

    * How many days of data do we have ?  DISTINCTCOUNT(date) from your data table?

    * Counts of rows by each day

    SELECT c.date, COUNT(*) as recordcount

    FROM CalendarTable c LEFT JOIN FactTable f ON c.Date = f.Date

    GROUP BY Date

  • Basic stuff @voldemarg.

    Distinct dates

    SELECT COUNT(DISTINCT CAST(timestamp AS DATE)) FROM TABLE

    Rows per day

    SELECT CAST(timestamp AS DATE) AS TransactionDate, COUNT(*) FROM TABLE GROUP BY CAST(timestamp AS DATE)
  • Neither answer will work, because on one date there may be 1000 records and five on another, etc. The datetime value is different up to milliseconds for each row.  But I need

    September 20     1000 rows

    September 21    500 rows

    ETC

    Likes to play Chess

  • CAST(Timestamp AS DATE) will do that for you. It will strip the time part out.

    2022-09-22 14:30:54.843 becomes 2022-09-22

    WITH TEST
    AS (SELECT CAST(CURRENT_TIMESTAMP AS DATE) JUSTDATE,
    CURRENT_TIMESTAMP TSTAMP
    UNION
    SELECT CAST(CURRENT_TIMESTAMP AS DATE),
    DATEADD(SECOND, 20, CURRENT_TIMESTAMP))
    SELECT JUSTDATE,
    COUNT(*)
    FROM TEST
    GROUP BY TEST.JUSTDATE;

    Returns

    2022-09-22   2

     

  • VoldemarG wrote:

    Neither answer will work, because on one date there may be 1000 records and five on another, etc. The datetime value is different up to milliseconds for each row.  But I need

    September 20     1000 rows

    September 21    500 rows

    ETC

    You didn't even try MarkP's answer.  You've been on this site for over 12 years and yet you posted nothing about the datatypes in your original post.  You should know better on both fronts. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply