ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,691 through 3,705 (of 10,143 total)

RE: Need help with merge

It's by design, Kapil. There are two steps to the update/insert - a read to determine the rows which will be affected (by the join to source) and then 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

January 30, 2014 at 1:54 am

#1684546
RE: Referral Dates Nightmare

jsayerweb (1/29/2014)
Hello,
Thankyou for the bottom solution, it seems to work well, there are just a few instances were there is still a null start date and null end date. Its...
^{“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

January 30, 2014 at 1:41 am

#1684545
RE: Comparing string with date

Jeff Moden (1/29/2014)
Post deleted. I made a mistake. I'll be back.
A little history Jeff. Might save you some time.
^{“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

January 29, 2014 at 9:26 am

#1684366
RE: Referral Dates Nightmare

Using Gail's script:
/*
I need to get the list of referrals, per client, in chronological order using the dates from the referralaction table.
A allocations status will be given once a...
^{“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

January 29, 2014 at 7:11 am

#1684300
RE: Referral Dates Nightmare

Msg 8106, Level 16, State 1, Line 5
Table 'ChildReferrals' does not have the identity property. Cannot perform SET operation.
Jon - I suspect you will learn as much from this exercise...
^{“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

January 29, 2014 at 6:24 am

#1684276
RE: Referral Dates Nightmare

jsayerweb (1/29/2014)
...Hope that makes sense and my above code works...
Did you test it? I was planning to work on your problem for you over lunch but the sample data script...
^{“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

January 29, 2014 at 5:57 am

#1684266
RE: How to have multiple select statements in CASE

Where does the column [Type] come 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

January 29, 2014 at 5:01 am

#1684248
RE: How to have multiple select statements in CASE

sabeer.mvit (1/29/2014)
So as per your comments, I need to use multiple If's.... But if there are more than 100 types , then having 100 if's would not be good practice...
^{“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

January 29, 2014 at 4:24 am

#1684231
RE: Query just hangs!

Abu Dina (1/29/2014)
ChrisM@Work (1/28/2014)
Gail's suggestion using DENSE_RANK() is well worth a try when you get time.
Is it?
So what happens if I have two records with the same Organisationname but 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

January 29, 2014 at 4:12 am

#1684228
RE: How to have multiple select statements in CASE

Not really. CASE is used within a query. Can you post the whole query within which you want to use the CASE construct?
^{“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

January 29, 2014 at 3:48 am

#1684219
RE: Referral Dates Nightmare

jsayerweb (1/29/2014)
Yes?!
Cheers
J
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ':'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ':'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax...
^{“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

January 29, 2014 at 3:08 am

#1684206
RE: Referral Dates Nightmare

Did you test it before posting?
^{“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

January 29, 2014 at 3:03 am

#1684202
RE: Referral Dates Nightmare

Hi Jon
Anyone wishing to test queries against your tables would first have to convert this:
Referral Table:
CLI_PER_ID,REF_ID,REF_REFERRED_BY,REF_REFERRER_ROLE,REF_SOURCE_TEAM,REF_REFERRED_TO,REF_RECEIVED_BY,REF_REFERRED_ON,REF_RECEIVED_ON,REF_RECEIVED_AT,REF_REFERRED_AT,REF_TO_PER_ID,REF_PRIORITY,REF_METHOD,REF_TREAT_REASON,REF_REASON,REF_STATUS,REF_CLI_AWARE,REF_FAM_AWARE,REF_ANONYMOUS,REF_CREATED_ON,REF_CREATED_AT,REF_CREATED_BY,REF_AMENDED_ON,REF_AMENDED_AT,REF_AMENDED_BY,REF_ACCESS_KEY,REF_PARENT_ID,REF_OWNER_TEM_ID,StartDate,EndDate,REF_TYPE
27429,621,NULL,1,NULL,T0286 ,NULL,00:00.0,00:00.0,NULL,8.26,NULL,NULL,LACMIG ,60,*,CLO ,0,0,0,31:10.0,18.31,1,45:48.0,11.45,1,U ,NULL,T0286 ,00:00.0,00:00.0,MIG
27429,622,NULL,79,NULL,T0286 ,NULL,00:00.0,00:00.0,NULL,13.18,NULL,NULL,LACMIG ,G2 ,*,CLO ,0,0,0,31:10.0,18.31,1,45:48.0,11.45,1,U ,NULL,T0286 ,00:00.0,00:00.0,MIG
27429,624,NULL,1,NULL,T0064 ,NULL,00:00.0,00:00.0,NULL,15.33,NULL,NULL,LACMIG...
^{“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

January 29, 2014 at 1:57 am

#1684188
RE: How to have multiple select statements in CASE

Can you show the context in which this would be used?
^{“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

January 29, 2014 at 1:51 am

#1684186
RE: Comparing string with date

Use the source column for the string-dates, as discussed here.
^{“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

January 28, 2014 at 8:17 am

#1683943

Viewing 15 posts - 3,691 through 3,705 (of 10,143 total)