problems with subquery counts
-
I am trying to get distinct counts on forms a certain user has filed for a certain day. I am using a subquery to do the counts and returning all the same counts. I am not sure what I am doing wrong. Thoughts? Suggestions?
PROCEDURE [dbo].[testDailyLogReport]
(
@SelectedOffice varchar(100),
@SelectedDate DateTime,
@Outreach bit
)
AS
--Table for the main query
Declare @DailyLog Table
(
NatureOfLog varchar(200),
ReportDate DateTime,
NoSC numeric,
NoNSC numeric,
)
if @Outreach = 0
Begin
insert into @DailyLog
Select distinct
f.Name as 'NatureOfLog'
,DateAdd(day,270,vf.SubmittedDate) as 'ReportDate'
,
,(Select RecordCount from FunctionNumberofNODand9(@SelectedOffice,@SelectedDate,@Outreach)) as 'NoNOD'
--Code 1
,(Select count (DISTINCT NatureId) from FunctionDailyClaimsForm4138(@SelectedOffice,@SelectedDate,@Outreach) where NatureId in (1,2,3,4,5,6,10,11,12,13,14,16) )+
(Select count (DISTINCT FORMID) from FunctionDailyClaims(@SelectedOffice,@SelectedDate,@Outreach) where Formid in (1,8,9,13,14))
as 'NoSC'
--Code 2
,(Select count (DISTINCT NatureId) from FunctionDailyClaimsForm4138(@SelectedOffice,@SelectedDate,@Outreach) where NatureId in (15,11,18,13,7) )+
(Select count (DISTINCT FORMID) from FunctionDailyClaims(@SelectedOffice,@SelectedDate,@Outreach) where Formid in (1,8,7))
as 'NoNSC'
from
Events e
join Veg v on v.VegID = e.VegID
join Forms f on e.VegID = f.VegID
join Forms f on f.FormId = f.FormId
join Offices o on o.OfficeId = e.OfficeId
join Regions r on r.RegionId = o.RegionID
where
dateadd(dd,0, datediff(dd,0,vf.SubmittedDate)) = dateadd(dd,0, datediff(dd,0,@SelectedDate))
and o.Name = @SelectedOffice
and e.OutReachTypeId is null
and f.FormStatusId= 3
End
--Return the data
Select * from @DailyLog
______________________________
AJ Mendo | @SQLAJ -
Eeek. Function subqueries with a cross join on top: join Forms f on f.FormId = f.FormId (And one that is completely wrong, since you already joined that alias)
Not to mention there are tons of syntax errors in this piece of code. Try again?
-
I can't post all the code, I tried to clean it and it appears I messed it up more for the post. Oh well.
Thanks for the help.
______________________________
AJ Mendo | @SQLAJ -
AJ Mendo (12/15/2008)
I am trying to get distinct counts on forms a certain user has filed for a certain day...,(Select RecordCount from FunctionNumberofNODand9(@SelectedOffice,@SelectedDate,@Outreach)) as 'NoNOD'
--Code 1
,(Select count (DISTINCT NatureId) from FunctionDailyClaimsForm4138(@SelectedOffice,@SelectedDate,@Outreach) where NatureId in (1,2,3,4,5,6,10,11,12,13,14,16) )+
(Select count (DISTINCT FORMID) from FunctionDailyClaims(@SelectedOffice,@SelectedDate,@Outreach) where Formid in (1,8,9,13,14))
as 'NoSC'
--Code 2
,(Select count (DISTINCT NatureId) from FunctionDailyClaimsForm4138(@SelectedOffice,@SelectedDate,@Outreach) where NatureId in (15,11,18,13,7) )+
(Select count (DISTINCT FORMID) from FunctionDailyClaims(@SelectedOffice,@SelectedDate,@Outreach) where Formid in (1,8,7))
as 'NoNSC'
Which one of these is the one that is calculating wrong? NoNOD, NoSC, NoNSC? You mention you're getting count by user, which paremeter to your functions represents that user? As has been already mentioned, executing scalar functions in your SELECT clause like that can be slow, is there a set based way to get the counts as a derived table (subquery in the FROM clause)?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply