union sum
-
module log
Name DOB [Access Module Time] [Module Name]
ABC 1/12/02 1/12/07 Help
ABC 1/12/02 1/12/07 Help2
ACC 1/12/62 11/12/07 Help2
ACC 1/12/62 12/12/07 Help
pratice log
Name DOB [Access Practice Time] [Practice Name]
ABC 1/12/02 1/12/07 P1
ABC 1/12/02 1/12/07 P2
ACC 1/12/62 11/12/07 P1
ACE 1/12/62 12/12/07 P
HREMP
Name DOB [CC]
ABC 1/12/02 A11
ACD 1/12/62 A1D
ACc 1/12/62 A11
I want to create a query when user select A11 from the CC
It will show
Name Count ( sum of the sum count of module log for ABC and sum of count of Practice Log)
ABC 4
ACC 3
so how can I accomplish this ? Thx.
-
See if this works for you:
declare @Name varchar(50)
set @Name = 'ABC' -- or what ever nema you are testing
select
HREMP.Name,
count(dt.Name)
from
dbo.HREMP
inner join (
select
mlog.Name
from
dbo.mlog -- module log
union all
select
plog.Name
from
dbo.plog -- practice log
) dt
on (HREMP.Name = dt.Name)
where
HREMP.Name = @Name
group by
HREMP.Name
-
Lynn;
Thx.
the parameter should be cc. secondly the sql error is from
SELECT h.[FULL NAME] AS Expr1,
COUNT(dt.[Employe Name)
FROM Annual_Edu_2006.dbo.HREMP h INNER JOIN
(SELECT [module log].[Employee Name],
[Module Log].DOB
FROM [module log]
UNION
SELECT [practice log].[Employee Name], [Practice Log].DOB
FROM [Practice log]) dt
on [hremp].[Full Name] = dt.[Employee Name] AND [hremp].[dob] = dt.[dob]
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'FROM'.
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near ')'.
I did not put in the cc as paremeter yet.
-
Lynn is almost there. I think the INNER JOIN should be replaced with a LEFT JOIN, the get all zero counts too.
SELECT
h.Name,
COUNT(*) AS [Count]
FROM HREMP AS h
LEFT JOIN (
SELECT Name
FROM ModuleLog
UNION ALL
SELECT Name
FROM PraticeLog
) AS x ON x.Name = h.Name
WHERE h.CC = 'A11'
GROUP BY h.Name
ORDER BY h.Name
N 56°04'39.16"
E 12°55'05.25" -
Did you even try to figure out the error, Frances? Remember, lot's of folks don't have the time to setup test data if you don't provide it in an INSERT/SELECT format... ipso-facto, they also can't test their code... when you test it and it errors, take a minute and troubleshoot it.
COUNT(dt.[Employe Name)
should be
COUNT(dt.[Employe Name])
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
???
Where is the difference, Jeff?
"Employe" or "Employee"?
N 56°04'39.16"
E 12°55'05.25" -
Heh... "Employe" is a "short timer"
Had to leave something for him to troubleshoot 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You're bad Jeff, bad...
N 56°04'39.16"
E 12°55'05.25" -
Hey, have a Happy Easter. Hope you and yours are doing fine!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thx. I did not think about union join first and left join.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply