Background Hello all! I recently learned that in newer versions of SQL Server, the query optimizer can “expand” a SQL view and utilize inline performance benefits. This could have some drastic effects going forward on what kinds of database objects I create and why and when I create them, dependin…
Tag: sql-server
Calculating percentages in SQL query
I have a table in SQL that has two columns of data, first is a week number, second is a value. There can be multiple records for the same week. Here’s some sample data (I added some SQL for this data at the end): id WeekNo MyAmount 1 1 5200.00 2 1 180.00 3 1 100.00 4 2 100.00 5
Showing null instead of throwing a zero division error by try-catch
Why does this procedure return NULL when count and sum are both 0? and does not produce output like this? How to change the first procedure to act same as the second one when there is nothing in the table, meaning when sum and count are both 0? Thanks. Answer If there is nothing in the table then you have
Why does this query return all the rows?
I have the following code: I want to get all the students who have a min grade 6, and I need to do it in subquery in where (most wanted to do it with exists, but not obligatory). My query right now returns all the students, however subquery gets not all of them. Answer If just one grade satisfies the
How to convert nvarchar string mm/dd/yyyy hh:mm:ss to datetime?
I have a nvarchar column with dates formatted like this: “04/18/2021 13:04:15” (without quotes). I want to change this column to be datetime and migrate all of it data. I’ve looked for format codes and figured out, that closest code to my variant is U.S (101). So I tried to But I get error: …
SQL for identifying % of orders placed within 20 minutes of each other
Have a dataset like below and would like to know various ways to solve the question of : what % of orders were within 20 minutes of each other? CustomerId Order_# Order_Date 123 000112 12/25/2011 10:30 123 000113 12/25/2011 10:35 123 000114 12/25/2011 10:45 123 000115 12/25/2011 10:55 456 000113 12/25/2011 10…
Passing parameters for a constraint function
I have the following table that joins driver and truck tables in order to assign trucks to drivers. I need to constraint entries that belong to the same driver on the same day, and DO NOT include the same truck code. It was suggested to create a boolean function to pass along dateval, truckval and driverval, …
How can you group data by multiple fields with separate totals in SQL or Power query
I have a set of data that I want to count by multiple characteristics. I am starting with something likes this (True/False for catagories) week PP SEN 1 T F 1 T T 1 F F 2 T F 2 T T 2 F F 2 F F 3 T F 3 F F I want to end up with
How to use the results of a query to filter a table comparing one field to the result?
I want to filter a table showing only the rows where total is between ± 3 standard deviations from the average. The query I’m using is this: But I’m getting the error: An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the …
Trigger to check if the combination of two field exist
I want to create a trigger which will check if my showtime with Cinema Hall Id exist or not. This will prevent me from booking one Cinema hall at two same Showtimes. Secondly in this trigger I am also checking if the showtime I am assigning to movie lies in movie release and last date range or not. But I