Bucket Wise Order - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Bucket Wise Order

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2011-02-18 : 02:27:16

SELECT
CASE WHEN DATEDIFF(DAY,DueDate,CONVERT(datetime,'01/01/2011',103)) <= 30 THEN '<30'
WHEN DATEDIFF(DAY,DueDate,CONVERT(datetime,'01/01/2011',103)) > 30 AND DATEDIFF(DAY,DueDate,CONVERT(datetime,'01/01/2011',103))<= 60 THEN '30-60'
WHEN DATEDIFF(DAY,DueDate,CONVERT(datetime,'01/01/2011',103)) > 60 AND DATEDIFF(DAY,DueDate,CONVERT(datetime,'01/01/2011',103))<= 90 THEN '60-90'
WHEN DATEDIFF(DAY,DueDate,CONVERT(datetime,'01/01/2011',103)) > 90 AND DATEDIFF(DAY,DueDate,CONVERT(datetime,'01/01/2011',103))<= 120 THEN '90-120'
WHEN DATEDIFF(DAY,DueDate,CONVERT(datetime,'01/01/2011',103)) >120 THEN '>120'
END AS Bucket ,
SUM(PrincipleOS) AS [Principle Outstanding] ,
SUM(ArrearsPOD) AS [Principle Overdue],
SUM(ArrearInterestOD) AS [Interest Overdue],
SUM(ArrearsPOD)+SUM(ArrearInterestOD) AS [Total Overdue]
FROM [PARReport_January]
WHERE BranchName IS NOT NULL GROUP BY
CASE WHEN DATEDIFF(DAY,DueDate,CONVERT(datetime,'01/01/2011',103)) <= 30 THEN '<30'
WHEN DATEDIFF(DAY,DueDate,CONVERT(datetime,'01/01/2011',103)) > 30 AND DATEDIFF(DAY,DueDate,CONVERT(datetime,'01/01/2011',103))<= 60 THEN '30-60'
WHEN DATEDIFF(DAY,DueDate,CONVERT(datetime,'01/01/2011',103)) > 60 AND DATEDIFF(DAY,DueDate,CONVERT(datetime,'01/01/2011',103))<= 90 THEN '60-90'
WHEN DATEDIFF(DAY,DueDate,CONVERT(datetime,'01/01/2011',103)) > 90 AND DATEDIFF(DAY,DueDate,CONVERT(datetime,'01/01/2011',103))<= 120 THEN '90-120'
WHEN DATEDIFF(DAY,DueDate,CONVERT(datetime,'01/01/2011',103)) >120 THEN '>120'
END

OUTPUT IS:
<30 34660000.00 680946.40 38508.6 719455.00
>120 3675000.00 75392.70 3607.30 79000.00
30-60 3687500.00 76770.40 2189.60 78960.00
60-90 4440000.00 90769.20 2370.80 93140.00
90-120 2250000.00 49772.30 1227.70 51000.00

But i need it in order like this:
<30 34660000.00 680946.40 38508.6 719455.00
30-60 3687500.00 76770.40 2189.60 78960.00
60-90 4440000.00 90769.20 2370.80 93140.00
90-120 2250000.00 49772.30 1227.70 51000.00
>120 3675000.00 75392.70 3607.30 79000.00

Help me to solve this Issue ASAP....

Regards,
Kalaiselvan R
Love Yourself First....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-18 : 02:47:31

[code]select *
from (
<your existing query here>
) q
order by case Bucket
when '<30' then 1
when '30-60' then 2
when '60-90' then 3
. . . .
end
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources