ChrisM@Work

Forum Replies Created

Viewing 15 posts - 1,861 through 1,875 (of 10,143 total)

RE: Pivot query using dates between 2 dates

Tallboy (12/17/2015)
Hi Guys,
Yes there is a limit of either 1 week, 2 weeks or 4 weeks maximum.
So the user chooses an option 1,2 or 4 week period and 2 dates...
^{“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

December 17, 2015 at 2:14 am

#1846498
RE: Pivot query using dates between 2 dates

twin.devil (12/17/2015)
Is there is any limit between two dates? as far as i know cross tab get very slow on reports if the columns list get higher.
you can also check...
^{“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

December 17, 2015 at 1:43 am

#1846491
RE: How to pad a string with spaces when size of data is unknown

Jeff Moden (12/16/2015)
ChrisM@Work (12/15/2015)
Jeff Moden (12/12/2015)
ChrisM@Work (12/10/2015)
Jeff Moden (12/9/2015)
ChrisM@Work (12/9/2015)
suvesh.sonker 2691 (12/9/2015)
-- Ten digit
Declare @field as varchar(20) = '559'
Select Replace(right(replicate(' ',10) + @field ,10),' ','0')
In what circumstances might this be...
^{“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

December 17, 2015 at 1:39 am

#1846490
RE: How to pad a string with spaces when size of data is unknown

Jeff Moden (12/12/2015)
ChrisM@Work (12/10/2015)
Jeff Moden (12/9/2015)
ChrisM@Work (12/9/2015)
suvesh.sonker 2691 (12/9/2015)
-- Ten digit
Declare @field as varchar(20) = '559'
Select Replace(right(replicate(' ',10) + @field ,10),' ','0')
In what circumstances might this be preferable to the...
^{“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

December 15, 2015 at 9:41 am

#1846134
RE: Dead Lock SQL SERVER 2000

SQLPain (12/15/2015)
Here you go:
SELECT DISTINCT
a.AppID
, CONVERT(varchar(10), a.DateAppRec, 101) AS DateAppRec
, ch.NameLong AS Dealer
, CASE WHEN ch.assignedbranch = ch.channelid THEN '' ELSE (SELECT namelong FROM channels WHERE channelid =...
^{“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

December 15, 2015 at 8:48 am

#1846114
RE: "Arithmetic overflow error" but data calculated as expected.

LinksUp (12/14/2015)
mr.databases (12/14/2015)
Hi Group!
CASE WHEN det_dte IS NULL THEN CAST({ fn NOW() } - rec_dte AS int) ELSE CAST(det_dte - rec_dte AS int) END
rec_dte and det_dte are both data type...
^{“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

December 15, 2015 at 2:24 am

#1846017
RE: Problem Pivot : with multi fix columns

MadAdmin (12/14/2015)
serg-52 (12/9/2015)
Pivot is just a convenience method to say
select id,title,
ParentIdLevel1 = max(case ParentLevel when 1 then ParentId end),
ParentTitleLevel1 = max(case ParentLevel when 1 then ParentTitle end),
ParentIdLevel2 = max(case...
^{“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

December 14, 2015 at 2:47 am

#1845739
RE: Today's Random Word!

Steve Jones - SSC Editor (12/11/2015)
Friday
Happy!
^{“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

December 11, 2015 at 8:18 am

#1845513
RE: need to help update query in sql server

baludw22 (12/11/2015)
Hi for unmathced record will be handle like below syntax
Empkey = isnull(aa.empkey,(select empkey from dimemp where empid=-1)),
deptKey = isnull(bb.deptkey,(select deptkey from dimdept where deptid=-1))
it will work good.
when we...
^{“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

December 11, 2015 at 6:09 am

#1845454
RE: need to help update query in sql server

You mean, like this?
SELECT
o.*,
NewEmpkey = ISNULL(x1.empkey,-1),
NewdeptKey = ISNULL(x2.deptkey,-1),
NewProjectname = b.projectname,
b.*
FROM Facttran O
INNER JOIN trantable b
ON o.transactionid=b.transactionid
OUTER APPLY (SELECT TOP 1 empkey FROM dimemp WHERE empid = b.empid ORDER...
^{“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

December 11, 2015 at 4:28 am

#1845440
RE: need to help update query in sql server

baludw22 (12/10/2015)
insert statement working fine with merge or with out merge statement.mine s trucked with update statement based conditions .here mainly trantable related transactionid is exist or not in...
^{“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

December 11, 2015 at 2:10 am

#1845426
RE: need to help update query in sql server

The MERGE statement can be tricky to implement even for experienced developers. Since you're only using it for inserts it's overkill anyway. I'd recommend you use INSERT instead - it's...
^{“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

December 10, 2015 at 9:14 am

#1845275
RE: Deadlock issue with "x" lock trying to convert to "u" lock

pghdisturbed1 (12/10/2015)
If it were me, I would make some changes to code/indexes prior to putting in locking hints.
The index locking seems to be based on a date. Does that...
^{“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

December 10, 2015 at 9:05 am

#1845272
RE: Deadlock issue with "x" lock trying to convert to "u" lock

Can you provide an execution plan of one of these updates please, also the definition of index IX_Table_Task_Eventdate.
^{“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

December 10, 2015 at 8:46 am

#1845252
RE: Need Help SQL Query

dprasannain (12/10/2015)
Why doesn't this query return's any value . Whats wrong with this query
Select * from #emps Where EN IN (@Names)
- because @Names isn't the comma-delimited list of string values...
^{“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

December 10, 2015 at 8:31 am

#1845243

Viewing 15 posts - 1,861 through 1,875 (of 10,143 total)