Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old July 1st, 2003, 10:55 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Arlington, TX, USA.
Posts: 119
Thanks: 0
Thanked 1 Time in 1 Post
Default Prb: COUNT qry result NOT SAME as SELECT qry

I have a problem where I am getting a different COUNT result than the number of rows displayed from my SELECT query.††ANY HELP as to why they would be different would be appreciated.††Thank you.††Here is the SELECT statement (following is the COUNT query):

DECLARE @date datetime
DECLARE @date2 datetime
DECLARE @date3 datetime
SET @date=convert(datetime,convert(varchar(10),getdate (),101))
--current date with no time so get's 12:00AM
SET @date2=datepart(m,@date)
SET @date3=datepart(yyyy,@date)

--Hosp A Physicians and their surrogates.

SELECT DISTINCT hu.hcin_user_id as 'Physician_hcin_user_id', MIL.Hcin_user_id as 'Surrogate_hcin_user_id',
rtrim(hu2.lastname) + ', ' + rtrim(hu2.firstname) as 'Surrogate_User',
CAST(wl.Access_Date as smalldatetime) as Access_date, wl.Report_Name, wl.Patient_Last, wl.Patient_First, wl.Patient_ID,
wl.Med_Rec_No, hlo.ods_enterprsfacilitycode, crr.Descrptn as 'Relationship_Access'
FROM hcin_user hu
LEFT OUTER join hosp_status_link HSL
ON HSL.Hcin_user_ID = hu.Hcin_user_Id††-- get hosp affiliations of drs.
LEFT OUTER join Hcin_Module_Info HMI
ON HMI.Hcin_user_id = hu.hcin_user_id -- get physician hcin_user_id of persons mf id
AND HMI.Description = 'PHIS_ID'
LEFT OUTER Join mainframe_ID_lookup MIL
ON hmi.value = MIL.Mainframe_id††--get hcin_user_Ids of surrogates
LEFT OUTER join hcin_user hu2
ON mil.hcin_user_id = hu2.hcin_user_id††--get names of surrogates
LEFT OUTER join web_log wl
ON wl.hcin_user_id = mil.hcin_user_id --get weblog activity
LEFT OUTER join hospital_link_ods hlo
ON wl.hospital_id = hlo.hospital_id
LEFT OUTER JOIN clinical_reporting_relationships crr
ON wl.relationship_id = crr.relatnshp_id --get relationship information
WHERE hu.isphysician = 1
AND substring(MIL.Mainframe_ID,4,3) <> 'ALL'†††-- ignore "All" mainframe ID's
AND HSL.status_ID not in (1,15) -- status is not denied
AND hu.helpdesk_clientid <> 10900 -- eliminate pss staff
AND hu.accountStatus not in (-1, -3, -6) -- eliminate users disabled††by pss, accnt expired††or disabled by phys from caregate
AND datepart(month, wl.access_date) = '6'
AND datepart(year, wl.access_date) = '2003'
AND hu.hcin_user_id = 9999
AND crr.Descrptn IS NOT NULL
AND wl.report_name <> 'patient selection'

UNION ALL

--Hosp B Physicians and their surrogates.

SELECT DISTINCT crm.hcin_user_id as 'Physician_hcin_user_id',
crm.surrogate_id as 'Surrogate_hcin_user_id', rtrim(hu.lastname) + ', ' + rtrim(hu.firstname) as 'Surrogate_User',
CAST(wl.Access_Date as smalldatetime) as Access_date, wl.Report_Name, wl.Patient_Last, wl.Patient_First, wl.Patient_ID,
wl.Med_Rec_No, hlo.ods_enterprsfacilitycode, crr.Descrptn as 'Relationship_Access'
FROM clinical_reporting_main crm
INNER Join Hcin_user hu
ON hu.hciN_user_id = crm.surrogate_id
INNER join web_log wl
ON wl.hcin_user_id = crm.surrogate_id
LEFT OUTER join hospital_link_ods hlo
ON wl.hospital_id = hlo.hospital_id
LEFT OUTER JOIN clinical_reporting_relationships crr
ON wl.relationship_id = crr.relatnshp_id
WHERE accountstatus = 0 -- active accounts only
AND datepart(month,wl.access_date) = '6'
AND datepart(year, wl.access_date) = '2003'
AND crm.hcin_user_id = 9999
AND crr.Descrptn IS NOT NULL
ORDER BY Surrogate_User

Here is my COUNT query:

DECLARE @date datetime
DECLARE @date2 datetime
DECLARE @date3 datetime
SET @date=convert(datetime,convert(varchar(10),getdate (),101))
--current date with no time so get's 12:00AM
SET @date2=datepart(m,@date)
SET @date3=datepart(yyyy,@date)

--Hosp A Physicians and their surrogates.
SELECT (
SELECT COUNT (DISTINCT CAST(wl.Access_Date as smalldatetime))
FROM hcin_user hu
LEFT OUTER join hosp_status_link HSL
ON HSL.Hcin_user_ID = hu.Hcin_user_Id††-- get hosp affiliations of drs.
LEFT OUTER join Hcin_Module_Info HMI
ON HMI.Hcin_user_id = hu.hcin_user_id -- get physician hcin_user_id of persons mf id
AND HMI.Description = 'PHIS_ID'
LEFT OUTER Join mainframe_ID_lookup MIL
ON hmi.value = MIL.Mainframe_id††--get hcin_user_Ids of surrogates
LEFT OUTER join hcin_user hu2
ON mil.hcin_user_id = hu2.hcin_user_id††--get names of surrogates
LEFT OUTER join web_log wl
ON wl.hcin_user_id = mil.hcin_user_id --get weblog activity
LEFT OUTER join hospital_link_ods hlo
ON wl.hospital_id = hlo.hospital_id
LEFT OUTER JOIN clinical_reporting_relationships crr
ON wl.relationship_id = crr.relatnshp_id --get relationship information
WHERE hu.isphysician = 1
AND substring(MIL.Mainframe_ID,4,3) <> 'ALL'†††-- ignore "All" mainframe ID's
AND HSL.status_ID not in (1,15) -- status is not denied
AND hu.helpdesk_clientid <> 10900 -- eliminate pss staff
AND hu.accountStatus not in (-1, -3, -6) -- eliminate users disabled††by pss, accnt expired††or disabled by phys from caregate
AND datepart(month, wl.access_date) = '6'
AND datepart(year, wl.access_date) = '2003'
AND hu.hcin_user_id = 9999
AND crr.Descrptn IS NOT NULL
AND wl.report_name <> 'patient selection')

+

--Hosp B Physicians and their surrogates.

(SELECT COUNT (DISTINCT CAST(wl.Access_Date as smalldatetime))
FROM clinical_reporting_main crm
INNER Join Hcin_user hu
ON hu.hciN_user_id = crm.surrogate_id
INNER join web_log wl
ON wl.hcin_user_id = crm.surrogate_id
LEFT OUTER join hospital_link_ods hlo
ON wl.hospital_id = hlo.hospital_id
LEFT OUTER JOIN clinical_reporting_relationships crr
ON wl.relationship_id = crr.relatnshp_id
WHERE accountstatus = 0 -- active accounts only
AND datepart(month,wl.access_date) = '6'
AND datepart(year, wl.access_date) = '2003'
AND crm.hcin_user_id = 9999
AND crr.Descrptn IS NOT NULL) as RecCount
Reply With Quote
  #2 (permalink)  
Old July 1st, 2003, 12:15 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

At first I though it was because there might be nulls in the field you're counting. But this is not the case because your where clause should filter any rows with nulls in that field. You could try changing the field you are counting to your id field, but I don't think that will necessarily work. It's worth a shot.

Hopefully the expert will come through with a difinative answer.
Reply With Quote
  #3 (permalink)  
Old July 1st, 2003, 12:25 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

No offense meant, but I'm not going to wade through all those JOIN expressions and WHERE clauses to be sure that the same selections are done in each query and that the same tables are JOINed together the same way. Did you?

I'm astounded that there are so many OUTER JOINs in these queries.

A quick look at the two shows the first query selecting distinct rows with about a dozen columns, whereas the COUNT query is counting distinct values of a date. I guess I'm not surprised these return different results.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #4 (permalink)  
Old July 1st, 2003, 12:49 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Arlington, TX, USA.
Posts: 119
Thanks: 0
Thanked 1 Time in 1 Post
Default

No offense taken. The same tables do exist in the joins. I appreciate everyone's time.

The COUNT query only takes into account the field (date value) that is different per row each time giving me an exact count.

Thanks.

Quote:
quote:Originally posted by Jeff Mason
 No offense meant, but I'm not going to wade through all those JOIN expressions and WHERE clauses to be sure that the same selections are done in each query and that the same tables are JOINed together the same way.††Did you?

I'm astounded that there are so many OUTER JOINs in these queries.

A quick look at the two shows the first query selecting distinct rows with about a dozen columns, whereas the COUNT query is counting distinct values of a date.††I guess I'm not surprised these return different results.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #5 (permalink)  
Old July 1st, 2003, 01:02 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by savoym
 The COUNT query only takes into account the field (date value) that is different per row each time giving me an exact count.
Well, you know your data best, but I'm suspicious.

The COUNT query is counting how many different date values there are in a set of rows that meet your criteria. The first query is returning a set of rows, (hopefully meeting the same criteria) with possible duplicates (UNION ALL), and you are looking at the count of the number of rows returned. It sure looks like to me you are comparing apples and oranges...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
  #6 (permalink)  
Old July 2nd, 2003, 04:44 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Just one more thing. Could the column 'wl.Access_Date' be null in some rows? The COUNT (DISTINCT ...) aggregate function ignores null values. In the first case you are returning whole rows, so these counts could be different if the data value is NULL somewhere.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Union Qry Corey Access 1 November 20th, 2006 05:21 AM
Qry result to xls97 worksheet? len Excel VBA 0 August 25th, 2005 01:12 PM
Qry to retrieve from 2 tables and display tsimsha Classic ASP Databases 3 October 29th, 2004 02:03 AM
cmd.qry mohiddin52 Access 0 December 29th, 2003 07:49 AM
command button qry mohiddin52 Access 0 December 22nd, 2003 07:20 AM



All times are GMT -4. The time now is 01:14 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.