Tag: sql

Merging sql row groups

My applications splits a single row data into different row chunks which are always in sorted order of startdate. Where rowpart = 0 is the start and rowpart=2 is always the end rowpart=1 is the middle part,which can be repeated n no of times. I need to return row in such form like startdate of […]

Reducing the length of my SQL code

I am using a third party software for this SQL query which has various limitations, one being a character limit of 1,000 characters. I have created code that will pull through a list of contact numbers registered to a particular account, and have had a view created specifically for this task. My current script (see […]

SQL Server map payments to products

We have shopping carts that might have included products and payments. Since these payments will be made to the carts, there will be no relation between the products and the payments except that they are in the same cart. There are cases that these products will be invoiced individually even though they are in the […]

Case statement inside a SELECT

ALTER PROCEDURE Transform.usp_CustomerInfo AS BEGIN TRUNCATE TABLE [Transform].[CustomerInfo] INSERT INTO [Transform].[CustomerInfo] (CustomerName, Total, AvgPurchaseTime, StoreName) SELECT CASE WHEN C.LastName = NULL THEN C.FirstName ELSE (C.LastName + ‘, ‘ + C.FirstName) END AS ‘Name’, SUM(T.Total), D.FullDate, UPPER(C.StoreName) AS ‘Store Name’ FROM [Extract].[DimTransaction] AS T JOIN [Extract].[DimCustomer] AS C ON T.CustomerID = C.CustomerID JOIN [Extract].[DimState] AS S […]

Subquery returned more than 1 value error when using Update-Database in Entity Framework

I am using Entity Framework and trying to run a database migration from the Package Manager Console in Visual Studio. When I do, I get the following error and stack trace. PM> Update-Database -Verbose Using StartUp project ‘Project’. Using NuGet project ‘DataModels’. Specify the ‘-Verbose’ flag to view the SQL statements being applied to the […]

Why would a sub query perform better than a litteral value in a WHERE clause with multiple joins?

Take to following query: SELECT * FROM FactALSAppSnapshot AS LB LEFT OUTER JOIN MLALSInfoStage AS LA ON LB.ProcessDate = LA.ProcessDate AND LB.ALSAppID = LA.ALSNumber LEFT OUTER JOIN MLMonthlyIncomeStage AS LC ON LB.ProcessDate = LC.ProcessDate AND LB.ALSAppID = LC.ALSNumber LEFT OUTER JOIN DimBranchCategory AS LI on LB.ALSAppBranchKey = LI.Branch WHERE LB.ProcessDate=(SELECT TOP 1 LatestProcessDateKey FROM DimDate) […]

SQL Server trigger updating other table based on the table with the trigger

I’m a newbie in SQL and I want to ask a specific question about the triggers. I have a table Purchases and I want to make an update to an other table with the same ID CREATE TRIGGER tr_EmployeesSalaryPurchasesUpdate ON Purchases AFTER INSERT AS BEGIN UPDATE EmployeesSalary SET MonthlySalesMade = MonthlySalesMade + 1 WHERE EmployeeID […]

SELECT with group concat if a value is represented in a set

I have a really peculiar task of creating a SELECT with following conditions: Source table is as follows: +—————-+ | source | +—–+———-+ | id | values | +—–+———-+ | 1 | 1,2,3 | +—–+———-+ | 3 | 4,5,6 | +—–+———-+ | 8 | 1,4,7 | +—–+———-+ | 9 | 8,9 | +—–+———-+ | 21 […]

SQL Query incorrect syntax error c#

I’m getting the following error An unhandled exception of type ‘System.Data.SqlClient.SqlException’ occurred in System.Data.dll Additional information: Incorrect syntax near the keyword ‘Group’. When I run the following query string query = “Update Job Set Name = @Name, Date = @Date, Material = @Material, Instructions = @Instructions, Group = @Group, Time = @Time, Address = @Address […]

IDENT_CURRENT returning less value than MAX(ID)

I have a table like this: CREATE TABLE [dbo].[RequestQueue] ( [QueueID] [BIGINT] IDENTITY(1,1) NOT NULL, [EntryDate] [DATETIME] NULL CONSTRAINT [DF_SecurityQueue_EntryDate] DEFAULT (GETDATE()), [AsOfDate] [DATE] NULL, [ItemID] [VARCHAR](32) NULL, [ItemType] [VARCHAR](50) NULL, [Message] [VARCHAR](MAX) NULL, CONSTRAINT [PK_QueueID] PRIMARY KEY CLUSTERED ([QueueID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.