Analytical function for my Q
-
Hi,
Is there any analytical function in sql server for achieve results like below:
I did this with 2 temp tables, but might be something already there to look it nicer.
I also need this group by DateIn, and display % for each delDays category
Thanks all
M
create table #log (shipID int, datein datetime, delDays int)
INSERT INTO #LOG (shipid , datein, delDays)
select 1001, '2012-01-06', 9 union all
select 1002, '2012-01-06', 11 union all
select 1002 , '2012-01-06', 11 union all
select 1003 , '2012-01-06', 11 union all
select 1004 , '2012-01-06', 11 union all
select 1005 , '2012-01-06', 11 union all
select 1006 , '2012-01-06', 11 union all
select 1007 , '2012-01-06', 11 union all
select 1008 , '2012-01-06', 11 union all
select 1009 , '2012-01-06', 11 union all
select 1010 , '2012-01-06', 22 union all
select 2001 , '2012-02-06', 15 union all
select 2002 , '2012-02-06', 16 union all
select 2003 , '2012-02-06', 33 union all
select 2004 , '2012-02-06', 22
-- Need this result result:
DateIn | <20days | <30days | >30days |
----------|----------|-----------------------
2012-01-06| 90% | 10% | |
2012-02-06| 50% | 25% | 25% |
-
Your expected results are off for the <30 days column (can't be less of a percent than <20 days), but something like this should do it:
SELECT datein
,[<20days]=ROUND(100.*SUM(CASE WHEN delDays < 20 THEN 1 END)/COUNT(*),0)
,[<30days]=ROUND(100.*SUM(CASE WHEN delDays < 30 THEN 1 END)/COUNT(*),0)
,[>30days]=ROUND(100.*SUM(CASE WHEN delDays > 30 THEN 1 END)/COUNT(*),0)
FROM #LOG
GROUP BY datein
If you meant >=20 and < 30 for the [<30 days] column just change the WHEN clause to:
CASE WHEN delDays BETWEEN 20 AND 30 THEN 1 END
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
Dwain is a master of minimilism and his response is certainly more succinct than mine. But I'll post what I came up with before Dwain beat me to it. I think the only redeeming quality I add is the number of days and the totals, but mine is a lot more verbose. :laugh:
SELECT
p.datein
,p.[<20DelDays]
,CAST(CAST(p.[<20DelDays] AS NUMERIC)/CAST(p.[TotalDelDays] AS NUMERIC) AS DECIMAL(6,2)) AS [<20DelDays%]
,p.[<30DelDays]
,CAST(CAST(p.[<30DelDays] AS NUMERIC)/CAST(p.[TotalDelDays] AS NUMERIC) AS DECIMAL(6,2)) AS [<20DelDays%]
,p.[>30DelDays]
,CAST(CAST(p.[>30DelDays] AS NUMERIC)/CAST(p.[TotalDelDays] AS NUMERIC) AS DECIMAL(6,2)) AS [<20DelDays%]
,p.TotalDelDays
FROM
(
SELECT DISTINCT
r.datein
,ISNULL(SUM([<20DelDays]) OVER (PARTITION BY r.datein),0) AS [<20DelDays]
,ISNULL(SUM([<30DelDays]) OVER (PARTITION BY r.datein),0) AS [<30DelDays]
,ISNULL(SUM([>30DelDays]) OVER (PARTITION BY r.datein),0) AS [>30DelDays]
,(SELECT COUNT(ALL shipID) AS [TotalShipments]
FROM #log AS l2 WHERE datein = r.datein)
AS [TotalDelDays]
FROM
(
SELECT
l.datein
,l.delDays
,l.shipID
,(SELECT
COUNT(delDays)
WHERE
datein = l.datein
AND delDays < 20)
AS [<20DelDays]
,(SELECT
COUNT(delDays)
WHERE
datein = l.datein
AND delDays < 30
AND delDays >= 20)
AS [<30DelDays]
,(SELECT
COUNT(delDays)
WHERE
datein = l.datein
AND delDays > 30)
AS [>30DelDays]
FROM
#log AS l
GROUP BY
l.datein
,l.delDays
,l.shipID
) r
) p
ORDER BY
p.datein
-
Also...looking at the query plans and statistics, Dwain's solution is certainly more efficient if it satifies your requirements...
-
Steven Willis (7/29/2013)
Dwain is a master of minimilism...I like it. I'm trying to think of a way to incorporate it in my signature.
Reminds me of the quote ChrisM has in his sig:
"Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
dwain.c (7/29/2013)
Steven Willis (7/29/2013)
Dwain is a master of minimilism...I like it. I'm trying to think of a way to incorporate it in my signature.
Reminds me of the quote ChrisM has in his sig:
"Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
My SQL coding is like a tractor trailer...it gets the job done but carries a lot of baggage with it. Dwain's code is like a fine Italian sports car... 😛
-
Steven Willis (7/29/2013)
dwain.c (7/29/2013)
Steven Willis (7/29/2013)
Dwain is a master of minimilism...I like it. I'm trying to think of a way to incorporate it in my signature.
Reminds me of the quote ChrisM has in his sig:
"Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
My SQL coding is like a tractor trailer...it gets the job done but carries a lot of baggage with it. Dwain's code is like a fine Italian sports car... 😛
Yup, that's me all right...
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
Thanks all, guys!!!!
I'm very happy that so nice (not mentioned smart) people are around our planet !!!
Best to all.
Dwain, is this your trophy fish?
Mario
-
mario17 (7/29/2013)
Dwain, is this your trophy fish?Mario
That is my 45kg Mekong Catfish! Not a record by any means (they grow well over 100kg) but a record for me.
And you're welcome.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
dwain.c (7/29/2013)
Your expected results are off for the <30 days column (can't be less of a percent than <20 days), but something like this should do it:SELECT datein
,[<20days]=ROUND(100.*SUM(CASE WHEN delDays < 20 THEN 1 END)/COUNT(*),0)
,[<30days]=ROUND(100.*SUM(CASE WHEN delDays < 30 THEN 1 END)/COUNT(*),0)
,[>30days]=ROUND(100.*SUM(CASE WHEN delDays > 30 THEN 1 END)/COUNT(*),0)
FROM #LOG
GROUP BY datein
If you meant >=20 and < 30 for the [<30 days] column just change the WHEN clause to:
CASE WHEN delDays BETWEEN 20 AND 30 THEN 1 END
A couple of things to bear in mind:
1. Both the lower and upper values in the BETWEEN clause are included in the return results e.g. Dwain's last example will include values of 20 and 30. An alternative is to use this construct:
CASE WHEN delDays>=20 AND delDays<30 THEN 1 END
2. Having logic that reads "<30" and ">30" misses out values that are spot on 30. May make more sense to choose all of your lower bounds to be <= and upper ones to be >, or similar.
Forum Etiquette: How to post data/code on a forum to get the best helpMake sure that you include code in the appropriate IFCode tags, e.g.
<your code here>. You can find the IFCode tags under the INSERT options when you are writing a post.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply