May 28, 2009 at 12:40 pm
Hello - I am SO GLAD I found this site.... I'm pulling my hair out trying to figure out how to create a query that will compare the TOP 2 rows of an inquiry:
This is a sub-select statement of a larger query; what I have so far is:
SELECT TOP(1) Invoice.InvoiceID
FROM Invoice
INNER JOIN WorkOrder InnerWorkOrder ON Invoice.WorkOrderID = InnerWorkOrder.WorkOrderID
WHERE
InnerWorkOrder.AffiliateID = Affiliate.AffiliateID AND
DATEDIFF(m,
***WHAT THE HECK DO I PUT TO LOOK AT THE SECONDROW???***
, InnerWorkOrder.CreatedOn) <= 13[/quote-0]
Anyway, I hope this makes sense and that someone will be able to point me in the correct direction. Thank you in advance
May 28, 2009 at 12:46 pm
Really? You want us to work with just a partial piece of code? I don't think so. How about posting the entire query or at least the subquery you are attempting to modify.
May 28, 2009 at 12:48 pm
Well, first things first, define what you mean by 'top 2' please? Your query has no Order By on it, so Top 1 will just return you 1 row. Which one is not defined.
Once you've got an order, check out the Row_Number function (full details in books online), as that will allow you to select 1st, 2nd, 3rd, etc row, according to whatever order you specify. Once you've got queries to get the top row and the second row, you can join the two subqueries together to compare the two rows.
Make sense? If you need further assistance, show us the entire query and say which column defines that order.
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
May 28, 2009 at 12:52 pm
Sure! I can supply the entire code with the missing link! Thank you for looking at this:
DECLARE @StartDate datetime, @EndDate datetime
SELECT @StartDate = '5/1/2009', @EndDate = '5/28/2009'
SELECT Customer.Number, Customer.Name, Affiliate.FirstName + ' ' + Affiliate.LastName AS FullName, CASE WHEN EXISTS
(SELECT *
FROM WorkOrder InnerWorkOrder
WHERE InnerWorkOrder.AffiliateID = Affiliate.AffiliateID AND (DATEDIFF(m, InnerWorkOrder.CreatedOn, WorkOrder.CreatedOn) > 13))
THEN 'False' ELSE 'True' END AS OldNewAffiliate
FROM (
SELECT Affiliate.*, dbo.DateOnly(dbo.GetNewestAffiliateOrderDateForCommission(Affiliate.AffiliateID)) CommissionDate FROM Affiliate
) Affiliate INNER JOIN
Customer ON Affiliate.CustomerID = Customer.CustomerID INNER JOIN
Staff ON Staff.StaffID = Customer.SalesRepStaffID INNER JOIN
WorkOrder ON Affiliate.AffiliateID = WorkOrder.AffiliateID INNER JOIN
Invoice ON WorkOrder.WorkOrderID = Invoice.WorkOrderID
WHERE (Customer.SalesRepStaffID IS NOT NULL) AND (Customer.SalesRepStaffID NOT IN (162, 167, 194, 135, 169))
AND (Customer.IsProspect = 0) AND (Invoice.InvoiceDateDateOnly BETWEEN dbo.DateOnly(@StartDate) AND dbo.DateOnly(@EndDate)) AND
Invoice.InvoiceID =
(
SELECT TOP(1) Invoice.InvoiceID
FROM Invoice
INNER JOIN WorkOrder InnerWorkOrder ON Invoice.WorkOrderID = InnerWorkOrder.WorkOrderID
WHERE
InnerWorkOrder.AffiliateID = Affiliate.AffiliateID AND
DATEDIFF(m,
***WHAT THE HECK DO I PUT HERE??? ***
, InnerWorkOrder.CreatedOn) <= 13
ORDER BY Invoice.InvoiceDate ASC
)
ORDER BY Customer.Number,Affiliate.AffiliateID
May 28, 2009 at 1:02 pm
Ok, taking a couple steps back, what are you trying to do here? What's the purpose of that IN statement? It looks like you're trying to return 1 invoice, though I'm not sure which one or why.
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
May 28, 2009 at 1:50 pm
DATEDIFF(m,
***WHAT THE HECK DO I PUT HERE??? ***
, InnerWorkOrder.CreatedOn) <= 13
ORDER BY Invoice.InvoiceDate ASC
)
DATEDIFF(m,(select created_on from
(
select CreatedOn , row_number() over (order by Invoice.InvoiceDate ASC) AS Row_Numbers
from InnerWorkOrder) t where t.Row_numbers = 2
),InnerWorkOrder.CreatedOn) <= 13
ORDER BY Invoice.InvoiceDate ASC
)
May 28, 2009 at 2:36 pm
Thank you everyone for your responses.
The purpose of this report is to list new customers (or customers that are in our system but have not used our services in the last 13 months) that have used our services within a specific date range.
The part that I'm having great difficulty with is being able to compare the newest order to the next-to-newest one. If there is only one order that is returned, that would signify that this is a new client, if there is more than one order then I need to omit those that are within 13 months and only return those that are 13 months or more apart...
I hope this makes sense... Thank you again
May 28, 2009 at 2:42 pm
iklektic, copy paste the code I had written initially, with two changes:
1) I mistyped "createdon" date column in the query by create_on
2) in This part of the query, I am not sure how u want ur date column to be sorted, By invoiceDate or "Createdon"
row_number() over (order by Invoice.InvoiceDate ASC)
June 1, 2009 at 6:49 am
iklektic (5/28/2009)
Thank you everyone for your responses.The purpose of this report is to list new customers (or customers that are in our system but have not used our services in the last 13 months) that have used our services within a specific date range.
The part that I'm having great difficulty with is being able to compare the newest order to the next-to-newest one. If there is only one order that is returned, that would signify that this is a new client, if there is more than one order then I need to omit those that are within 13 months and only return those that are 13 months or more apart...
I hope this makes sense... Thank you again
Simplify, simplify.
You want to select every customer...
... except those with more than 1 order in the last 13 months
You can identify every customer NOT HAVING a COUNT of Orders > 1 WHERE OrderDate <= GetDate()-395 [approximation of 13 months]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply