May 1, 2012 at 6:20 am
Cadavre,
Today I will study and dive into the CTE element much deeper - and yes, you are absolutely correct - we developers who have spent years (decades in my case) doing things in a top-down, looping manner, unfortunately take the same approach most of the time with SQL when it goes beyond the basic stored procedure code I know well.
Lynn,
...and you certainly drive that point home with your example - thanks for that. Changing the old thinking on my part is a must!
To you both,
A bit more depth here - what I am doing is taking old VB dataset code for reporting (lots and lots of loops) and trying to convert about 9 routines to SQL. Some of these reports take a long time to churn and burn and even I know working with VB and datasets is a lousy way to go. Instead of duplicating these 'loopers' (if you will), you have helped me to understand that I have to take the time to learn, and get these things set based or the entire venture is just converting bad code to bad code in a different place.
I will dive in over these next couple days and if I get stuck, its nice to know you guys are out there with your impressive expertise!
Thanks again for all the help and the kick in the right direction.
May 1, 2012 at 6:30 am
No problem, make sure you post with any questions you have.
May 1, 2012 at 6:38 am
I am glad my very simplistic example helped. We are here if you have any questions.
May 1, 2012 at 12:30 pm
Hello again Cadavre / Lynn,
I have a question regarding the CTE Cadavre shared with me. I have done a bit of studying to get the brain wrapped around this powerful feature, but there is one thing I am trying to engineer that I dont think I am doing correctly, or at least my attempts thus far are falling short... Here is a small block from my query...
--====================================================================================================
-- Get the oldest date in the table
--====================================================================================================
Declare @StartDate as Date, @StartYear As Int
SELECT @StartDate = MIN(LastModified) FROM #TempTable
SET @StartYear = YEAR(@StartDate)
Declare @StrStartYear as VarChar(4) = CAST(@StartYear As varchar(4))
Declare @StrStartDate as VarChar(10) = (@StrStartYear + '-01-01')
SET @StartDate = CAST(@StrStartDate as DateTime)
--====================================================================================================
-- Create the Summary Table
--====================================================================================================
CREATE TABLE #Summary (
ModDate DateTime,
RMonth VARCHAR (2) ,
RYear VARCHAR (4) ,
AccessCount INT ,
AccessRunTot INT ,
AccessRunPct Decimal ,
ExcelCount INT ,
ExcelRunTot INT ,
ExcelRunPct Decimal ,
WordCount INT ,
WordRunTot INT ,
WordRunPct Decimal ,
PPointCount INT ,
PPointRunTot INT ,
PPointRunPct Decimal ,
LineTotal Decimal ,
RunTotal Decimal ,
PctOfTot Decimal);
--====================================================================================================
-- Use a Common Table Expression to gather the start dates for each month/year in the span
--====================================================================================================
WITH CTE(n) AS(SELECT 1 UNION ALL SELECT 1),
CTE2(n) AS (SELECT 1 FROM CTE x, CTE y),
CTE3(n) AS (SELECT 1 FROM CTE2 x, CTE2 y),
CTE4(n) AS (SELECT 1 FROM CTE3 x, CTE3 y),
CTE5(n) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM CTE4 x, CTE4 y)
SELECT DATEADD(M,n,modDate)
FROM CTE5
CROSS APPLY (SELECT CAST(@StrStartDate AS DATETIME)) a(modDate)
WHERE DATEADD(M,n,modDate) <= GETDATE()
I get the oldest possible date from the core data and then use that and the CTE to develop my date run through the temp file you see called Summary. That works very well. But my "bad" habits then lead me to loop through the CTE results and "stuff" them into the Temp table and it just seems I should be able to engineer that in a better way.
In other words (if not adding more confusion), the Summary Temp Table has the columns that the report that this query feeds - so I need that structure in place. But I also need those dates in there to go to the next step and it seems "looping" through the CTE results to plug them into that table is probably not the best way to do this.
Is there a better way? I could not find any documentation that specifically spells that out - so just wondering, is there some more set based way to do that?
Thanks!
May 1, 2012 at 12:42 pm
Minor thing first, then I'll look at the rest.
Change this:
Declare @StartDate as Date,
@StartYear As Int
SELECT @StartDate = MIN(LastModified) FROM #TempTable
SET @StartYear = YEAR(@StartDate)
Declare @StrStartYear as VarChar(4) = CAST(@StartYear As varchar(4))
Declare @StrStartDate as VarChar(10) = (@StrStartYear + '-01-01')
SET @StartDate = CAST(@StrStartDate as DateTime)
To this:
DECLARE @StartDate as Date;
SELECT @StartDate = CAST(DATEADD(yy,DATEDIFF(yy,0,MIN(LastModified)),0) AS DATE) FROM #TempTable;
May 1, 2012 at 12:45 pm
And at this point, what are we doing? Just putting a date into your table? What about the rest of the data? Looks to me like there is more to work on here.
May 1, 2012 at 12:53 pm
Thinking this is what you are trying to get so far?
DECLARE @StartDate as Date;
SELECT @StartDate = CAST(DATEADD(yy,DATEDIFF(yy,0,MIN(LastModified)),0) AS DATE) FROM #TempTable;
WITH CTE(n) AS(SELECT 1 UNION ALL SELECT 1), -- 2 rows
CTE2(n) AS (SELECT 1 FROM CTE x, CTE y), -- 4 rows
CTE3(n) AS (SELECT 1 FROM CTE2 x, CTE2 y), -- 16 rows
CTE4(n) AS (SELECT 1 FROM CTE3 x, CTE3 y), -- 256 rows
CTE5(n) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM CTE4 x, CTE4 y) -- 65537 rows
SELECT
DATEADD(M,n,@StartDate)
FROM
CTE5
WHERE
n < DATEDIFF(mm,@StartDate, GETDATE());
May 1, 2012 at 12:56 pm
Lynn,
Thanks - sweet bit of code! Uh, yes... There is more work to do here and I promise I will try to do it with set based thinking - but right now I (think anyway that I) need to get the dates in there so that I can then query for the other values you see in the Summary Temp Table.
My guess is that if I get that step done and then have you guys take a look, surely you will get it down to one or two lines - but its a great learning experience for me - as surely I will "overdo it" to some degree.
As I say, you can see from the column names in the summary table, I have to count up various MS Office files, get percentages, and also calc their 'run times' - all that coming from the "raw data" in my initial query (shown in my first post, NOT in the most recent one).
Again, thanks - my brain hurts so I must be actually learning some stuff!!!
May 1, 2012 at 1:03 pm
Lynn,
In your second post you say "Thinking this is what you are trying get so far?" - uh, no... I dont get that many rows as shown in your comments there.
What Cadavre helped me do is take the Min date in the raw data, lets say 01/01/1994 and build a table like this...
01/01/1994
02/01/1994
03/01/1994
.
.
04/01/2012
05/01/2012
In other words - we run totals by month for this report so eventually when I do the final, or ending query I will be "WHERE clausing" it based on MONTH = and YEAR =. What I mean by that is it would be MONTH(MODDATE), YEAR(MODDATE).
Did I make myself clear enough there? Or just confuse you a bit?
As an aside - you can imagine how long this report takes to process when done in VB.NET with Datasets....
As always - thanks for the excellent help.
May 1, 2012 at 1:04 pm
Would you please post the code for these two functions:
dbo.fn_IsAppType
dbo.fn_Split
May 1, 2012 at 1:06 pm
Also, what counts are you trying to get. You provided the query you built the cursor on but you didn't show us what you were going to do with each row inside the loop.
May 1, 2012 at 1:10 pm
Here is fn_ISAppType
ALTER FUNCTION [dbo].[fn_IsAppType](@LookingForAppBrand varchar(100),
@AppBrandValue varChar(100))
RETURNS int
As
BEGIN
Declare @IsApp int
IF @AppBrandValue = @LookingForAppBrand set @IsApp = 1
ELSE
set @IsApp = 0
RETURN @IsApp
END
Here is fn_Split
ALTER FUNCTION [dbo].[fn_Split] (@value varchar(8000),@sep char(1))
RETURNS @split_table TABLE(val varchar(512))
AS
BEGIN
DECLARE @char char(1)
DECLARE @pos int
DECLARE @max-2 int
DECLARE @split_val varchar(512)
SET @max-2 = LEN(@value)
SET @pos = 1
SET @split_val = ''
WHILE @pos <= @max-2
BEGIN
SET @char = substring(@value, @pos, 1)
IF @char <> @sep SET @split_val = @split_val + @char
IF @char = @sep or @pos = @max-2
BEGIN
IF @split_val <> ''
INSERT @split_table VALUES(@split_val)
SET @split_val = ''
END
SET @pos= @pos + 1
END
RETURN
END
These are both functions that were done long before my time here in this job - but here are the commented descriptions of them in case it helps...
fn_AppType
-- If the passed string is equivelent to the string you are looking
-- for this returns true. If one wants to know if an application is
-- 'Word' this will answer with a one or a zero. This provided a way
-- 'to break out a report into columns representing 'Access' 'Word'
-- and Excel. See The Aging report.
fn_Split
-- Description: converts a character seperated string into a table
-- Specify the one character delimiter in variable @sep
-- To use: select * from dbo.fnSplit('aaa,bbb,ccc,ddd')
Hope this helps.
May 1, 2012 at 1:15 pm
Lynn Pettis (5/1/2012)
Also, what counts are you trying to get. You provided the query you built the cursor on but you didn't show us what you were going to do with each row inside the loop.
Not sure I exactly understand the question - but this report shows a client counts and percentage of MS Office files created over a long period of time. So we count the files by month, show what months are high vs low percentage months - what apps (Word, Excel, etc) are high vs low percentage, and what the Run times are of file and apps.
* In our business "run time" is defined by how long someone has office files open - roughly representing how much time they spend say, working a spreadsheet version an Access table, versus a Word doc, etc etc.
Hope that helps.
May 1, 2012 at 1:15 pm
First, there is a much better split function that we can use. Will get to that later if someone doesn't beat me to it.
Second, I am assuming the the value in FileTypes.Application is either 'Access', 'Word', 'Excel', or 'PowerPoint'. Is this correct or can there be other values?
May 1, 2012 at 1:18 pm
Lynn Pettis (5/1/2012)
First, there is a much better split function that we can use. Will get to that later if someone doesn't beat me to it.Second, I am assuming the the value in FileTypes.Application is either 'Access', 'Word', 'Excel', or 'PowerPoint'. Is this correct or can there be other values?
Yeah that Split function is at least 7 years old!
And yes, although we do handle Open Office docs, Google docs, and some others - your presumption is right on target - its Access, Excel, PPoint or Word.
Viewing 15 posts - 16 through 30 (of 77 total)
You must be logged in to reply to this topic. Login to reply