ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,786 through 1,800 (of 10,143 total)

RE: Pivoting Problem

silverbullettruck (2/3/2016)
Yes, it would be ok to switch values for the values that exist multiple times for the same LOB with different values, but the values that only exist once...
^{“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

February 3, 2016 at 8:44 am

#1856320
RE: Pivoting Problem

silverbullettruck (2/3/2016)
Yes, that would be ok.
So it's okay to switch values between rows of the same LOB? That makes it easier - but it's still quite fiddly coding. One way...
^{“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

February 3, 2016 at 8:20 am

#1856309
RE: Are boolean clauses executed always in the provided order?

sql.queries (2/3/2016)
Hi!
I have a query which needs to filter table according provided filters.
I need to filter just by the provided filters.
Example:
SELECT
Col1,
Col2,
Col3,
Col4
FROM
MyTable
WHERE
((@FilterCol1 IS NULL) OR (Col1 like @FilterCol1))
AND
((@FilterCol2 IS NULL) OR...
^{“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

February 3, 2016 at 8:16 am

#1856307
RE: Pivoting Problem

silverbullettruck (2/3/2016)
MNS has two XLOC's so it makes it necessary to have both an 'MS' and 'ML' that is what is keeping the 'ML' from being an 'MS' or vice...
^{“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

February 3, 2016 at 8:10 am

#1856303
RE: Pivoting Problem

silverbullettruck (2/3/2016)
The uniqueness is across all of the attributes as a set. So in the example, there is a unique combination for MNS across XLOC, BPL, CRE, and ARA....
^{“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

February 3, 2016 at 7:35 am

#1856287
RE: Merge repetitve queries into a single one

huasoBruto (2/3/2016)
Thanks for your replies... I'm thinking, its also sort of like the same, isnt it? I mean, you'll need to repeat each year as a new line... But next...
^{“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

February 3, 2016 at 6:50 am

#1856268
RE: Merge repetitve queries into a single one

SELECT
Employee2006 = SUM(CASE WHEN [Year] = 2006 THEN EmployeePaid*2 ELSE 0 END),
Employee2015 = SUM(CASE WHEN [Year] = 2015 THEN EmployeePaid*2 ELSE 0 END)
FROM TblRecords
WHERE...
^{“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

February 3, 2016 at 6:30 am

#1856264
RE: Trying to replace UNION

SQL006 (2/3/2016)
Thank you guys for your valuable inputs. I checked the execution plan for the UNION and the CROSS APPLY query, i am surprised that the UNION query is still...
^{“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

February 3, 2016 at 5:33 am

#1856239
RE: wrong result in join

msh083 (2/3/2016)
i have a view:
SELECT id_reg, name_reg, id_card, name_card, SUM(Expr1) AS Expr1
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

February 3, 2016 at 5:00 am

#1856221
RE: Query Needed

ROW_NUMBER() tricks with a CROSSTAB:
CREATE TABLE #PatientAppointmentDetails (Sno INT, PatID CHAR(2), Appoiintment_Date DATETIME)
INSERT INTO #PatientAppointmentDetails (Sno, PatID, Appoiintment_Date) VALUES
(1 , 'P1' , '01-01-2016'),
(2 , 'P1' , '02-01-2016'),
(3 , 'P1' ,...
^{“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

February 3, 2016 at 1:45 am

#1856181
RE: Need help on SQL

SELECT *
FROM crpt_Test
WHERE batch_Id = '76D79E5E-07A6-4B8E-964F-6287DC4873F9'
AND chart_code BETWEEN '10000' AND '13000'
AND chart_code NOT IN ('12101','12110','12116')
^{“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

February 2, 2016 at 5:35 am

#1855946
RE: An expression of non boolean type specified in a context where a condition is expected, near ','

eobiki10 (2/2/2016)
This seems to work for me in another way. I now have ALL in the parameter select box which shows everything .
AND
(
(NULLIF(isnull(d.dw_ac_approval_date,''),'') IS NOT NULL and @approval_date = 'YES')
OR...
^{“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

February 2, 2016 at 5:24 am

#1855944
RE: Pivoting Problem

Cadavre (2/2/2016)
silverbullettruck (2/1/2016)
ChrisM/ER...Here is the full dataset i am working with and then below that is the what i need the data to look like after it is pivoted.
Sorry, I...
^{“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

February 2, 2016 at 2:26 am

#1855932
RE: Pivoting Problem

Ahah! A bogstandard cross-tab. This article by Jeff [/url]explains how it works and how to extend the functionality:
SELECT
LOB,
[XLO] = MAX(CASE WHEN Attribute = 'XLO' THEN Value ELSE ''...
^{“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

February 2, 2016 at 2:13 am

#1855929
RE: Pivoting Problem

silverbullettruck (2/1/2016)
ChrisM...in your solution, if there were additional possible values in the Attribute column, would you need to have an OUTER APPLY for each of the possible attribute values?
Possibly 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

February 1, 2016 at 9:13 am

#1855725

Viewing 15 posts - 1,786 through 1,800 (of 10,143 total)