April 22, 2015 at 9:10 am
Hello, New to this so bear with me please...
I have a query which contains detail of all invoices but I only need to retrieve for my report the first record for each invoice...
Here is the query I have for my report.
SELECT
V_Phocas_Sales_All_Inner.Customer
,V_Phocas_Sales_All_Inner.[Customer Name]
,V_Phocas_Sales_All_Inner.[Invoice Number]
,V_Phocas_Sales_All_Inner.[Line Number]
,V_Phocas_Sales_All_Inner.[Invoice Release Date]
,V_Phocas_Sales_All_Inner.[Miscellaneous Sales]
,V_Phocas_Sales_All_Inner.[Freight Amount]
,V_Phocas_Sales_All_Inner.[Invoice Total]
,V_Phocas_Sales_All_Inner.[Sales Tax Amount]
FROM
V_Phocas_Sales_All_Inner
WHERE
V_Phocas_Sales_All_Inner.[Stream Name] = N'Sales'
and V_Phocas_Sales_All_Inner.Customer = @Customer
and V_Phocas_Sales_All_Inner.Date between @StartDate and @EndDate
and V_Phocas_Sales_All_Inner.[Miscellaneous Sales] != N'0'
Order by
V_Phocas_Sales_All_Inner.[Invoice Number] ASC
, V_Phocas_Sales_All_Inner.[Line Number] ASC
looked on several forums but cannot seem to find the answer, any help would be great.
Regards,
rproulx
April 22, 2015 at 9:16 am
It is hard to know exactly what you need here. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
In the meantime, I did a little formatting to your query and used an alias instead of that really long view name. Notice how much cleaner and simpler this code looks.
SELECT
psa.Customer
,psa.[Customer Name]
,psa.[Invoice Number]
,psa.[Line Number]
,psa.[Invoice Release Date]
,psa.[Miscellaneous Sales]
,psa.[Freight Amount]
,psa.[Invoice Total]
,psa.[Sales Tax Amount]
FROM
V_Phocas_Sales_All_Inner psa
WHERE
psa.[Stream Name] = N'Sales'
and psa.Customer = @Customer
and psa.Date between @StartDate and @EndDate
and psa.[Miscellaneous Sales] != N'0'
Order by
psa.[Invoice Number] ASC
, psa.[Line Number] ASC
_______________________________________________________________
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/
April 22, 2015 at 11:33 am
As Sean has mentioned some sample data would be very helpful here. That being said I have a funny feeling you are getting the same data multiple times because of your [Line Number]. I have created reports for Purchase Orders in the past and your line number is for different items in an order if memory serves. My gut tells me all you need to do is just omit [Line Number] from your SELECT statement and by lieu of that in your ORDER BY clause. This sounds like it should give you just your totals for the whole invoice.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply