June 17, 2013 at 3:22 pm
dob111283 (6/17/2013)
It does make some sense, it's just a lot of information for a newcomer to absorb. Do you have some time later tonight to maybe help me with this realtime? If so, what is your contact information, whether it's skype or aim?
Take it one step at a time. First see if you can create a new version of the original proc as an iTVF. This will solve the immediate need. Then look at making it nice and tidy.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 17, 2013 at 3:24 pm
Ok Sean thanks. I'll post back as soon as I do that. You've been a big help. I really enjoy doing this SQL stuff. It's almost like a chess match.
June 19, 2013 at 4:13 pm
Hey Sean, I went back and redid the whole thing. I changed the ExchangeRate function and and added an "exchangerate" column in the clearedtransacions table to calculate the exchange rate, making this metrics procedure much easier.
CREATE PROC dbo.PEXDEV_MetricsTopFiveWeakest
@LPPProgramID UNIQUEIDENTIFIER,
@DateFrom DATETIME,
@DateTo DATETIME
AS
SELECT TOP(5) BoughtLoyaltyProgramId, SoldLoyaltyProgramId, DateTransactionCleared, ExchangeRate
FROM dbo.PEX_ClearedTransactions
WHERE DateTransactionCleared>=@DateFrom AND DateTransactionCleared<@DateTo
AND (BoughtLoyaltyProgramId=@LPPProgramID
OR SoldLoyaltyProgramId=@LPPProgramID)
ORDER BY ExchangeRate ;
And the strongest stored procedure would just order by ExchangeRate DESC;
My question is, why can't I do an offset fetch here? IT won't let me. I have to stick to Top.
June 24, 2013 at 7:28 am
dob111283 (6/19/2013)
My question is, why can't I do an offset fetch here? IT won't let me. I have to stick to Top.
When you say it won't let you what does that mean? Do you get an error message? What is the actual code? I still don't really understand why you want to use offset fetch when you are doing the same thing as top since the offset is 0.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 24, 2013 at 7:39 am
dob111283 (6/19/2013)
Hey Sean, I went back and redid the whole thing. I changed the ExchangeRate function and and added an "exchangerate" column in the clearedtransacions table to calculate the exchange rate, making this metrics procedure much easier.CREATE PROC dbo.PEXDEV_MetricsTopFiveWeakest
@LPPProgramID UNIQUEIDENTIFIER,
@DateFrom DATETIME,
@DateTo DATETIME
AS
SELECT TOP(5) BoughtLoyaltyProgramId, SoldLoyaltyProgramId, DateTransactionCleared, ExchangeRate
FROM dbo.PEX_ClearedTransactions
WHERE DateTransactionCleared>=@DateFrom AND DateTransactionCleared<@DateTo
AND (BoughtLoyaltyProgramId=@LPPProgramID
OR SoldLoyaltyProgramId=@LPPProgramID)
ORDER BY ExchangeRate ;
And the strongest stored procedure would just order by ExchangeRate DESC;
My question is, why can't I do an offset fetch here? IT won't let me. I have to stick to Top.
You are making things incredibly difficult for people who want to help you by not providing sample data. The fact that English seems not to be your native language only serves to muddy the already murky waters.
Can you provide sample base data - for querying - and sample output showing what you would want to see, based on the sample input? It will save everyone's time in the long run.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 25, 2013 at 8:42 pm
Me being unable to fully explain what I'm doing with a foreign programming language somehow indicates that English isn't my native language? Where did you get your powers of logical deduction, or lack thereof? I'll provide more information but I would appreciate that if you're going to criticize someone, don't make a fool out of yourself doing it.
June 25, 2013 at 10:49 pm
dob111283 (6/25/2013)
Me being unable to fully explain what I'm doing with a foreign programming language somehow indicates that English isn't my native language? Where did you get your powers of logical deduction, or lack thereof? I'll provide more information but I would appreciate that if you're going to criticize someone, don't make a fool out of yourself doing it.
If you reread my comment, you will find no criticism - it merely states my opinion and provides some advice about how to get a better response.
I've been posting in this forum for several years now and have seen many threads which would have been resolved very quickly, had the original poster taken the time to frame their questions in the way indicated in the link in my signature. This is one of them.
I look forward to seeing your next posts.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 26, 2013 at 6:35 am
Sean, maybe because I'm having trouble understanding between TOP and OFFSET-FETCH. I know OFFSET FETCH was added to sql 2012 and I forget what the benefits are, I just remember there are benefits over TOP.
June 26, 2013 at 7:54 am
dob111283 (6/26/2013)
Sean, maybe because I'm having trouble understanding between TOP and OFFSET-FETCH. I know OFFSET FETCH was added to sql 2012 and I forget what the benefits are, I just remember there are benefits over TOP.
The big advantage is when you need to retrieve some rows "out of the middle". Prior to 2012 we had to jump through some hoops to do that kind of thing. Think about paging results, with offset fetch that is pretty simple. Without it the query is a bit more challenging.
In your case, your offset is 0 so there really is no difference in the two. I would like to understand what you mean about not being able to use it for this query.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2013 at 9:51 am
It just said "Error near Offset" and "error near fetch", when I completed the aforementioned query. Only top worked.
June 26, 2013 at 9:58 am
dob111283 (6/26/2013)
It just said "Error near Offset" and "error near fetch", when I completed the aforementioned query. Only top worked.
But you still haven't posted the actual query that you ran. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2013 at 10:38 am
Whoops
CREATE PROC dbo.PEXDEV_MetricsTopFiveWeakest
@LPPProgramID UNIQUEIDENTIFIER,
@DateFrom DATETIME,
@DateTo DATETIME
AS
SELECT BoughtLoyaltyProgramId, SoldLoyaltyProgramId, DateTransactionCleared, ExchangeRate
FROM dbo.PEX_ClearedTransactions
WHERE DateTransactionCleared>=@DateFrom AND DateTransactionCleared<@DateTo
AND (BoughtLoyaltyProgramId=@LPPProgramID
OR SoldLoyaltyProgramId=@LPPProgramID)
ORDER BY ExchangeRate
OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY;
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply