W.E.B
asked on
SQL Group By, or Excel Macro
Hello,
Can you please help,
I use below script to get the data from SQL. (I need to do some data comparison)
I need to group it By Reference , Invoice Date.
Please see sample attached. (2 tabs, Original Sheet1, Sheet 2 is how I would Like to see it).
Any help is appreciated.
SQL Script or Excel Macro.
SELECT ISNULL(FO.Reference, '') AS [Reference], FO.SubTotalAmount AS [SubTotAmt], FinalizedOrdersInvoices.In voiceNumbe r AS [InvoiceNumber],
(Select TransactionDate from AROpenTransactions where AROpenTransactions.Transac tionNumber = FinalizedOrdersInvoices.In voiceNumbe r) AS [InvoiceDate]
FROM FinalizedOrders FO
INNER JOIN FinalizedOrdersInvoices ON FinalizedOrdersInvoices.Or derNo = FO.OrderNo
Where ((FO.AccountNumber in (9500,9600,9601,9602,9603, 10012))) and ((FO.OrderDate >= '2015-01-01') And (FO.OrderDate <='2015-02-28'))
Thanks
Sample.xls
Can you please help,
I use below script to get the data from SQL. (I need to do some data comparison)
I need to group it By Reference , Invoice Date.
Please see sample attached. (2 tabs, Original Sheet1, Sheet 2 is how I would Like to see it).
Any help is appreciated.
SQL Script or Excel Macro.
SELECT ISNULL(FO.Reference, '') AS [Reference], FO.SubTotalAmount AS [SubTotAmt], FinalizedOrdersInvoices.In
(Select TransactionDate from AROpenTransactions where AROpenTransactions.Transac
FROM FinalizedOrders FO
INNER JOIN FinalizedOrdersInvoices ON FinalizedOrdersInvoices.Or
Where ((FO.AccountNumber in (9500,9600,9601,9602,9603,
Thanks
Sample.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just query the data and then pivot it in Excel..
The reason you're getting it in one column is because your question and your spreadsheet are not aligned as I pointed out in my answer. You asked for the data to be grouped by reference and invoice date but that is not how your excel sheet is organized.
ASKER
Thank you both,
I guess my question should have been how to pivot what I have in SQL.
Anyway, I got it figured out.
thanks,
I guess my question should have been how to pivot what I have in SQL.
Anyway, I got it figured out.
thanks,
ASKER
Stefan script is showing in one column
Example:
Reference SubTotAmt InvoiceDate
0010 26.70 2015-02-15 00:00:00.000
0010 24.03 2015-02-28 00:00:00.000
0010 31.01 2015-01-15 00:00:00.000
0010 28.60 2015-01-31 00:00:00.000
0014 31.01 2015-01-15 00:00:00.000
0014 28.60 2015-01-31 00:00:00.000
0014 26.70 2015-02-15 00:00:00.000
0014 24.03 2015-02-28 00:00:00.000
Brian script is showing in one column Also
Example
Reference InvoiceNumber InvoiceDate SubTotAmt
0010 548515 2015-01-31 00:00:00.000 28.60
0010 549316 2015-02-15 00:00:00.000 26.70
0010 549831 2015-02-28 00:00:00.000 24.03
0010 548038 2015-01-15 00:00:00.000 31.01
0014 548515 2015-01-31 00:00:00.000 28.60
0014 549316 2015-02-15 00:00:00.000 26.70
0014 548038 2015-01-15 00:00:00.000 31.01
0014 549831 2015-02-28 00:00:00.000 24.03
Thanks