-1

I have a table containing webpage 'hits' and timestamps associated daily cumulative hits. Using a 24-hour clock, I want to calculate the "grossed up" hits. For example: hits of 10,000 for 2020-07-13 10:10 is 10,000 hits for 10 hours and 10 minutes, or 610 minutes. Grossing the 10k based on 24 hrs = 1440 / timestamp minutes. 1440 / 610 = 2.36. Grossed up hits = 23,607

I tried a calculating datepart but got SQL error: datetime2 incompatible with int.

Would CAST help? I'm not that versed on manipulating date values.

Thanks!

Thom A
  • 88,727
  • 11
  • 45
  • 75
Coding_Newbie
  • 365
  • 1
  • 3
  • 11
  • 1
    Sample data, expected results, and your attempt(s) will help us help you. – Thom A Jul 13 '20 at 16:46
  • Show us the code which fails. Ideally, show us the relevent table definition as well. When you say "timestamp", you don't mean the actual sql server `timestamp` datatype, do you? – allmhuran Jul 13 '20 at 16:46

1 Answers1

0

datetime2 incompatible with int => Date requires to be wrapped with single quotes eg. '2020-07-13'

Do you have a date anywhere in your code that isn't wrapped with a single quote (which becomes a number). 2020-07-13 = 2020 minus 07 minus 13 = 2000

Edit: It may also occur when attempting to perform a multiplication of datetime/datetime2 with a number. After a chat in the comments section, it seems like this was the issue.

Sudip Shrestha
  • 441
  • 4
  • 12
  • code is referencing the latest timestamp: ,MAX ([Time_Utc]) AS 'Max Time' – Coding_Newbie Jul 13 '20 at 18:19
  • Maybe share some code? It would remove a lot of guesswork on what you are trying to do. – Sudip Shrestha Jul 13 '20 at 18:23
  • SELECT [Date_utc] ,FORMAT(sum([hit_Tot]), 'N0') AS 'hits' ,newRunRate = (sum([hit_Tot]) * 1440) / (DATEPART(hour, MAX ([Time_Utc])*60)) – Coding_Newbie Jul 13 '20 at 18:27
  • `MAX ([Time_Utc])*60` => is the problem. What are you trying to do? Maybe move the *60 outside the ). `DATEPART(hour, MAX ([Time_Utc])*60)` => `DATEPART(hour, MAX ([Time_Utc]))*60` – Sudip Shrestha Jul 13 '20 at 18:29
  • I'm trying to calculate the 24 hour run rate based on a value a timestamp that reflects < 24hrs. For example if hits are 500 and timestamp is 6:00am, then the 24hr runrate would be 2,000 (500 per 6 hrs = 2,000 per 24 hrs). – Coding_Newbie Jul 13 '20 at 18:34
  • ok. So you want total minutes in a day / (datedif between timestamp 12:00 AM in the morning in minutes) * 500 , no? – Sudip Shrestha Jul 13 '20 at 18:39
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/217755/discussion-between-coding-newbie-and-sudip-shrestha). – Coding_Newbie Jul 13 '20 at 18:43