SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Trying to sort dates in a graph
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gschwab
Starting Member

7 Posts

Posted - 03/08/2013 :  12:35:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5932 Posts

Posted - 03/08/2013 :  13:30:36  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000