How do I create one SQL query to have MTD and YTD values in same row?

Question

0.00/5 (No votes)

See more:

SSRS-2008

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 31-Mar-22 8:03am

Ashan Glenn

Updated 18-Apr-22 5:31am

v2

Add a Solution

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)

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2022-04-01T00:43:00

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[^]

Maciej Los · Answer 2 · 2022-04-01T22:20:00

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[^]

How do I create one SQL query to have MTD and YTD values in same row?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0