Earliest Date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Earliest Date

Author

Topic

Oliviaf2012
Starting Member

16 Posts

Posted - 2013-01-16 : 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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-16 : 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 - 2013-01-16 : 07:19:19

Hi
aa.dispatcheddate is the date of the invoice.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-16 : 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 - 2013-01-18 : 07:07:52

Hi Jimf
Thats that worked with a little tweaking.

Your a gent.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources