INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Report with Subreport Min Date Live Tables

Report with Subreport Min Date Live Tables

(OP)
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.

RE: Report with Subreport Min Date Live Tables

try this query.

CODE

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

RE: Report with Subreport Min Date Live Tables

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

RE: Report with Subreport Min Date Live Tables

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.

RE: Report with Subreport Min Date Live Tables

My mistake.

CODE

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

RE: Report with Subreport Min Date Live Tables

(OP)
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?



RE: Report with Subreport Min Date Live Tables

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....

CODE

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...

CODE

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.

CODE

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' 

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

RE: Report with Subreport Min Date Live Tables

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

RE: Report with Subreport Min Date Live Tables

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: Select [Log].[key] from Log as L. 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.

RE: Report with Subreport Min Date Live Tables

(OP)
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.

RE: Report with Subreport Min Date Live Tables

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close