Calculating 0 value from static value

L

Lucinda

Hi,

I am not sure if this is an Access question or a SQL question. I have
written an Access-front end database (2003) with a SQL back-end. I need to
calulate vacatation/personal balances, however, since its the first of the
year all employees receive 16 hours of personal time. They have yet to take
the personal time so I am trying to calculate 16 - 0 = 16. Please keep in
mind that this query does several functions by date: Here is my query:
SELECT A.EMPLID, A.NAME, H.DESCR AS DEPTNAME, C.COMPANY, C.HOURLY_RT,
dbo.[2009_Vac_Balances12-23].Total_Vac_Earned,
SUM(C.HOURLY_RT *
dbo.[2009_Vac_Balances12-23].Total_Vac_Earned) AS EXPR2, CASE WHEN
datepart(mm, F.HIRE_DT)
< 07 THEN SUM(16 - COALESCE
(dbo.Floater_Used1.Floater_Used, 0)) ELSE SUM(8 - COALESCE
(dbo.Floater_Used1.Floater_Used, 0)) END AS EXPR1,
CAST(SUM(C.HOURLY_RT *
(dbo.[2009_Vac_Balances12-23].Total_Vac_Earned + COALESCE
(dbo.Floater_Balance.Total_Floater_Balance, 0)))
AS MONEY) AS LIABILITY, G.NAME AS SUPERVISOR_NAME,
C.DEPTID, dbo.Floater_Balance.Total_Floater_Balance, CASE WHEN datepart(mm,
F.HIRE_DT) < 07 THEN SUM(16 - COALESCE
(dbo.Floater_Used1.Floater_Used, 0)) ELSE SUM(8 - COALESCE
(dbo.Floater_Used1.Floater_Used, 0))
END AS floater1_balance
FROM dbo.[2009_Vac_Balances12-23] RIGHT OUTER JOIN
dbo.Floater_Balance RIGHT OUTER JOIN
dbo.PS_JOB C INNER JOIN
dbo.PS_PERSONAL_DATA A ON C.EMPLID = A.EMPLID INNER JOIN
dbo.PS_EMPLOYMENT F ON C.EMPLID = F.EMPLID AND
C.EMPL_RCD_NBR = F.EMPL_RCD_NBR INNER JOIN
dbo.PS_PERSONAL_DATA G ON F.SUPERVISOR_ID = G.EMPLID
INNER JOIN
dbo.PS_DEPT_TBL H ON C.DEPTID = H.DEPTID LEFT OUTER JOIN
dbo.Floater_Used1 ON A.EMPLID =
dbo.Floater_Used1.EMPLID ON dbo.Floater_Balance.EMPLID = A.EMPLID ON
dbo.[2009_Vac_Balances12-23].EMPLID = A.EMPLID
WHERE (C.EFFDT =
(SELECT MAX(E.EFFDT)
FROM dbo.PS_JOB E
WHERE C.EMPLID = E.EMPLID AND
C.EMPL_RCD_NBR = E.EMPL_RCD_NBR AND E.EFFDT <= GETDATE())) AND (H.EFFDT =
(SELECT MAX(I.EFFDT)
FROM PS_DEPT_TBL I
WHERE H.DEPTID = I.DEPTID AND I.EFFDT <=
GETDATE()))
GROUP BY A.EMPLID, A.NAME, H.DESCR,
dbo.Floater_Balance.Total_Floater_Balance,
dbo.[2009_Vac_Balances12-23].Total_Vac_Earned, F.SUPERVISOR_ID,
G.NAME, dbo.Floater_Balance.EMPLID, C.DEPTID,
C.HOURLY_RT, F.HIRE_DT, C.COMPANY
HAVING (dbo.[2009_Vac_Balances12-23].Total_Vac_Earned <> 0)

As you can see I'm trying to write the liability by multiplying the hourly
rate, by the 12-23 balances + 16 (for the year beginning balance) - 0
personal (floater) days used. My calculation comes back with the hourly rate
multiplied by the 12-23 balances but does not pick up the 16 or 8 and does
not subtract the 0. HELP!!
 
L

Lucinda

I have an update; I basically broke it down.

I ran a query to extrapolate the 16 or 8 values:
SELECT dbo.Floater_Balance.EMPLID, dbo.PS_PERSONAL_DATA.NAME,
dbo.Floater_Balance.Total_Floater_Balance, CASE WHEN datepart(mm,
dbo.PS_EMPLOYMENT.HIRE_DT) < 07 THEN SUM(16 - COALESCE
(dbo.Floater_Used1.Floater_Used, 0))
ELSE SUM(8 - COALESCE (dbo.Floater_Used1.Floater_Used,
0)) END AS Starter_Floater
FROM dbo.[2009_Vac_Balances12-23] LEFT OUTER JOIN
dbo.PS_PERSONAL_DATA ON
dbo.[2009_Vac_Balances12-23].EMPLID = dbo.PS_PERSONAL_DATA.EMPLID LEFT OUTER
JOIN
dbo.PS_EMPLOYMENT ON dbo.PS_PERSONAL_DATA.EMPLID =
dbo.PS_EMPLOYMENT.EMPLID LEFT OUTER JOIN
dbo.Floater_Used1 ON dbo.PS_PERSONAL_DATA.EMPLID =
dbo.Floater_Used1.EMPLID LEFT OUTER JOIN
dbo.Floater_Balance ON dbo.PS_PERSONAL_DATA.EMPLID =
dbo.Floater_Balance.EMPLID FULL OUTER JOIN
dbo.FloaterHours ON dbo.PS_PERSONAL_DATA.EMPLID =
dbo.FloaterHours.[Employee ID]
GROUP BY dbo.Floater_Balance.EMPLID, dbo.PS_PERSONAL_DATA.NAME,
dbo.Floater_Balance.Total_Floater_Balance, dbo.PS_EMPLOYMENT.HIRE_DT

I got return values; I then added this to another query but it comes out
blank:

SELECT dbo.[2009_Vac_Balances12-23].Total_Vac_Earned,
dbo.starter_floater.Starter_Floater,
SUM(dbo.[2009_Vac_Balances12-23].Total_Vac_Earned +
dbo.starter_floater.Starter_Floater + COALESCE
(dbo.starter_floater.Total_Floater_Balance,
0)) AS Start_Point,
dbo.[2009_Vac_Balances12-23].EMPLID, dbo.[2009_Vac_Balances12-23].NAME
FROM dbo.[2009_Vac_Balances12-23] LEFT OUTER JOIN
dbo.starter_floater ON
dbo.[2009_Vac_Balances12-23].EMPLID = dbo.starter_floater.EMPLID
GROUP BY dbo.[2009_Vac_Balances12-23].Total_Vac_Earned,
dbo.starter_floater.Starter_Floater, dbo.[2009_Vac_Balances12-23].EMPLID,
dbo.[2009_Vac_Balances12-23].NAME

the only value returned is from the
dbo.[2009_Vac_Balances12-23].Total_Vac_Earned.

Any idea why?
Lucinda said:
Hi,

I am not sure if this is an Access question or a SQL question. I have
written an Access-front end database (2003) with a SQL back-end. I need to
calulate vacatation/personal balances, however, since its the first of the
year all employees receive 16 hours of personal time. They have yet to take
the personal time so I am trying to calculate 16 - 0 = 16. Please keep in
mind that this query does several functions by date: Here is my query:
SELECT A.EMPLID, A.NAME, H.DESCR AS DEPTNAME, C.COMPANY, C.HOURLY_RT,
dbo.[2009_Vac_Balances12-23].Total_Vac_Earned,
SUM(C.HOURLY_RT *
dbo.[2009_Vac_Balances12-23].Total_Vac_Earned) AS EXPR2, CASE WHEN
datepart(mm, F.HIRE_DT)
< 07 THEN SUM(16 - COALESCE
(dbo.Floater_Used1.Floater_Used, 0)) ELSE SUM(8 - COALESCE
(dbo.Floater_Used1.Floater_Used, 0)) END AS EXPR1,
CAST(SUM(C.HOURLY_RT *
(dbo.[2009_Vac_Balances12-23].Total_Vac_Earned + COALESCE
(dbo.Floater_Balance.Total_Floater_Balance, 0)))
AS MONEY) AS LIABILITY, G.NAME AS SUPERVISOR_NAME,
C.DEPTID, dbo.Floater_Balance.Total_Floater_Balance, CASE WHEN datepart(mm,
F.HIRE_DT) < 07 THEN SUM(16 - COALESCE
(dbo.Floater_Used1.Floater_Used, 0)) ELSE SUM(8 - COALESCE
(dbo.Floater_Used1.Floater_Used, 0))
END AS floater1_balance
FROM dbo.[2009_Vac_Balances12-23] RIGHT OUTER JOIN
dbo.Floater_Balance RIGHT OUTER JOIN
dbo.PS_JOB C INNER JOIN
dbo.PS_PERSONAL_DATA A ON C.EMPLID = A.EMPLID INNER JOIN
dbo.PS_EMPLOYMENT F ON C.EMPLID = F.EMPLID AND
C.EMPL_RCD_NBR = F.EMPL_RCD_NBR INNER JOIN
dbo.PS_PERSONAL_DATA G ON F.SUPERVISOR_ID = G.EMPLID
INNER JOIN
dbo.PS_DEPT_TBL H ON C.DEPTID = H.DEPTID LEFT OUTER JOIN
dbo.Floater_Used1 ON A.EMPLID =
dbo.Floater_Used1.EMPLID ON dbo.Floater_Balance.EMPLID = A.EMPLID ON
dbo.[2009_Vac_Balances12-23].EMPLID = A.EMPLID
WHERE (C.EFFDT =
(SELECT MAX(E.EFFDT)
FROM dbo.PS_JOB E
WHERE C.EMPLID = E.EMPLID AND
C.EMPL_RCD_NBR = E.EMPL_RCD_NBR AND E.EFFDT <= GETDATE())) AND (H.EFFDT =
(SELECT MAX(I.EFFDT)
FROM PS_DEPT_TBL I
WHERE H.DEPTID = I.DEPTID AND I.EFFDT <=
GETDATE()))
GROUP BY A.EMPLID, A.NAME, H.DESCR,
dbo.Floater_Balance.Total_Floater_Balance,
dbo.[2009_Vac_Balances12-23].Total_Vac_Earned, F.SUPERVISOR_ID,
G.NAME, dbo.Floater_Balance.EMPLID, C.DEPTID,
C.HOURLY_RT, F.HIRE_DT, C.COMPANY
HAVING (dbo.[2009_Vac_Balances12-23].Total_Vac_Earned <> 0)

As you can see I'm trying to write the liability by multiplying the hourly
rate, by the 12-23 balances + 16 (for the year beginning balance) - 0
personal (floater) days used. My calculation comes back with the hourly rate
multiplied by the 12-23 balances but does not pick up the 16 or 8 and does
not subtract the 0. HELP!!
Click to expand...
 
Top