July 21, 2014 at 12:03 pm
Hi,
Here is a sample table:
CustomerID------InvoiceID------Total
----95---------------3546--------#30
----100--------------3547--------$12
----95---------------3548--------$42
----100--------------3549--------$25
----100--------------3550--------$30
I'm looking to write a query that will only return the invoices for each customer that have a Total difference of greater than $10. So in this instance, I would want to see:
CustomerID------InvoiceID------Total
----95---------------3546--------#30
----100--------------3547--------$12
----95---------------3548--------$42
----100--------------3549--------$25
Since InvoiceID 3550 is only $5 greater than the previous invoice for that customer, it would not be returned in the result set.
Any ideas would be greatly appreciated!
July 21, 2014 at 12:41 pm
First you have to define what you mean by previous. I am guessing you mean InvoiceID? If you can post some consumable ddl and sample data I can help you figure this out. Please take a few minutes and read the first article in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 22, 2014 at 11:28 am
My apologies. Hopefully this helps:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#invoices','U') IS NOT NULL
DROP TABLE #invoices
--===== Create the test table with
CREATE TABLE #invoices
(
InvoiceID INT,
CustomerID INT,
Total MONEY
)
INSERT INTO #invoices VALUES (3546,95,30)
INSERT INTO #invoices VALUES (3547,100,12)
INSERT INTO #invoices VALUES (3548,95,42)
INSERT INTO #invoices VALUES (3549,100,25)
INSERT INTO #invoices VALUES (3550,100,30)
SELECT * FROM #invoices
Also, to revise my request a bit, I would only need the records returned that at least $10 greater than the previous invoice for that customer. The previous invoice would not need to be shown (unless it too was $10 greater than its previous invoice).
So the expected result set:
InvoiceID------CustomerID------Total
--3549-----------100-------------25.00
--3548-----------95---------------42.00
Thank you!
July 22, 2014 at 11:54 am
Not a Pro but came up with this query
with CTE as
(select ROW_NUMBER() over( partition by CustomerID order by invoiceid) as rn,* from #invoices)
select t1.invoiceid,t1.customerid,t1.total from CTE t1
left outer join CTE t2
on t1.CustomerID = t2.CustomerID
and t1.rn = t2.rn +1
where (t1.Total-t2.Total) > 10
July 22, 2014 at 12:01 pm
Sean Lange (7/21/2014)
First you have to define what you mean by previous. I am guessing you mean InvoiceID? If you can post some consumable ddl and sample data I can help you figure this out. Please take a few minutes and read the first article in my signature.
OK so assuming that InvoiceID is what you would use to determine "previous" this should work.
with MySortedData as
(
select InvoiceID
, CustomerID
, Total
, ROW_NUMBER() over(partition by CustomerID order by InvoiceID) as RowNum
from #invoices
)
select s2.InvoiceID
, s2.CustomerID
, s2.Total
from MySortedData s1
inner join MySortedData s2 on s2.CustomerID = s1.CustomerID and s2.RowNum = s1.RowNum + 1
where s2.Total - s1.Total >= 10
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 22, 2014 at 12:06 pm
tarr94 (7/21/2014)
Hi,Here is a sample table:
CustomerID------InvoiceID------Total
----95---------------3546--------#30
----100--------------3547--------$12
----95---------------3548--------$42
----100--------------3549--------$25
----100--------------3550--------$30
I'm looking to write a query that will only return the invoices for each customer that have a Total difference of greater than $10. So in this instance, I would want to see:
CustomerID------InvoiceID------Total
----95---------------3546--------#30
----100--------------3547--------$12
----95---------------3548--------$42
----100--------------3549--------$25
Since InvoiceID 3550 is only $5 greater than the previous invoice for that customer, it would not be returned in the result set.
Any ideas would be greatly appreciated!
What if there is no previous invoice?
July 22, 2014 at 12:10 pm
Sean Lange (7/22/2014)
Sean Lange (7/21/2014)
First you have to define what you mean by previous. I am guessing you mean InvoiceID? If you can post some consumable ddl and sample data I can help you figure this out. Please take a few minutes and read the first article in my signature.OK so assuming that InvoiceID is what you would use to determine "previous" this should work.
with MySortedData as
(
select InvoiceID
, CustomerID
, Total
, ROW_NUMBER() over(partition by CustomerID order by InvoiceID) as RowNum
from #invoices
)
select s2.InvoiceID
, s2.CustomerID
, s2.Total
from MySortedData s1
inner join MySortedData s2 on s2.CustomerID = s1.CustomerID and s2.RowNum = s1.RowNum + 1
where s2.Total - s1.Total >= 10
This is what I came up with but then I started wondering about there not being a previous row.
July 22, 2014 at 12:20 pm
Lynn Pettis (7/22/2014)
Sean Lange (7/22/2014)
Sean Lange (7/21/2014)
First you have to define what you mean by previous. I am guessing you mean InvoiceID? If you can post some consumable ddl and sample data I can help you figure this out. Please take a few minutes and read the first article in my signature.OK so assuming that InvoiceID is what you would use to determine "previous" this should work.
with MySortedData as
(
select InvoiceID
, CustomerID
, Total
, ROW_NUMBER() over(partition by CustomerID order by InvoiceID) as RowNum
from #invoices
)
select s2.InvoiceID
, s2.CustomerID
, s2.Total
from MySortedData s1
inner join MySortedData s2 on s2.CustomerID = s1.CustomerID and s2.RowNum = s1.RowNum + 1
where s2.Total - s1.Total >= 10
This is what I came up with but then I started wondering about there not being a previous row.
Me too, but since there were many many details given I figured I would let them figure that part out. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 22, 2014 at 12:28 pm
;with CTE as
(select ROW_NUMBER() over( partition by CustomerID order by invoiceid) as rn,* from #invoices)
select
t1.invoiceid,t1.customerid,t1.total
from CTE t1
left outer join CTE t2
on t1.CustomerID = t2.CustomerID
and t1.rn = t2.rn +1
where (t1.Total-t2.Total) > 10
union
select * from #invoices where customerid in (select customerid from #invoices
group by customerid
having COUNT(customerid) = 1);
July 22, 2014 at 12:36 pm
Thank you for your suggestions, everyone! I will give these a try.
July 22, 2014 at 2:26 pm
rxm119528 (7/22/2014)
;with CTE as(select ROW_NUMBER() over( partition by CustomerID order by invoiceid) as rn,* from #invoices)
select
t1.invoiceid,t1.customerid,t1.total
from CTE t1
left outer join CTE t2
on t1.CustomerID = t2.CustomerID
and t1.rn = t2.rn +1
where (t1.Total-t2.Total) > 10
union
select * from #invoices where customerid in (select customerid from #invoices
group by customerid
having COUNT(customerid) = 1);
A couple of suggestions to make this a bit quicker. First change the UNION to UNION ALL since any rows in the second query are by definition not in the first. Secondly you can do aggregates without having the aggregate column in the result. In this case it means hitting the base once for this query instead of twice.
union ALL
select i.invoiceid
,i.customerid
,i.total
from #invoices
group by i.invoiceid
,i.customerid
,i.total
having COUNT(customerid) = 1);
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 22, 2014 at 2:54 pm
thankyou Sean.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply