November 7, 2011 at 7:04 am
ChrisM@Work (11/7/2011)
Changing the query in the first post on this thread from using a temp table to a derived table is this straightforward:1.Change FROM #TXActivityTemp to FROM () d
2.Copy the query which creates the temp table and paste it between the brackets
3.Delete (or comment out) the line INTO #TXActivityTemp
That’s it.
Chris,
I agree that creating a derived table is a straight forward process and even though I got the CTE to work I would still like to get the derived table to work.
The only table that is referenced is the Quote Table. I need the CASE Expression on the SalesTransferredTS Column and I need to Join to the outer query based on Quote ID.
The Joins of the other tables are needed to get the other columns.
I did not see where the INNER and OUTER TAbles are being Joined?
Thanks again.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 7, 2011 at 9:03 am
Welsh Corgi (11/7/2011)
ChrisM@Work (11/7/2011)
Changing the query in the first post on this thread from using a temp table to a derived table is this straightforward:1.Change FROM #TXActivityTemp to FROM () d
2.Copy the query which creates the temp table and paste it between the brackets
3.Delete (or comment out) the line INTO #TXActivityTemp
That’s it.
Chris,
I agree that creating a derived table is a straight forward process and even though I got the CTE to work I would still like to get the derived table to work.
The only table that is referenced is the Quote Table. I need the CASE Expression on the SalesTransferredTS Column and I need to Join to the outer query based on Quote ID.
The Joins of the other tables are needed to get the other columns.
I did not see where the INNER and OUTER TAbles are being Joined?
Thanks again.
I'm sorry, I don't understand where the problem is. If you've followed the recipe to the letter then it should work. You don't need to change any CASE expressions or joins.
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
November 7, 2011 at 9:15 am
ChrisM@Work (11/7/2011)
ok, I will try it again, thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 7, 2011 at 9:26 am
is this part of the code correct?...not sure this is what you want.
CASE Datediff(mm, SaleTransferredTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS CurrentMonth,
CASE Datediff(mm, SaleTransferredTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS PreviousMonth,
CASE Datediff(week, SaleTransferredTSTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS CurrentWeek,
CASE Datediff(week, SaleTransferredTS, GetDate())
WHEN 0 THEN 1
ELSE 0
END AS PreviousWeek,
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 7, 2011 at 10:08 am
J Livingston SQL (11/7/2011)
is this part of the code correct?...not sure this is what you want.
No that was wrong, I found and corrected it after I made the post.
Thank goodness it was just a silly error. I thought it was something serious.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 7, 2011 at 10:39 am
You can start by 1) adding this to your company table
alter table company
add [account number] as left(companyname,6)
2) Then create the index for it.
3) Change your sql to use this new field, which will then use the index.
4) Put the [account number] filter in the where clause, not the having clause.
*****
Give a man a fish, and you've given him a meal. Teach a man to fish and he'll ask to borrow your boat.:-P
November 7, 2011 at 12:20 pm
eric.lyons (11/7/2011)
You can start by 1) adding this to your company tablealter table company
add [account number] as left(companyname,6)
2) Then create the index for it.
3) Change your sql to use this new field, which will then use the index.
4) Put the [account number] filter in the where clause, not the having clause.
Yes I do not like the design of the Database. The table should have been created with two seperate columns, the first 6 characters are the Account Number and the last two are the branch.
The database is not normalized and even though I have permissions I can't just make changes to production.
Thanks.
Unfortunately
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply