Very challenging TSQL Query

Question

Very challenging TSQL Query

Naveen J V

SSC-Addicted

Points: 446
More actions
October 16, 2017 at 8:58 am

#324088

Hi Experts,
I have a table (test) with the following data :
FL COL VAL
Coverage Coverage NULL
Premium Premium NULL
CALC1 CALC1 1000
CALC2 CALC1 * 0.50 NULL
CALC3 CALC2 + 10 NULL
and would like to get data like:
Coverage Premium CALC1 CALC2 CALC3
Null Null 1000 500 510

Queries for building table is given below:
create table test
(
FL varchar(20),
COL varchar(20),
VAL varchar(20)
);
insert into test (FL,COL,VAL)
values ('Coverage','Coverage',NULL),('Premium','Premium',NULL),('CALC1','CALC1',1000),('CALC2','CALC1 * 0.50',NULL),('CALC3','CALC2 + 10',NULL);
Please do help me to create a query to get the output as shown in the second table. First row shows column name from FL, values are derived like calc2=calc1*0.50 and calc3=calc2+10.
This is just an example, there might be different formula based on different columns.
Thanks in Advance.
Naveen

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

Blargh. I hate queries like this. Personally, I don't think SQL Server is the right choice for this type of syntax (and i imagine many others here will agree).

Do you have anything to determine the order in which things should be derived? For example, in your query, Calc3 relies on Calc2. If Calc3 is determined before Calc2 it'll have a value of NULL. Can people SAFELY assume that all of your calculation row values will have the name "calc" and then a integer representing the order in which is must be derived?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Why choose the hard way? Store 1000, 0.5 and 10 with their correct names on the same row and this becomes trivially easy.
What you are asking - the coding of this simple example - can be done very easily but the overall design is not generally viewed as an intelligent solution because it can be bloody slow and it can very quickly get out of control.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

hapsa10 SSC Rookie Points: 39 More actions · Answer 3

Podrías intentar hacer algo como esto.

;With CTE_Source As (
    Select Fl, Col
    , Val = Case
                When Val Is Null Then 0
                Else Case
                        When IsNumeric(Val) = 1 Then Cast(Val As Money)
                        Else 0
                     End
            End
    From dbo.Test
)

Select
     Sum(Case When t.[Coverage] IS Null Then 0 Else t.[Coverage] End) As [Coverage]
    , Sum(Case When t.[Premium] IS Null Then 0 Else t.[Premium] End) As [Premium]
    , Sum(Case When t.[CALC1] IS Null Then 0 Else t.[CALC1] End) As [CALC1]
    , Sum(Case When t.[CALC1] IS Null Then 0 Else t.[CALC1] End) * 0.50 As [CALC2]
    , (Sum(Case When t.[CALC1] IS Null Then 0 Else t.[CALC1] End) * 0.50) + 10 As [CALC3]
From(
        SELECT [Coverage] AS [Coverage], [Premium] AS [Premium], [CALC1] AS [CALC1]
        FROM ( SELECT Fl, Col, Val FROM CTE_Source ) As Data
        PIVOT ( SUM(Val) FOR Col IN( [Coverage], [Premium], [CALC1] ) ) AS pvt
    ) As t

Alan Burstein SSC Guru Points: 61136 More actions · Answer 4

hapsa10 - Monday, October 16, 2017 11:10 AM
PodrÃas intentar hacer algo como esto.
;With CTE_Source As (
    Select Fl, Col
    , Val = Case
                When Val Is Null Then 0
                Else Case
                        When IsNumeric(Val) = 1 Then Cast(Val As Money)
                        Else 0
                     End
            End
    From dbo.Test
)
Select
     Sum(Case When t.[Coverage] IS Null Then 0 Else t.[Coverage] End) As [Coverage]
    , Sum(Case When t.[Premium] IS Null Then 0 Else t.[Premium] End) As [Premium]
    , Sum(Case When t.[CALC1] IS Null Then 0 Else t.[CALC1] End) As [CALC1]
    , Sum(Case When t.[CALC1] IS Null Then 0 Else t.[CALC1] End) * 0.50 As [CALC2]
    , (Sum(Case When t.[CALC1] IS Null Then 0 Else t.[CALC1] End) * 0.50) + 10 As [CALC3]
From(
        SELECT [Coverage] AS [Coverage], [Premium] AS [Premium], [CALC1] AS [CALC1]
        FROM ( SELECT Fl, Col, Val FROM CTE_Source ) As Data
        PIVOT ( SUM(Val) FOR Col IN( [Coverage], [Premium], [CALC1] ) ) AS pvt
    ) As t

...Sum(Case When t.[CALC1] IS Null Then 0 Else t.[CALC1] End) As [CALC1]

Esto puede reducirse a

...Sum(ISNULL(t.[CALC1],0))

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jeff Moden SSC Guru Points: 1003943 More actions · Answer 5

hapsa10 - Monday, October 16, 2017 11:10 AM
PodrÃas intentar hacer algo como esto.
;With CTE_Source As (
    Select Fl, Col
    , Val = Case
                When Val Is Null Then 0
                Else Case
                        When IsNumeric(Val) = 1 Then Cast(Val As Money)
                        Else 0
                     End
            End
    From dbo.Test
)
Select
     Sum(Case When t.[Coverage] IS Null Then 0 Else t.[Coverage] End) As [Coverage]
    , Sum(Case When t.[Premium] IS Null Then 0 Else t.[Premium] End) As [Premium]
    , Sum(Case When t.[CALC1] IS Null Then 0 Else t.[CALC1] End) As [CALC1]
    , Sum(Case When t.[CALC1] IS Null Then 0 Else t.[CALC1] End) * 0.50 As [CALC2]
    , (Sum(Case When t.[CALC1] IS Null Then 0 Else t.[CALC1] End) * 0.50) + 10 As [CALC3]
From(
        SELECT [Coverage] AS [Coverage], [Premium] AS [Premium], [CALC1] AS [CALC1]
        FROM ( SELECT Fl, Col, Val FROM CTE_Source ) As Data
        PIVOT ( SUM(Val) FOR Col IN( [Coverage], [Premium], [CALC1] ) ) AS pvt
    ) As t

Bienvenido a bordo. Aparentemente puedes leer inglÃ©s. Por favor, responda en ingles.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003943 More actions · Answer 6

Alan.B - Monday, October 16, 2017 3:01 PM
hapsa10 - Monday, October 16, 2017 11:10 AM
PodrÃas intentar hacer algo como esto.
;With CTE_Source As (
    Select Fl, Col
    , Val = Case
                When Val Is Null Then 0
                Else Case
                        When IsNumeric(Val) = 1 Then Cast(Val As Money)
                        Else 0
                     End
            End
    From dbo.Test
)
Select
     Sum(Case When t.[Coverage] IS Null Then 0 Else t.[Coverage] End) As [Coverage]
    , Sum(Case When t.[Premium] IS Null Then 0 Else t.[Premium] End) As [Premium]
    , Sum(Case When t.[CALC1] IS Null Then 0 Else t.[CALC1] End) As [CALC1]
    , Sum(Case When t.[CALC1] IS Null Then 0 Else t.[CALC1] End) * 0.50 As [CALC2]
    , (Sum(Case When t.[CALC1] IS Null Then 0 Else t.[CALC1] End) * 0.50) + 10 As [CALC3]
From(
        SELECT [Coverage] AS [Coverage], [Premium] AS [Premium], [CALC1] AS [CALC1]
        FROM ( SELECT Fl, Col, Val FROM CTE_Source ) As Data
        PIVOT ( SUM(Val) FOR Col IN( [Coverage], [Premium], [CALC1] ) ) AS pvt
    ) As t
...Sum(Case When t.[CALC1] IS Null Then 0 Else t.[CALC1] End) As [CALC1]

Esto puede reducirse a
...Sum(ISNULL(t.[CALC1],0))

ouya oota. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003943 More actions · Answer 7

hapsa10 - Monday, October 16, 2017 11:10 AM
PodrÃas intentar hacer algo como esto.
;With CTE_Source As (
    Select Fl, Col
    , Val = Case
                When Val Is Null Then 0
                Else Case
                        When IsNumeric(Val) = 1 Then Cast(Val As Money)
                        Else 0
                     End
            End
    From dbo.Test
)
Select
     Sum(Case When t.[Coverage] IS Null Then 0 Else t.[Coverage] End) As [Coverage]
    , Sum(Case When t.[Premium] IS Null Then 0 Else t.[Premium] End) As [Premium]
    , Sum(Case When t.[CALC1] IS Null Then 0 Else t.[CALC1] End) As [CALC1]
    , Sum(Case When t.[CALC1] IS Null Then 0 Else t.[CALC1] End) * 0.50 As [CALC2]
    , (Sum(Case When t.[CALC1] IS Null Then 0 Else t.[CALC1] End) * 0.50) + 10 As [CALC3]
From(
        SELECT [Coverage] AS [Coverage], [Premium] AS [Premium], [CALC1] AS [CALC1]
        FROM ( SELECT Fl, Col, Val FROM CTE_Source ) As Data
        PIVOT ( SUM(Val) FOR Col IN( [Coverage], [Premium], [CALC1] ) ) AS pvt
    ) As t

That doesn't actually work as the OP requested. The goal is to read the formulas from the given table and apply them... not hard-code them.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110207 More actions · Answer 8

Computed columns would do.

Or a view - recreated when a formula in the table changed.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1003943 More actions · Answer 9

Naveen J V - Monday, October 16, 2017 8:58 AM
Hi Experts,
I have a table (test) with the following data :
FL COL VAL
Coverage Coverage NULL
Premium Premium NULL
CALC1 CALC1 1000
CALC2 CALC1 * 0.50 NULL
CALC3 CALC2 + 10 NULL
and would like to get data like:
Coverage Premium CALC1 CALC2 CALC3
Null Null 1000 500 510

Queries for building table is given below:
create table test
(
FL varchar(20),
COL varchar(20),
VAL varchar(20)
);
insert into test (FL,COL,VAL)
values ('Coverage','Coverage',NULL),('Premium','Premium',NULL),('CALC1','CALC1',1000),('CALC2','CALC1 * 0.50',NULL),('CALC3','CALC2 + 10',NULL);
Please do help me to create a query to get the output as shown in the second table. First row shows column name from FL, values are derived like calc2=calc1*0.50 and calc3=calc2+10.
This is just an example, there might be different formula based on different columns.
Thanks in Advance.
Naveen

I can sometimes understand the need to store things as EAVs (Entity, Attribute, Value) but if you need more than one "collection" of such things (known as the "entity), you're need to add another column to identify which entity you currently want to work with.

My other question would be... is the "VAL" from CALC 1 ever actually going to be from a table or not?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

FL	COL	VAL
Coverage	Coverage	NULL
Premium	Premium	NULL
CALC1	CALC1	1000
CALC2	CALC1 * 0.50	NULL
CALC3	CALC2 + 10	NULL