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

Question

Post reply

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

VoldemarG

Hall of Fame

Points: 3883
More actions
September 21, 2022 at 10:35 pm

#4090527

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

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

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

pietlinden SSC Guru Points: 63500 More actions · Answer 1

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

MarkP Old Hand Points: 305 More actions · Answer 2

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)

VoldemarG Hall of Fame Points: 3883 More actions · Answer 3

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

MarkP Old Hand Points: 305 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

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)