I'm having a hard time getting "yesterday's" row joined to pull historical data. I'm removing a lot of my code for readability, and am simply using "SELECT *" in lieu - it's a big one.
Here is a stripped version of the UNPIVOT:
SELECT *
FROM EODMarkHistory
UNPIVOT
(
Value
FOR Name IN (C0,C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11,C12
,P0,PLow,PHigh,P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12
,F0,F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F15,F16,F17,F18,F19,F20,F21,F22,F23,F24
,Q1,Q2,Q3,Q4,Q5,Q6,Q7,Q8)
) piv
INNER JOIN ...
All of these columns hold "mark" values, and I need to grab yesterday's "marks" to compare. I'm trying to keep from having to list each of those in a IF or CASE statement in a function. I wrote a stored procedure that works with dynamic SQL:
SET @MarkDate = DATEADD(DAY,-1,@MarkDate)
DECLARE @DynamicSQL nvarchar(800) = N'SELECT ' + @Column + ' FROM EODMarkHistory WHERE SubGroupCode=''' + @ProductCode + ''' AND MarkDate = ''' + CONVERT(varchar(10),@MarkDate,121) + ''''
exec sp_executesql @DynamicSQL
But of course dynamic SQL can't go into a function, this is a SP, so I can't call this inline.
Do I have any other options than listing all possible columns in a function? If we add columns in the future, maintenance becomes an issue..
I'm looking for a built-in, non-hacky way to accomplish this. If I must do it with hard coding in a function (or a cursor) I will. I was just hoping someone may know of a faster way to get dynamic SQL (or some other option with the same result) to work in this case? Preferably in-line. If in-line dynamic isn't possible, I can hard code it without assistance.
EDIT - small working example
Example Code Only - This is nowhere near correct...
DECLARE @Test TABLE(MarkDate varchar(10),C1 int, C2 int, C3 int, C4 int)
DECLARE @MarkDate datetime = '3/2/2017'
INSERT INTO @Test
VALUES
('3/2/2017',50,-50,100,-100)
,('3/1/2017',75,-75,125,-125)
SELECT * FROM @Test
--Current result
SELECT *
FROM @Test
UNPIVOT
(
VALUE
FOR Name IN (C1,C2,C3,C4)
) piv
--Wanted Result
SELECT '3/2/2017' AS MarkDate, 50 AS Value, 'C1' AS Name, 75 AS YestValue
UNION
SELECT '3/2/2017' AS MarkDate, -50 AS Value, 'C2' AS Name, -75 AS YestValue
UNION
SELECT '3/2/2017' AS MarkDate, 100 AS Value, 'C3' AS Name, 125 AS YestValue
UNION
SELECT '3/2/2017' AS MarkDate, -100 AS Value, 'C4' AS Name, -125 AS YestValue
This approach uses a TVF to create an EAV structure (Entity Attribute Value) from virtually any table, record, or data-set. Can apply be applied in a CROSS APPLY as well. See the comments at the bottom of the UDF.
Now, if you don't want a UDF, this approach can easily be used in a CROSS APPLY
Example
-- Create Some Sample Data
Declare @Test Table (MarkDate varchar(10),C1 int, C2 int, C3 int, C4 int)
Insert Into @Test Values ('3/2/2017',50,-50,100,-100),('3/1/2017',75,-75,125,-125)
-- Set Key Dates
Declare @Date1 date = '2017-03-01'
Declare @Date2 date = '2017-03-02'
-- Execute Code
Select MarkDate = @Date2
,Value = max(case when Entity=@Date2 then Value end)
,Name = Attribute
,YestValue = max(case when Entity=@Date1 then Value end)
From [dbo].[udf-EAV]((Select * from @Test Where MarkDate in (@Date1,@Date2) for XML RAW))
Group By Attribute
Returns
MarkDate Value Name YestValue
2017-03-02 50 C1 75
2017-03-02 -50 C2 -75
2017-03-02 100 C3 125
2017-03-02 -100 C4 -125
The UDF if Interested
CREATE FUNCTION [dbo].[udf-EAV](@XML xml)
Returns Table
As
Return (
with cteKey(k) as (Select Top 1 xAtt.value('local-name(.)','varchar(100)') From @XML.nodes('/row') As A(xRow) Cross Apply A.xRow.nodes('./@*') As B(xAtt))
Select Entity = xRow.value('@*[1]','varchar(50)')
,Attribute = xAtt.value('local-name(.)','varchar(100)')
,Value = xAtt.value('.','varchar(max)')
From @XML.nodes('/row') As A(xRow)
Cross Apply A.xRow.nodes('./@*') As B(xAtt)
Where xAtt.value('local-name(.)','varchar(100)') Not In (Select k From cteKey)
)
-- Notes: First Field in Query will be the Entity
-- Select * From [dbo].[udf-EAV]((Select UTCDate=GetUTCDate(),* From sys.dm_os_sys_info for XML RAW))