Large Data Set Concatenate Values into Query

I have a view with literally millions of rows, this times out when running it for everything but can do selects within it to pull back snapshots of data. VIEW A (over 5000000 rows - Times out when running for everything) I'm trying to concatenate ...
more »

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

Using multiple statements in WHEN MATCHED

I am using MERGE statement in order to insert XML input to SQL Server database table. How to execute multiple conditions in WHEN MATCHED block. Please refer the below code. USING TableRelationship AS new ON (new.TableRelationshipTypeID = old.Table...
more »

2017-09-10 15:09 (4) Answers

How to Pivot/Transpose Top 5 Rows to Columns

I have used Pivot before but I can quite seem to get it to work on this particular data set. Perhaps it's because I am trying to get the top 5 results and pivot at the same time. Here is a sample of my data (notice the month will stay the same, sin...
more »

2017-09-10 00:09 (3) Answers

Send-MailMessage based on Conditions

I am trying to sending email to submittedby users based on below conditions : UserCountry = "Yes" and UserTeamInTheList = "No" → send MailMessage1 UserCountry = "No" and UserTeamInTheList = "No" → send MailMessage2 NOTE : Also my body o...
more »

2017-09-09 08:09 (1) Answers

SQL query to count a column in all tables

So far, I am able to extract the list of database table using the below SQL query: SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS In each of these table, the first column is named "Year". The values are f...
more »

2017-09-08 04:09 (1) Answers

TSQL joins using more than one 'ON' keyword

I came across a stored procedure that contained some syntax I found to be confusing to read. I am hoping someone can help to explain the multiple usage of the 'ON' keyword in JOINS (see the generalized example below). I am having trouble finding a cl...
more »

2017-09-05 21:09 (4) Answers

Error converting data type varchar to int.?

My table is below CREATE TABLE Customers ( CustomerID int identity(1,1) not null primary key, Name varchar(50) not null, PhoneNumber varchar(20) not null constraint chk_PhoneNumber check(PhoneNumber not like '%[^0-9]%'), ...
more »

2017-09-02 06:09 (4) Answers

How to keep the leap year when substracting 1 year

I have this query that gives me a given date for each of the past 15 years. When my starting date is February 29 it does not return the 29 for year 2012, 2008 and 2004. How can I have this query to return the 29 for those years? DECLARE @TempDate1 T...
more »

2017-08-31 20:08 (3) Answers

Select row data in addition to structured XML data

I know how to join an XML variable to other tables, but in this case, I am trying to select each row from a table plus the structure of the XML from each respective table row, alongside that row. I cannot find any examples online to help with this, ...
more »

2017-08-29 14:08 (3) Answers

Update each row in table

I have a table with some value columns, a formula, and a result column. |rownum|value1|value2|value3|formula |result| |------|------|------|------|--------------------|------| |1 |11 |30 |8 |value1/value2*value3| | |2 ...
more »

2017-08-29 11:08 (6) Answers

Tolerance with Min Max

I am trying to adjust the below code by adding a 2 week tolerance piece. What it does it looks when the first time a customer (identifier) created a request and the first time it was completed and counts the days which happened in between. However ...
more »

2017-08-27 01:08 (1) Answers

SQL 15th of the month for Next Year

SQL query to get the 15 of the month for the following year. Today select getdate() = 2017-08-23 17:05:24.143 Looking for: 2018-8-15 00:00:00 I know how to get a year from today: select dateadd(year,1,datediff(day,0,getdate())) I know how t...
more »

2017-08-23 19:08 (4) Answers

SQL ORDER BY - Keep column with same value grouped

I have a lot of difficulties to express in words what I want (this title is the best I could come with), it's easier with a schema, but I'll first try to explain myself. I want to order by a query using this rule: Group the query by their "Group" ...
more »

2017-08-23 17:08 (4) Answers

Can I use dynamically LIKE and IN together?

I want to be able to say : SELECT * FROM myTable WHERE accountName LIKE('%john%', '%bill%', '%lory%'.....) I want that to be dynamically, which means depend on user input the list of '%name%' parts will be different. One time could have 3 names a...
more »

2017-08-21 18:08 (3) Answers

TSQL - FOR XML add data to the root node

I have a table: CREATE TABLE [dbo].[xdOthPay] ( AccountNo char(10) NOT NULL, PaymentDoneOn datetime NULL, PaymentDoneBy char(30) NULL, InvoiceNumber char(10) NULL, AmountPaid decimal(12,3) NULL, Comments char(254) NULL, C...
more »

2017-08-20 18:08 (1) Answers