Create Temp Table with Range of Numbers

Create Temp Table with Range of Numbers

WITH    q AS
        (
        SELECT  startId, endId
        FROM    ranges
        UNION ALL
        SELECT  startId + 1, endId
        FROM    q
        WHERE   startId < endId
        )
SELECT  startId
FROM    q
OPTION  (MAXRECURSION 0)

How to generate temp table with integers 1-52 for weeks of year to join on?

Without creating temporary table, you can simplify this query using CTE, like below.
- Use recursive CTE to generate week numbers
- Get distinct years from TicketTable
- Cross join distinct years and weeks to get all combinations
- Then left join it with TicketTable to get count for each year-week

;With WEEK_CTE as (
Select 1 as WeekNo
UNION ALL
SELECT 1 + WeekNo from WEEK_CTE 
WHERE WeekNo < 52
)
Select yr.Year AS 'TicketYear' 
, wk.WeekNo AS 'TicketWeek'
, COUNT(t.TicketStatus) AS 'WeekTotal'
from Week_CTE wk
cross join (select distinct year(TicketQueuedDateTime) as [Year] from TicketTable) yr
left join TicketTable t on wk.WeekNo = DATEPART(WEEK, t.TicketQueuedDateTime) and yr.Year = YEAR(t.TicketQueuedDateTime)
group by yr.Year, wk.WeekNo

SQL Server 2005 Function Create Temp Table of Date Ranges with Week Number

Can you use the below logic. I am not creating a function, just because I don't know what exactly you want from the function.

Declare @refDate datetime       = '2014-07-26 11:54:04.293',
        @NoWeeksforRange INT    = 6, 
        @dayName Varchar(30)    = 'Sunday'
----------------------------------------------------------------
Declare @ref int,   
        @start int              = 1
Declare @mytemptablevariable table (weeknumber INT,fromdate DATETIME,todate DATETIME)

Select  @ref = Case When @dayName = 'Sunday'    Then -1
                    When @dayName = 'Monday'    Then 5
                    When @dayName = 'Tuesday'   Then 4
                    When @dayName = 'Wednesday' Then 3
                    When @dayName = 'Thursday'  Then 2
                    When @dayName = 'friday'    Then 1
                    When @dayName = 'Saturday'  Then 0 Else 0 End

Select  @ref = Cast(@refDate As int) - (DATEPART(Weekday,@refDate)) - @ref

While @NoWeeksforRange > 0
Begin
    Insert into @mytemptablevariable
    Select  @NoWeeksforRange, 
            Cast((@ref - (7 * (@start - 1))) - 6 As datetime),
            Cast(@ref - (7 * (@start - 1)) As datetime)
    Set @NoWeeksforRange = @NoWeeksforRange - 1
    Set @start = @start + 1
End

Select  * From @mytemptablevariable order By weeknumber
----------------------------------------------------------------

Result:

How can I create a new temp table for each day given in a range?

As everyone here is suggesting you that you do not need a table for each date but a row in a table for each date, Also you don't necessarily need to store the results into a temp table.

You can write a query which produces this date range and join your existing query with the result of date range query.

Storing results of a query is good for humans to visually see the data but sql server doesn't need data in a temp table to proceed with data processing, if you have a query which you know will produce a specific result set, you can join another table with that result set, you don't see the result set at runtime but sql server gets that result set at runtime and joins it with your table.

Now hopefully if I have convinced you that you do not need a temp table for each date lets talk about the solution.

All you need is a query which produces a result set with dates ranging 2015-06-08 to 2015-06-29 for this you can do the following:

DECLARE @From DATE = '2015-06-08' 
DECLARE @To   DATE = '2015-06-29'

SELECT Dates 
FROM 
  (
    SELECT TOP  (DATEDIFF(DAY,@From , @To))
      DATEADD(DAY , 
                  ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
                ,@To) AS Dates
   FROM master..spt_values
  )Dates_Table

The above query will produce a result set with dates, now you can join another table with this result set to get the final result you need.

MySQL Attempt to create temp table with big integers fails

Dont rely on the database to give you a BIGINT from CAST(... UNSIGNED).

Max Value for UNSIGNED INT is

4,294,967,295

see here. Your value is

4,298,001,000

Try using BIGINT UNSIGNED explicit:

CREATE TEMPORARY TABLE IF NOT EXISTS lutemp
(SELECT tkey, ca01,ce01 FROM lu LIMIT 0);

ALTER TABLE lutemp MODIFY tkey BIGINT UNSIGNED;

INSERT INTO lutemp (tkey, ca01,ce01)
SELECT tkey, ca01,ce01 FROM lu
;

Random numbers and temp tables in SQL

There are two different methods for processing CTEs. One method stores the CTE in a temporary table, so the code is only executed once regardless of the number of references.

The second method is to incorporate the CTE code into the query, so the code may be run more than one time.

As far as I know, the ANSI standard does not specify which method to use and different databases use different methods -- some even choose between the two using the optimizer.

Vertica would seem to use the second method (this is speculation based on your results, not something that I know for sure for Vertica). As a result, the second reference to temp is recalculated -- resulting in the results you see.

What can you do?

One thing would be to use a "random" number generator that will return the same values. To be honest, that might not work on a larger table in Vertica due to parallelization and timing. But, providing a seed might help.

A similar alternative is to use something like "row_number * 17 - 39 mod 101". If the row_number() value uses a stable sort, then this will return the same rows each time.

Another approach is to store the results in a temporary table.

Finally, there might be a compiler option instructing Vertica to materialize the CTE.

Personally, I would use the second method, because I know that it works across multiple databases, but the other methods might work for your particular situation.

EDIT:

with temp as (
      select t.*
      from (select t.user_id
                   row_number() over (partition by tt.g order by  mod(71 * seqnum - 31, 101), user_id) as psuedo
            from (select tt.*,
                         row_number() over (partition by tt.g order by user_id) as seqnum
                  from test tt
                 ) t 
           ) t
      where t.pseudo <= 2
     )

The row_number() is stable -- it should produce the same values every time it is run. The arithmetic turns this into a pseudo-random number, which is then turned into another sequence. This approach is often good enough. You can adjust the prime numbers to change values.

---

Related Topics