Ordering Issue - 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 2005 Forums

Transact-SQL (2005)

Ordering Issue

Author

Topic

Wozer
Starting Member

29 Posts

Posted - 2009-04-16 : 09:05:38

Hi,

I have a view that does a count of claims per contract per month. to create the view I am taking data from 2 tables. the contract is grabbed off the contract table and the claims from the claim table. The date is extracted from the claim table by an entry date with the following code:


RIGHT('0'+CAST(MONTH(Date) AS varchar),2) + CAST(DATEPART(YYYY, Date) AS nvarchar(4)) AS "newdate"

I am then using this view to put data on a report. The issue that I am running into is I want it to order by Contract and then by date so that my table looks like this:


                 Jan 07     Feb 07    ...  June 07
Contract 1           0        1               5
Contract 2          14        4               0
Contract 3          12        3               1

But, if there is no count for Jan 07 in contract 1, then the view doesn't have a row for that entry, so it skips January and picks it up in Contract 2 so instead the table looks like this:


                 Feb 07     ....    June 07      Jan 07
Contract 1           1                 5            0
Contract 2           4                 0           14
Contract 3           3                 1           12

My code for the view is this: I used a right outer Join in hoping that there would be a count for dates without a contract, but obviously that didn't work because the date is extracted and not an actual column. Any ideas how to make it so for Contract 1 Jan 07 there is a row that shows up as

ClaimCount Contract Date

null Contract1 012007

Thanks

[code]
ALTER VIEW [dbo].[openclaim]
AS
SELECT
count(dbo.claim.claimid) as "Count",
dbo.contract.name,
RIGHT('0'+CAST(MONTH(Date) AS varchar),2) + CAST(DATEPART(YYYY, Date) AS nvarchar(4)) AS "OpenMonthYear",

FROM claim
RIGHT OUTER JOIN dbo.contract ON dbo.claim.contractID = dbo.contract.contractID
GROUP BY RIGHT('0'+CAST(MONTH(Date) AS varchar),2) + CAST(DATEPART(YYYY, Date) AS nvarchar(4)), dbo.contract.Name

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-16 : 10:05:19

I would create a stored procedure based on this view to make it do what you want it to do. Views will not be helpful in your case. With a proc you can do if no row for a specific month, force a bogus row.
So, you are using an entry date for the claim date? Isn't that problematic? What happens if user goes into claim and changes one of the values in the claim. Does the entry date then change? What if for some reason the date on the server is messed up during creation of the claim record?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-16 : 14:16:55

i think it would be better for you to create a calendar table with all month values and then left join your table with it to get counts. for months without data show 0 using isnull() or coalesce() function

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources