I have this below query which takes about 15-20 secs to run. with cte0 as ( SELECT label, date, CASE WHEN Lead(label || date || “number”) OVER (PARTITION BY label || date || “number” ORDER BY “label”, “date”, “number”, “time”) IS NULL THE…
Tag: sql
Postgresql query between date ranges
I am trying to query my postgresql db to return results where a date is in certain month and year. In other words I would like all the values for a month-year. The only way i’ve been able to do it so far is like this: Problem with this is that I have to calculate the first date and last
Distinct random time generation in the fixed interval
I’m trying to generate a random time between 8:00 AM and 8:00 PM for each row that is selected from a data set, however, I always get the same random value for each row – I want it to be different for each row. Table schema & data: Сurrent SQL statement: Current results (same time for each row in th…
‘MOD’ is not a recognized built-in function name
I wanted to use MOD function in SQL Server 2008R2 and followed this link but still got the message: ‘MOD’ is not a recognized built-in function name. Error: Msg 195, Level 15, State 10, Line 2 ‘MOD’ is not a recognized built-in function name. Why I can’t use this function from th…
SQL Server: round decimal number and convert to int (within Select)
I am using the following line within a Select which returns a number with decimals, e.g. 33.33333. How can I round this within the Select and convert to integers so that I don’t have decimals, e.g. in the above example it should return 33 ? Answer You can use ROUND function to round the value to integer…
Query database tables for missing link
I have two tables in my database and they each share the a field called cID. So here’s an example: Parent Table _______________ cID Name — —— 1 Record #1 2 Record #2 3 Record #3 …
Rails User.joins.not(…) in Active Record?
Im looking to query all Users without Comments in a single sql query? Models: class User < ActiveRecord::Base has_many :comments end class Comment < ActiveRecord::Base belongs_to :user ...
Is a query with table separated by comma a cross join query?
I know some of SQL but, I always use join, left, cross and so on, but in a query where the tables are separated by a comma. It’s looks like a cross join to me. But I don’t know how to test it (the …
Stored procedure to return count
I am trying to make a stored procedure for the query I have: or I have written this stored procedure but it returns count of all the records in column not the result of query plus I need to write stored procedure in plpgsql not in SQL. Help me write this type of stored procedure in plpgsql which returns retur…
INSERT or UPDATE from another table in mysql
I have two tables, with same schema – create table test1 ( a INT NOT NULL , b INT NOT NULL , c INT, PRIMARY KEY (a,b) ); create table test2 ( a INT NOT NULL , b INT NOT NULL , c INT, PRIMARY KEY (a,…