September 27, 2010 at 11:37 am
Kit, did you get a working solution yet on this? (It seems like the solutions offered by Chris and Brandie are working.)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 27, 2010 at 12:13 pm
Yes, I did, Wayne. Chris' is working great and so is Brandie's. Of course, now that I have it working, I get told, "Oh, that's nice, but the data is getting input in a different sequence not all the data will be available and I also want to do it a different way for visibility of the original source of the data." <sigh>
But, I learned something and I think I'm slowing wrapping my brain around the self-join so it's all good. 🙂
-- Kit
September 27, 2010 at 12:17 pm
Brandie Tarvin (9/27/2010)
On second thought, that might not be the best metaphor I've ever used... Let me know if I've confused you.
I'm always confused... it's just a matter of degree. 😀 I think I got the concept though.
-- Kit
September 27, 2010 at 1:34 pm
Kit G (9/27/2010)
steve-893342 (9/24/2010)
I think you can solve this one with a PIVOT and some splittingThanks Steve for the code. That date at Los Mules where the Event date is the same but the time is different is throwing a big monkey wrench into the works. It does the job, except for those two dates where it gives me NULL for the time.
Shouldn't do. Apart from missing an LTRIM on Menu2, which I have now corrected, my results appear to be identical to Chris's.
September 27, 2010 at 1:53 pm
steve-893342 (9/27/2010)
Kit G (9/27/2010)
steve-893342 (9/24/2010)
I think you can solve this one with a PIVOT and some splittingThanks Steve for the code. That date at Los Mules where the Event date is the same but the time is different is throwing a big monkey wrench into the works. It does the job, except for those two dates where it gives me NULL for the time.
Shouldn't do. Apart from missing an LTRIM on Menu2, which I have now corrected, my results appear to be identical to Chris's.
huh. I just went back and ran your code and Chris' code side by side and I got NULL for the event dates for the Los Mules event in your code, but not Chris' code.
Is there a setting might be different between your installation of SQL and mine that would account for the difference? I haven't the foggiest what it might be as I didn't install SQL Server on my machine so I have no clue if any of the defaults were changed.
-- Kit
September 27, 2010 at 2:05 pm
Kit G (9/27/2010)
steve-893342 (9/27/2010)
Kit G (9/27/2010)
steve-893342 (9/24/2010)
I think you can solve this one with a PIVOT and some splittingThanks Steve for the code. That date at Los Mules where the Event date is the same but the time is different is throwing a big monkey wrench into the works. It does the job, except for those two dates where it gives me NULL for the time.
Shouldn't do. Apart from missing an LTRIM on Menu2, which I have now corrected, my results appear to be identical to Chris's.
huh. I just went back and ran your code and Chris' code side by side and I got NULL for the event dates for the Los Mules event in your code, but not Chris' code.
Is there a setting might be different between your installation of SQL and mine that would account for the difference? I haven't the foggiest what it might be as I didn't install SQL Server on my machine so I have no clue if any of the defaults were changed.
It looks like you spelled the street address 2 different ways for Los Mules in your sample data. I never noticed the first time around.
'4971 Buriito Ave'
'4971 Burrito Ave'
-Luke.
September 27, 2010 at 2:08 pm
Kit G (9/27/2010)
steve-893342 (9/27/2010)
Kit G (9/27/2010)
steve-893342 (9/24/2010)
I think you can solve this one with a PIVOT and some splittingThanks Steve for the code. That date at Los Mules where the Event date is the same but the time is different is throwing a big monkey wrench into the works. It does the job, except for those two dates where it gives me NULL for the time.
Shouldn't do. Apart from missing an LTRIM on Menu2, which I have now corrected, my results appear to be identical to Chris's.
huh. I just went back and ran your code and Chris' code side by side and I got NULL for the event dates for the Los Mules event in your code, but not Chris' code.
Is there a setting might be different between your installation of SQL and mine that would account for the difference? I haven't the foggiest what it might be as I didn't install SQL Server on my machine so I have no clue if any of the defaults were changed.
D'oh! Helps if I actually LOOK at the results.
Steve, the NULLs are coming from the second date for the Los Mules event. Since the two events are on the same date, but a different times, I think that is throwing off your code. So it is EDate2 and ETime2 that are missing and the results of the query give me two rows for Los Mules with each row being a date and time instead of one row with the two dates and times in that row.
Sorry about that. My most profound apologies for jumping to conclusions about the results of your code.
-- Kit
September 27, 2010 at 2:11 pm
Luke L (9/27/2010)[/b
It looks like you spelled the street address 2 different ways for Los Mules in your sample data. I never noticed the first time around.
'4971 Buriito Ave'
'4971 Burrito Ave'
-Luke.
oops :ermm: Thanks for the catch. I've fixed it.
-- Kit
September 27, 2010 at 2:11 pm
Kit G (9/27/2010)
Kit G (9/27/2010)
steve-893342 (9/27/2010)
Kit G (9/27/2010)
steve-893342 (9/24/2010)
I think you can solve this one with a PIVOT and some splittingThanks Steve for the code. That date at Los Mules where the Event date is the same but the time is different is throwing a big monkey wrench into the works. It does the job, except for those two dates where it gives me NULL for the time.
Shouldn't do. Apart from missing an LTRIM on Menu2, which I have now corrected, my results appear to be identical to Chris's.
huh. I just went back and ran your code and Chris' code side by side and I got NULL for the event dates for the Los Mules event in your code, but not Chris' code.
Is there a setting might be different between your installation of SQL and mine that would account for the difference? I haven't the foggiest what it might be as I didn't install SQL Server on my machine so I have no clue if any of the defaults were changed.
D'oh! Helps if I actually LOOK at the results.
Steve, the NULLs are coming from the second date for the Los Mules event. Since the two events are on the same date, but a different times, I think that is throwing off your code. So it is EDate2 and ETime2 that are missing and the results of the query give me two rows for Los Mules with each row being a date and time instead of one row with the two dates and times in that row.
Sorry about that. My most profound apologies for jumping to conclusions about the results of your code.
No worries:-) I just like to get to the bottom of these things.
September 27, 2010 at 2:14 pm
And with my test data fixed, everyone's code works now! Yay! Now to go study the code and figure out why it works and how to use it in the future to better my code. Thanks again folks!
-- Kit
September 28, 2010 at 2:28 am
Brandie Tarvin (9/27/2010)
Kit G (9/27/2010)
The part makes my head hurt is the joining the table to itself. Know of any articles about that which might help make my head hurt less?It's called a Recursive Query. That's what CTEs (Common Table Expressions) are built for. Google both terms and you'll find a plethora of articles about both.
Think of it like a pool pump. There's got to be water in the pump before the pump can work (or it burns out), the pump adds water to the pool, then takes water from the pool to pump it back in. The pump's filter? That's the SELECT statement.
On second thought, that might not be the best metaphor I've ever used... Let me know if I've confused you.
As a metaphor I think it's a darned good one and I'll nick it from you, Brandie, if you don't mind!
The query isn't a recursive one, it's a fairly simple use of a CTE to replace what would have been a temp table in earlier versions of SQL Server. A recursive query refers to itself as input. As an example, here's a recursive query which outputs a kind of running total.
-----------------------------------------------------------
-- Create a working table to play with.
-- Perform an update against this table and read some updated records
-- The quirky update will require a temp table copy to work against, which will have to be copied back
-- The rCTE will run against the original table but must also be written back
-----------------------------------------------------------
DROP TABLE #Numbers
SELECT TOP 1000000
n = ROW_NUMBER() OVER (ORDER BY a.name),
CalcValue = CAST(NULL AS BIGINT)
INTO #Numbers
FROM master.dbo.syscolumns a, master.dbo.syscolumns b
CREATE UNIQUE CLUSTERED INDEX CIn ON #Numbers ([n] ASC)
-----------------------------------------------------------
-- Recursive CTE
-----------------------------------------------------------
SET STATISTICS IO ON
SET STATISTICS TIME ON
;WITH Calculator (n, CalcValue) AS (
SELECT n.n, CalcValue = CAST(n.n AS BIGINT)
FROM #Numbers n
WHERE n.n = 1
UNION ALL
SELECT n.n, CalcValue = n.n + c.n
FROM #Numbers n
INNER JOIN Calculator c ON c.n + 1 = n.n
)
SELECT n, CalcValue
FROM Calculator
WHERE n = 3063
OPTION (MAXRECURSION 0)
-- (1,000,000 row(s) affected) / CPU time = 32438 ms, elapsed time = 35148 ms.
-- Table 'Worktable'. Scan count 2, logical reads 6000001, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SET STATISTICS IO Off
SET STATISTICS TIME Off
-----------------------------------------------------------
-- 'Quirky' update
-----------------------------------------------------------
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @Lastval INT = 0, @CalcValue BIGINT
UPDATE #Numbers SET
@CalcValue = CalcValue = (@Lastval + n),
@Lastval = n
OPTION (MAXDOP 1)
-- (1,000,000 row(s) affected) / CPU time = 4218 ms, elapsed time = 5719 ms.
-- Table #Numbers... Scan count 1, logical reads 3113, physical reads 6, read-ahead reads 3146, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SELECT * FROM #Numbers WHERE n = 3063 --ORDER BY n
-- CPU time = 719 ms, elapsed time = 14043 ms.
SET STATISTICS IO Off
SET STATISTICS TIME Off
The first part (the 'anchor') of the recursive CTE (rCTE) pulls out the very first row of output. Subsequent rows are returned by the UNION ALL'ed statement. Notice that this second statement contains a reference to the CTE of which it is part of the definition. This is the recursive bit. Hope this helps, if it doesn't then holler. Took me ages to get my head around this!
Incidentally, the two queries there show the use of a rCTE as an alternative to the quirky update. First thing you notice is that although the rCTE is "only" 7 times slower, it hits the CPU a heck of a lot harder. However the next thing you will notice is that the quirky update and the rCTE do different things.
The quirky update does just that - if you want output from it, you have to run a SELECT. Chances are you will also need to create, populate and index a temp table, to run the update against.
The rCTE does the opposite - it's a read, so if you want to persist the results then you have to spool the output to a temp table to use as an update source, or use the rCTE itself as an update source, which can be really slow.
EDIT: added OPTION (MAXDOP 1) to quirky update
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
September 28, 2010 at 4:32 am
Kit G (9/27/2010)
Of course, now that I have it working, I get told, "...not all the data will be available..."
The worst thing a DBA can be told is "not all the data will be available." How do they expect you to give them what they want if you don't have their data?
Kit G (9/27/2010)
"... and I also want to do it a different way for visibility of the original source of the data."
Careful now. This won't affect your code nearly as much as you think. They're confusing how the database data looks with how their report will look. Two different things. Just remember, the report is how to make things pretty for the users. The database is how to make things functional.
Don't let them use reporting standards to force you into making bad database design decisions.
September 28, 2010 at 4:36 am
Chris Morris-439714 (9/28/2010)
Brandie Tarvin (9/27/2010)
Kit G (9/27/2010)
The part makes my head hurt is the joining the table to itself. Know of any articles about that which might help make my head hurt less?It's called a Recursive Query. That's what CTEs (Common Table Expressions) are built for. Google both terms and you'll find a plethora of articles about both.
Think of it like a pool pump. There's got to be water in the pump before the pump can work (or it burns out), the pump adds water to the pool, then takes water from the pool to pump it back in. The pump's filter? That's the SELECT statement.
On second thought, that might not be the best metaphor I've ever used... Let me know if I've confused you.
As a metaphor I think it's a darned good one and I'll nick it from you, Brandie, if you don't mind!
Hey, if it helps other people understand it, go right ahead and use it. But I reserve the right to be embarrassed by it if people say it's the worst metaphor ever. @=)
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply