1ST TIME CUSTOMER...

Question

1ST TIME CUSTOMER...

b_boy

SSCertifiable

Points: 5145
More actions
August 7, 2008 at 10:49 am

#195618

Hello
I am using the following query:
ELECT DISTINCT T_OrderHeader.OrderDate, T_OrderHeader.OrderID, T_OrderHeader.CustomerID, T_OrderHeader.ShipTitle, T_OrderHeader.ShipForename,
T_OrderHeader.ShipSurname, T_ReturnsReasons.ReasonDesc, T_Returns.ReturnTotal
FROM T_ReturnTypes INNER JOIN
T_Returns INNER JOIN
T_ReturnLines ON T_Returns.ReturnID = T_ReturnLines.ReturnId INNER JOIN
T_ReturnsReasons ON T_ReturnLines.ReasonID = T_ReturnsReasons.ReasonID ON
T_ReturnTypes.ReturnTypeID = T_Returns.ReturnTypeID INNER JOIN
T_OrderHeader ON T_Returns.OrderID = T_OrderHeader.OrderID
WHERE T_Returns.CustomerID < 1
and I want to pull out the orders returned by a customers for the 1st time, can anyone help?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

By 1st time, do you mean the order with the lowest order date?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

b_boy SSCertifiable Points: 5145 More actions · Answer 2

I used the following query:

SELECT DISTINCT T_OrderHeader.OrderDate, T_OrderHeader.OrderID, T_OrderHeader.CustomerID, T_OrderHeader.ShipTitle, T_OrderHeader.ShipForename,

T_OrderHeader.ShipSurname, T_ReturnsReasons.ReasonDesc, T_Returns.ReturnTotal

FROM T_ReturnTypes INNER JOIN

T_Returns INNER JOIN

T_ReturnLines ON T_Returns.ReturnID = T_ReturnLines.ReturnId INNER JOIN

T_ReturnsReasons ON T_ReturnLines.ReasonID = T_ReturnsReasons.ReasonID ON

T_ReturnTypes.ReturnTypeID = T_Returns.ReturnTypeID INNER JOIN

T_OrderHeader ON T_Returns.OrderID = T_OrderHeader.OrderID

GROUP BY T_OrderHeader.OrderDate, T_OrderHeader.OrderID, T_OrderHeader.CustomerID, T_OrderHeader.ShipTitle, T_OrderHeader.ShipForename,

T_OrderHeader.ShipSurname, T_ReturnsReasons.ReasonDesc, T_Returns.ReturnTotal

HAVING COUNT (T_Returns.CustomerID) = 1

--

I want to know the customers who returned items for the 1st time only

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Reformatted your code some so I could read it better, but really can't help you with the query as there really isn't enough information.

It would help if you provided the ddl for the tables (create table statements), sample data (in the form of insert statements we could copy and paste into SSMS to load the tables), expected results based on the sample data. You have already provided us with your current code, so that isn't needed.

Reformatted code:

SELECT DISTINCT

T_OrderHeader.OrderDate,

T_OrderHeader.OrderID,

T_OrderHeader.CustomerID,

T_OrderHeader.ShipTitle,

T_OrderHeader.ShipForename,

T_OrderHeader.ShipSurname,

T_ReturnsReasons.ReasonDesc,

T_Returns.ReturnTotal

FROM

T_ReturnTypes

INNER JOIN T_Returns

ON T_ReturnTypes.ReturnTypeID = T_Returns.ReturnTypeID

INNER JOIN T_ReturnLines

ON T_Returns.ReturnID = T_ReturnLines.ReturnId

INNER JOIN T_ReturnsReasons

ON T_ReturnLines.ReasonID = T_ReturnsReasons.ReasonID

INNER JOIN T_OrderHeader

ON T_Returns.OrderID = T_OrderHeader.OrderID

WHERE

T_Returns.CustomerID < 1

Edit: Also noted that your query just changed between posts. Which query are you trying to fix?

😎

hill4m SSC Veteran Points: 218 More actions · Answer 4

If I am understanding the problem correctly, the second query you posted should work. Are you saying that is the solution you came up with? If it doesn't work, what is it doing that is incorrect?

b_boy SSCertifiable Points: 5145 More actions · Answer 5

Hello All

I used the following query eventuallu without the DISTINCT keyword and it worked just fine:

SELECT CONVERT (NVARCHAR (20),T_OrderHeader.OrderDate,106) AS OrderDate, T_OrderHeader.OrderID, T_OrderHeader.CustomerID, T_OrderHeader.ShipTitle, T_OrderHeader.ShipForename,

T_OrderHeader.ShipSurname, T_ReturnsReasons.ReasonDesc, T_Returns.ReturnTotal

FROM T_ReturnTypes INNER JOIN

T_Returns INNER JOIN

T_ReturnLines ON T_Returns.ReturnID = T_ReturnLines.ReturnId INNER JOIN

T_ReturnsReasons ON T_ReturnLines.ReasonID = T_ReturnsReasons.ReasonID ON

T_ReturnTypes.ReturnTypeID = T_Returns.ReturnTypeID INNER JOIN

T_OrderHeader ON T_Returns.OrderID = T_OrderHeader.OrderID

GROUP BY T_OrderHeader.OrderDate, T_OrderHeader.OrderID, T_OrderHeader.CustomerID, T_OrderHeader.ShipTitle, T_OrderHeader.ShipForename,

T_OrderHeader.ShipSurname, T_ReturnsReasons.ReasonDesc, T_Returns.ReturnTotal

HAVING COUNT (T_Returns.CustomerID)< 2

ORDER BY T_OrderHeader.OrderDate DESC