Report with Subreport Min Date Live Tables 1

[codrutza]

I have to develop a report. I have:
The main report based on:

USE [db]
GO
/****** Object: StoredProcedure [dbo].[SCRIPT_1] Script Date: 14/07/2016 11:53:22 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[SCRIPT_1]

AS

set nocount on;
SELECT
Headers.Date AS InvDate,
Headers.PostedDate,
Headers.OurReference OurRefTr,
Headers.Userid UserTr,
Headers.Number

FROM
Headers WITH (READUNCOMMITTED)
where
Headers.Ledger='Purchases'
and Headers.OurReference Not Like '%CT'


And a subreport:

USE [DB]
GO
/****** Object: StoredProcedure [dbo].[SCRIPT_2] Script Date: 14/07/2016 12:00:53 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[SCRIPT_2]
@LogKey char(13)
AS

set nocount on;
SELECT
min([Log].[Date]) as MinLogDate

FROM
[Log] WITH (READUNCOMMITTED) WHERE @LogKey=[Log].[Key]

And I link the Headers.Number with the [Log].[Key]


I don’t know to write complicated stored procedures. The report is extremely slow, I assume because the tables Headers and Log are updated permanently with all the information that users input. Table Log in special is very big and live. Is there another way to write the scripts? Or a stored procedure to speed the report? Or is a temporary table any use?

I use SAP Crystal Reports and Microsoft SQL Server Management Studio.

 

[gmmastros]

try this query.

SELECT  Headers.Date AS InvDate, 
        Headers.PostedDate, 
        Headers.OurReference OurRefTr, 
        Headers.Userid UserTr,
        Headers.Number,
		MinDate.MinLogDate
FROM    Headers WITH (READUNCOMMITTED) 
        Left Join (
          SELECT [Log].[key],
		         min([Log].[Date]) as MinLogDate
          FROM   [Log] WITH (READUNCOMMITTED) 
		  Group By [Log].[Key]
		  ) As MinDate
		  On Headers.Number = [Log].[Key]
where   Headers.Ledger='Purchases'
        and Headers.OurReference Not Like '%CT'

If this works for you, let me know and I will explain it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[codrutza]

Thank you George for reply. I get The multi-part identifier "Log.Key" could not be bound.

 

[Olaf Doschke]

For the column name Key you have to fill in the key column of the log table. If there is no unique value in any Log column, then you're not able to use such a subquery, so you'd better add a key column. Every table, even Log tables, should have a primary key column, they are essential in any relational database.

Bye, Olaf.

 

[gmmastros]

My mistake.

SELECT  Headers.Date AS InvDate, 
        Headers.PostedDate, 
        Headers.OurReference OurRefTr, 
        Headers.Userid UserTr,
        Headers.Number,
        MinDate.MinLogDate
FROM    Headers WITH (READUNCOMMITTED) 
        Left Join (
          SELECT [Log].[key],
		         min([Log].[Date]) as MinLogDate
          FROM   [Log] WITH (READUNCOMMITTED) 
		  Group By [Log].[Key]
		  ) As MinDate
		  On Headers.Number = [!]MinDate.[Key][/!]
where   Headers.Ledger='Purchases'
        and Headers.OurReference Not Like '%CT'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[codrutza]

Thank you, Olaf. I can't modify tables but I had a look in SQL Server and I can't see a primary key PK on Log table.
George, I put instead of the join:
FROM Headers WITH (READUNCOMMITTED)
Left Join (
SELECT [Log].[key],
min([Log].[Date]) as MinLogDate
FROM [Log] WITH (READUNCOMMITTED)
Group By [Log].[Key]
) As MinDate
On Headers.Number = [Log].[Key]

the join

Headers WITH (READUNCOMMITTED)
Left Join (
SELECT [Log].[key],
min([Log].[Date]) as MinLogDate
FROM [Log] WITH (READUNCOMMITTED)
Group By [Log].[Key]
) As MinDate
On Headers.Number = MinDate.[Key]

and it seems this works, but I'm not sure why. Can you explain, please?

 

[gmmastros]

My thought process was like this....

You execute the first query just once. Then, for each row in the first query, you execute the second query. To truly speed up the process, getting all the data from the database at once (without looping through the first query) would result in the best performance.

The technique I use is called a derived table. Basically, I combine the 2 queries in such a way that it returns exactly the data you want. The only thing you really need from the second query is the Min(Date). In order to join this to the first query, we'll need to know the Key as well. So, I created this query....

          SELECT [Log].[key],
		         min([Log].[Date]) as MinLogDate
          FROM   [Log] WITH (READUNCOMMITTED) 
		  Group By [Log].[Key]

Think of it this way... If there was a real (permanent) table that had the max date and key, it would be super simple to join to that table to get the results. That query would look something like this...

SELECT  Headers.Date AS InvDate, 
        Headers.PostedDate, 
        Headers.OurReference OurRefTr, 
        Headers.Userid UserTr,
        Headers.Number,
        [!]ImaginaryTable[/!].MinLogDate
FROM    Headers WITH (READUNCOMMITTED) 
        Left Join [!]ImaginaryTable[/!]
		  On Headers.Number = [!]ImaginaryTable[/!].[Key]
where   Headers.Ledger='Purchases'
        and Headers.OurReference Not Like '%CT'

Since the imaginary table doesn't exist, we need to swap it out with the query.

SELECT  Headers.Date AS InvDate, 
        Headers.PostedDate, 
        Headers.OurReference OurRefTr, 
        Headers.Userid UserTr,
        Headers.Number,
        [blue]MinDate[/blue].MinLogDate
FROM    Headers WITH (READUNCOMMITTED) 
        Left Join [!]([/!]
          SELECT [Log].[key],
		         min([Log].[Date]) as MinLogDate
          FROM   [Log] WITH (READUNCOMMITTED) 
		  Group By [Log].[Key]
		  [!])[/!] As [blue]MinDate[/blue]
		  On Headers.Number = [blue]MinDate[/blue].[Key]
where   Headers.Ledger='Purchases'
        and Headers.OurReference Not Like '%CT'

There are a couple things you need to do for this to work. You need to put parenthesis around the query, you need to give the query an alias, and you need to reference the alias outside the paranthesis (the join and the column list).

Does this make sense? If there's anything about this that is confusing, let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[codrutza]

Thank you so much, George. It's working very fast as well.

 

[Olaf Doschke]

Hm, makes me wonder about the error, as the identifier [Log].[key] is within the subquery, still. So the field itself exists, of course, it's not available outside the brackets, as the imaginary table is called MinDate and not Log. That's the explanation of the error, but the error description should be more precise, specifying "near ON Headers.Numer = [Log].[key]", I could have spotted the problem then, too, but it sounded like there is no column named [key] at all and you have to find one, eg ID, or LogID.

So the [key] field exists, it may not be set as primary key, but that's not the most important thing. The important thing is it's unique, each [key] value only exists once in the Log table. Then it could be a primary key and to make it a primary key would ensure that quality. It's not your job or task to do it, but just to clarify that aspect. In the first place, you could have looked whether there is a column named [key] in the [Log] table. And as there is, the problem would be about the usage of this field in another place, in the join condition.

Why it's not visible there? Well, to explain that let's go with a simpler example: [tt]Select [Log].[key] from Log as L[/tt]. Since this gives Log the alias name L, Log is not a known name anymore. The same is happening due to "As MinDate", the name of the imaginary table is MinDate and its [key] field is the value taken from [Log].[key], also named [key], but now MinDate.[Key].

Bye, Olaf.

 

[codrutza]

Hi Olaf. I don't know if I understand, but I put brackets, because I think Log, Key are reserved words, anyway appear magenta if I don't put the brackets. I don't remember the rest of error.

 

[Olaf Doschke]

I didn't mean the square brackets, but the brackets around the whole subquery. Never mind. At some point you'll come to understand alias names and their scope.

Bye, Olaf.