get 5 latest invoices (based on InvoiceDate) for each customer.

I have two tables:
- CustomerID(CustID,CustName)
- Invoice(CustID,InvoiceID,InvoiceDate)
How can i get a table that has 5 latest invoices (based on InvoiceDate) for each customer.
Example: The result has format like this:
CustID CustName InvoiceID InvoiceDate
0001 Ng V A 126121 20061028
0001 Ng V A 126128 20061026
0001 Ng V A 126130 20061022
0001 Ng V A 126132 20061019
0001 Ng V A 126140 20061018
0003 Ng V B 126050 20061024
0003 Ng V B 126046 20061016
0003 Ng V B 126038 20061012
0003 Ng V B 126012 20061010
Please help me to use query to solve this problem. Many thanks for your kind support.
Best regardscan't you just do the following

SELECT C.CustId, C.CustName, I.InvoiceId, I.InvoiceDate
FROM CustomerID C LEFT JOIN Invoice I on C.CustId = I.CustId
WHERE InvoivceDate in (Select Top 5 InvoiceDate from Invoice Order By InvoiceDate DESC)|||

Quote:

Originally Posted by Taftheman

can't you just do the following

SELECT C.CustId, C.CustName, I.InvoiceId, I.InvoiceDate
FROM CustomerID C LEFT JOIN Invoice I on C.CustId = I.CustId
WHERE InvoivceDate in (Select Top 5 InvoiceDate from Invoice Order By InvoiceDate DESC)

Thanks for your responding. But I think you misunderstand my idea. I want to retreive a table which contains a customer list with their corresponding latest invoice.

I'm appreciate for your helping.|||

Quote:

Originally Posted by thanhphong122

Thanks for your responding. But I think you misunderstand my idea. I want to retreive a table which contains a customer list with their corresponding latest invoice.

I'm appreciate for your helping.

Im not sure but try just joining the two tables.

SELECT C.CustId, C.CustName, I.InvoiceId, I.InvoiceDate
FROM CustomerID C LEFT JOIN Invoice I on C.CustId = I.CustId
WHERE InvoivceDate in (Select Top 5 I.InvoiceDate from Invoice I Left Join CustomerID C I.CustId = C.CustID Order By InvoiceDate DESC)

If that doesn't work then put custid in both where clauses|||You can use this, it gets all the top 5 dates from the customer id of QUICK, change to suit your needs... You can even run it on query analyzer as it uses the northwind database

Select C.CustomerId, C.ContactName, O.OrderDate
from Customers C left join Orders O on C.CustomerId = O.CustomerId
Where C.CustomerId = 'QUICK' and O.OrderDate in (Select top 5 OrderDate from Orders Where CustomerId = 'QUICK' order by OrderDate DESC)

Let us know if it works|||The query below runs very good.

SELECT c.Customer, c.Name1, Invoice,i.ILDATE
FROM Customer C join Invoice I on (c.Customer = i.MKH)
WHERE (i.Invoice IN (SELECT TOP 5 Invoice FROM Invoice j WHERE (c.Customer = j.MKH) ORDER BY j.ILDate DESC))
ORDER BY c.Customer, i.ILDate DESC

Thanks for your help.|||

Quote:

Originally Posted by thanhphong122

The query below runs very good.

SELECT c.Customer, c.Name1, Invoice,i.ILDATE
FROM Customer C join Invoice I on (c.Customer = i.MKH)
WHERE (i.Invoice IN (SELECT TOP 5 Invoice FROM Invoice j WHERE (c.Customer = j.MKH) ORDER BY j.ILDate DESC))
ORDER BY c.Customer, i.ILDate DESC

Thanks for your help.

would this not be simpler?

SELECT DISTINCT TOP 5 c.Customer, c.Name1, i.ILDATE
FROM Customer c
INNER JOIN Invoice i
ON c.Customer = i.MKH
ORDER BY i.ILDATE|||

Quote:

Originally Posted by willakawill

would this not be simpler?

SELECT DISTINCT TOP 5 c.Customer, c.Name1, i.ILDATE
FROM Customer c
INNER JOIN Invoice i
ON c.Customer = i.MKH
ORDER BY i.ILDATE

Oops!

ORDER BY i.ILDATE DESC

get @@trancount for all users/ connections URGENT

Hi all,

Is there any way to get the @.@.trancount for a connection from outside the connection?

The reason i'm asking is that a customer just lost a days work, and there is nothing in any table from a certain time onward. One theoy is that a backup was restored, but we checked and that is not the case.

So another theory is that a certain sproc began a transaction, but never finished because of an error. (We had some strange timeouts as well, so this is quite plausible.)

So the question: Can i get a list of current connections with their trancount? I could just run this to see if a certain connection would never get back to zero to check the transaction theory.

Thanks in advance,

Gert-Jan

In 2005, you can use this query to see that information.

select der.session_id, der.wait_type, der.wait_time,
der.status as requestStatus,
des.login_name,
cast(db_name(der.database_id) as varchar(30)) as databaseName,
des.program_name,

der.command as commandType,

execText.text as objectText,
case when der.statement_end_offset = -1 then '--see objectText--'
else SUBSTRING(execText.text, der.statement_start_offset/2,
(der.statement_end_offset - der.statement_start_offset)/2)
end AS currentExecutingCommand,

der.open_transaction_count
from sys.dm_exec_sessions des
join sys.dm_exec_requests as der
on der.session_id = des.session_id
cross apply sys.dm_exec_sql_text(der.sql_handle) as execText
where des.session_id <> @.@.spid --eliminate the current connection

|||The DB is on 2K, do you have this script for 2K?|||

Yeah, it is in sysprocesses. The column is open_tran

select spid, open_tran

from master..sysprocesses

|||

Louis,

Thanks for the very quick response, i'll get back to you if you saved the day.

Regards Gert-Jan

|||

Hi Gert-Jan van der Kamp,

> So another theory is that a certain sproc began a transaction, but never finished because of an error.

> (We had some strange timeouts as well, so this is quite plausible.)

I think you are looking for open transactions. It that case, the "select" statement provided by Louis (hope we can have your new book about DMVs and DMFs soon), will not help you much because that session will not have a match in sys.dm_exec_requests. You can find an example in BOL, under the topic about "sys.dm_exec_sessions".

This is from BOL.

SELECT s.* FROM sys.dm_exec_sessions AS s WHERE EXISTS ( SELECT * FROM sys.dm_tran_session_transactions AS t WHERE t.session_id = s.session_id ) AND NOT EXISTS ( SELECT * FROM sys.dm_exec_requests AS r WHERE r.session_id = s.session_id );

Forgot to mention that in SS 2000 your choice is to use "dbcc opentran".AMB

sql

generation of sql for an alter column etc

Hi.

I have a database I need to supply something (I'm assuming a t-sql script..
maybe something else is better) to update customer tables with.
The operations include mostly changing varchar lengths, though a couple of
columns were renamed.

I'd like to maybe figure out how to get Enterprise Manager or Query Analyzer
to generate the scripts.

I can't just send alter table scripts because I'm involving all sorts of
constraints that have to be disabled/or dropped, the alter made, then have
them enabled/ or re-created.

Basically I'm hoping to get the tools to do the rather large amount of work
for me. I'm targetting sql server 2000.

Can someone make a knowledgeable suggestion?

Regards
Jeff KishJeff Kish wrote:
> Hi.
> I have a database I need to supply something (I'm assuming a t-sql script..
> maybe something else is better) to update customer tables with.
> The operations include mostly changing varchar lengths, though a couple of
> columns were renamed.

Not a good idea IMHO although you can use sp_rename.

> I'd like to maybe figure out how to get Enterprise Manager or Query Analyzer
> to generate the scripts.
> I can't just send alter table scripts because I'm involving all sorts of
> constraints that have to be disabled/or dropped, the alter made, then have
> them enabled/ or re-created.

Then generate the SQL for the target state and insert drops yourself.

> Basically I'm hoping to get the tools to do the rather large amount of work
> for me. I'm targetting sql server 2000.
> Can someone make a knowledgeable suggestion?

I don't think you will be able to get this out of EM - at least not
directly. It would basically mean to trace your operations and generate
SQL from that. I don't think EM will do that for such a complex set of
operations. You'll have to do some manual work.

Kind regards

robert|||Jeff Kish (jeff.kish@.mro.com) writes:
> I have a database I need to supply something (I'm assuming a t-sql
> script.. maybe something else is better) to update customer tables with.
> The operations include mostly changing varchar lengths, though a couple
> of columns were renamed.
> I'd like to maybe figure out how to get Enterprise Manager or Query
> Analyzer to generate the scripts.
> I can't just send alter table scripts because I'm involving all sorts of
> constraints that have to be disabled/or dropped, the alter made, then have
> them enabled/ or re-created.
> Basically I'm hoping to get the tools to do the rather large amount of
> work for me. I'm targetting sql server 2000.

Composing change scripts for production environments is not a task to
take lightly. Particularly not if you have to apply them while the system
is operating. (If the system closes for business, you may be able to repair
a disaster by restorin a backup.)

It requires good understanding of what can go wrong, and how to prevent
that. For instance, if you need to drop constraints to alter a column,
you should probably wrap that in a transaction, so you don't end up with
losing the constraint.

At the same time, ALTER operations that require changes to the physical
data pages, can take a huge toll on the transaction log, causing it to
grow rapidly. (Changing varchar lengths should be metadata so that should
be safe.)

You can use Enterprise Manager to have it to generate change scripts.
However, there are several flaw in those scripts, and you need to review
them carefully, and also make several changes to them. For instance, the
transaction scope in those scripts are wacko.

What may prove to be a show-stopper is that EM works with SQL 6.5 as its
target DB (same in Mgmt Studio in SQL 2005, by the way). 6.5 did not
have ALTER TABLE ALTER COLUMN, so I would guess that it implements the
update as create new table and copy data over. Which sometimes is the right
thing, but not when ALTER TABLE ALTER COLUMN is only a metadata change.

There are other tools on the market. Red Gate's SQL Compare get a lot
of positive acclaim, but I have not used it myself.

One potential problem is that you don't know the name of the constraints,
because they were added without a name, so all there is a system-generated
name. In this case, you need to retrieve the name, and then run DROP
CONSTRAINT dynamically. I would suggest that you restore the constraints
with a given name.

Speaking of constraints, make sure that you re-add them WITH CHECK. The
script from EM uses WITH NOCHEK, which means that they are not checked. This
is a lot faster, but it also means that the optimizer will neglect them,
which can have expensive consequences.

Finally, before you run in production, test on a copy of production!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Fri, 24 Mar 2006 13:45:19 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>Jeff Kish (jeff.kish@.mro.com) writes:
>> I have a database I need to supply something (I'm assuming a t-sql
<snip>
>Composing change scripts for production environments is not a task to
>take lightly. Particularly not if you have to apply them while the system
>is operating. (If the system closes for business, you may be able to repair
>a disaster by restorin a backup.)
>It requires good understanding of what can go wrong, and how to prevent
>that. For instance, if you need to drop constraints to alter a column,
>you should probably wrap that in a transaction, so you don't end up with
>losing the constraint.
ahh.. I had no idea that transactions could wrap/rollback ddl. I don't think
that is the case in Oracle.
>At the same time, ALTER operations that require changes to the physical
>data pages, can take a huge toll on the transaction log, causing it to
>grow rapidly. (Changing varchar lengths should be metadata so that should
>be safe.)
>You can use Enterprise Manager to have it to generate change scripts.
>However, there are several flaw in those scripts, and you need to review
>them carefully, and also make several changes to them. For instance, the
>transaction scope in those scripts are wacko.
Can you tell me how? I'm having some problem seeing how to get the equivalent
alter table etc scripts out of EM. I looked, honest. I'll even look some more.

>What may prove to be a show-stopper is that EM works with SQL 6.5 as its
>target DB (same in Mgmt Studio in SQL 2005, by the way). 6.5 did not
>have ALTER TABLE ALTER COLUMN, so I would guess that it implements the
>update as create new table and copy data over. Which sometimes is the right
>thing, but not when ALTER TABLE ALTER COLUMN is only a metadata change.
target is only sql server 2000 right now.
>There are other tools on the market. Red Gate's SQL Compare get a lot
>of positive acclaim, but I have not used it myself.
>One potential problem is that you don't know the name of the constraints,
>because they were added without a name, so all there is a system-generated
>name. In this case, you need to retrieve the name, and then run DROP
>CONSTRAINT dynamically. I would suggest that you restore the constraints
>with a given name.
mmm not sure I understand.. they are originally added specifically. can I just
disable them or do I need to drop them?

>Speaking of constraints, make sure that you re-add them WITH CHECK. The
>script from EM uses WITH NOCHEK, which means that they are not checked. This
>is a lot faster, but it also means that the optimizer will neglect them,
>which can have expensive consequences.
>Finally, before you run in production, test on a copy of production!
of course! the scars I have should remind me of that. :> )

