Click here to Skip to main content
15,353,229 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have a Crystal Report that I'm converting to SSRS currently. This report has 3 columns for MTD(Month to Date values) and 3 columns for YTD(Year to Date values). I'm able to get those 3 columns separate but for the data accuracy I need to combine it in a one SQL statement to output in one row. I was able to get it almost close to it using some coaleasce functions in SQL. The issue is it would not let me use group by commands and throws me a error. What should I edit in my SQL query to the way that shows in the below Crystal Report?
Error :
Msg 512, Level 16, State 1, Line 106
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


Current Crystal Report that I'm converting into SSRS

EDIT : I just created a sample dbfiddle : DB Fiddle - SQL Database Playground[^]

In the Code 2 I have tried to use some with and unions but it is giving me some error as
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.


What I have tried:

Code 1
SQL
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
SELECT coalesce((Select ((SUM( "IS_vwSalesbyProductLineComp"."sls_amt")-SUM( "IS_vwSalesbyProductLineComp"."cost_amt"))/SUM( "IS_vwSalesbyProductLineComp"."sls_amt"))*100 ),0) AS G1MTD,
	   "IS_vwSalesbyProductLineComp"."Mainslspsn_name",
	   "IS_vwSalesbyProductLineComp"."PCSummarized",
	   coalesce ((SELECT SUM("IS_vwSalesbyProductLineComp"."qty_to_ship"-"IS_vwSalesbyProductLineComp"."qty_return_to_stk")FROM "100"."dbo"."IS_vwSalesbyProductLineComp"
   where Year = 2021
   and Month between 1 and 12 Group by Mainslspsn_name,PCSummarized),0) AS Qty_Sold,
	   coalesce ((SELECT SUM("IS_vwSalesbyProductLineComp"."sls_amt")FROM "100"."dbo"."IS_vwSalesbyProductLineComp"
   where Year = 2021
   and Month between 1 and 12 Group by Mainslspsn_name,PCSummarized),0) AS YTD_Sales,
	   coalesce ((SELECT SUM("IS_vwSalesbyProductLineComp"."cost_amt")FROM "100"."dbo"."IS_vwSalesbyProductLineComp"
   where Year = 2021
   and Month between 1 and 12 Group by Mainslspsn_name,PCSummarized),0) AS YTD_COGS,
	   coalesce((Select ((SUM( "IS_vwSalesbyProductLineComp"."sls_amt")-SUM( "IS_vwSalesbyProductLineComp"."cost_amt"))/SUM( "IS_vwSalesbyProductLineComp"."sls_amt"))*100 
	   FROM "100"."dbo"."IS_vwSalesbyProductLineComp"
   where Year = 2021
   and Month between 1 and 12 Group by Mainslspsn_name,PCSummarized),0) AS G2YTD,
		SUM("IS_vwSalesbyProductLineComp"."qty_to_ship"-"IS_vwSalesbyProductLineComp"."qty_return_to_stk") 
		AS Qty_Sold,
   		SUM("IS_vwSalesbyProductLineComp"."sls_amt") 
		AS MTD_Sales,
      	SUM("IS_vwSalesbyProductLineComp"."cost_amt") 
		AS MTD_COGS   
   FROM "100"."dbo"."IS_vwSalesbyProductLineComp"
   where Year = 2021
   and Month = 12
   Group by Mainslspsn_name,PCSummarized
   order by PCSummarized


Code 2
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
WITH 
T1 AS
(
SELECT (
       SELECT((SUM(IS_vwSalesbyProductLineComp.sls_amt) - SUM(IS_vwSalesbyProductLineComp.cost_amt)) / SUM(IS_vwSalesbyProductLineComp.sls_amt)) * 100) AS G1MTD, 
       IS_vwSalesbyProductLineComp.Mainslspsn_name, 
       IS_vwSalesbyProductLineComp.PCSummarized, 
       (
       SELECT SUM(IS_vwSalesbyProductLineComp.qty_to_ship - IS_vwSalesbyProductLineComp.qty_return_to_stk)
       FROM "100".dbo.IS_vwSalesbyProductLineComp
       WHERE Year = 2021
             AND Month BETWEEN 1 AND 12
       GROUP BY Mainslspsn_name, 
                PCSummarized) AS Qty_Sold, 
       (
       SELECT SUM(IS_vwSalesbyProductLineComp.sls_amt)
       FROM "100".dbo.IS_vwSalesbyProductLineComp
       WHERE Year = 2021
             AND Month BETWEEN 1 AND 12
       GROUP BY Mainslspsn_name, 
                PCSummarized) AS YTD_Sales, 
      (
       SELECT SUM(IS_vwSalesbyProductLineComp.cost_amt)
       FROM "100".dbo.IS_vwSalesbyProductLineComp
       WHERE Year = 2021
             AND Month BETWEEN 1 AND 12
       GROUP BY Mainslspsn_name, 
                PCSummarized) AS YTD_COGS, 
       (
       SELECT((SUM(IS_vwSalesbyProductLineComp.sls_amt) - SUM(IS_vwSalesbyProductLineComp.cost_amt)) / SUM(IS_vwSalesbyProductLineComp.sls_amt)) * 100
       FROM "100".dbo.IS_vwSalesbyProductLineComp
       WHERE Year = 2021
             AND Month BETWEEN 1 AND 12
       GROUP BY Mainslspsn_name, 
                PCSummarized) AS G2YTD, 
       SUM(IS_vwSalesbyProductLineComp.qty_to_ship - IS_vwSalesbyProductLineComp.qty_return_to_stk) AS Qty_Sold2, --> renamed because ambigous
       SUM(IS_vwSalesbyProductLineComp.sls_amt) AS MTD_Sales, 
       SUM(IS_vwSalesbyProductLineComp.cost_amt) AS MTD_COGS
FROM "100".dbo.IS_vwSalesbyProductLineComp
WHERE Year = 2021
      AND Month = 12
GROUP BY Mainslspsn_name, 
         PCSummarized
),
T2 AS 
(
SELECT 0 AS C1, '' AS C2, '' AS C3, 0 AS C4, 0 AS C5, 0 AS C6, 0 AS C7, 0 AS C8, 0 AS C9, 0 AS C10
),
T3 AS
(
SELECT * FROM T1
UNION 
SELECT * FROM T2
)
SELECT SUM(G1MTD) AS G1MTD, 
   (SELECT [text()] = Mainslspsn_name
       FROM "100".dbo.IS_vwSalesbyProductLineComp 
       FOR XML 
           PATH(''),
           TYPE
   ).value
     (
         './text()[1]', 
         'nvarchar(max)'
     ),
   (SELECT [text()] = PCSummarized
       FROM "100".dbo.IS_vwSalesbyProductLineComp 
       FOR XML 
           PATH(''),
           TYPE
   ).value
     (
         './text()[1]', 
         'nvarchar(max)'
     ),
       SUM(Qty_Sold) AS Qty_Sold,
       SUM(YTD_Sales) AS YTD_Sales,
       SUM(YTD_COGS) AS YTD_COGS,
       SUM(G2YTD) AS G2YTD,
       SUM(Qty_Sold2) AS Qty_Sold2,
       SUM(MTD_Sales) AS MTD_Sales,
       SUM(MTD_COGS) AS MTD_COGS
FROM T3
GROUP BY Mainslspsn_name, 
         PCSummarized
ORDER BY PCSummarized;
Posted
Updated 18-Apr-22 5:31am
v2
Comments
CHill60 1-Apr-22 4:52am
   
We need some sample data to be able to help you, not just a picture of the results. (It's also better to put your expected results into your question not as a link to an external site - many of us cannot use those links while at work)

In the absence of any sample data, start by addressing these points.

-- 100.dbo.IS_vwSalesbyProductLineComp is not a valid SQL identifier. SQL Identifiers must start with a letter or underscore

-- you don't need to qualify the table name in columns if only selecting from one table

-- if you are going to qualify the columns then use aliases instead of the full table name to make the script more readable e.g. instead of
SQL
select IS_vwSalesbyProductLineComp.sls_amt
from IS_vwSalesbyProductLineComp
use
SQL
select splc.sls_amt 
from IS_vwSalesbyProductLineComp splc

-- get rid of the delimiters on the column and table names - they are not necessary and just make your query unreadable

-- If you do use delimiters then use [ ] i.e. instead of "IS_vwSalesbyProductLineComp" use [IS_vwSalesbyProductLineComp] - again, it's more readable

-- your statement
Quote:
but for the data accuracy I need to combine it in a one SQL statement
is not valid. The number of SQL statements used has no bearing whatsoever on data accuracy. Obsessing with getting everything into one statement can be detrimental to clarity, accuracy (of your script, not the data) and performance.

-- there is absolutely no need for all those sub-queries - plus, as you have seen, you are getting multiple rows back

Get all of the data you need in separate rows - if you insist on it being a single SQL statement then use a Common Table Expression (CTE - WITH common_table_expression (Transact-SQL) - SQL Server | Microsoft Docs[^] ).

Then PIVOT those results to get the table you want - see SQL Server PIVOT Operator Explained Clearly By Practical Examples[^]
   
Comments
Maciej Los 2-Apr-22 3:30am
   
5ed!
At first glance... your query is really bad:
The most important mistake is: too many SELECT's!
You've got 1 main SELECT and then 4 sub-SELECT!

Try to re-write your query to be able to use less number of SELECT.
Check out every single subquery. If any of it returns more than one record, you have to improve that query.

In the meantime please, read this: Fix Msg 512 “Subquery returned more than 1 value” in SQL Server[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900