Grouping data - 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)

Grouping data

Author

Topic

rajsrangs
Starting Member

2 Posts

Posted - 2009-10-22 : 11:01:21

Tricky function

I have a table tblClaim with a field IsUserClaimed (bit field).
I did need a query which would return me the count of claims grouped by the month and then the isUserClaimed field.
I have the following query which lists tha data snapshot

SELECT Count(ClaimId) CountClaims ,
Year(ClaimDate) YearOfClaim ,
Month(ClaimDate) MonthOfClaim,
UserClaimed isUserClaimed
FROM tblRefUserClaimed tRUC
LEFT OUTER JOIN tblClaim tC
ON tRUC.UserClaimed = tC.IsUserClaimed
GROUP BY userclaimed, Year(ClaimDate), Month(ClaimDate)
ORDER BY Year(ClaimDate),Month(ClaimDate), UserClaimed

Count Year Month isUserClaimed
863 2009 8 0
59 2009 8 1
8 2009 9 1
1 2009 10 0
6 2009 10 1

However on close inspection it should include in the list the following for Month 9.

Count Year Month isUserClaimed
0/NULL 2009 9 0

Could someone help me please?

Rajesh

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-22 : 11:30:41

Rajesh,

tricky function?!? more like freaky :) Why the heck are you joining two tables on a bit field. what kind of design is that. This is new to me, never seen a table join on bit field. could this be a design issue?

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

rajsrangs
Starting Member

2 Posts

Posted - 2009-10-22 : 11:48:24

well it is a reference table / dummy. How else do I get record for each type of isUserClaimed?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources