| Author |
 Topic  |
|
|
Oliviaf2012
Starting Member
16 Posts |
 Posted - 01/16/2013 : 06:50:03
|
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
2868 Posts |
Posted - 01/16/2013 : 07:17:50
|
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 |
 |
|
|
Oliviaf2012
Starting Member
16 Posts |
Posted - 01/16/2013 : 07:19:19
|
Hi
aa.dispatcheddate is the date of the invoice. |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 01/16/2013 : 07:43:26
|
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 |
|
|
|
Oliviaf2012
Starting Member
16 Posts |
Posted - 01/18/2013 : 07:07:52
|
Hi Jimf
Thats that worked with a little tweaking.
Your a gent.
|
|
|
| |
Topic |
|
|
|
Earliest Date
