can anyone make sense of this query?

[ingernet]

Hi all,

For those of you who remember me from last week's post, I'm STILL trying to figure out what the heck my predecessor was thinking when he wrote this SQL query, among a variety of others:

CREATE VIEW dbo.vTime_ElapsedByTask
AS
SELECT     
[BEGIN].[id], [BEGIN].user_id,  [BEGIN].[Date],  [BEGIN].Task_Code, [BEGIN].Project_task,  [BEGIN].task_paid,  [BEGIN].Project_Number,  [BEGIN].LoginTime AS START,  [STOP] = CASE  WHEN MIN([END].LoginTime) IS NULL  THEN getdate() ELSE MIN([END].LoginTime)  END,  [TimeOnTask] = CASE  WHEN MIN([END].LoginTime) IS NULL  THEN CONVERT(decimal(9, 2), DATEDIFF(s, [BEGIN].LoginTime, getdate()))    / 60 / 60  ELSE CONVERT(decimal(9, 2), DATEDIFF(s, [BEGIN].LoginTime, MIN([END].LoginTime))) / 60 / 60  END 

FROM         dbo.vTime_Entries [END]  

RIGHT OUTER JOIN dbo.vTime_Entries [BEGIN] ON [END].user_id = [BEGIN].user_id  AND [END].LoginTime >= [BEGIN].LoginTime AND [BEGIN].[ID] <> [END].[ID]

GROUP BY [BEGIN].[id], [BEGIN].user_id, [BEGIN].[Date], [BEGIN].LoginTime, [BEGIN].Task_Code, [BEGIN].Project_task, [BEGIN].task_paid, 
                      [BEGIN].Project_Number, [BEGIN].[Date]

Okay, A: BEGIN, END, START AND STOP are all reserved words. I'm unfamiliar with the practice of using them as...table names? BEGIN, incidentally, is not a table name listed in vTime_Entries, the view that this view queries.

and B: How is it that you can have 23 BEGINs and, like, 7 ENDs?

and C: Any idea what kind of WYSIWYG this guy used to get this crazy code? When I got it, it was written in paragraph form, and no DBA *I * know writes SQL that complex in paragraph form, which leads me to believe that he used a GUI of some sort. Is this something Query Analyzer would do? Seems weird.

I'm not a total ignoramus...just a little out of my depth here. Ideas, anyone? Thanks!

Inger

 

[Trusts]

Can you just open it in view design mode? Or paste it into Query Analyzer, with the correct database selected in the drop down near the top. Try to parse it (the green arrow). What kind of error do you get?

 

[SQLDenis]

BEGIN and END are just aliases, this query is the same

CREATE VIEW dbo.vTime_ElapsedByTask
AS
SELECT     
b.[id], b.user_id,  b.[Date],  b.Task_Code, b.Project_task,  b.task_paid,  b.Project_Number,  b.LoginTime AS START,  [STOP] = CASE  WHEN MIN(e.LoginTime) IS NULL  THEN getdate() ELSE MIN(e.LoginTime)  END,  [TimeOnTask] = CASE  WHEN MIN(e.LoginTime) IS NULL  THEN CONVERT(decimal(9, 2), DATEDIFF(s, b.LoginTime, getdate()))    / 60 / 60  ELSE CONVERT(decimal(9, 2), DATEDIFF(s, b.LoginTime, MIN(e.LoginTime))) / 60 / 60  END 
FROM         dbo.vTime_Entries e  
RIGHT OUTER JOIN dbo.vTime_Entries b ON e.user_id = b.user_id  AND e.LoginTime >= b.LoginTime AND b.[ID] <> e.[ID]
GROUP BY b.[id], b.user_id, b.[Date], b.LoginTime, b.Task_Code, b.Project_task, b.task_paid, 
                      b.Project_Number, b.[Date]

You can not open a view that has CASE staements in design view use query analyzer instead


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ingernet]

I did a syntax check in Query Analyzer and got no errors. Then I parsed it and it took 13 seconds to run. And that's just the SQL - if you include the post-SQL processing (with ColdFusion), the whole page takes anywhere from 23 to 35 seconds to load. Oy, vey!

The thing is, this script is doing what I think it's supposed to be doing, but the application relies on views that are nested 7 deep. That's right, 7 nested views. There has to be a faster way. I'm just trying to figure out what BEGIN means in this context, so that I can see what data he's really after, so that I can get to that data in a more efficient way.

 

[ingernet]

Thanks to Trusts and SQLDenis for following up on this. It just seems like bad form to use a reserved word as an alias, but now that you point it out, that alias stands out like a beacon. Thanks again for your help!

Inger

 

[r937]

I'm just trying to figure out what BEGIN means in this context

it's just a table alias



here's your query --

CREATE VIEW dbo.vTime_ElapsedByTask
AS
SELECT [BEGIN].[id]
     , [BEGIN].user_id
     , [BEGIN].[Date]
     , [BEGIN].Task_Code
     , [BEGIN].Project_task
     , [BEGIN].task_paid
     , [BEGIN].Project_Number
     , [BEGIN].LoginTime AS START
     , [STOP] = CASE  
            WHEN MIN([END].LoginTime) IS NULL  
            THEN getdate() 
            ELSE MIN([END].LoginTime)
               END
     ,   [TimeOnTask] = CASE  
            WHEN MIN([END].LoginTime) IS NULL  
            THEN CONVERT(decimal(9,2)
                 ,DATEDIFF(s
                   ,[BEGIN].LoginTime
                   ,getdate())
                        ) / 60 / 60 
            ELSE CONVERT(decimal(9,2)
                 ,DATEDIFF(s
                   ,[BEGIN].LoginTime
                   ,MIN([END].LoginTime))
                        ) / 60 / 60 
               END 
  FROM dbo.vTime_Entries [END]  
RIGHT OUTER 
  JOIN dbo.vTime_Entries [BEGIN] 
    ON [END].user_id = [BEGIN].user_id  
   AND [END].LoginTime >= [BEGIN].LoginTime 
   AND [BEGIN].[ID] <> [END].[ID]
GROUP 
    BY [BEGIN].[id]
     , [BEGIN].user_id
     , [BEGIN].[Date]
     , [BEGIN].LoginTime
     , [BEGIN].Task_Code
     , [BEGIN].Project_task
     , [BEGIN].task_paid
     , [BEGIN].Project_Number
     , [BEGIN].[Date]

and here it is transcribed as a LEFT OUTER JOIN with different aliases --

CREATE VIEW dbo.vTime_ElapsedByTask
AS
SELECT start.id
     , start.user_id
     , start.Date
     , start.Task_Code
     , start.Project_task
     , start.task_paid
     , start.Project_Number
     , start.LoginTime AS start_time
     , case when min(finish.LoginTime) is null  
            then getdate() 
            else min(finish.LoginTime)
        end as finish_time
     , case when min(finish.LoginTime) is null  
            then convert(decimal(9,2)
                 ,datediff(s
                   ,start.LoginTime
                   ,getdate())
                        ) / 60 / 60 
            else convert(decimal(9,2)
                 ,datediff(s
                   ,start.LoginTime
                   ,min(finish.LoginTime))
                        ) / 60 / 60 
        end as [TimeOnTask] 
  from dbo.vTime_Entries as start 
left outer
  join dbo.vTime_Entries as finish
    on finish.user_id = start.user_id  
   and finish.LoginTime >= start.LoginTime 
   and start.id <> finish.id
group 
    by start.id
     , start.user_id
     , start.Date
     , start.LoginTime
     , start.Task_Code
     , start.Project_task
     , start.task_paid
     , start.Project_Number
     , start.Date

i find it easier to understand what's happening if i do my joins from left to right, start before finish

;-)

r937.com | rudy.ca