Temporary Table Problem

  • 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

  • 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)
  • 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.

  • This was removed by the editor as SPAM

  • 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