July 26, 2011 at 4:52 pm
Hey everyone,
I am formulating a query from two of my databases invoice tables. The two tables are an invoice header and invoice line table. The invoice line table will have line items for each product being invoiced. I ultimately want to just calculate total amounts and percentages. When I run the below statement I will get each line per invoice and the Amount, Profit, and Profit % is not correct since it is only showing the per line amount and not the per invoice amount.
select Distinct
sil.[Document No_] AS 'Invoice Number'
,sih.[Posting Date] AS 'Invoice Date'
,sih.[Sell-to Customer No_] AS 'Bill-To Customer'
,sih.[Ship-to Name] AS 'Name'
,sih.[Payment Terms Code] AS 'Payment Terms'
,sih.[Order No_] AS 'Order No.'
,sih.[Salesperson Code] AS 'Person'
,SUM((sil.[Quantity])*(sil.[Unit Price])) AS Amount
,SUM([Retail Price]-[Vendor Quoted Cost]) AS Profit
,(
(
(
(sil.[Quantity] * sil.[Unit Price])-(sil.[Quantity] * sil.[Unit Cost (LCY)])
)
/(NULLIF(sil.[Quantity],0.0) * (NullIF(sil.[Unit Price],0.0)))* 100
)
) AS 'Profit%'
From [dbo].[GK Live$Sales Invoice Line] AS sil
Inner Join [dbo].[GK Live$Sales Invoice Header] AS sih ON sih.No_ = sil.[Document No_]
GROUP BY
sil.[Document No_]
,sih.[Posting Date]
,sih.[Sell-to Customer No_]
,sih.[Ship-to Name]
,sih.[Payment Terms Code]
,sih.[Order No_]
,sih.[Salesperson Code]
,sil.Quantity
,sil.[Unit Price]
,sil.[Unit Cost (LCY)]
,sil.[Retail Price]
,sil.[Vendor Quoted Cost]
Having sih.[Posting Date] = '07/15/2011'
Order by sil.[Document No_]
However when I run the following code I get the results I want for Amount, Profit, and Profit %, but this code is just running the select statement in the invoice line table only.
Select
SUM([Quantity]*[Unit Price]) AS Amount
,(Sum([Retail Price]) - Sum([Vendor Quoted Cost])) AS Profit
,Round(
(
(
Sum([Retail Price]) - Sum([Vendor Quoted Cost])
)
/ SUM([Quantity]*[Unit Price]) * 100
)
,2) AS 'Profit%'
From [dbo].[GK Live$Sales Invoice Line]
Where [Document No_] = 'IXSCO-34206'
I have 2 attachments of my result sets. untitled.jpg is sample data from the first query. untitled2.jpg is sample data from the second query.
If anyone can point me in the right direction it would be greatly appreciated.
Keith
July 27, 2011 at 4:44 am
Ignoring Celko's comment about best SQL programming practices (which he and I will always disagree on to some extent), he's correct that we need more information in order to assist you with your problem.
Please include CREATE TABLE statements and sample data formatted in an INSERT INTO statement so we can test our solutions and be reasonably certain we don't give you broken code.
July 27, 2011 at 4:50 am
Okay, I do have a question. Is this query for a report?
You can't have line item detail and a summary together that will display correctly in SSMS. You get to pick: Detail or Summary. Not both.
If this is a report, you'll want a subreport for either the summary or the detail (pick one) and two different queries. Don't worry about pretty in SSMS. SSMS isn't made for making things pretty. That's what the client interface or the reports are for.
If this is a single SSMS query that doesn't display to the end user, then just sum it up without the detail.
July 27, 2011 at 5:23 am
Brandie Tarvin (7/27/2011)
...You can't have line item detail and a summary together that will display correctly in SSMS. You get to pick: Detail or Summary. Not both.
...
You can display them both, but it's usually useless. However, if you are sure you want it, here is direction you can follow: use COMPUTE ... BY
Next time, when you ask question on this forum, please provide DDL to setup tables and data (follow the link in my signature).
declare @invhdr table (invId int, Hdr varchar(20))
insert @invhdr values (1,'Inv1'), (2, 'Inv2'), (3, 'Inv3')
declare @invlns table (invId int, prd varchar(20), qnt int)
insert @invlns values (1,'Prd1',10), (1, 'Prd2',15), (2, 'Prd1',5), (2, 'Prd2',10), (3, 'Prd3',2)
declare @prd table (prd varchar(20), price money)
insert @prd values ('Prd1',2.50), ('Prd2', 5.40), ('Prd3', 24.99)
select h.Hdr, l.prd, SUM(l.qnt) ttlQ, SUM(l.qnt) * MIN(p.price) ttlP
from @invhdr h
join @invlns l on l.invId = h.invId
join @prd p on p.prd = l.prd
group by h.Hdr, l.prd
order by h.Hdr
compute sum(SUM(l.qnt) * MIN(p.price)) by h.Hdr
July 27, 2011 at 5:46 am
Eugene Elutin (7/27/2011)
Brandie Tarvin (7/27/2011)
...You can't have line item detail and a summary together that will display correctly in SSMS. You get to pick: Detail or Summary. Not both.
...
You can display them both, but it's usually useless. However, if you are sure you want it, here is direction you can follow: use COMPUTE ... BY
Which doesn't do what I think he wants, Eugene. Compute By puts the summary in a separate results set. I think he wants to display them together in the same result set.
July 27, 2011 at 6:01 am
Brandie Tarvin (7/27/2011)
Eugene Elutin (7/27/2011)
Brandie Tarvin (7/27/2011)
...You can't have line item detail and a summary together that will display correctly in SSMS. You get to pick: Detail or Summary. Not both.
...
You can display them both, but it's usually useless. However, if you are sure you want it, here is direction you can follow: use COMPUTE ... BY
Which doesn't do what I think he wants, Eugene. Compute By puts the summary in a separate results set. I think he wants to display them together in the same result set.
It's also possible. If you really wish:
declare @invhdr table (invId int, Hdr varchar(20))
insert @invhdr values (1,'Inv1'), (2, 'Inv2'), (3, 'Inv3')
declare @invlns table (invId int, prd varchar(20), qnt int)
insert @invlns values (1,'Prd1',10), (1, 'Prd2',15), (2, 'Prd1',5), (2, 'Prd2',10), (3, 'Prd3',2)
declare @prd table (prd varchar(20), price money)
insert @prd values ('Prd1',2.50), ('Prd2', 5.40), ('Prd3', 24.99)
;with i
as
(
select h.Hdr, l.prd, SUM(l.qnt) ttlQ, SUM(l.qnt) * MIN(p.price) ttlP
from @invhdr h
join @invlns l on l.invId = h.invId
join @prd p on p.prd = l.prd
group by h.Hdr, l.prd
)
,s
as
(
select 0 as ord, hdr, prd, CAST(ttlQ as varchar(11)) ttlQ, ttlP
from i
union
select 1 as ord,hdr,'Total:','',SUM(ttlP)
from i
group by hdr
)
select hdr, prd, ttlQ, ttlP from s order by Hdr, prd, ord
July 27, 2011 at 7:05 am
Brandie/Celko, The DDL's are coming up. I didn't create the DDL, this is from a Microsoft Dynamics database called Navision. Its not pretty to say the least.
As far as the Group By clause which is of my usage, all I can say is i'm an SQL rookie and look to pro's like yourself to give me some guidance.
The Distinct keyword was put in by me to see if the resultes were different. They weren't so I left it in when I copied the script.
Without futher delay......
USE [GK50LIVE]
GO
/****** Object: Table [dbo].[GK Live$Sales Invoice Header] Script Date: 07/27/2011 09:02:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[GK Live$Sales Invoice Header](
[timestamp] [timestamp] NOT NULL,
[No_] [varchar](20) NOT NULL,
[Sell-to Customer No_] [varchar](20) NOT NULL,
[Bill-to Customer No_] [varchar](20) NOT NULL,
[Bill-to Name] [varchar](50) NOT NULL,
[Bill-to Name 2] [varchar](50) NOT NULL,
[Bill-to Address] [varchar](50) NOT NULL,
[Bill-to Address 2] [varchar](50) NOT NULL,
[Bill-to City] [varchar](30) NOT NULL,
[Bill-to Contact] [varchar](50) NOT NULL,
[Customer PO No] [varchar](30) NOT NULL,
[Ship-to Code] [varchar](10) NOT NULL,
[Ship-to Name] [varchar](50) NOT NULL,
[Ship-to Name 2] [varchar](50) NOT NULL,
[Ship-to Address] [varchar](50) NOT NULL,
[Ship-to Address 2] [varchar](50) NOT NULL,
[Ship-to City] [varchar](30) NOT NULL,
[Ship-to Contact] [varchar](50) NOT NULL,
[Order Date] [datetime] NOT NULL,
[Posting Date] [datetime] NOT NULL,
[Shipment Date] [datetime] NOT NULL,
[Posting Description] [varchar](50) NOT NULL,
[Payment Terms Code] [varchar](10) NOT NULL,
[Due Date] [datetime] NOT NULL,
[Payment Discount %] [decimal](38, 20) NOT NULL,
[Pmt_ Discount Date] [datetime] NOT NULL,
[Shipment Method Code] [varchar](10) NOT NULL,
[Location Code] [varchar](10) NOT NULL,
[Shortcut Dimension 1 Code] [varchar](20) NOT NULL,
[Shortcut Dimension 2 Code] [varchar](20) NOT NULL,
[Customer Posting Group] [varchar](10) NOT NULL,
[Currency Code] [varchar](10) NOT NULL,
[Currency Factor] [decimal](38, 20) NOT NULL,
[Customer Price Group] [varchar](10) NOT NULL,
[Prices Including VAT] [tinyint] NOT NULL,
[Invoice Disc_ Code] [varchar](20) NOT NULL,
[Customer Disc_ Group] [varchar](10) NOT NULL,
[Language Code] [varchar](10) NOT NULL,
[Salesperson Code] [varchar](10) NOT NULL,
[Order No_] [varchar](20) NOT NULL,
[No_ Printed] [int] NOT NULL,
[On Hold] [varchar](3) NOT NULL,
[Applies-to Doc_ Type] [int] NOT NULL,
[Applies-to Doc_ No_] [varchar](20) NOT NULL,
[Bal_ Account No_] [varchar](20) NOT NULL,
[VAT Registration No_] [varchar](20) NOT NULL,
[Reason Code] [varchar](25) NOT NULL,
[Gen_ Bus_ Posting Group] [varchar](10) NOT NULL,
[EU 3-Party Trade] [tinyint] NOT NULL,
[Transaction Type] [varchar](10) NOT NULL,
[Transport Method] [varchar](10) NOT NULL,
[VAT Country_Region Code] [varchar](10) NOT NULL,
[Sell-to Customer Name] [varchar](50) NOT NULL,
[Sell-to Customer Name 2] [varchar](50) NOT NULL,
[Sell-to Address] [varchar](50) NOT NULL,
[Sell-to Address 2] [varchar](50) NOT NULL,
[Sell-to City] [varchar](30) NOT NULL,
[Sell-to Contact] [varchar](50) NOT NULL,
[Bill-to Post Code] [varchar](20) NOT NULL,
[Bill-to County] [varchar](30) NOT NULL,
[Bill-to Country_Region Code] [varchar](10) NOT NULL,
[Sell-to Post Code] [varchar](20) NOT NULL,
[Sell-to County] [varchar](30) NOT NULL,
[Sell-to Country_Region Code] [varchar](10) NOT NULL,
[Ship-to Post Code] [varchar](20) NOT NULL,
[Ship-to County] [varchar](30) NOT NULL,
[Ship-to Country_Region Code] [varchar](10) NOT NULL,
[Bal_ Account Type] [int] NOT NULL,
[Exit Point] [varchar](10) NOT NULL,
[Correction] [tinyint] NOT NULL,
[Document Date] [datetime] NOT NULL,
[External Document No_] [varchar](20) NOT NULL,
[Area] [varchar](10) NOT NULL,
[Transaction Specification] [varchar](10) NOT NULL,
[Payment Method Code] [varchar](10) NOT NULL,
[Shipping Agent Code] [varchar](10) NOT NULL,
[Package Tracking No_] [varchar](30) NOT NULL,
[Pre-Assigned No_ Series] [varchar](10) NOT NULL,
[No_ Series] [varchar](10) NOT NULL,
[Order No_ Series] [varchar](10) NOT NULL,
[Pre-Assigned No_] [varchar](20) NOT NULL,
[User ID] [varchar](20) NOT NULL,
[Source Code] [varchar](10) NOT NULL,
[Tax Area Code] [varchar](20) NOT NULL,
[Tax Liable] [tinyint] NOT NULL,
[VAT Bus_ Posting Group] [varchar](10) NOT NULL,
[VAT Base Discount %] [decimal](38, 20) NOT NULL,
[Prepayment No_ Series] [varchar](10) NOT NULL,
[Prepayment Invoice] [tinyint] NOT NULL,
[Prepayment Order No_] [varchar](20) NOT NULL,
[Campaign No_] [varchar](20) NOT NULL,
[Sell-to Contact No_] [varchar](20) NOT NULL,
[Bill-to Contact No_] [varchar](20) NOT NULL,
[Responsibility Center] [varchar](10) NOT NULL,
[Service Mgt_ Document] [tinyint] NOT NULL,
[Allow Line Disc_] [tinyint] NOT NULL,
[Get Shipment Used] [tinyint] NOT NULL,
[Ship-to UPS Zone] [varchar](2) NOT NULL,
[Tax Exemption No_] [varchar](30) NOT NULL,
[Expiration Date] [datetime] NOT NULL,
[Quoted By] [varchar](20) NOT NULL,
[Delivery Term_FOB] [varchar](20) NOT NULL,
[Follow Up Date] [datetime] NOT NULL,
[Original Quote No_] [varchar](20) NOT NULL,
[Quote Converted] [tinyint] NOT NULL,
[RGA] [tinyint] NOT NULL,
[Work Order No_] [varchar](20) NOT NULL,
[Date Returned] [datetime] NOT NULL,
[Reason for Return] [varchar](50) NOT NULL,
[Return Type] [int] NOT NULL,
[DM No_] [varchar](20) NOT NULL,
[Sales Agent] [varchar](10) NOT NULL,
[Status Code] [int] NOT NULL,
[Order Handling Code] [int] NOT NULL,
[Hold] [tinyint] NOT NULL,
[PO Created] [tinyint] NOT NULL,
[Initial Warehouse] [varchar](10) NOT NULL,
[xName] [varchar](30) NOT NULL,
[xAddress] [varchar](30) NOT NULL,
[xAddress 2] [varchar](30) NOT NULL,
[xAddress 3] [varchar](30) NOT NULL,
[xCity] [varchar](30) NOT NULL,
[xState] [varchar](30) NOT NULL,
[xZip Code] [varchar](30) NOT NULL,
[Tax Code] [varchar](10) NOT NULL,
[Late Penalty Fee] [tinyint] NOT NULL,
[Late Penalty Date] [datetime] NOT NULL,
[Delivery Term] [varchar](30) NOT NULL,
[Requested_Expected Date] [datetime] NOT NULL,
[Buy From Vendor No_] [varchar](10) NOT NULL,
[Vendor Name] [varchar](50) NOT NULL,
[Expected Receipt Date] [datetime] NOT NULL,
[Vendor Quote Date] [datetime] NOT NULL,
[Vendor Quote Ref_ No_] [varchar](20) NOT NULL,
[Vendor Quoted By] [varchar](30) NOT NULL,
[Country of Destination] [varchar](30) NOT NULL,
[Ship From] [varchar](30) NOT NULL,
[Comment for PO Requisition] [varchar](80) NOT NULL,
[Weight] [decimal](38, 20) NOT NULL,
[Applies to Entire Doc_] [tinyint] NOT NULL,
[Cust Service Rep] [varchar](20) NOT NULL,
[Ship-to Phone No_] [varchar](30) NOT NULL,
[Last Modified By] [varchar](20) NOT NULL,
[Last Modified Date] [datetime] NOT NULL,
[Project Manager] [varchar](20) NOT NULL,
[XML Supplier OI] [varchar](20) NOT NULL,
[XML Vendor Name] [varchar](30) NOT NULL,
[XML Vendor Doc Reference] [varchar](20) NOT NULL,
[XML RFQ Number] [varchar](20) NOT NULL,
[XML Created By] [varchar](30) NOT NULL,
[XML Created By Phone] [varchar](30) NOT NULL,
[XML Created Date] [varchar](10) NOT NULL,
[XML Partner ID] [varchar](10) NOT NULL,
[XML Effective Date] [varchar](10) NOT NULL,
[XML Expired Date] [varchar](10) NOT NULL,
[XML Lead Time (Days)] [int] NOT NULL,
[XML Bid Required By] [varchar](10) NOT NULL,
[XML PO No_] [varchar](20) NOT NULL,
[XML AP Company] [varchar](10) NOT NULL,
[XML Delivery Code] [varchar](30) NOT NULL,
[Exclude from Usage] [tinyint] NOT NULL,
[EDI Order] [tinyint] NOT NULL,
[EDI Internal Doc_ No_] [varchar](10) NOT NULL,
[EDI Invoice Generated] [tinyint] NOT NULL,
[EDI Trade Partner] [varchar](10) NOT NULL,
[EDI Sell-to Code] [varchar](10) NOT NULL,
[EDI Ship-to Code] [varchar](10) NOT NULL,
[EDI Ship-for Code] [varchar](20) NOT NULL,
[E-Ship Agent Service] [varchar](30) NOT NULL,
[Residential Delivery] [tinyint] NOT NULL,
[Free Freight] [tinyint] NOT NULL,
[COD Payment] [tinyint] NOT NULL,
[World Wide Service] [tinyint] NOT NULL,
[Blind Shipment] [tinyint] NOT NULL,
[Double Blind Shipment] [tinyint] NOT NULL,
[Double Blind Ship-from Cust No] [varchar](20) NOT NULL,
[No Free Freight Lines on Order] [tinyint] NOT NULL,
[Shipping Payment Type] [int] NOT NULL,
[Third Party Ship_ Account No_] [varchar](20) NOT NULL,
[Shipping Insurance] [int] NOT NULL,
[Ship-for Code] [varchar](20) NOT NULL,
[External Sell-to No_] [varchar](20) NOT NULL,
[External Ship-to No_] [varchar](20) NOT NULL,
[External Ship-for No_] [varchar](20) NOT NULL,
[Invoice for Bill of Lading No_] [varchar](20) NOT NULL,
[Invoice for Shipment No_] [varchar](20) NOT NULL,
[Shipment Invoice Override] [tinyint] NOT NULL,
[E-Mail Confirmation Handled] [tinyint] NOT NULL,
[E-Mail Invoice Notice Handled] [tinyint] NOT NULL,
[Date Sent] [datetime] NOT NULL,
[Time Sent] [datetime] NOT NULL,
[BizTalk Sales Invoice] [tinyint] NOT NULL,
[Customer Order No_] [varchar](20) NOT NULL,
[BizTalk Document Sent] [tinyint] NOT NULL,
[Alternate Billing Address Code] [varchar](10) NOT NULL,
[PRGA] [varchar](20) NOT NULL,
[Doc_ Creation User ID] [varchar](20) NOT NULL,
[Data Entry Date] [datetime] NOT NULL,
[Quote Date] [datetime] NOT NULL,
[Use E-Ship] [tinyint] NOT NULL,
[FOB Destination] [varchar](30) NOT NULL,
[Do Not Ship Prior To] [datetime] NOT NULL,
[Email Invoice] [tinyint] NOT NULL,
[Invoice Email Sent] [tinyint] NOT NULL,
[Invoice Email Sent By] [varchar](20) NOT NULL,
[Invoice Email Sent Date] [datetime] NOT NULL,
[Invoice Email Sent Time] [datetime] NOT NULL,
[Quote No_] [varchar](20) NOT NULL,
[STE Transaction ID] [varchar](20) NOT NULL,
[Notification] [int] NOT NULL,
[Quote Followup Email Address] [varchar](60) NOT NULL,
CONSTRAINT [GK Live$Sales Invoice Header$0] PRIMARY KEY CLUSTERED
(
[No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data Filegroup 1]
) ON [Data Filegroup 1]
GO
SET ANSI_PADDING OFF
GO
Here is the Sales Invoice Line table
USE [GK50LIVE]
GO
/****** Object: Table [dbo].[GK Live$Sales Invoice Line] Script Date: 07/27/2011 09:03:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[GK Live$Sales Invoice Line](
[timestamp] [timestamp] NOT NULL,
[Document No_] [varchar](20) NOT NULL,
[Line No_] [int] NOT NULL,
[Sell-to Customer No_] [varchar](20) NOT NULL,
[Type] [int] NOT NULL,
[No_] [varchar](50) NOT NULL,
[Location Code] [varchar](10) NOT NULL,
[Posting Group] [varchar](10) NOT NULL,
[Shipment Date] [datetime] NOT NULL,
[Description] [varchar](50) NOT NULL,
[Description 2] [varchar](50) NOT NULL,
[Unit of Measure] [varchar](10) NOT NULL,
[Quantity] [decimal](38, 20) NOT NULL,
[Unit Price] [decimal](38, 20) NOT NULL,
[Unit Cost (LCY)] [decimal](38, 20) NOT NULL,
[VAT %] [decimal](38, 20) NOT NULL,
[Line Discount %] [decimal](38, 20) NOT NULL,
[Line Discount Amount] [decimal](38, 20) NOT NULL,
[Amount] [decimal](38, 20) NOT NULL,
[Amount Including VAT] [decimal](38, 20) NOT NULL,
[Allow Invoice Disc_] [tinyint] NOT NULL,
[Gross Weight] [decimal](38, 20) NOT NULL,
[Net Weight] [decimal](38, 20) NOT NULL,
[Units per Parcel] [decimal](38, 20) NOT NULL,
[Unit Volume] [decimal](38, 20) NOT NULL,
[Appl_-to Item Entry] [int] NOT NULL,
[Shortcut Dimension 1 Code] [varchar](20) NOT NULL,
[Shortcut Dimension 2 Code] [varchar](20) NOT NULL,
[Customer Price Group] [varchar](10) NOT NULL,
[Job No_] [varchar](20) NOT NULL,
[Work Type Code] [varchar](10) NOT NULL,
[Shipment No_] [varchar](20) NOT NULL,
[Shipment Line No_] [int] NOT NULL,
[Bill-to Customer No_] [varchar](20) NOT NULL,
[Inv_ Discount Amount] [decimal](38, 20) NOT NULL,
[Drop Shipment] [tinyint] NOT NULL,
[Gen_ Bus_ Posting Group] [varchar](10) NOT NULL,
[Gen_ Prod_ Posting Group] [varchar](10) NOT NULL,
[VAT Calculation Type] [int] NOT NULL,
[Transaction Type] [varchar](10) NOT NULL,
[Transport Method] [varchar](10) NOT NULL,
[Attached to Line No_] [int] NOT NULL,
[Exit Point] [varchar](10) NOT NULL,
[Area] [varchar](10) NOT NULL,
[Transaction Specification] [varchar](10) NOT NULL,
[Tax Area Code] [varchar](20) NOT NULL,
[Tax Liable] [tinyint] NOT NULL,
[Tax Group Code] [varchar](10) NOT NULL,
[VAT Bus_ Posting Group] [varchar](10) NOT NULL,
[VAT Prod_ Posting Group] [varchar](10) NOT NULL,
[Blanket Order No_] [varchar](20) NOT NULL,
[Blanket Order Line No_] [int] NOT NULL,
[VAT Base Amount] [decimal](38, 20) NOT NULL,
[Unit Cost] [decimal](38, 20) NOT NULL,
[System-Created Entry] [tinyint] NOT NULL,
[Line Amount] [decimal](38, 20) NOT NULL,
[VAT Difference] [decimal](38, 20) NOT NULL,
[VAT Identifier] [varchar](10) NOT NULL,
[IC Partner Ref_ Type] [int] NOT NULL,
[IC Partner Reference] [varchar](20) NOT NULL,
[Prepayment Line] [tinyint] NOT NULL,
[IC Partner Code] [varchar](20) NOT NULL,
[Job Task No_] [varchar](20) NOT NULL,
[Job Contract Entry No_] [int] NOT NULL,
[Variant Code] [varchar](10) NOT NULL,
[Bin Code] [varchar](20) NOT NULL,
[Qty_ per Unit of Measure] [decimal](38, 20) NOT NULL,
[Unit of Measure Code] [varchar](10) NOT NULL,
[Quantity (Base)] [decimal](38, 20) NOT NULL,
[FA Posting Date] [datetime] NOT NULL,
[Depreciation Book Code] [varchar](10) NOT NULL,
[Depr_ until FA Posting Date] [tinyint] NOT NULL,
[Duplicate in Depreciation Book] [varchar](10) NOT NULL,
[Use Duplication List] [tinyint] NOT NULL,
[Responsibility Center] [varchar](10) NOT NULL,
[Cross-Reference No_] [varchar](20) NOT NULL,
[Unit of Measure (Cross Ref_)] [varchar](10) NOT NULL,
[Cross-Reference Type] [int] NOT NULL,
[Cross-Reference Type No_] [varchar](30) NOT NULL,
[Item Category Code] [varchar](10) NOT NULL,
[Nonstock] [tinyint] NOT NULL,
[Purchasing Code] [varchar](10) NOT NULL,
[Product Group Code] [varchar](10) NOT NULL,
[Appl_-from Item Entry] [int] NOT NULL,
[Return Reason Code] [varchar](10) NOT NULL,
[Allow Line Disc_] [tinyint] NOT NULL,
[Customer Disc_ Group] [varchar](10) NOT NULL,
[Package Tracking No_] [varchar](30) NOT NULL,
[Kit Item] [tinyint] NOT NULL,
[Build Kit] [tinyint] NOT NULL,
[List Price] [decimal](38, 20) NOT NULL,
[Calc_ Avg_ Cost] [decimal](38, 20) NOT NULL,
[List Cost] [decimal](38, 20) NOT NULL,
[Customer Quoted Cost] [decimal](38, 20) NOT NULL,
[Mfr_ Name] [varchar](20) NOT NULL,
[Mfr_ Part No_] [varchar](50) NOT NULL,
[Cust_ Item No_] [varchar](50) NOT NULL,
[On Hand Qty_] [decimal](38, 20) NOT NULL,
[Available Qty_] [decimal](38, 20) NOT NULL,
[Uncommitted Qty_] [decimal](38, 20) NOT NULL,
[Last Direct Cost] [decimal](38, 20) NOT NULL,
[Last Direct Cost Date] [datetime] NOT NULL,
[Cross Reference Item] [varchar](50) NOT NULL,
[Cross Ref_ Unit of Measure] [varchar](10) NOT NULL,
[Cross Reference Type] [int] NOT NULL,
[Cross Reference Type Code] [varchar](30) NOT NULL,
[Original Ordered No_] [varchar](20) NOT NULL,
[Out of Stock Subst_] [tinyint] NOT NULL,
[Substitution Available] [tinyint] NOT NULL,
[xNonStock] [tinyint] NOT NULL,
[xPurchasing Code] [varchar](10) NOT NULL,
[PO Alloc_ Entered] [tinyint] NOT NULL,
[PO Exisits] [tinyint] NOT NULL,
[Old Cust_ Line No_] [int] NOT NULL,
[To Convert] [tinyint] NOT NULL,
[Converted] [tinyint] NOT NULL,
[Cust_ Line No_] [varchar](10) NOT NULL,
[To Create RFQ] [tinyint] NOT NULL,
[RFQ Created] [tinyint] NOT NULL,
[To Create Req_] [tinyint] NOT NULL,
[Req_ Created] [tinyint] NOT NULL,
[Qty_ Committed] [decimal](38, 20) NOT NULL,
[Qty_ Committed (Base)] [decimal](38, 20) NOT NULL,
[Special Order Purchase No_] [varchar](20) NOT NULL,
[Special Order Purch_ Line No_] [int] NOT NULL,
[Special Order] [tinyint] NOT NULL,
[Pricing UOM] [int] NOT NULL,
[Qty_ per Pricing UOM] [decimal](38, 20) NOT NULL,
[Retail Price] [decimal](38, 20) NOT NULL,
[Line Type] [int] NOT NULL,
[Base UOM] [varchar](10) NOT NULL,
[Current Commitment] [decimal](38, 20) NOT NULL,
[Current Commitment from PO No_] [varchar](10) NOT NULL,
[Current Comm_ from PO Line No_] [int] NOT NULL,
[Buy From Vendor No_] [varchar](10) NOT NULL,
[Vendor Name] [varchar](50) NOT NULL,
[Vendor Item No_] [varchar](50) NOT NULL,
[Expected Receipt Date] [datetime] NOT NULL,
[Vendor Quote Date] [datetime] NOT NULL,
[Vendor Quote Ref_ No_] [varchar](20) NOT NULL,
[Vendor Quoted By] [varchar](30) NOT NULL,
[Country of Destination] [varchar](30) NOT NULL,
[Ship From] [varchar](30) NOT NULL,
[Comment for PO Requisition] [varchar](80) NOT NULL,
[Weight] [decimal](38, 20) NOT NULL,
[Return to Vendor] [varchar](20) NOT NULL,
[DM Order No_] [varchar](20) NOT NULL,
[DM Line No_] [int] NOT NULL,
[Quote Date] [datetime] NOT NULL,
[Qty_ Not Invoiced] [decimal](38, 20) NOT NULL,
[Hazmat Information] [varchar](50) NOT NULL,
[Promised Vendor Ship Date] [datetime] NOT NULL,
[SO No_] [varchar](20) NOT NULL,
[XML RFQ Line Number] [varchar](10) NOT NULL,
[XML RFQ Resource OI] [varchar](10) NOT NULL,
[XML Resource Description] [varchar](100) NOT NULL,
[XML UOMCode] [varchar](10) NOT NULL,
[XML Resource ID] [varchar](20) NOT NULL,
[XML Supplier Prod_ Number] [varchar](30) NOT NULL,
[XML Date Required] [varchar](10) NOT NULL,
[XML Lead Time (Days)] [int] NOT NULL,
[XML Mfr_ Name] [varchar](20) NOT NULL,
[XML Mfr_ Part No_] [varchar](50) NOT NULL,
[Original Quote No_] [varchar](20) NOT NULL,
[Original Line No_] [int] NOT NULL,
[Original Order Qty_] [decimal](38, 20) NOT NULL,
[First Shipment] [tinyint] NOT NULL,
[First Shipment Complete] [tinyint] NOT NULL,
[Exclude from Usage] [tinyint] NOT NULL,
[EDI Item Cross Ref_] [varchar](20) NOT NULL,
[EDI Unit of Measure] [varchar](10) NOT NULL,
[EDI Segment Group] [int] NOT NULL,
[EDI Release No_] [varchar](10) NOT NULL,
[EDI Ship Req_ Date] [datetime] NOT NULL,
[EDI Kanban No_] [varchar](10) NOT NULL,
[Shipping Charge] [tinyint] NOT NULL,
[Qty_ Packed (Base)] [decimal](38, 20) NOT NULL,
[Pack] [tinyint] NOT NULL,
[Rate Quoted] [tinyint] NOT NULL,
[Std_ Package Unit of Meas Code] [varchar](10) NOT NULL,
[Std_ Package Quantity] [decimal](38, 20) NOT NULL,
[Qty_ per Std_ Package] [decimal](38, 20) NOT NULL,
[Std_ Package Qty_ to Ship] [decimal](38, 20) NOT NULL,
[Std_ Packs per Package] [int] NOT NULL,
[Package Quantity] [decimal](38, 20) NOT NULL,
[Package Qty_ to Ship] [decimal](38, 20) NOT NULL,
[E-Ship Whse_ Outst_ Qty (Base)] [decimal](38, 20) NOT NULL,
[Shipping Charge BOL No_] [varchar](20) NOT NULL,
[Required Shipping Agent Code] [varchar](10) NOT NULL,
[Required E-Ship Agent Service] [varchar](30) NOT NULL,
[Allow Other Ship_ Agent_Serv_] [tinyint] NOT NULL,
[Quoted Cost (LCY)] [decimal](38, 20) NOT NULL,
[Vendor Quoted Cost] [decimal](38, 20) NOT NULL,
[Vendor Quoted By Currency] [varchar](10) NOT NULL,
[Vendor Qtd_ By Currency Factor] [decimal](38, 20) NOT NULL,
[Profit %] [decimal](38, 20) NOT NULL,
[Profit Amount ($)] [decimal](38, 20) NOT NULL,
[Posting Date] [datetime] NOT NULL,
CONSTRAINT [GK Live$Sales Invoice Line$0] PRIMARY KEY CLUSTERED
(
[Document No_] ASC,
[Line No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data Filegroup 1]
) ON [Data Filegroup 1]
GO
SET ANSI_PADDING OFF
GO
July 27, 2011 at 7:19 am
Could you clarify what you're looking for in the result set. As you can see from the back-n-forth here, we all have different thoughts about what you're looking for.
July 27, 2011 at 7:23 am
Would you mind to add data-population script, so tables would have sample data to play with. Also, sample of how expected results should look like will help a lot.
And just to make sure, are you looking for solution or directions?
July 27, 2011 at 8:54 am
I believe that the basic problem you are having is in your GROUP BY within the first query. You have the following fields being used in calculations only but they are also listed in the grouping.
Try running your query again but remove
,sil.Quantity
,sil.[Unit Price]
,sil.[Unit Cost (LCY)]
,sil.[Retail Price]
,sil.[Vendor Quoted Cost]
from the Group By.
I think this will solve your issue.
July 27, 2011 at 9:05 am
Kath Smith (7/27/2011)
I believe that the basic problem you are having is in your GROUP BY within the first query. You have the following fields being used in calculations only but they are also listed in the grouping.Try running your query again but remove
,sil.Quantity
,sil.[Unit Price]
,sil.[Unit Cost (LCY)]
,sil.[Retail Price]
,sil.[Vendor Quoted Cost]
from the Group By.
I think this will solve your issue.
Thats what I thought at first but I get the below error
Msg 8120, Level 16, State 1, Line 15
Column 'dbo.GK Live$Sales Invoice Line.Quantity' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 15
Column 'dbo.GK Live$Sales Invoice Line.Unit Price' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 15
Column 'dbo.GK Live$Sales Invoice Line.Quantity' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 15
Column 'dbo.GK Live$Sales Invoice Line.Unit Cost (LCY)' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 17
Column 'dbo.GK Live$Sales Invoice Line.Quantity' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 17
Column 'dbo.GK Live$Sales Invoice Line.Quantity' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 17
Column 'dbo.GK Live$Sales Invoice Line.Unit Price' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 17
Column 'dbo.GK Live$Sales Invoice Line.Unit Price' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 145, Level 15, State 1, Line 2
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
July 27, 2011 at 9:21 am
Brandie Tarvin (7/27/2011)
Could you clarify what you're looking for in the result set. As you can see from the back-n-forth here, we all have different thoughts about what you're looking for.
Brandie,
Yes, My apologies for not being clear. As you asked earlier I am looking to create a report using SSRS.
We are looking to move from the Navision reporting engine to SSRS and I am learning, trial by fire, how to use the ssrs tools.
The ideal result set would be a summarization of values from the invoice line table but I also need customer information which is obtained in the invoice header table. For example
Invoice Number Invoice Date Customer Amount Profit Profit%
12345 7/15/2011 abc_comp 242.06 121.03 50.00
However my invoice line table looks like the following
invoice_number Item Amount vendor_cost profit
12345 8484 159.57 79.79 79.78
12345 6210 82.49 41.25 41.24
This is just an example as there can be many item numbers per invoice_number.
Also I will get an insert into statement with data up soon. Sorry for the delay. You folks are fast on the posts.
July 27, 2011 at 10:41 am
kwoznica (7/27/2011)
Brandie Tarvin (7/27/2011)
Could you clarify what you're looking for in the result set. As you can see from the back-n-forth here, we all have different thoughts about what you're looking for.Brandie,
Yes, My apologies for not being clear. As you asked earlier I am looking to create a report using SSRS.
We are looking to move from the Navision reporting engine to SSRS and I am learning, trial by fire, how to use the ssrs tools.
The ideal result set would be a summarization of values from the invoice line table but I also need customer information which is obtained in the invoice header table. For example
Invoice Number Invoice Date Customer Amount Profit Profit%
12345 7/15/2011 abc_comp 242.06 121.03 50.00
However my invoice line table looks like the following
invoice_number Item Amount vendor_cost profit
12345 8484 159.57 79.79 79.78
12345 6210 82.49 41.25 41.24
This is just an example as there can be many item numbers per invoice_number.
Also I will get an insert into statement with data up soon. Sorry for the delay. You folks are fast on the posts.
ok I grabbed your code and wrote it with the fields I have within my data here. Then re-did your code with your fields. I think this will work for you with the exception there is still the possible division by zero issue.
Your fields code:
select
sil.[Document No_] AS 'Invoice Number'
,sih.[Posting Date] AS 'Invoice Date'
,sih.[Sell-to Customer No_] AS 'Bill-To Customer'
,sih.[Ship-to Name] AS 'Name'
,sih.[Payment Terms Code] AS 'Payment Terms'
,sih.[Order No_] AS 'Order No.'
,sih.[Salesperson Code] AS 'Person'
,SUM((sil.[Quantity] * sil.[Unit Price])) AS Amount
,SUM([Retail Price]-[Vendor Quoted Cost]) AS Profit
,((( sum(sil.[Quantity] * sil.[Unit Price]) - sum(sil.[Quantity] * sil.[Unit Cost (LCY)]))
/ sum(isnull(sil.[Quantity],0.0) * sum(isnull(sil.[Unit Price],0.0)))* 100)) AS 'Profit%'
From [dbo].[GK Live$Sales Invoice Line] AS sil
Inner Join [dbo].[GK Live$Sales Invoice Header] AS sih ON sih.No_ = sil.[Document No_]
WHERE sih.[Posting Date] = '07/15/2011'
GROUP BY
sil.[Document No_]
,sih.[Posting Date]
,sih.[Sell-to Customer No_]
,sih.[Ship-to Name]
,sih.[Payment Terms Code]
,sih.[Order No_]
,sih.[Salesperson Code]
Order by sil.[Document No_]
From my data fields mine is working from below but again I have different table layouts and also needed to link to a third table for cost per unit:
select
sil.IHl_INVOICE_NUMBER AS 'Invoice Number'
,sih.IHH_DATE_POSTED AS 'Invoice Date'
,sih.IHH_CUST_NUMBER AS 'Bill-To Customer'
,sih.IHH_CUST_NUMBER AS 'Name'
,sih.IHH_CHARGE_TYPE AS 'Payment Terms'
,sih.IHH_ORDER_NUMBER AS 'Order No.'
,sih.IHH_SALESMAN_ID AS 'Person'
, sum((sil.IHL_QTY_SHIPPED * sil.IHL_PRICE)) AS 'Amount'
, sum(sih.IHH_NET_AMT - sih.IHH_COST_OF_GOODS) AS 'Profit'
, (( sum(sil.IHL_QTY_SHIPPED * sil.IHL_PRICE)- sum(sil.IHL_QTY_SHIPPED * Control_Cost)
/ ( sum(isnull(sil.IHL_QTY_SHIPPED,0.00)) * sum(isnull(sil.IHL_PRICE,0.00))) * 100)) AS 'Profit%'
From tbl_Invoice_lines AS sil
Inner Join tbl_Invoice_header AS sih ON sih.header_id = sil.fk_header_id
inner join tbl_product on IHL_PART_INT_NUM = product_number
WHERE sih.IHH_DATE_POSTED = '07/15/2011' and sil.IHl_INVOICE_NUMBER > 0
GROUP BY
sil.IHl_INVOICE_NUMBER
,sih.IHH_DATE_POSTED
,sih.IHH_CUST_NUMBER
,sih.IHH_CUST_NUMBER
,sih.IHH_CHARGE_TYPE
,sih.IHH_ORDER_NUMBER
,sih.IHH_SALESMAN_ID
order by sil.IHl_INVOICE_NUMBER
July 27, 2011 at 1:32 pm
Eugene Elutin (7/27/2011)
Would you mind to add data-population script, so tables would have sample data to play with. Also, sample of how expected results should look like will help a lot.And just to make sure, are you looking for solution or directions?
Eugene/Brandie,
Below are create table, and insert statements. Lastly is the script to produce the result sets.
USE [TEST]
GO
/****** Object: Table [dbo].[GK Live$Sales Invoice Header] Script Date: 07/27/2011 09:02:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[GK Live$Sales Invoice Header](
[No_] [varchar](20) NOT NULL,
[Sell-to Customer No_] [varchar](20) NOT NULL,
[Ship-to Name] [varchar](50) NOT NULL,
[Posting Date] [datetime] NOT NULL,
[Payment Terms Code] [varchar](10) NOT NULL,
[Salesperson Code] [varchar](10) NOT NULL,
[Order No_] [varchar](20) NOT NULL,
CONSTRAINT [GK Live$Sales Invoice Header$0] PRIMARY KEY CLUSTERED
(
[No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Primary]
) ON [Primary]
GO
SET ANSI_PADDING OFF
GO
INSERT INTO [Test].[dbo].[GK Live$Sales Invoice Header]
([No_]
,[Sell-to Customer No_]
,[Ship-to Name]
,[Posting Date]
,[Payment Terms Code]
,[Salesperson Code]
,[Order No_])
VALUES
('IXSCO-34206','9151','RMSC','2011-07-15 00:00:00.000','N30','CN','SO-19013'),
('IXSCO-34207','8181','ABCD','2011-07-15 00:00:00.000','N30','AD','SO-19014')
GO
Use [Test]
Go
Drop Table [dbo].[GK Live$Sales Invoice Line]
GO
USE [Test]
GO
/****** Object: Table [dbo].[GK Live$Sales Invoice Line] Script Date: 07/27/2011 09:03:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[GK Live$Sales Invoice Line](
[Key] [int] NOT NULL,
[Document No_] [varchar](20) NOT NULL,
[Quantity] [decimal](38, 20) NOT NULL,
[Unit Price] [decimal](38, 20) NOT NULL,
[Unit Cost (LCY)] [decimal](38, 20) NOT NULL,
[Unit Cost] [decimal](38, 20) NOT NULL,
[Retail Price] [decimal](38, 20) NOT NULL,
[Vendor Quoted Cost] [decimal](38, 20) NOT NULL,
CONSTRAINT [GK Live$Sales Invoice Line$0] PRIMARY KEY CLUSTERED
(
[Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Primary]
) ON [Primary]
GO
SET ANSI_PADDING OFF
GO
INSERT INTO [Test].[dbo].[GK Live$Sales Invoice Line]
([Key]
,[Document No_]
,[Quantity]
,[Unit Price]
,[Unit Cost (LCY)]
,[Unit Cost]
,[Retail Price]
,[Vendor Quoted Cost])
VALUES
('1','IXSCO-34206','1.00','159.57','69.24182','0.00','159.57','69.24'),
('2','IXSCO-34206','1.00','82.49','38.532','0.00','82.49','38.53'),
('3','IXSCO-34207','1.00','100.22','54.281','0.00','100.22','54.28')
GO
Finally the script to produce the results.
select
sil.[Document No_] AS 'Invoice Number'
,sih.[Posting Date] AS 'Invoice Date'
,sih.[Sell-to Customer No_] AS 'Bill-To Customer'
,sih.[Ship-to Name] AS 'Name'
,sih.[Payment Terms Code] AS 'Payment Terms'
,sih.[Order No_] AS 'Order No.'
,sih.[Salesperson Code] AS 'Person'
,SUM((sil.[Quantity])*(sil.[Unit Price])) AS Amount
,SUM([Retail Price]-[Vendor Quoted Cost]) AS Profit
,(
(
(
(sil.[Quantity] * sil.[Unit Price])-(sil.[Quantity] * sil.[Unit Cost (LCY)])
)
/(NULLIF(sil.[Quantity],0.0) * (NullIF(sil.[Unit Price],0.0)))* 100
)
) AS 'Profit%'
From [dbo].[GK Live$Sales Invoice Line] AS sil
Inner Join [dbo].[GK Live$Sales Invoice Header] AS sih ON sih.No_ = sil.[Document No_]
GROUP BY
sil.[Document No_]
,sih.[Posting Date]
,sih.[Sell-to Customer No_]
,sih.[Ship-to Name]
,sih.[Payment Terms Code]
,sih.[Order No_]
,sih.[Salesperson Code]
,sil.Quantity
,sil.[Unit Price]
,sil.[Unit Cost (LCY)]
,sil.[Retail Price]
,sil.[Vendor Quoted Cost]
Having sih.[Posting Date] = '07/15/2011'
Order by sil.[Document No_]
Expected results would look like this.
Invoice Num InvDate BillCust Name Terms OrderNum Person Amount Profit Profit%
IXSCO-34206 7/15/2011 9181 RMSC N30 SO-9013 CN 242.06 134.29 55.48
I condensed the table definitions to include only what is necessary as the original table definitions are to lengthy. Please let me know if more informaiton is needed as your help is greatly appreciated.
July 28, 2011 at 4:57 am
I'm taking a look at this now.
Just an FYI: Your DROP TABLE script will error out for those of us who don't have your table in our dbs. If you want to add these in, it's always best to do it in such a way that it will drop the table if it exists, and do nothing if it doesn't exist.
For user tables (not temp tables), I use the following script:
IF (SELECT Table_Name FROM Information_Schema.Tables
WHERE Table_Name = [GK Live$Sales Invoice Line]) IS NOT NULL
DROP TABLE [GK Live$Sales Invoice Line];
It's a good practice to check for existence before dropping something.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply