August 21, 2017 at 10:53 pm
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
ItemID INT,
ItemName Varchar(100),
ItemsubType varchar(100),
Roylaty DECIMAL(9,2),
advance DECIMAL(9,2),
Rights DECIMAL(9,2),
TotalSales DECIMAL(9,2),
Totalqty int
)
Insert into #mytable values(1,'Item1','ItemsubType1',16659.14,-30000.00,138.2,175450.41,3784)
Insert into #mytable values(2,'Item1','ItemsubType2',4101.10,0.00,0.00,19207.73,1600)
Insert into #mytable values(3,'Item2','ItemsubType3',1659.14,600.00,138.2,75450.41,1784)
Insert into #mytable values(4,'Item3','ItemsubType4',12259.14,-10000.00,100.2,13450.41,2584)
Insert into #mytable values(5,'Item5','ItemsubType5',16659.14,-30000.00,200.2,14450.41,1584)
Insert into #mytable values(6,'Item5','ItemsubType6',16659.14,-30000.00,300.2,15450.41,1184)
Select
ItemID,Itemname,ItemsubType,qty,sales,unearnedadv,
CASE
WHEN ( sales = 0) THEN 0
ELSE 1-(isnull(advance,0) + isnull(Roylaty,0) + isnull(unearnedadv,0)) / sales
END AS Profitpercent
from(
Select ItemID,Itemname,ItemsubType,sum(TotalQty) as qty,sum(TotalSales) as sales,advance,roylaty,rights,
CASE WHEN (isnull(Roylaty,0)+isnull(advance,0)) =0 THEN 0
ELSE
CASE WHEN isnull(Roylaty,0)+isnull(Advance,0) + isnull(Rights,0) >0 THEN 0
ELSE isnull(Roylaty,0)+isnull(Advance,0) + isnull(Rights,0)
END
END as unearnedadv
from #mytable
group by itemid,Itemname,ItemsubType,Roylaty,Advance,Rights) a
I have lot of other calculated columns in actual query which are like unearnedadv in the above sql.They inturn used to calculate some other columns in the upper select statement.
But because of some issues in data, i changed select statement and end up calculating the unearnedadv in the upper select statement(got rid of the calculation in the subquery) in my procedures which resulted in lots of case statements while calculating the Profitpercent.
Can someone help me if there a way i can simply my query using advanced sql functions ?
I have posted the above sql for just as reference.If i can get some sample query's or examples i will try to analyse
Thanks.
August 22, 2017 at 1:46 am
The first thing is you don't seem to quite understand CASE expressions. There is often little need to NEST them like you have done here. If you have a different expression to resolve you can just add an extra WHEN (no need for CASE). We can, however, simplify that section a little more, as you're testing for 0 and then >0 but evaluate the same result. Thus this will do the same:CASE WHEN (ISNULL(Roylaty,0) + ISNULL(advance,0)) >= 0 THEN 0
ELSE ISNULL(Roylaty,0) + ISNULL(Advance,0) + ISNULL(Rights,0)
END as unearnedadv
I wouldn't have, however, said that you SQL was overly complex. What are you looking to simplify exactly? it is quite concise in all honesty (after changing the CASE expression). If you wanted, you could change the Sub query into a WITH, but they are very similar.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 22, 2017 at 12:10 pm
Most often you can use CROSS APPLY to assign an alias name to a calculated value, which you can then use in the main query or in another CROSS APPLY, that is, an alias of a calc'd value can be used to alias another value -- very nice!
For example:
...
Select ItemID,Itemname,ItemsubType,sum(TotalQty) as qty,sum(TotalSales) as sales,advance,roylaty,rights,
unearnedadv
from #mytable
cross apply (
Select CASE WHEN (isnull(Roylaty,0)+isnull(advance,0)) = 0 THEN 0 Select CASE WHEN (isnull(Roylaty,0)+isnull(advance,0)) = 0 THEN 0
WHEN isnull(Roylaty,0)+isnull(Advance,0)+isnull(Rights,0) > 0 THEN 0 WHEN isnull(Roylaty,0)+isnull(Advance,0)+isnull(Rights,0) > 0 THEN 0
ELSE isnull(Roylaty,0)+isnull(Advance,0) + isnull(Rights,0) ELSE isnull(Roylaty,0)+isnull(Advance,0) + isnull(Rights,0)
END AS unearnedadv END AS unearnedadv
) As ca1 ) As ca1
...
Or, more generically:
SELECT *
FROM ( VALUES(1) ) AS test_data(column1)
CROSS APPLY (
SELECT GETDATE() AS work_date
) AS ca1
CROSS APPLY (
SELECT DATEADD(DAY, column1, ca1.work_date) AS work_date2
) AS ca2
CROSS APPLY (
SELECT DATEADD(MONTH, column1, ca2.work_date2) AS work_date3
) AS ca3
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 25, 2017 at 8:09 am
My understanding is you need to aggregate data and then do some calculation on top.
You can use CTE for your below group by query. this will not optimize your query, but it will be clean to use and maintain in future.With CTE as
(
Select ItemID,Itemname,ItemsubType,sum(TotalQty) as qty,sum(TotalSales) as sales,advance,roylaty,rights,
CASE WHEN (isnull(Roylaty,0)+isnull(advance,0)) =0 THEN 0
ELSE
CASE WHEN isnull(Roylaty,0)+isnull(Advance,0) + isnull(Rights,0) >0 THEN 0
ELSE isnull(Roylaty,0)+isnull(Advance,0) + isnull(Rights,0)
END
END as unearnedadv
from #mytable
group by itemid,Itemname,ItemsubType,Roylaty,Advance,Rights
)
select * from CTE
And as suggested earlier by Thom A, you can use single case statement for above criteria.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply