ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,456 through 1,470 (of 10,143 total)

RE: Issue with query

narayanamoorthy.a (6/28/2016)
Yes ID column is primary key and ID_TASK_USER is foreign key with unique identifiers. Below is the script
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[task_attach](
[id] [uniqueidentifier] NOT NULL,
[id_task_user] [uniqueidentifier] NOT...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 28, 2016 at 8:51 am

#1887333
RE: Issue with query

narayanamoorthy.a (6/28/2016)
Its foreign key. Unique identifier Not Null.
Both columns?
Please post CREATE TABLE scripts for both tables.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 28, 2016 at 8:41 am

#1887325
RE: Issue with query

What is the datatype of the two columns in the join?
...id_task_user in (select id ...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 28, 2016 at 8:38 am

#1887323
RE: Are the posted questions getting worse?

Ed Wagner (6/27/2016)
ChrisM@Work (6/27/2016)
Beatrix Kiddo (6/27/2016)
GilaMonster (6/26/2016)
Hugo Kornelis (6/26/2016)
I like the sentiment, but I don't think there is any realistic chance of this having any effect. The votes have been...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 28, 2016 at 8:36 am

#1887322
RE: Issue with query

Does the code execute successfully in SSMS?
select count(*), avg(datalength(embedded_data)) from task_attach where id_task_user in (
select id
from task_user
where task_type = 1 and [status] =7
and date_begin >= CAST(CAST(DATEADD(DAY,-180,GETDATE()) AS DATE)...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 28, 2016 at 8:18 am

#1887309
RE: UNION with CASE statement

Newbi (6/28/2016)
It will b better of with looking up UNION and UNION ALL first.
I really am not aware of importance of order in group by. Those are not intentionally...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 28, 2016 at 6:57 am

#1887267
RE: UNION with CASE statement

Newbi (6/28/2016)
Sorry for all the confusion.:crying:
This is the final query which I make consistency in naming the columns.
Select * from
(SELECT Zone,ItemName, ItemCode, Quantity, GrossWeight
FROM ...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 28, 2016 at 6:43 am

#1887259
RE: UNION with CASE statement

Newbi (6/28/2016)
After changing the order of group by in inner query ,now it is running ok. But it is still showing error: Conversion failed when converting the varchar value 'TotalQ'...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 28, 2016 at 6:25 am

#1887247
RE: UNION with CASE statement

Newbi (6/28/2016)
After changing the order of group by in inner query ,now it is running ok. But it is still showing error: Conversion failed when converting the varchar value 'TotalQ'...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 28, 2016 at 6:23 am

#1887245
RE: Transpose data

SELECT
NewColumn,
[Old] = MAX(CASE WHEN ItemState = 'old' THEN [Stuff] ELSE NULL END),
[New] = MAX(CASE WHEN ItemState = 'new' THEN [Stuff] ELSE NULL END),
[Used] = MAX(CASE WHEN ItemState =...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 28, 2016 at 5:47 am

#1887236
RE: UNION with CASE statement

The inner query has errors. Work on it in isolation from the whole query.
SELECT Dept, IName, ICode, Quantity, GrossWeight
FROM Data_Details
UNION
SELECT Zone, ICode, IName, SUM(Quantity) AS TotalQ, SUM(GrossWeight) AS GrossWt...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 28, 2016 at 4:43 am

#1887228
RE: Updating variable columns with variable value and derived values from variable

Are you by any chance attempting to perform a pivot or unpivot (rows to columns or vice versa)? If so, there may be alternative methods which are a) "standard" and...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 27, 2016 at 9:58 am

#1887083
RE: Are the posted questions getting worse?

Beatrix Kiddo (6/27/2016)
GilaMonster (6/26/2016)
Hugo Kornelis (6/26/2016)
I like the sentiment, but I don't think there is any realistic chance of this having any effect. The votes have been counted; the results...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 27, 2016 at 8:44 am

#1887069
RE: Are the posted questions getting worse?

jasona.work (6/24/2016)
ChrisM@Work (6/24/2016)
Eirikur Eiriksson (6/24/2016)
Luis Cazares (6/24/2016)
I'm sorry, is there any country that can give me good news? It seems that Mexico, US and UK are full of negative news...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 24, 2016 at 10:03 am

#1886789
RE: Are the posted questions getting worse?

Eirikur Eiriksson (6/24/2016)
Luis Cazares (6/24/2016)
I'm sorry, is there any country that can give me good news? It seems that Mexico, US and UK are full of negative news and I'm...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

June 24, 2016 at 9:50 am

#1886783

Viewing 15 posts - 1,456 through 1,470 (of 10,143 total)