• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

SUM the COUNTED records ACROSS a column SQL SERVER 2000

THIS IS A TIME SLIP SCENARIO...using SQL SERVER 2000

I have a table (exempt) with the days of the week represented as columns (MONam, MONpm, TUESam, TUESpm, WEDam, WEDpm and so on).

For each day (am and pm) I am allowing a FORM to pass a value of  P or S or H (p=present, S=Sick, H=holiday)

Edning up with...
MONam: P MONpm: P
TUEam: P TUEpm: P
WEDam: H WEDpm: H
THURam: P THURpm: P
FRIam: S FRIpm: S

I would like to COUNT the number of Ps, Ss and Hs across the column (for each record) and then SUM that COUNT.

Ending up with a summary for the week/username

P= 6*.5=3
H= 2*.5=1
S= 2*.5=1
TOTAL= 5

This is what I have so far:

SELECT COUNT(MONam,MONpm,TUESam,TUESpm,WEDam,WEDpm, THURam,THURpm,FRIam,FRIpm) AS hours, SUM(hours * .5) AS days FROM exempt WHERE (username =  '::username::')

Any assistance is MUCH appreciated!!
0
jvescio
Asked:
jvescio
1 Solution
 
pbleightonCommented:
My suggestion is tedious, and someone may have a slicker suggestion, but:
1) Create feeder queries for each day and value:
SELECT COUNT(*) AS hours, FROM exempt WHERE (username =  '::username::') and (MONam = 'P')
2) Create 3 union queries, one for each value:
select hours from qry_MONam_P
UNION
select hours from qry_MONpm_P
etc.
3) For each union query, create another query to do the sum
Use AS PHours, HHours, SHours
4) Your final query gets the sums from the 3 sum queries

A pain, but it will work.  Hope someone has a better idea.
0
 
RimvisCommented:
Try query like this:
(replace field names, add '* 0.5', etc.)

SELECT
SUM(CASE X1 WHEN 'P' THEN 1 ELSE 0 END
+ CASE X2 WHEN 'P' THEN 1 ELSE 0 END
+ CASE X3 WHEN 'P' THEN 1 ELSE 0 END
+ CASE X4 WHEN 'P' THEN 1 ELSE 0 END
+ CASE X5 WHEN 'P' THEN 1 ELSE 0 END
+ CASE X6 WHEN 'P' THEN 1 ELSE 0 END
+ CASE X7 WHEN 'P' THEN 1 ELSE 0 END)AS Ps,
SUM(CASE X1 WHEN 'S' THEN 1 ELSE 0 END
+ CASE X2 WHEN 'S' THEN 1 ELSE 0 END
+ CASE X3 WHEN 'S' THEN 1 ELSE 0 END
+ CASE X4 WHEN 'S' THEN 1 ELSE 0 END
+ CASE X5 WHEN 'S' THEN 1 ELSE 0 END
+ CASE X6 WHEN 'S' THEN 1 ELSE 0 END
+ CASE X7 WHEN 'S' THEN 1 ELSE 0 END) AS Ss,
SUM(CASE X1 WHEN 'H' THEN 1 ELSE 0 END
+ CASE X2 WHEN 'H' THEN 1 ELSE 0 END
+ CASE X3 WHEN 'H' THEN 1 ELSE 0 END
+ CASE X4 WHEN 'H' THEN 1 ELSE 0 END
+ CASE X5 WHEN 'H' THEN 1 ELSE 0 END
+ CASE X6 WHEN 'H' THEN 1 ELSE 0 END
+ CASE X7 WHEN 'H' THEN 1 ELSE 0 END) AS Hs,
SUM(CASE WHEN X1 IN ('P', 'S', 'H') THEN 1 ELSE 0 END
+ CASE WHEN X1 IN ('P', 'S', 'H') THEN 1 ELSE 0 END
+ CASE WHEN X1 IN ('P', 'S', 'H') THEN 1 ELSE 0 END
+ CASE WHEN X1 IN ('P', 'S', 'H') THEN 1 ELSE 0 END
+ CASE WHEN X1 IN ('P', 'S', 'H') THEN 1 ELSE 0 END
+ CASE WHEN X1 IN ('P', 'S', 'H') THEN 1 ELSE 0 END
+ CASE WHEN X1 IN ('P', 'S', 'H') THEN 1 ELSE 0 END) * 0.5 AS TOTAL
 FROM days
0
 
SuomenlapinkoiraCommented:
It's probably too late now, but for the future I would suggest you normalise your tables properly. Normalisation theory may seem like mumbo-jumbo, but there are very good, practical, reasons for it, and you have found one of them.

