Creating a bill like view for invoice - Northwind
-
I have a little problem with my last tasks. I am using an old Northwind database. First, I had to create a query, that will give me all the important information for the invoice. My query looks like this:
SELECT b.OrderID,
b.CustomerID,
c.CompanyName,
c.Address,
c.City,
c.PostalCode,
c.CountryID as CustomersCountryID,
concat(d.FirstName, ' ', d.LastName) as Salesperson,
a.CompanyName as ShippingVia,
e.ProductID, f.ProductName,
e.Quantity,
e.UnitPrice * e.Quantity * (1 - e.Discount) as ExtendedPrice
from Shippers a
inner join Orders b on a.ShipperID = b.ShipVia
inner join Customers c on c.CustomerID = b.CustomerID
inner join Employees d on d.EmployeeID = b.EmployeeID
inner join [Order Details] e on b.OrderID = e.OrderID
inner join Products f on f.ProductID = e.ProductID
order by b.OrderIDIt works, it gives me all the orders made with informations. But now, I need to create a table view for an invoice of particular OrderId. When I write something like this:
CREATE VIEW FAKTURA AS
SELECT b.OrderID,
b.CustomerID,
c.CompanyName,
c.Address,
c.City,
c.PostalCode,
c.CountryID as CustomersCountryID,
concat(d.FirstName, ' ', d.LastName) as Salesperson,
a.CompanyName as ShippingVia,
e.ProductID,
f.ProductName,
e.Quantity,
e.UnitPrice * e.Quantity * (1 - e.Discount) as ExtendedPrice
from Shippers a
inner join Orders b on a.ShipperID = b.ShipVia
inner join Customers c on c.CustomerID = b.CustomerID
inner join Employees d on d.EmployeeID = b.EmployeeID
inner join [Order Details] e on b.OrderID = e.OrderID
inner join Products f on f.ProductID = e.ProductID
WHERE b.OrderID = 10248
I am just creating a separate view file for that particular OrderID. It doesn't look like a real life bill at all.
It should resemble something like this:I need to separate general data about invoice and customer from data about order itself, product ID, quantity etc. Is it possible to create something similar in SQL Server Management Studio? How can I do it?
- I think you are confusing a DBMS with a reporting tool. The query retrieves the data where a report will display the data in a nice format for the end user. You do NOT want to try to do formatting like this in a query. It would be incredibly ugly and brittle.
As a side note you should consider using aliases that have some meaning. Things like a,b,c are just awful. For example you have Employees d, how confusing is that? You should create meaningful aliases so it is easy to maintain your queries. http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3.aspx
_______________________________________________________________
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 - Friday, April 27, 2018 8:06 AM
Thanks for the tip, I will use it in the future.
Still, my problem exists. I know about Reporting Systems/Tools, but I wonder if my superior knows exactly what he asks me to do. I mean, I knew it was something weird to request, since I haven't learned about formatting queries in such way on any site.
Some people advise me to learn about CTE, but I am not sure if that would help me as well.Is there anything I can do to create something that would resemble thing he wants?
- margor20 - Friday, April 27, 2018 8:15 AM
Not really if you are only allowed to do this with a query. A query is not a report. A CTE is not going to help here, you are talking about formatting output. Why not go back to your boss and tell him/her that a query will not work? To format output you need a presentation tool of some sort.
_______________________________________________________________
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/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply