I’m trying to merge overlapping dates between Admit and discharge dates of patients. There are a few edge cases which I couldn’t cover in the query. Input Expected Output Query I used the logic that was here But this doesn’t cover the edge case for ID 2 and 3. Also the subquery is slower whe…
Tag: tsql
Convert values in related table to comma-separated list
I have two SQL Server tables: I’m trying to the following result where the related activities are in a comma-separated list: I tried: Can someone help me with this SQL query? Thank you. Answer You could use OUTER APPLY to aggregate the string if you’re using SQL Server 2017 or higher.
Compare two tables via the three tables SQL
I plan to compare two tables via the three table. my query is as the following if the count is zore, then the tableA and TableC match, otherwise, these two tables do not match It takes a long time to run the query. Do we have a way to compare tableA and tableC fast? Question: How to compare tableA and
SQL query to count rows based on condition? Keep getting syntax error for my count logic
So I want my query to return how many books total as a column, but how many books are returned as a column. I’m using count also applying a condition, but I’m getting error saying: Incorrect syntax near ‘)’. Anyway to achieve this? Answer Here is the syntax error count(bookstatus = R) …
Add rows accumulated by current month
Is there a way to translate my hard coded SQL script into a dynamic? My problem with this code is that, it still adding the SEPT-DEC it should be zero since we are not yet covered this month My table Expected Result: Starts from JAN then Added the succeeding covered months. Answer You can UNPIVOT your data, u…
Query dates from different rows in the same table to appear on the same row in the query
I have seen many posts about this issue using joins but not for the same table. I am trying to compare the dates from different rows on the same table. I am writing tax software that tracks liens. The tax charge is on one line and any interest charged is on another. I need to compare the dates of the
SQL DATEPART(qq, @date) date quarter start October
I have a query that prints the data grouped quarter, the query uses datepart(qq,repo.lesson_date) SQL function. My issue is I want my first quarter to be October-December and not Jan-March like the output I get from the SQL function above. Is there anyone can assist? Answer You may use an additional calculati…
Round to nearest 15 minute interval
I’ve seen this question asked and answered for time that is stored as a date, but I have duration of hours and minutes stored as numeric(4,2). For example 2.12, is 2 hours 12 minutes. I need to round that to the nearest 15 minute interval, doing this (CONVERT([numeric](4,2),round(2.12/(25),(2))*(25))) d…
How to implement this CASE Statement logic on my Date variable?
I am using SQL Server 2014 and I need to implement a specific CASE STATEMENT logic in my T-SQL query. I need to create a new column (Period) in my output based on a column in the Table I am running the query against. This column will contain either “Peak” or “Off-Peak” based on the …
T SQL Count and Group by calculable column
This code gives the following table cntqueue cntdate cntINKTONERBLACK signalcolumn Queue01 2001-04-04 3 0 Queue01 2001-04-05 1 0 Queue01 2001-04-06 100 1 Is there a way to count ‘1’ in signal column and group 1st column to have? cntqueue NumberOfJumps Queue01 1 Answer You can use a subquery: