I have a query that selects 3 columns. Each row row should be a unique combination of county, city,and zip. However, I have reason to believe I’m getting a duplicate somewhere. How do I find the …
Tag: sql
How do you get the size of the gap from LEAD or LAG?
In my table, I have messages grouped by threads. Using LEAD, I can get the next message within a thread… [ThreadNextMessageId] = LEAD(gm.GroupMessageId, 1) OVER(PARTITION BY gm.ThreadId ORDER BY gm….
CASE condition and SUM() common table expressions or Group By
Hope some can help ? I have a 3rd party software were i can do custom sql querys but not able to change any of the database. Struggling to get the result i would like. Do I use GROUP BY or CTE to get the result i need? This give me the result below . I have removed Where c=1
Count times date occurs between two dates in a second table
I have two tables [Charges] and [Defects] and want to produce [Desired Query Output] where the output counts the occurrances of defect when [Charges].ChargeDate is between (and including) [Defects].OpenDate and [Defects].CloseDate. For [Defects] table, a close date of NULL means it has not closed yet. Seems s…
After adding cursor to this query the execution time went from 10s to 2m.10s
I’m working on this query and after i added the bloc of cursor the execution time goes from 10s to 2m. Is there any way to reduce the execution time? The query is a result of 3 selects on the same table but with different conditions. I needed the cursor because I should get the price of an article from
SQL Join query with a null in Main table
Hi guys so in simple terms I have a Main table “A” and then another table “B”. All the data I want is basically from table A, but to figure one part out I need to join with B. My problem is although the column im joining with a column in B is correct, sometimes the column in A will
Conditional Grouping in SQL
This should be easy, but I’m having trouble with it. I have a results table that looks like this: Notice that the pct column sums to 1.0 for each store. Here’s the code to create this table: I’m trying to group the results by store, then by cust_id while creating a new category A&B if th…
Case on Join condition
I have a two tables data as below table1: Order orderitem value O1 B-IV122 10 O2 B-IV144 10 table2: order Productionorder productionitem …
fetch id who have register all adv
There are 3 types of adventures for which I used distinct function in query. There is only one 1 customer who have booked all types of adventures. The query i used to fetch the data is: Answer You can get the customer ids using aggregation and having: Or, if you don’t want to hardcode the “3”…
Query by a specific month when you only have start and end dates
I need to query a MS SQL table of events by a specific month. In this scenario, I want to return all events which took place during May 2020. I would expect to return ID’s 1, 6 and 9. I’m finding this query quick tricky, so thanks in advance for any help. Answer This article explains why you can u…