May 14, 2013 at 9:49 am
I have developed this query to separate distinct invoice IDs under a certain sales id.. The one problem I am having is that our company currently has two different types of sales IDs. As of now, we currently have one Sales ID per order and then different Invoice IDs based on when the different parts of the order ship. This query has worked fairly well for this new system to let me know which orders have multiple invoices. However, before we transitioned to this system, our sales order numbers changed based on when the item shipped. for example, the first salesID that was shipped would look like "3224-0" and the second set "3224-1" even if it was originally ordered together. Any help on how I can group all of those together? Sorry if it's confusing, let me know if I need to be more clear. Also, to clarify, I have excluded any results that begin with QR because that is a different segment of our company, as well as any blank invoices that may have found a home in the database.
select it.SALESID 'Sales Order Number',ij.invoiceaccount 'Account Number',
count (distinct ij.INVOICEID) as 'Lines Per Order'
from CUSTINVOICETRANS it
full join custinvoicejour ij
on it.INVOICEID=ij.INVOICEID
where it.INVOICEDATE between '1/1/2012' and '12/31/2012'
and ij.INVOICEACCOUNT = '3845331'
and left (it.INVOICEID,2) != 'QR'
and it.INVOICEID != ''
group by it.SALESID,ij.INVOICEACCOUNT
May 14, 2013 at 10:03 am
Didn't we just go through the do's and dont's of posting yesterday? Can you please post some ddl and sample data along with desired output?
I did it for you yesterday. Today it is your turn.
_______________________________________________________________
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/
May 14, 2013 at 10:18 am
the results I get are currently something like this:
Sales Order NumberAccount NumberLines Per Order
19319-02711
17987-02711
19148-02711
19052-02711
19052-12711
19545-02711
19564-02711
19704-02711
19030-02711
19661-02711
19662-02711
19662-12711
SO-4343 271 3
SO-4313 271 1
See how the SO-4343 tells me there are 3 distinct invoice lines in that order? I want the "19052-0" and "19052-1" to consolidate. The -1's to consolidate with the -0 that it corresponds with
May 14, 2013 at 10:25 am
OK I am trying to help you but you don't seem to want to put in even as much effort as I do. You are getting paid for this and I am doing it for free. I did it for you yesterday. I suggested after your first post here that you should post like I did yesterday. Here is the link if you need a refresher on how to post it.
http://www.sqlservercentral.com/Forums/FindPost1452323.aspx
Maybe somebody else will come along and help but unless you are willing to put in some of the effort I am done.
_______________________________________________________________
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/
May 14, 2013 at 10:26 am
asaini (5/14/2013)
the results I get are currently something like this:Sales Order NumberAccount NumberLines Per Order
19319-02711
17987-02711
19148-02711
19052-02711
19052-12711
19545-02711
19564-02711
19704-02711
19030-02711
19661-02711
19662-02711
19662-12711
SO-4343 271 3
SO-4313 271 1
See how the SO-4343 tells me there are 3 distinct invoice lines in that order? I want the "19052-0" and "19052-1" to consolidate. The -1's to consolidate with the -0 that it corresponds with
Still don't see any DDL or DML statements that we could cut/paste/execute in SSMS to help you work this problem.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply