I have a table like this Opening Transfer 0 12 12 -2 10 -2 8 -1 7 -7 I need to recalculate this due to a gap in the real table. I know the value of the opening will be the opening + transfer of the line above but how can I do this? I’ve… Read More Set a value equal to the value of a sum from a row above?
DECLARE @tablename VARCHAR(100) = ‘House’; IF OBJECT_ID(@tablename, N’U’) IS NOT NULL BEGIN IF EXISTS(SELECT 1 FROM @tablename) BEGIN PRINT ‘Table already exists and has data, not dropping it.’ END ELSE BEGIN DROP TABLE @tablename PRINT ‘Table dropped successfully.’ END END ELSE BEGIN PRINT ‘Table does not exist.’ END Please correct it for SQL Server >Solution… Read More T SQL variable showing incorrect syntax
Let’s say I want to get all of the people in a database by name, and have a BIT field to denote whether or not at least one of their jobs is as a plumber. If I have a query like this: SELECT p.Name, CASE WHEN EXISTS (select 1 FROM j WHERE j.Name = ‘plumber’)… Read More How to check for the existence of a value in a column within a Select CASE statement
I need to pull information from the database between 01.01.2021 and 31.12.2021 The first day of the week is Monday (not Sunday) This is a fairly easy task, but I have problems with it, I get information only from 07.01.2021 to 16.09.2021 I do not fully understand how these functions work, so please help me… Read More Incorrect information counting
I am just learning SQL, using SQL Server Management Studio. The current query is shown below and I am struggling with trying to add a column to my results query, where it converts the PRODUCTION_DATE into fiscal week. SELECT ORDER_QTY, SKU, INVOICE_NUMBER, CUSTOMER_NUMBER, ROUTE, ALLOCATED_QTY, SHORTED_QTY, PRODUCTION_DATE FROM [DATEBASE_NAME].[XYZ].[ORDERS] WHERE [PRODUCTION_DATE] >= DATEADD(day, -300, GETDATE())… Read More Add week number column in query results from existing date column
I am trying to solve this question from leetcode. I wrote my query as below: delete from person p1 where not exists ( select p2.email,min(p2.id) from person p2 group by p2.email having min(p2.id)=p1.id ) But I am getting the error: [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ‘p1’. (102) (SQLExecDirectW) Why am… Read More MSSQL Incorrect syntax near p1
in the below Query Benz table car_no is repeated. when I join and compare with other table car_no how do use distinct? table 1: Cars Table 2: benz join Benz bz on bz.car_no = cr.car_no in the above car_no values in the Benz table are repeated. in Cars table car_no values unique. then how to… Read More Distinct in Join Query
Can anyone tell me why the following syntax is resulting in error? I would like to update the ip address field with dynamic ip addresses if a current record exists with a matching primary key. INSERT INTO Overview([Computername], [IpAddress]) VALUES(‘test’, ‘127.0.0.1’) ON DUPLICATE KEY UPDATE Overview set IpAddress = ‘127.0.0.1’; >Solution : There is no… Read More Incorrect syntax near the keyword 'ON' – SQL Server 2019
I have two tables: Job(ID,Name, etc.) and Address(ID, Job_ID, Name etc). I want to get result like this: [ { "Job_ID": 1, "JobName": "Test", "Addresses": [ { "ID": 1, "Name": "King street" }, { "ID": 2, "Name": "Queen`s street } ] } ] My current query that gets only one address for a job looks… Read More How can I put SELECT result set to SELECT column?
I need to add a foreign key column in an existing table using alter statement. And need to set default value to insert default in case of null. For that I added below alter query: ALTER TABLE COM.USER_GENERATED_TASK_LIST ADD Application_ID INTEGER NOT NULL CONSTRAINT DK_Application_ID DEFAULT (5) WITH VALUES, CONSTRAINT FK_Application_ID FOREIGN KEY (Application_ID) REFERENCES… Read More Add foreign column with default value