pp Codd & Date :)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LowfatspreadCommented:
Select convert(float,sum(p)) * 0.5 as P,
       convert(float,sum(s)) * 0.5 as S,
       convert(float,sum(h)) * 0.5 as H,
       convert(float,Sum(p+s+h)) * 0.5 as Total
 From (Select
         Case when MonAm='P' Then 1 else 0 end
       + Case when Monpm='P' Then 1 else 0 end
          ...
       + Case when Sunpm='P' Then 1 else 0 end
       as P
       , Case when MonAm='S' Then 1 else 0 end
       + Case when Monpm='S' Then 1 else 0 end
          ...
       + Case when Sunpm='S' Then 1 else 0 end
       as S
       , Case when MonAm='H' Then 1 else 0 end
       + Case when Monpm='H' Then 1 else 0 end
          ...
       + Case when Sunpm='H' Then 1 else 0 end
       as H
       From Exempt
         where user=::username::
       ) as Ex
 
  is one solution......      
0
 
jvescioAuthor Commented:
WELL DONE!!!  This is your code after adding my infoL:

SELECT
SUM(CASE MONam WHEN 'WO' THEN 1 ELSE 0 END
+ CASE MONpm WHEN 'WO' THEN 1 ELSE 0 END
+ CASE TUEam WHEN 'WO' THEN 1 ELSE 0 END
+ CASE TUEpm WHEN 'WO' THEN 1 ELSE 0 END
+ CASE WEDam WHEN 'WO' THEN 1 ELSE 0 END
+ CASE WEDpm WHEN 'WO' THEN 1 ELSE 0 END
+ CASE THURam WHEN 'WO' THEN 1 ELSE 0 END
+ CASE THURpm WHEN 'WO' THEN 1 ELSE 0 END
+ CASE FRIam WHEN 'WO' THEN 1 ELSE 0 END
+ CASE FRIpm WHEN 'WO' THEN 1 ELSE 0 END)  * 0.5 AS WOs,
SUM(CASE MONam WHEN 'J' THEN 1 ELSE 0 END
+ CASE MONpm WHEN 'J' THEN 1 ELSE 0 END
+ CASE TUEam WHEN 'J' THEN 1 ELSE 0 END
+ CASE TUEpm WHEN 'J' THEN 1 ELSE 0 END
+ CASE WEDam WHEN 'J' THEN 1 ELSE 0 END
+ CASE WEDpm WHEN 'J' THEN 1 ELSE 0 END
+ CASE THURam WHEN 'J' THEN 1 ELSE 0 END
+ CASE THURpm WHEN 'J' THEN 1 ELSE 0 END
+ CASE FRIam WHEN 'J' THEN 1 ELSE 0 END
+ CASE FRIpm WHEN 'J' THEN 1 ELSE 0 END)  * 0.5 AS Js,
SUM(CASE MONam WHEN 'B' THEN 1 ELSE 0 END
+ CASE MONpm WHEN 'B' THEN 1 ELSE 0 END
+ CASE TUEam WHEN 'B' THEN 1 ELSE 0 END
+ CASE TUEpm WHEN 'B' THEN 1 ELSE 0 END
+ CASE WEDam WHEN 'B' THEN 1 ELSE 0 END
+ CASE WEDpm WHEN 'B' THEN 1 ELSE 0 END
+ CASE THURam WHEN 'B' THEN 1 ELSE 0 END
+ CASE THURpm WHEN 'B' THEN 1 ELSE 0 END
+ CASE FRIam WHEN 'B' THEN 1 ELSE 0 END
+ CASE FRIpm WHEN 'B' THEN 1 ELSE 0 END)  * 0.5 AS Bs,
SUM(CASE MONam WHEN 'V' THEN 1 ELSE 0 END
+ CASE MONpm WHEN 'V' THEN 1 ELSE 0 END
+ CASE TUEam WHEN 'V' THEN 1 ELSE 0 END
+ CASE TUEpm WHEN 'V' THEN 1 ELSE 0 END
+ CASE WEDam WHEN 'V' THEN 1 ELSE 0 END
+ CASE WEDpm WHEN 'V' THEN 1 ELSE 0 END
+ CASE THURam WHEN 'V' THEN 1 ELSE 0 END
+ CASE THURpm WHEN 'V' THEN 1 ELSE 0 END
+ CASE FRIam WHEN 'V' THEN 1 ELSE 0 END
+ CASE FRIpm WHEN 'V' THEN 1 ELSE 0 END)  * 0.5 AS Vs,
SUM(CASE MONam WHEN 'PE' THEN 1 ELSE 0 END
+ CASE MONpm WHEN 'PE' THEN 1 ELSE 0 END
+ CASE TUEam WHEN 'PE' THEN 1 ELSE 0 END
+ CASE TUEpm WHEN 'PE' THEN 1 ELSE 0 END
+ CASE WEDam WHEN 'PE' THEN 1 ELSE 0 END
+ CASE WEDpm WHEN 'PE' THEN 1 ELSE 0 END
+ CASE THURam WHEN 'PE' THEN 1 ELSE 0 END
+ CASE THURpm WHEN 'PE' THEN 1 ELSE 0 END
+ CASE FRIam WHEN 'PE' THEN 1 ELSE 0 END
+ CASE FRIpm WHEN 'PE' THEN 1 ELSE 0 END)  * 0.5 AS PEs,
SUM(CASE MONam WHEN 'P' THEN 1 ELSE 0 END
+ CASE MONpm WHEN 'P' THEN 1 ELSE 0 END
+ CASE TUEam WHEN 'P' THEN 1 ELSE 0 END
+ CASE TUEpm WHEN 'P' THEN 1 ELSE 0 END
+ CASE WEDam WHEN 'P' THEN 1 ELSE 0 END
+ CASE WEDpm WHEN 'P' THEN 1 ELSE 0 END
+ CASE THURam WHEN 'P' THEN 1 ELSE 0 END
+ CASE THURpm WHEN 'P' THEN 1 ELSE 0 END
+ CASE FRIam WHEN 'P' THEN 1 ELSE 0 END
+ CASE FRIpm WHEN 'P' THEN 1 ELSE 0 END)  * 0.5 AS Ps,
SUM(CASE MONam WHEN 'S' THEN 1 ELSE 0 END
+ CASE MONpm WHEN 'S' THEN 1 ELSE 0 END
+ CASE TUEam WHEN 'S' THEN 1 ELSE 0 END
+ CASE TUEpm WHEN 'S' THEN 1 ELSE 0 END
+ CASE WEDam WHEN 'S' THEN 1 ELSE 0 END
+ CASE WEDpm WHEN 'S' THEN 1 ELSE 0 END
+ CASE THURam WHEN 'S' THEN 1 ELSE 0 END
+ CASE THURpm WHEN 'S' THEN 1 ELSE 0 END
+ CASE FRIam WHEN 'S' THEN 1 ELSE 0 END
+ CASE FRIpm WHEN 'S' THEN 1 ELSE 0 END)  * 0.5 AS Ss,
SUM(CASE MONam WHEN 'H' THEN 1 ELSE 0 END
+ CASE MONpm WHEN 'H' THEN 1 ELSE 0 END
+ CASE TUEam WHEN 'H' THEN 1 ELSE 0 END
+ CASE TUEpm WHEN 'H' THEN 1 ELSE 0 END
+ CASE WEDam WHEN 'H' THEN 1 ELSE 0 END
+ CASE WEDpm WHEN 'H' THEN 1 ELSE 0 END
+ CASE THURam WHEN 'H' THEN 1 ELSE 0 END
+ CASE THURpm WHEN 'H' THEN 1 ELSE 0 END
+ CASE FRIam WHEN 'H' THEN 1 ELSE 0 END
+ CASE FRIpm WHEN 'H' THEN 1 ELSE 0 END)  * 0.5 AS Hs,
SUM(CASE WHEN MONam IN ('P', 'S', 'H', 'WO', 'J', 'B', 'v', 'PE') THEN 1 ELSE 0 END
+ CASE WHEN MONpm IN ('P', 'S', 'H', 'WO', 'J', 'B', 'v', 'PE') THEN 1 ELSE 0 END
+ CASE WHEN TUEam IN ('P', 'S', 'H', 'WO', 'J', 'B', 'v', 'PE') THEN 1 ELSE 0 END
+ CASE WHEN TUEpm IN ('P', 'S', 'H', 'WO', 'J', 'B', 'v', 'PE') THEN 1 ELSE 0 END
+ CASE WHEN WEDam IN ('P', 'S', 'H', 'WO', 'J', 'B', 'v', 'PE') THEN 1 ELSE 0 END
+ CASE WHEN WEDpm IN ('P', 'S', 'H', 'WO', 'J', 'B', 'v', 'PE') THEN 1 ELSE 0 END
+ CASE WHEN THURam IN ('P', 'S', 'H', 'WO', 'J', 'B', 'v', 'PE') THEN 1 ELSE 0 END
+ CASE WHEN THURpm IN ('P', 'S', 'H', 'WO', 'J', 'B', 'v', 'PE') THEN 1 ELSE 0 END
+ CASE WHEN FRIam IN ('P', 'S', 'H', 'WO', 'J', 'B', 'v', 'PE') THEN 1 ELSE 0 END
+ CASE WHEN FRIpm IN ('P', 'S', 'H', 'WO', 'J', 'B', 'v', 'PE') THEN 1 ELSE 0 END) * 0.5 AS TOTAL
FROM exempt WHERE (username =  '::username::')



It worked like a charm!  Thanks again!!
0
 
RimvisCommented:
Thanx. I'm glad it helped :o)
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now