Query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query help

Author

Topic

kaos_king
Starting Member

13 Posts

Posted - 2013-07-25 : 07:31:36

Hi guys,

Its been a few years since I've done SQL so I'm a little rusty (when I was doing it I didnt really write stored proc's etc so I wasn't too advanced), however I have to start getting involved again now I've started a new job. I've been looking around and seems like a nice place here.

I have been trying to get a query to work and now hit a point where I'm not sure how to achieve what I want.

I will explain this as best I can

. Basically, I am looking at a log table to see who ran what reports along with the parameters. The table has a separate rows for the report name, from date, to date and a couple of other filters. Now, what "links" these rows together is the date & time. All the rows that relate to one instance of running a report have the exact same time (to the second), apart from that I am unsure how else they could be linked unless the row count column can be used (row count column increases by 1 per each row but I dont know how you differentiate between each instance of running a report).

So what I want do is combine these seperate rows into one row for each instance of running a report.

An example of the data would look a little like this:


Date		        User Name       Service	        Message						        Row Count
05/03/2013 13:06	GENERIC		Reporting	Run from Query Builder query TF02 Supply Of Details     68933312
05/03/2013 13:06	GENERIC		Reporting	Parameter FromDate = 2013/02/25 00:00:00		68933313
05/03/2013 13:06	GENERIC		Reporting	Parameter ToDate = 2013/02/26 00:00:00		        68933314
05/03/2013 13:06	GENERIC		Reporting	Parameter Can = N					68933315
05/03/2013 13:06	GENERIC		Reporting	Parameter Deadline = 8					68933316
05/03/2013 13:06	GENERIC		Reporting	Parameter filter = I				        68933317
05/03/2013 13:06	GENERIC		Reporting	Parameter RFTF02 = 85e2214c-f04c-49aa-9b05-df5ccda6eb45	68933318
05/03/2013 13:06	GENERIC		Reporting	Builder query completed TF02 Supply Of Details		68933319
05/03/2013 13:10	GENERIC		Reporting	Run from Query Builder query TF02 Excepti		68933537
05/03/2013 13:10	GENERIC		Reporting	Parameter FromDate = 2013/02/25 00:00:00		68933538
05/03/2013 13:10	GENERIC		Reporting	Parameter ToDate = 2013/02/25 00:00:00			68933539
05/03/2013 13:10	GENERIC		Reporting	Parameter Can = N					68933540
05/03/2013 13:10	GENERIC		Reporting	Parameter Deadline = 8					68933541
05/03/2013 13:10	GENERIC		Reporting	Parameter filter = I					68933542
05/03/2013 13:10	GENERIC		Reporting	Parameter RFTF02 = 85e2214c-f04c-49aa-9b05-df5ccda6eb45	68933543
05/03/2013 13:10	GENERIC		Reporting	Builder query completed TF02 Exceptions			68933544

In this example, if you look at the date/time and group them together, there are two instances of running reports. I would like my output to be like this based on the example:


Date                User Name           Service         Report Name				From Date 		To Date 	          Filter
05/03/2013 13:06    GENERIC		Reporting	TF02 Supply Of Details	  		2013/02/25 00:00:00	2013/02/26 00:00:00	  85e2214c-f04c-49aa-9b05-df5ccda6eb45
05/03/2013 13:10    GENERIC		Reporting	TF02 Exceptions				2013/02/25 00:00:00	2013/02/25 00:00:00	  85e2214c-f04c-49aa-9b05-df5ccda6eb45

As you can see, the report name is a substring so it trims off 'Run from Query Builder query' from the message column. Also the filter is a substring to give a GUID which I would like to join to the corresponding table.

Now this is where I am stuck as I have no idea how to combine these rows to get the information how I want it. I would appreciate any help/ideas and thank you in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-25 : 07:45:13

Try like below


SELECT [Date],[User Name],[Service],
MAX(CASE WHEN Message LIKE '%Query Builder%' THEN STUFF(Message,1,PATINDEX('%Query Builder query%',Message)+19,'') END) AS ReportName,
MAX(CASE WHEN Message LIKE 'Parameter FromDate%' THEN REPLACE(STUFF(Message,1,PATINDEX('%Parameter FromDate%',Message)+19,''),'/','-') END) AS FromDate,
MAX(CASE WHEN Message LIKE 'Parameter ToDate%' THEN REPLACE(STUFF(Message,1,PATINDEX('%Parameter ToDate%',Message)+18,''),'/','-') END) AS ToDate,
MAX(CASE WHEN Message LIKE 'Parameter RFTF02%' THEN STUFF(Message,1,PATINDEX('%Parameter RFTF02%',Message)+18,'') END) AS Filter
FROM Table
GROUP BY [Date],[User Name],[Service]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kaos_king
Starting Member

13 Posts

Posted - 2013-07-25 : 09:50:12

Wow, thank you for your quick response!

I have tried your code, and I seem to get a different row for each of the selects, although this is already looking miles better

I have taken a screenshot of the output as it was easier than to format the result into a table here like I did before.

[url]http://www.freeimagehosting.net/1ozy9[/url]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-26 : 02:08:04

Nope..You're not doing it as I suggested I'm afraid.
If you just group on [Date],[User Name],[Service] you should get only one row per [Date],[User Name],[Service] combination which is not what the output is showing. hence I'm sure you are adding some other columns also in the group by which i'm unaware of.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kaos_king
Starting Member

13 Posts

Posted - 2013-07-26 : 02:46:59

Okay, I didn't change the code too too much, just just things like the table name and taking the capitals out of 'UserName'.

I will post the actual code in in about 2hours when I get get to work.

Thank you for your continued help though

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-26 : 02:54:03

no problem
you're welcome
Will check code once you post and see if we can adjust it to get your required output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kaos_king
Starting Member

13 Posts

Posted - 2013-07-26 : 04:39:12

Here is the code I used. I am aware that things like the date variables are redundant as I haven't included it in the where clause yet but I thought I'd post it all just in case.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @DisplayFrom as DATETIME
DECLARE @DisplayTo as DATETIME
DECLARE @FromDate as DATETIME
DECLARE @ToDate as DATETIME

SET @DisplayFrom = <%FromDate|Enter start date%>
SET @DisplayTo = <%ToDate|Enter end date%>
SET @FromDate = dbo.fUniversalTime(@DisplayFrom)
SET @ToDate = dbo.fUniversalTime(@DisplayTo)

declare @User varChar(40)
set @User = <%User|Please enter the username to report on|GENERIC|User%>

SELECT [date], [username], [service],
MAX(CASE WHEN message LIKE '%Query Builder%' THEN STUFF(message,1,PATINDEX('%Query Builder query%',message)+19,'') END) AS ReportName,
MAX(CASE WHEN message LIKE 'Parameter FromDate%' THEN REPLACE(STUFF(message,1,PATINDEX('%Parameter FromDate%',message)+19,''),'/','-') END) AS FromDate,
MAX(CASE WHEN message LIKE 'Parameter ToDate%' THEN REPLACE(STUFF(message,1,PATINDEX('%Parameter ToDate%',message)+18,''),'/','-') END) AS ToDate,
MAX(CASE WHEN message LIKE 'Parameter RF%' THEN STUFF(message,1,PATINDEX('%Parameter RFTF02%',message)+18,'') END) AS Filter
FROM log
WHERE username = @User and service = 'reporting'
GROUP BY [date],[username],[service]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-26 : 05:44:07

Are you sure your date values doesnt have milliseconds part? ALso whats the datatype used? I feel you're using varchar as format is different from native date values format.


SELECT DATEADD(ms,-1 * DATEPART(ms,[date]),[date]), [username], [service],
MAX(CASE WHEN message LIKE '%Query Builder%' THEN STUFF(message,1,PATINDEX('%Query Builder query%',message)+19,'') END) AS ReportName,
MAX(CASE WHEN message LIKE 'Parameter FromDate%' THEN REPLACE(STUFF(message,1,PATINDEX('%Parameter FromDate%',message)+19,''),'/','-') END) AS FromDate,
MAX(CASE WHEN message LIKE 'Parameter ToDate%' THEN REPLACE(STUFF(message,1,PATINDEX('%Parameter ToDate%',message)+18,''),'/','-') END) AS ToDate,
MAX(CASE WHEN message LIKE 'Parameter RF%' THEN STUFF(message,1,PATINDEX('%Parameter RFTF02%',message)+18,'') END) AS Filter
FROM log
WHERE username = @User and service = 'reporting'
GROUP BY DATEADD(ms,-1 * DATEPART(ms,[date]),[date]), [username],[service]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kaos_king
Starting Member

13 Posts

Posted - 2013-07-26 : 06:39:03

Hmm, not sure about the milliseconds however I think you must be right as it is now displaying correctly!

I have taken a screenshot of the schema to do with the log table - Schema

I do have a few blanks though - Output

However I cannot thank you enough for your help, also nearly 50,000 posts!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-26 : 11:25:51

You're welcome
Glad that I could sort it out for you
the blanks may be because you dont have required category values for those combination.

you can filter them by using filter condition below


SELECT DATEADD(ms,-1 * DATEPART(ms,[date]),[date]), [username], [service],
MAX(CASE WHEN message LIKE '%Query Builder%' THEN STUFF(message,1,PATINDEX('%Query Builder query%',message)+19,'') END) AS ReportName,
MAX(CASE WHEN message LIKE 'Parameter FromDate%' THEN REPLACE(STUFF(message,1,PATINDEX('%Parameter FromDate%',message)+19,''),'/','-') END) AS FromDate,
MAX(CASE WHEN message LIKE 'Parameter ToDate%' THEN REPLACE(STUFF(message,1,PATINDEX('%Parameter ToDate%',message)+18,''),'/','-') END) AS ToDate,
MAX(CASE WHEN message LIKE 'Parameter RF%' THEN STUFF(message,1,PATINDEX('%Parameter RFTF02%',message)+18,'') END) AS Filter
FROM log
WHERE username = @User and service = 'reporting'
AND (message LIKE '%Query Builder%' 
OR message LIKE 'Parameter FromDate%'
OR message LIKE 'Parameter ToDate%'
OR message LIKE 'Parameter RF%'
)
GROUP BY DATEADD(ms,-1 * DATEPART(ms,[date]),[date]), [username],[service]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kaos_king
Starting Member

13 Posts

Posted - 2013-07-29 : 05:45:36

Thank you visakh, I actually figured that out however I did it the opposite way to you by excluding what I didn't want. Your way is much more economical

Thanks once again for your help. I still have more to add to it as there are other things in the log table that I want to pull back with it although now I know the method I should be able to figure it out....hopefully

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-29 : 05:49:21

No problem..
Let me know if you need any more help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources