November 1, 2024 at 10:15 am
Hello everyone,
I hope you can help me.
I have a table with measurement data. Each measurement has its own ID and dozens of measurements are taken every day.
I have used HAVING COUNT to display which days have at least 600 measurements, because I only need these days.
The syntax for this is as follows:
select
count(id) as TotalIdPerDay,
gmtdate as Date
from
table
group by
date
having
count(*) > 600
order by
count(id) desc
Now I want to transfer these results to a temporary table.
Can this be done with a single query?
I did it manually, which is very time-consuming (the date is always a day that has more than 600 measurements:
create temporary table
temp_table
select
*
from
data
where
Date = '2024-10-13'
or
Date = '2024-10-23'
or
Date = '2024-10-01'
or
Date = '2024-10-06'
or
Date = '2024-10-20'
or
Date = '2024-10-21'
or
Date = '2024-10-02'
or
Date = '2024-10-11'
or
Date = '2024-10-15'
or
Date = '2024-10-25'
or
Date = '2024-10-08'
or
Date = '2024-10-09'
or
Date = '2024-10-03'
or
Date = '2024-10-07'
or
Date = '2024-10-04'
or
Date = '2024-10-27'
or
Date = '2024-09-30'
;
I would like this to be easier so that I don't forget anything.
So every day that has at least 600 measurements should be recorded and the data transferred to the temporary table.
Many thanks for your ideas and best regards
November 1, 2024 at 3:38 pm
Do you have one or two tables? are "data" and "table" the same table? Do you want all the data for dates where there are more than 600 measures? if so then either of these should work. You might prefer to use a CTE. If this doesn't do what you need, I suggest providing some consumable data with expected output. I would change the 600 to a much lower number like 2 or 3 for the sample so you only need a few rows.
SELECT *
INTO dbo.MyTable
--INTO #MyTable
FROM (
SELECT *, COUNT(*) OVER (PARTITION BY gmtdate) AS TotalIdPerDay
FROM Table
) AS a
WHERE a.TotalIdPerDay > 600
SELECT *
INTO dbo.MyTable
FROM Table AS a
WHERE EXISTS ( SELECT 1
FROM Table
WHERE gmtdate = a.gmtdate
GROUP BY gmtdate
HAVING COUNT(*) > 600)
SELECT *
INTO dbo.MyTable
FROM Table AS a
WHERE gmtdate IN (SELECT gmtdate
FROM Table
GROUP BY gmtdate
HAVING COUNT(*) > 600)
November 1, 2024 at 5:21 pm
Hy,
yes sorry, table and date is the same. There is only one table with the information in it.
Do you want all the data for dates where there are more than 600 measures? Yes
Thank you for your reply. I will try it and come back asap.
November 1, 2024 at 7:58 pm
This was removed by the editor as SPAM
November 3, 2024 at 8:57 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply