I have a table with columns username and timestamp . When a username is entered the current timestamp is as well. I would like to fetch the rows of all the new users from a certain time until now, but the start time varies. Lets make it a timestamp represented by x. I would like to use SQL to select
Tag: sql
SQL : remove last comma in string
I have a text memo field in SQL table that I need to remove the last character in the field if it’s a comma. So, for example, if I have these rows, I need to remove the commas from rows 2 and 4. The output would be: Any ideas? Answer Using REVERSE and STUFF: First, you want to TRIM your
SQL Server : ISNULL(compound NULL condition, ‘a string’) returns only the 1st character, under certain circumstance(s)
I’m a self-taught, vaguely competent SQL user. For a view that I’m writing, I’m trying to develop a ‘conditional LEFT’ string-splitting command (presumably later to be joined by a ‘conditional RIGHT’ – whereby: If a string (let’s call it ‘haystack…
How to check the query is using the indexes in mysql
I have a SQL query with where clause, I have created the index for the columns which are being used in the where clause. How can I check the query is using the indexes for these columns? Answer Write “explain ” in front of your query. The result will tell you which indexes might be used. For examp…
SQL query to get Stored Procedure crop result if the routine_definition is greater than 4000 char
I use this query to get all the stored procedure in my database (I’m using Microsoft SQL Server 2008): For almost all result everything is ok, but for the row with a very long ROUTINE_DEFINITION the result is cropped. Do you know how to solve it? Answer Please try with sp_helptext ‘ProcedureName&#…
C# sql create one connection and open and close for each query
I recently inherited a C# Web app that creates a new connection for every query like so: I understand that this is typically the best way to do it, but is it acceptable or “best practice” to have the constructor create the connection object, and then have each method/Query open and then close that…
How to convert Laravel migrations to raw SQL scripts?
Developers of my team are really used to the power of Laravel migrations, they are working great on local machines and our dev servers. But customer’s database admin will not accept Laravel migrations. He asks for raw SQL scripts for each new version of our application. Is there any tool or programming …
Find out available days and slots for doctor appointment
I have three tables: TimeSlotToken is the number of patients a doctor will see in his TimeSlot. TimeSlot 1, 2, and 3 are Morning, Afternoon and Evening respectively. I need to find the days where the TimeSlot is not full, i.e TimeSlot token is less than total bookings on that particular day and that slot. Sin…
How to retrieve count of records in SELECT statement
I am trying to retrieve the right count of records to mitigate an issue I am having. The below query returns 327 records from my database: Where I run into trouble is trying to reconcile that with the below query. One is for showing just a count of all the particular students the other is showing pertinent in…
How to use ANY instead of IN in a WHERE clause?
I used to have a query like in Rails: Which generates sql query like: Now I want to change this to use ANY instead of IN. I created this: Now when I use empty array ids = [] I get the folowing error: Answer There are two variants of IN expressions: expression IN (subquery) expression IN (value [, …]) Si…