thanks so much.
Jeff Kish|||Jeff Kish (jeff.kish@.mro.com) writes:
>>You can use Enterprise Manager to have it to generate change scripts.
>>However, there are several flaw in those scripts, and you need to review
>>them carefully, and also make several changes to them. For instance, the
>>transaction scope in those scripts are wacko.
>>
> Can you tell me how? I'm having some problem seeing how to get the
> equivalent alter table etc scripts out of EM. I looked, honest. I'll
> even look some more.

Right-click table and select Modify Table. As I said, it is not likely
it will generate ALTER TABLE commands, those you will have to write
yourself. But at least you will get some code to recreate constraints.
Just be sure to change WITH NOCHECK to WITH CHECK.

>>One potential problem is that you don't know the name of the constraints,
>>because they were added without a name, so all there is a system-generated
>>name. In this case, you need to retrieve the name, and then run DROP
>>CONSTRAINT dynamically. I would suggest that you restore the constraints
>>with a given name.
>>
> mmm not sure I understand.. they are originally added specifically. can
> I just disable them or do I need to drop them?

The error message from ALTER TABLE makes it clear that you have to
drop the constraint. Keep in mind that a FK column must match the
PK column it refers to, so if you change a PK/FK pair, you need to
drop constraints for both columns before changing.

Yes, it follows that from what I say that you should mainly generate
the script manually. This may seem like a crazy idea, but since it's
so important that you understand what the script does, it can be
dangerous to leave that in the hands of a tool.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Generating a personalised report for every vendor and mailing out the same

I have the following reporting scenario.
I have a table which contains a list of customers/vendors and each
record in this table as the customer/vendor number, name and email
address of the vendor and also the record as the value of the business
done by the vendor/customer for this month, year to date, last
year,etc our business wants us to generate a report for each
customer/vendor from this table and email the resulting output in a
.pdf report to the email address on this record. It is almost like the
account statement for each customer or vendor for that month.
Please clarify how I can implement this solution with DTS. I can see a
stored procedure can go through all the customers but how do I
generate the statement for each customer into a .pdf output and then
email that file to that vendor/customers email address from SQL+DTS
Thanks
KarenKaren
Start with www.sqldts.com
declare @.sql varchar(255)
set @.sql='bcp "select * FROM northwind.dbo.orders WHERE employeeid = 1"
queryout "c:\temp\1.csv" -c -T -SSERVER -Usa -Ppass'
exec master..xp_cmdshell @.sql
"Karen Middleton" <karenmiddleol@.yahoo.com> wrote in message
news:a5fd468a.0504111747.90b8491@.posting.google.com...
> I have the following reporting scenario.
> I have a table which contains a list of customers/vendors and each
> record in this table as the customer/vendor number, name and email
> address of the vendor and also the record as the value of the business
> done by the vendor/customer for this month, year to date, last
> year,etc our business wants us to generate a report for each
> customer/vendor from this table and email the resulting output in a
> .pdf report to the email address on this record. It is almost like the
> account statement for each customer or vendor for that month.
> Please clarify how I can implement this solution with DTS. I can see a
> stored procedure can go through all the customers but how do I
> generate the statement for each customer into a .pdf output and then
> email that file to that vendor/customers email address from SQL+DTS
> Thanks
> Karen