count - 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 2000 Forums

Transact-SQL (2000)

count

Author

Topic

mdhingra01
Posting Yak Master

179 Posts

Posted - 2006-01-23 : 13:46:00

I am trying to SUMMARIZE THIS DATA.working with

POSN_NO SURNAME ID RANK YR/MTH
029869 *** VACANT *** INSP 200501
029869 *** VACANT *** INSP 200502
029869 *** VACANT *** INSP 200503
029869 *** VACANT *** INSP 200504
029869 *** VACANT *** INSP 200505
029869 *** VACANT *** INSP 200506
029869 *** VACANT *** INSP 200507
029869 *** VACANT *** INSP 200508
016829 *** VACANT *** INSP 200502
016829 *** VACANT *** INSP 200503
016829 *** VACANT *** INSP 200504
016829 *** VACANT *** INSP 200505
016829 *** VACANT *** INSP 200506
016829 *** VACANT *** INSP 200507
016829 *** VACANT *** INSP 200508
016829 *** VACANT *** INSP 200509
016829 FRANCOIS 000088888 INSP 200501
016829 JOHNSON 000009999 INSP 200510
016829 JOHNSON 000009999 INSP 200511
016829 JOHNSON 000009999 INSP 200512
005891 LEMYRE 000011111 INSP 200501
005891 LEMYRE 000011111 INSP 200502
STE MELLON 000044444 INSP 200509
STE MELLON 000044444 INSP 200510
STE MELLON 000044444 INSP 200511
STE MELLON 000044444 INSP 200512
019048 NADEAU 000000555 INSP 200501
019048 NADEAU 000000555 INSP 200502
019048 NADEAU 000000555 INSP 200503
019048 NADEAU 000000555 INSP 200504
019048 NADEAU 000000555 INSP 200505
019048 NADEAU 000000555 INSP 200506
017766 OLIVER 001616161 INSP 200510
005891 SLOAN 000033335 INSP 200503
005891 SLOAN 000033335 INSP 200504
005891 SLOAN 000033335 INSP 200505
005891 SLOAN 000033335 INSP 200506
005891 SLOAN 000033335 INSP 200507
005891 SLOAN 000033335 INSP 200508
005891 SLOAN 000033335 INSP 200509
005891 SLOAN 000032225 INSP 200510
005891 SLOAN 000033335 INSP 200511
005891 SLOAN 000033335 INSP 200512

HERE IS THE QUERY i AM USING
select
a.erank,
b.pos_number,
CAST (count(distinct b.pos_number) AS NUMERIC)as pos_count,
CAST (count(b.pos_number)AS NUMERIC) as month_total,
CAST(
CASE
WHEN left(b.surname,3)!='***' THEN count(b.pos_number)else 0
END AS NUMERIC) as STF_mth,
CAST(
CASE
WHEN left(b.surname,3)='***' THEN count(b.pos_number)else 0
END AS NUMERIC) as VAC_mth,
FROM
a join b on a.erank=b.erank join c on b.unit_coll=c.unit_coll
where left(extracted,4)=2005 and a.erank='INSP'
group by
b.pos_number,
a.erank,
b.surname
order by
b.pos_number,
a.erank

THIS QURY RESULTS IN:

RANK POSN POS MONTH STF_ VAC_
_NO _COUNT COUNT MTH MTH
INSP 005891 1 2 2 0
INSP 005891 1 10 10 0
INSP 016829 1 12 4 8
INSP 017766 1 1 1 0
INSP 019048 1 6 6 0
INSP 029869 1 8 0 8
INSP STE 1 4 4 0

THIS SUMMARY IS TRYING IT COUNT THE POSITIONS AND COUNT THE TOTAL NUMBER OF MONTHS IN THE YEAR THAT THE POSITION WAS STAFFED VS VACANT. WHAT IS HAPPENEING THOUGH IS THAT BECUASE SURNAME IS PART OF THE CASE STATEMENT, I HAVE TO INCLUDE IN GROUP BY AND BECAUSE THERE WERE MULTIPLE PEOPLE WORKING ON THE SAME POSITIONS AT DIFFERENT TIMES OF THE MONTH, THE QUERY BREAKS THE COUNT INTO THE NUMBER OF PEOPLE THTA FILLED THAT POSITIONS IN THAT YEAR.

I WOULD LIKE TO SEE THE FOLLWOING RESULTS:
RANK POSN POS MONTH STF_ VAC_
_NO _COUNT COUNT MTH MTH
INSP 005891 1 12 12 0
INSP 016829 1 8 0 8
INSP 017766 1 1 1 0
INSP 019048 1 6 6 0
INSP 029869 1 8 0 8
INSP STE 1 4 4 0

ANY IDEA HOW TO ACHIEVE.
thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources