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.
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))ASBEGIN 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 OFFEND |
|
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 OptimizerTG |
|
|
|
|
|
|
|