SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Earliest Date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Oliviaf2012
Starting Member

16 Posts

Posted - 01/16/2013 :  06:50:03  Show Profile  Reply with Quote
I want to return data for customers from their first invoice only - this is the query I have but I do not know how to show only the data from the first invoice per person

SELECT aa.dispatcheddate ,AA.[Country Code]
, AA.firstname ,aa.lastname, aa.address2_fax,
aa.campaigncode, aa.campaigcodeidname,
aa.saleschannelname
FROM dbo.AllActiveAuthorised AS AA
WHERE dispatcheddate >= CAST('2008-01-01' as DATETIME)
AND dispatcheddate < CAST('2012-12-31' as DATETIME)
AND NOT EXISTS
(
SELECT TOP 1 AB.invoiceid
FROM dbo.FilteredInvoice AB INNER JOIN
dbo.FilteredInvoiceDetail ON AB.invoiceid = dbo.FilteredInvoiceDetail.invoiceid INNER JOIN
dbo.FilteredProduct ON dbo.FilteredInvoiceDetail.productid = dbo.FilteredProduct.productid
WHERE AB.contactid = AA.contactid
AND (AB.authorized2 = 1)
AND (dbo.FilteredProduct.l2g_brand = 38)

)
AND allowmail = 'Allow'
AND statuscode = '1'
AND l2g_brandname = 'V'
GROUP BY
aa.address2_fax ,[Country Code], aa.firstname , aa.invoicenumber , aa.dispatcheddate,aa.campaigncode,
aa.saleschannelname , aa.lastname , aa.campaigcodeidname
ORDER BY
[Country Code]

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/16/2013 :  07:17:50  Show Profile  Reply with Quote
How do you determine what their first invoice is. Is there a date associated with it, are invoiceids used, etc?


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

Oliviaf2012
Starting Member

16 Posts

Posted - 01/16/2013 :  07:19:19  Show Profile  Reply with Quote
Hi
aa.dispatcheddate is the date of the invoice.
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/16/2013 :  07:43:26  Show Profile  Reply with Quote
It's not clear to me where all your data lives, but I think you can adapt this technique to suit you. This will give you all the
info associated with the person's first invoice, based on dispatchdate (I'm assuming a person = ContactID)

SELECT *
FROM
(select aa.dispatcheddate ,AA.[Country Code]
, AA.firstname ,aa.lastname, aa.address2_fax,
aa.campaigncode, aa.campaigcodeidname, aa.saleschannelname
,rown = ROW_NUMBER() over(partition by contactid order by dispatchdate asc)
from dbo.AllActiveAuthorised
) t1
WHERE t1.rown = 1

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

Oliviaf2012
Starting Member

16 Posts

Posted - 01/18/2013 :  07:07:52  Show Profile  Reply with Quote
Hi Jimf
Thats that worked with a little tweaking.

Your a gent.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000