May 1, 2012 at 1:23 pm
Also, for each of the Columns below a variable is used to filter data. Please provide more info regarding these.
LastModified:
@DateFrom
@DateTo
ImportDate:
@ImpDateFrom
@ImpDateTo
ImportId:
@ImportID
May 1, 2012 at 1:34 pm
When our clients use our tools they can specify about 10 or so parameters - that is what you are asking about...
So, its the modified date of the file (eg, Word, Excel, etc)
They can also select a date range by the time the file was imported into our system (like a document Mgmt system, think "When did I start tracking this file?")
The Import ID identifies the company and the project....
In other words, we serve some VERY big WorldWide clients that actually have more than 1 company. So an ImportID tells us which company is ... doing whatever they might be doing. And then within any 1 company they can have multiple projects.
So, for example, Pettis Insurance, a worldwide provider of SQL insurance has sites all over the world. But at any 1 site, they also have a multitude of departments like Accounting, Human Resources, Investments, etc etc - so EACH combination of Company/Project has an ImportID and that allows us to drill-down to just the data any given user wants to work with.
Hope that helps.
May 1, 2012 at 2:09 pm
blandry (5/1/2012)
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.
Are you going to show us what processing you were going to do on each row of the cursor?
May 1, 2012 at 2:12 pm
blandry (5/1/2012)
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.
The comments show how many potential rows may be generated by the cte based tally table. The actual number of rows is restricted by the WHERE clause. Use '19940201' as your start date and remove the where clasue, you'll see what I mean.
May 2, 2012 at 1:41 am
I'm going to take a bit of a guess, because so far you're not very clear.
This is ugly, needs to be tidied up and requires some performance tuning. It's literally a check to see if I'm on the same page as you.
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(MONTH, DATEDIFF(MONTH, 0, DATEADD(M,n,modDate)), 0) AS modifiedMonth,
SUM(IsAccess) AS AccessCount,
SUM(IsExcel) AS ExcelCount,
SUM(IsWord) AS WordCount,
SUM(IsPowerPoint) AS PowerPointCount
FROM CTE5 a
LEFT OUTER JOIN (SELECT
Files.FileName,
Files.LastModified,
DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(M,n,Files.LastModified)), 0) AS monthModified,
Files.FileType,
SourceFiles.ImportType,
SourceFiles.ImportID,
FileTypes.Application,
dbo.fn_IsAppType('Access', FileTypes.Application) AS IsAccess,
dbo.fn_IsAppType('Excel', FileTypes.Application) AS IsExcel,
dbo.fn_IsAppType('Word', FileTypes.Application) AS IsWord,
dbo.fn_IsAppType('PowerPoint', FileTypes.Application) AS IsPowerPoint
FROM Files
INNER JOIN Groups ON Files.GroupID = Groups.GroupID
INNER JOIN SourceFiles ON Groups.BatchID = SourceFiles.BatchID
INNER JOIN Import ON SourceFiles.ImportID = Import.ImportID
INNER JOIN FileTypes ON Files.FileType = FileTypes.FileExtension
WHERE Files.FileType IN (SELECT * FROM dbo.fn_Split(@FileType, ','))
AND Files.LastModified > dateadd(day, - 1, CONVERT(DATETIME, @DateFrom, 102))
AND Files.LastModified < dateadd(day, 1, CONVERT(DATETIME, @DateTo, 102))
AND SourceFiles.ImportDate > dateadd(day, - 1, CONVERT(DATETIME, @ImpDateFrom, 102))
AND SourceFiles.ImportDate < dateadd(day, 1, CONVERT(DATETIME, @ImpDateTo, 102))
AND CHARINDEX(dbo.PADL(SourceFiles.ImportId, 2, '0'), @ImportID) > 0
AND SourceFiles.ImportType = @ImportType) b ON a.n = b.monthModified
CROSS APPLY (SELECT
DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(M,n,MIN(Files.LastModified))), 0)
FROM Files
INNER JOIN Groups ON Files.GroupID = Groups.GroupID
INNER JOIN SourceFiles ON Groups.BatchID = SourceFiles.BatchID
INNER JOIN Import ON SourceFiles.ImportID = Import.ImportID
INNER JOIN FileTypes ON Files.FileType = FileTypes.FileExtension
WHERE Files.FileType IN (SELECT * FROM dbo.fn_Split(@FileType, ','))
AND Files.LastModified > dateadd(day, - 1, CONVERT(DATETIME, @DateFrom, 102))
AND Files.LastModified < dateadd(day, 1, CONVERT(DATETIME, @DateTo, 102))
AND SourceFiles.ImportDate > dateadd(day, - 1, CONVERT(DATETIME, @ImpDateFrom, 102))
AND SourceFiles.ImportDate < dateadd(day, 1, CONVERT(DATETIME, @ImpDateTo, 102))
AND CHARINDEX(dbo.PADL(SourceFiles.ImportId, 2, '0'), @ImportID) > 0
AND SourceFiles.ImportType = @ImportType) c(modDate)
WHERE DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(M,n,modDate)), 0) <= GETDATE()
GROUP BY a.n;
Does that return the sort of results you're after? This would be a lot easier with suitably obfuscated sample data, DDL scripts and expected results based on the obfuscated sample data. See this article for the best way to lay it out.[/url]
May 2, 2012 at 6:41 am
Cadavre (5/2/2012)
I'm going to take a bit of a guess, because so far you're not very clear.This is ugly, needs to be tidied up and requires some performance tuning. It's literally a check to see if I'm on the same page as you.
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(MONTH, DATEDIFF(MONTH, 0, DATEADD(M,n,modDate)), 0) AS modifiedMonth,
SUM(IsAccess) AS AccessCount,
SUM(IsExcel) AS ExcelCount,
SUM(IsWord) AS WordCount,
SUM(IsPowerPoint) AS PowerPointCount
FROM CTE5 a
LEFT OUTER JOIN (SELECT
Files.FileName,
Files.LastModified,
DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(M,n,Files.LastModified)), 0) AS monthModified,
Files.FileType,
SourceFiles.ImportType,
SourceFiles.ImportID,
FileTypes.Application,
dbo.fn_IsAppType('Access', FileTypes.Application) AS IsAccess,
dbo.fn_IsAppType('Excel', FileTypes.Application) AS IsExcel,
dbo.fn_IsAppType('Word', FileTypes.Application) AS IsWord,
dbo.fn_IsAppType('PowerPoint', FileTypes.Application) AS IsPowerPoint
FROM Files
INNER JOIN Groups ON Files.GroupID = Groups.GroupID
INNER JOIN SourceFiles ON Groups.BatchID = SourceFiles.BatchID
INNER JOIN Import ON SourceFiles.ImportID = Import.ImportID
INNER JOIN FileTypes ON Files.FileType = FileTypes.FileExtension
WHERE Files.FileType IN (SELECT * FROM dbo.fn_Split(@FileType, ','))
AND Files.LastModified > dateadd(day, - 1, CONVERT(DATETIME, @DateFrom, 102))
AND Files.LastModified < dateadd(day, 1, CONVERT(DATETIME, @DateTo, 102))
AND SourceFiles.ImportDate > dateadd(day, - 1, CONVERT(DATETIME, @ImpDateFrom, 102))
AND SourceFiles.ImportDate < dateadd(day, 1, CONVERT(DATETIME, @ImpDateTo, 102))
AND CHARINDEX(dbo.PADL(SourceFiles.ImportId, 2, '0'), @ImportID) > 0
AND SourceFiles.ImportType = @ImportType) b ON a.n = b.monthModified
CROSS APPLY (SELECT
DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(M,n,MIN(Files.LastModified))), 0)
FROM Files
INNER JOIN Groups ON Files.GroupID = Groups.GroupID
INNER JOIN SourceFiles ON Groups.BatchID = SourceFiles.BatchID
INNER JOIN Import ON SourceFiles.ImportID = Import.ImportID
INNER JOIN FileTypes ON Files.FileType = FileTypes.FileExtension
WHERE Files.FileType IN (SELECT * FROM dbo.fn_Split(@FileType, ','))
AND Files.LastModified > dateadd(day, - 1, CONVERT(DATETIME, @DateFrom, 102))
AND Files.LastModified < dateadd(day, 1, CONVERT(DATETIME, @DateTo, 102))
AND SourceFiles.ImportDate > dateadd(day, - 1, CONVERT(DATETIME, @ImpDateFrom, 102))
AND SourceFiles.ImportDate < dateadd(day, 1, CONVERT(DATETIME, @ImpDateTo, 102))
AND CHARINDEX(dbo.PADL(SourceFiles.ImportId, 2, '0'), @ImportID) > 0
AND SourceFiles.ImportType = @ImportType) c(modDate)
WHERE DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(M,n,modDate)), 0) <= GETDATE()
GROUP BY a.n;
Does that return the sort of results you're after? This would be a lot easier with suitably obfuscated sample data, DDL scripts and expected results based on the obfuscated sample data. See this article for the best way to lay it out.[/url]
I'd replace the function calls to dbo.fn_IsAppType with case statements. This would eliminate the overhead of the scalar functions.
May 2, 2012 at 10:55 am
Hi Lynn / Cadavre,
Sorry I am late today - meetings in the AM and more coming too - but while I have time I looked over the code you sent, and well, am blown away. I see most of what you are doing and its good learning for me, but I never would have figured that kind of stuff out on my own.
I am however getting an error and I cant figure it out! Look at this line that is choking...
DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(M,n,Files.LastModified)), 0) AS monthModified,
It tells me that "n is an invalid column name" and Intellisense is highlighting the "n" in DATEADD, but it seems to me it cant be invalid as it serves the number piece of the function! Could this be a conflict with the CTE above it? Any ideas on that?
Also - I hear you on dropping the Scalar function and CASEing it out instead - but do I really suffer that big a hit on Scalar functions? Seems I hear a lot about avoiding them and am I wrong in guessing that these should be a "must use, nothing else" feature of SQL?
I am working to get you data by the way - I think you will just have to swear on pain of death that youre not spies from I guess, some other galaxy. I will keep you posted on that.
As always, thanks for the help, and the excellent code to study.
May 2, 2012 at 11:01 am
blandry (5/2/2012)
Hi Lynn / Cadavre,Sorry I am late today - meetings in the AM and more coming too - but while I have time I looked over the code you sent, and well, am blown away. I see most of what you are doing and its good learning for me, but I never would have figured that kind of stuff out on my own.
I am however getting an error and I cant figure it out! Look at this line that is choking...
DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(M,n,Files.LastModified)), 0) AS monthModified,
It tells me that "n is an invalid column name" and Intellisense is highlighting the "n" in DATEADD, but it seems to me it cant be invalid as it serves the number piece of the function! Could this be a conflict with the CTE above it? Any ideas on that?
Also - I hear you on dropping the Scalar function and CASEing it out instead - but do I really suffer that big a hit on Scalar functions? Seems I hear a lot about avoiding them and am I wrong in guessing that these should be a "must use, nothing else" feature of SQL?
I am working to get you data by the way - I think you will just have to swear on pain of death that youre not spies from I guess, some other galaxy. I will keep you posted on that.
As always, thanks for the help, and the excellent code to study.
Regarding UDF Scalar functions, read this blog post: http://www.sqlservercentral.com/blogs/lynnpettis/2009/05/07/comparing-hardcoded-functions-in-line-tvf-s-and-scalar-functions/
Also, it really depends on where and how you are using them. I can see them being used in a WHERE clause if they are evaluated once during the execution of the query, but in a select column list, they can kill performance
May 2, 2012 at 11:06 am
Also, replace your split function with the one you will find with this article (oh, read the article and its discussion):
May 2, 2012 at 11:08 am
blandry (5/2/2012)
I am however getting an error and I cant figure it out! Look at this line that is choking...
DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(M,n,Files.LastModified)), 0) AS monthModified,
Which one is it choking on? Double click on the error, it will take you to the problem line.
May 2, 2012 at 11:20 am
Lynn Pettis (5/2/2012)
blandry (5/2/2012)
I am however getting an error and I cant figure it out! Look at this line that is choking...
DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(M,n,Files.LastModified)), 0) AS monthModified,
Which one is it choking on? Double click on the error, it will take you to the problem line.
Yeah, that much I knew! Its choking on the "n" in DATEADD(M,n,Files.LastModified))... This is why its a bit confusing to me as that seems correct!
Thanks for the links to the articles - gave them both a read and I see what you are saying. Its not a black and white answer, and I can see how scale plays in. Jeff's post is an excellent walk-through for someone like me and I have seen his work before (and yours!). I printed them both out for more in-depth reading later on today. Thanks for that.
One drawback to it all is that I now see how old our queries are getting and how they could be very extensively improved. Guess I better talk that over with the boss too...
Let me know if you see something in that "n" that clearly I am missing. Thats a direct copy of Cadavre's code and I only kept the import parameters - just cant see how this is a problem cause the MS Help on that shows this should be correct?!?!
May 2, 2012 at 11:35 am
blandry (5/2/2012)
Lynn Pettis (5/2/2012)
blandry (5/2/2012)
I am however getting an error and I cant figure it out! Look at this line that is choking...
DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(M,n,Files.LastModified)), 0) AS monthModified,
Which one is it choking on? Double click on the error, it will take you to the problem line.
Yeah, that much I knew! Its choking on the "n" in DATEADD(M,n,Files.LastModified))... This is why its a bit confusing to me as that seems correct!
Thanks for the links to the articles - gave them both a read and I see what you are saying. Its not a black and white answer, and I can see how scale plays in. Jeff's post is an excellent walk-through for someone like me and I have seen his work before (and yours!). I printed them both out for more in-depth reading later on today. Thanks for that.
One drawback to it all is that I now see how old our queries are getting and how they could be very extensively improved. Guess I better talk that over with the boss too...
Let me know if you see something in that "n" that clearly I am missing. Thats a direct copy of Cadavre's code and I only kept the import parameters - just cant see how this is a problem cause the MS Help on that shows this should be correct?!?!
There are 2 lines with this code, DATEADD(M,n,Files.LastModified)), which one of the lines is giving you the error?
May 2, 2012 at 11:38 am
blandry (5/2/2012)
Lynn Pettis (5/2/2012)
blandry (5/2/2012)
I am however getting an error and I cant figure it out! Look at this line that is choking...
DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(M,n,Files.LastModified)), 0) AS monthModified,
Which one is it choking on? Double click on the error, it will take you to the problem line.
Yeah, that much I knew! Its choking on the "n" in DATEADD(M,n,Files.LastModified))... This is why its a bit confusing to me as that seems correct!
Thanks for the links to the articles - gave them both a read and I see what you are saying. Its not a black and white answer, and I can see how scale plays in. Jeff's post is an excellent walk-through for someone like me and I have seen his work before (and yours!). I printed them both out for more in-depth reading later on today. Thanks for that.
One drawback to it all is that I now see how old our queries are getting and how they could be very extensively improved. Guess I better talk that over with the boss too...
Let me know if you see something in that "n" that clearly I am missing. Thats a direct copy of Cadavre's code and I only kept the import parameters - just cant see how this is a problem cause the MS Help on that shows this should be correct?!?!
Regarding your conversation with your boss, don't be surprised if you get the "if it ain't broke, don't fix it" response. You may need to do a proof of concept to show him.
May 2, 2012 at 11:51 am
I can get the code to parse but without the DDL for the tables involved I can't do anything else.
May 2, 2012 at 12:17 pm
First, found another function that needs to be defined: dbo.PADL(sf.ImportId, 2, '0').
Second, as I went through the code manually I found 4 lines with the DATEADD code. Really need to know which line is causing you the error. No tables on my end so I can't figure that one out myself.
Viewing 15 posts - 31 through 45 (of 77 total)
You must be logged in to reply to this topic. Login to reply