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
 General SQL Server Forums
 New to SQL Server Programming
 Trying to sort dates in a graph

Author  Topic 

gschwab
Starting Member

7 Posts

Posted - 2013-03-08 : 12:35:47
I'm trying to create a graph that lists the number of claims entered in the past 6 months. The graph works fine, but the months are not in order. I've tried to make if sort in Visual Studio with no luck. The frustrating thing is that sometimes the months are in order, but most of the the are just randomly sorted. Any idea how I can sort it in either my stored procedure or maybe an expression in Visual Studio? Thanks!

ALTER PROCEDURE [dbo].[Report_Select_NewClaimants_Graph2]
(
@InsurerID INT,
@DateFrom DATETIME,
@DateTo DATETIME,
@xClaimantStatusID VARCHAR(20)
)

AS

BEGIN
SET NOCOUNT ON

SELECT
cl.ClaimantID,
ct.ClaimantTypeDesc,
DATENAME(MONTH,c.EntryDate) AS MonthNames,
YEAR(c.EntryDate) AS Years
FROM
dbo.Claimant cl (NOLOCK)
INNER JOIN dbo.Claim c (NOLOCK)
ON cl.ClaimID = c.ClaimID
INNER JOIN dbo.ClaimantType ct
ON cl.ClaimantTypeID = ct.ClaimantTypeID
INNER JOIN Policy p (NOLOCK)
ON c.policyid = p.policyid
WHERE
p.insurerid = @InsurerID
AND c.EntryDate BETWEEN DATEADD(MONTH, -6, @DateTo) AND @DateTo
AND cl.ClaimantStatusID IN (select * from dbo.list_to_tbl(@xClaimantStatusID))
AND cl.ClaimantTypeID NOT IN(1,7,8,9,12,13)


SET NOCOUNT OFF
END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-03-08 : 13:30:36
One thing you will probably need is to return in the SP is the DATEPART(month, c.EntryDate) in addition to the DATENAME. That way whatever you use to render your graph will have something to sort by.

As far as the stored procedure goes the only way to guarantee an order is to use the ORDER BY clause. But even if you order it correctly in the SP output doesn't necessarily mean your graph will display that order.

Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -