I have a fairly complex query which results in a list of Customer Ids where they have not ordered within a defined period, have no Rep assigned to them, and are business customers rather than private. This simply returns a sequential list of Ids which match the BIGINT primary keys of the matching customers, l…
Tag: sql-server
Showing the data with date information as the previous day before a certain time in SQL
I have a dataset with date information in MsSQL. The type of this data is registered as datetime. For example I have a date value like ‘2022-12-12 06:59:00’. Here, if the time is before 07 am, I want it to write ‘2022/12/11’ as the date. If the date value ‘2022-12-12 07:01:00R…
How to minimize my big query for showing counts using SQL
I am taking counts from CTE but common is Status(working,pening) and Divisions but my query is becoming big because left join is same but just status and division are different. I have wrote 10 left join count but by passing status and divisions. Below is my whole sql query Is there anything which we can mini…
SQL get row count from every table tenantwise
I am working on a multitenant application. Most of the tables contain a column tenant_id. I want the number of rows in all tables for a particular tenant_id. Example, Tables:- Sample result to get number of rows in every table having tenantid = 64 TableName Count Employee 2 Student 3 Teacher 1 How do I loop t…
Two foreign keys referring to one primary key on SQL SERVER query select items
I’d to create a database that records transactions between two users. A user can transfer points (think of it as money) to another user. user table looks like: And the transaction table should look like: just i needed this result: Could someone give hints to provide SQL Server code? Select from_user, to…
What should I do to join two columns with different name but has the same data type, so that I can get something I need from one of the table?
I have two table, [table a] and [table b]. So basically, I need work_week from [table a] therefore I want to join the columns together, shift_begin_datetime from [table a] and shift_start_datetime from [table b] as both of them have the same data type but they have different name. I want both of the column to…
Pass Parameters in Pivot SQL
I had this query below and got an error with parameter @emp_group during execution. Need your suggestion on how to pass the parameter. The query will run when removing the parameter but need to filter the records. thanks in advance. Answer You can can do the same thing with @emp_group like you did with @cols.…
SQL query to extract matching diagonal pairs in SQL Server database
I have a database table (mytable) with 2 columns x and y as shown below, from which I intend to extract rows with matching diagonal pairs of (x,y) and (y,x) e.g., 4 21 and 21 4 I have tried the accepted code on stackoverflow here (enter link description here) on my mytable which gives me the expected results …
Iterate through SQL result set adding subquery results to result set
I have a table [Robots] containing Robot IDs, and a [TestResults] table which has a FK to [Robots] of [RobotId] What I am aiming to get is a result set of each robotID in the [Robots] table and for each ID a total of all entries in the [TestResults] of Status ‘Active’, ‘Failed’ and tot…
Check in T-SQL whether certain value sets exist in a related table
I have following tables: User – userId, userName, … Settings – settingId, userId, settingKey, settingValue for example for userId = 123, I might have: Then for example how can I query for all users that have settingValue of “0” corresponding to settingKey of “allowClient…