Separating distinct invoices under one sales order

Question

Separating distinct invoices under one sales order

asaini

Valued Member

Points: 63
More actions
May 13, 2013 at 12:45 pm

#293198

I am having some trouble coming up with a query to do this. I want to distinguish the unique/distinct invoices I have under the same sales order. So for example, sales order SO4231 has 8 invoice lines but only 2 are unique. Any ideas on how to structure this?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

asaini (5/13/2013)
I am having some trouble coming up with a query to do this. I want to distinguish the unique/distinct invoices I have under the same sales order. So for example, sales order SO4231 has 8 invoice lines but only 2 are unique. Any ideas on how to structure this?

Hi and welcome to the forums!!! It is certainly a query that can be done. However to offer any detailed coding assistance we need to have a bit more detail. We need ddl (create table statements), sample data (insert statements) and desired output based on the sample data.

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

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/

asaini Valued Member Points: 63 More actions · Answer 2

Sorry about that, this is an idea of what I have:

Sales Order #InvoiceIDInvoiceDate InvoiceAmount

SO-100 INV-382224/12/2013 $7

SO-100 INV-382224/12/2013 $4

SO-100 INV-382224/12/2013 $7

SO-101 INV-48254/14/2013 $10

SO-101 INV-48254/14/2013 $8

SO-101 INV-48274/15/2013 $3

SO-102 INV-48304/20/2013 $8

SO-102 INV-48314/21/2013 $63

SO-102 INV-48324/22/2013 $26

In this, I have some sales orders with only 1 invoice. On the other hand, there are other orders that have multiple invoice IDs, meaning they were shipped in different chunks. I am looking to develop a query that could tell me which sales orders have multiple invoice IDs and how many, along with the invoice date. Been running into some dead ends so far. Any help would be much appreciated.

Lowell SSC Guru Points: 323504 More actions · Answer 3

it seems like a basic grouping can get you the data you want;

run this in SSMS, does this give you teh data youa re looking for?

with mySampleData ([Sales Order #],[InvoiceID],[InvoiceDate], [InvoiceAmount])

AS

(

SELECT 'SO-100',' INV-38222',convert(datetime,'4/12/2013'),7.00 UNION ALL

SELECT 'SO-100',' INV-38222','4/12/2013',4.00 UNION ALL

SELECT 'SO-100',' INV-38222','4/12/2013',7.00 UNION ALL

SELECT 'SO-101',' INV-4825','4/14/2013',10.00 UNION ALL

SELECT 'SO-101',' INV-4825','4/14/2013',8.00 UNION ALL

SELECT 'SO-101',' INV-4827','4/15/2013',3.00 UNION ALL

SELECT 'SO-102',' INV-4830','4/20/2013',8.00 UNION ALL

SELECT 'SO-102',' INV-4831','4/21/2013',63.00 UNION ALL

SELECT 'SO-102',' INV-4832','4/22/2013',26.00

)

SELECT

MySampleData.[Sales Order #],

COUNT([InvoiceID]) As TotalInvoicesForSalesOrder,

COUNT(DISTINCT [InvoiceID]) As TotalDistinctInvoicesForSalesOrder,

MAX([InvoiceDate]) As LastInvoiceDate

from MySampleData

Group By [Sales Order #]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286573 More actions · Answer 4

asaini (5/13/2013)
Sorry about that, this is an idea of what I have:
Sales Order #InvoiceIDInvoiceDate InvoiceAmount
SO-100 INV-382224/12/2013 $7
SO-100 INV-382224/12/2013 $4
SO-100 INV-382224/12/2013 $4
SO-100 INV-382224/12/2013 $7
SO-101 INV-48254/14/2013 $10
SO-101 INV-48254/14/2013 $8
SO-101 INV-48274/15/2013 $3
SO-102 INV-48304/20/2013 $8
SO-102 INV-48304/20/2013 $8
SO-102 INV-48304/20/2013 $8
SO-102 INV-48314/21/2013 $63
SO-102 INV-48324/22/2013 $26
In this, I have some sales orders with only 1 invoice. On the other hand, there are other orders that have multiple invoice IDs, meaning they were shipped in different chunks. I am looking to develop a query that could tell me which sales orders have multiple invoice IDs and how many, along with the invoice date. Been running into some dead ends so far. Any help would be much appreciated.

This is not really the create table and insert statements I asked for and pointed you to in the article. since you brand new around here I cobbled this together based on what you did post so you can see what I mean.

if OBJECT_ID('tempdb..#Sales') is not null

drop table #Sales

create table #Sales

(

SalesOrder char(6),

Invoice varchar(10),

InvoiceDate datetime,

InvoiceAmount int

)

insert #Sales

select 'SO-100', 'INV-38222', '4/12/2013', 7 union all

select 'SO-100', 'INV-38222', '4/12/2013', 4 union all

select 'SO-100', 'INV-38222', '4/12/2013', 7 union all

select 'SO-101', 'INV-4825', '4/14/2013', 10 union all

select 'SO-101', 'INV-4825', '4/14/2013', 8 union all

select 'SO-101', 'INV-4827', '4/15/2013', 3 union all

select 'SO-102', 'INV-4830', '4/20/2013', 8 union all

select 'SO-102', 'INV-4831', '4/21/2013', 63 union all

select 'SO-102', 'INV-4832', '4/22/2013', 26

I know this doesn't seem like it is so hard but if you post the ddl and sample data there are two major advantages for you. First, we don't have to guess at column names and more importantly datatypes. The second advantage is that since everyone who posts here is a volunteer, if you post everything in a readily consumable format you are much more likely to get more people helping you.

So now to pull this data like you stated, it is nothing more than a group by. Please note that some of the SalesOrders will show up multiple times because they were invoiced and shipped on different days. And since you want the invoice date in the output you either show the values or you have to aggregate them (MIN, MAX, etc)

select SalesOrder, InvoiceDate, count(Invoice) as NumShipments

from #Sales

group by SalesOrder, InvoiceDate

_______________________________________________________________

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/

Sean Lange SSC Guru Points: 286573 More actions · Answer 5

Lowell - you win this time. 😉

_______________________________________________________________

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/

Lowell SSC Guru Points: 323504 More actions · Answer 6

Sean Lange (5/13/2013)
Lowell - you win this time. 😉

Only because i have a macro that converts pasted text to a CTE; you built a complete sample DDL for it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

asaini Valued Member Points: 63 More actions · Answer 7

I actually had over 1000 sales orders so adding them individually would be a pain but Sean, thank you so much! Your count(invoice) section did the trick. I just changed it to count distinct invoice and that separated out how many distinct invoices there were within a sales order. Appreciate the assistance Lowell and Sean, you da men!

Sean Lange SSC Guru Points: 286573 More actions · Answer 8

Lowell (5/13/2013)
Sean Lange (5/13/2013)
Lowell - you win this time. 😉
Only because i have a macro that converts pasted text to a CTE; you built a complete sample DDL for it.

That sounds interesting. Wanna share?

_______________________________________________________________

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/

Sean Lange SSC Guru Points: 286573 More actions · Answer 9

asaini (5/13/2013)
I actually had over 1000 sales orders so adding them individually would be a pain but Sean, thank you so much! Your count(invoice) section did the trick. I just changed it to count distinct invoice and that separated out how many distinct invoices there were within a sales order. Appreciate the assistance Lowell and Sean, you da men!

You certainly would not need to post all of your data. The idea here is to post enough to be representative of the problem which I think your 10-12 rows did just fine.

Glad you were able to get it working.

_______________________________________________________________

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/

Lowell SSC Guru Points: 323504 More actions · Answer 10

Sean Lange (5/13/2013)
Lowell (5/13/2013)
Sean Lange (5/13/2013)
Lowell - you win this time. 😉
Only because i have a macro that converts pasted text to a CTE; you built a complete sample DDL for it.
That sounds interesting. Wanna share?

Sean it depends on your favorite text editor; the macro is certainly sharable, but it's run in the text editor EditPlus 2 or EditPlus 3(editplus.com

If you are using Notepad++ I could build the same macro; it's just a basic find and replace;

I don't have one for SSMS, sorry

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!