SQL improvement - UNION?

I have 2 tables: "packages" and "items". "packages" has the following columns: pack_id | item_id "items" has the following columns.......: item_id | type One package can have multiple items. UPDATE: Sample Data and Expected Results: pack_i...
more »

2017-11-14 15:11 (3) Answers

Setting a parameter using dynamic SQL

I have been attempting to set a parameter using dynamic SQL however everytime i do it it is always returning a NULL. Below i have copied a basic version of my code, can anyone advise how i would do what i am looking to do. DECLARE @SQL_Prev_Values n...
more »

2017-11-13 10:11 (1) Answers

T-SQL Left join twice

Query below works as planned, it shows exactly the way i joined it, and that is fine, but problem with it, is that if you have more "specialization" tables for users, something like "Mail type" or anything that user can have more then one data ... yo...
more »

2017-11-08 14:11 (2) Answers

Group rows with Id from specific row

I've been breaking my head trying to work this out, so some help here would be appreciated very much. Basically I've got a database table, for example: I'm trying to get the below result: Id | Name | Total ------------------------ 590954 | ...
more »

2017-11-07 15:11 (4) Answers

SQL Constraints OR Condition

How can I have an SQL constraint with an OR condition: USE [dbname] GO ALTER TABLE [dbo].[tablename] WITH NOCHECK ADD CONSTRAINT [CK_table] CHECK (([field1] like ('[0-9a-zA-Z][0-9a-zA-Z]' OR '[0-9a-zA-Z][0-9a-zA-Z][0-9a-zA-Z]') AND [field1] IS N...
more »

2017-11-06 17:11 (2) Answers

What can I use other than Group By?

I have a question that uses this DML statement SELECT SupplierID, COUNT(*) AS TotalProducts FROM Products GROUP BY SupplierID; I'm trying to get the same results without using "Group By". I can use a table variable or a temp table, with Insert and...
more »

2017-11-05 15:11 (4) Answers

Most recent shipment SQL

So I am wondering if there is a more efficient way to do the same thing as is happening below. Essentially for a given shipment I need to find the most recent shipment of the same product. On small data it is not a huge issue but on a million plus ro...
more »

2017-11-05 14:11 (1) Answers

Add missing rows to result set

I asked similar questions hour ago. Previous Question This time situation is different I have a fact and dim table. Comparing previous question, my fact table has an extra field called Amount create table #fact (SKey int, HT varchar(5), TitleId i...
more »

2017-11-04 01:11 (1) Answers

TSQL Group by Column with Multiple Values

SQLServer 2008r2 I have a dataset as below. I want to select all the records where the [Fg] column = 1 that consecutively by [Id] order lead into value 2 for each [T_Id] and [N_Id] combination. There can be instances where record prior to [Fg]...
more »

2017-11-03 12:11 (2) Answers

How to find the nearest color in SQL?

I have a table with an incrementing color-number, and a corresponding HTML-hex value. (hex-value without #) CREATE TABLE dbo.T_SYS_ColorToHex ( COL_Number int NOT NULL ,COL_Hex varchar(6) NULL ,CONSTRAINT PK_T_SYS_ColorToHex PRIMARY ...
more »

2017-11-01 16:11 (1) Answers

How to Select distinct values in FOR XML PATH?

Given the following tables where T_DATA.ID = PARENT_ID or CHILD.ID Name: T_DATA +----+------+--------+ | ID | CODE | VALUE | +----+------+--------+ | 1 | 3186 | value1 | | 2 | 3186 | value2 | | 3 | 3189 | value3 | | 4 | 3189 | value4 | | 5 | ...
more »

2017-10-31 17:10 (1) Answers

How do I use where clause in the sql trigger?

I am trying to create a trigger where the account balance is automatically updated from the transaction table, but I am not being able to update a particular table. My trigger changes the account balance of every customer. I want to know how I can us...
more »

2017-10-29 21:10 (1) Answers

correlated subquery on same table

here is table schema emp(no, name, salary) and insert data like this. insert into emp values (1, 'gandalf', 3000); insert into emp values (2, 'dobby', 4000); insert into emp values (3, 'legolas', 5000); and I select data, like this. select no,...
more »

2017-10-29 17:10 (2) Answers

T-SQL get the last date time record

My table looks like this: +---------+------------------------+-------+---------+---------+ |channel |date |code |comment |order_id | +---------+------------------------+-------+---------+---------+ |1 |2017-10-27 12:04:...
more »

2017-10-27 19:10 (3) Answers

MS SQL Pivot Dynamic Headers

I have the following two tables: Serial Key Value ABC Attr1 ABC1 ABC Attr2 ABC2 ABC Attr3 ABC3 XYZ Attr1 XYZ1 XYZ Attr2 XYZ2 XYZ Attr3 XYZ3 and Key Include IncludeOrder Attr1 Y 2 Attr2 NULL ...
more »

2017-10-26 16:10 (1) Answers