Styjun

I'm writing a SQL query using a table. My requirement is that I need to generate two logical columns from one physical column with certain conditions. In SQL how to generate two logical columns in final result set?

I have so far tried using sub-queries to derive those logical columns. But that sub-query returns error when incorporate it as a column in main query.

Overall there are other tables which will be joined using SQL JOIN to derive respective columns.

Columns:

1. CarrierName NVARCHAR(10)


2. MonthDate DATETIME


3. Stage INT

Scenario:

In my SQL Server table there is a column called Stage of type int that contains values like 1, 2, 3, 4.

Now, I have two date criteria to apply on above column to derive two logical columns in final result set.

Criteria #1:

Get carriers from past 12 months and priors to past month end date and value of "CurrentStage" should be less than and derive "PriorStage"

Example:

Current month is: March 2019 (2019-03-25) or any given date
Past latest month end date would be: 2019-02-28

12 months prior to above past latest month would be:
From 2018-02-01 To 2019-01-31

Criteria #2:

Get Carriers from past latest month end date and derive "CurrentStage"

While writing two independent SQL SELECT statements I get my desired results.
My challenge is when I think them to integrate in one select statement.

I get this error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression

Code:

DECLARE @DATE DATETIME
SET @DATE = '2018-08-25';

--QUERY 1 - RECORDS WITH PREVIOUS MONTH END DATE
SELECT 
 T1.CarrierName AS 'Carrier_Number', 
 T1.Stage AS 'Monitoring_Stage–Current'
FROM 
 table1 T1
WHERE 
 T1.Stage IS NOT NULL AND 
 CONVERT(DATE, T1.MonthDate) = CONVERT(DATE, DATEADD(D, -(DAY(@DATE)), @DATE)) 

--QUERY 2 - RECORDS FROM PAST 12 MONTHS PRIOR PREVIOUS MONTH END DATE
SELECT 
 T2.CarrierName,
 T2.Stage AS 'Monitoring_Stage–Prior'
FROM
 table2 T2
WHERE 
 T2.Stage IS NOT NULL AND 
 CONVERT(DATE, T2.MonthDate) BETWEEN CONVERT(DATE, DATEADD(M, -12, DATEADD(D, -(DAY(@DATE)), @DATE))) 
 AND CONVERT(DATE, DATEADD(D, -(DAY(@DATE) + (DAY(DATEADD(D, -(DAY(@DATE)), @DATE)))), @DATE)) 
 AND T2.Stage) > (SELECT DISTINCT MAX(m.Stage AS INT))
 FROM table1 m 
 WHERE CONVERT(DATE, m.MonthDate) = CONVERT(DATE, DATEADD(D, -(DAY(@DATE)), @DATE)) 
 AND T2.CarrierName = m.CarrierName)

My final expected result set should contain below columns.
Where CurrentStage value is less than PriorStage value.

Expected Results

CarrierName | CurrentStage | PriorStage
--------------+--------------+-------------
C11122 | 1 | 2
C32233 | 3 | 4

Actual Result

I am looking for alternatives. I.e. CTE, Union, temp table etc.

Something like:

SELECT 
 CarrierName,
 Query 1 Result As 'CurrentStage',
 Query 2 Result As 'PrioreStage'
FROM
 table1

To improve this post, I am adding my response here. My resolution below for this posted question is still under evaluation hence not posting it as my final answer. But it really brought a light to my effort.

RESOLUTION:

SELECT 
 DISTINCT M.CarrierName, A.[CurrentStage], B.[PriorStage] 
 FROM
 --QUERY 1 - RECORDS WITH CURRENT MONTH END DATE
(SELECT M.CarrierName, M.CarrierID
 , Stage AS 'CurrentStage'
 FROM table1 M 
 WHERE M.Stage IS NOT NULL AND 
 CONVERT(date, M.MonthDate) = CONVERT(date, DATEADD(D,-(DAY(@DATE)), @DATE)) 
) 
A **inner join**
(
--QUERY 2 - RECORDS FROM PAST 12 MONTHS PRIOR CURRENT MONTH END DATE
SELECT M2.CarrierName, M2.CarrierID
 , Stage AS 'PriorStage'
 FROM table1 M2
 WHERE M2.Stage IS NOT NULL AND 
 CONVERT(date, M2.MonthDate) BETWEEN CONVERT(date, DATEADD(M, -12, DATEADD(D,-(DAY(@DATE)), @DATE))) 
 AND CONVERT(date, DATEADD(D,-(DAY(@DATE)+(DAY(DATEADD(D,-(DAY(@DATE)), @DATE)))), @DATE)) 
 AND M2.Stage > (SELECT DISTINCT max(m.Stage)
 FROM table1 m
 WHERE CONVERT(date, m.MonthDate) = CONVERT(date, DATEADD(D,-(DAY(@DATE)), @DATE)) AND
 M2.CarrierName = m.CarrierName
 )

) B on b.Carrier_Number = a.Carrier_Number
INNER JOIN table1 M ON A.CarrierID = M.CarrierID AND B.CarrierID = M.CarrierID