Which function's which? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Which function's which?

Author

Topic

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2003-11-26 : 08:35:35

Hi please bear with me - I'm a fairly new sql user and I need plenty of help.

Here's my problem: I have to draw a reprort based on the figures that I return from the following queries.

Query 1:

SELECT count (distinct(a.en)) '4 mil response'
FROM  STATSHOFW7D33D.AGRIC2003.DBO.AGRIC2003 AS A
INNER JOIN SQL.sms_system.dbo.history_reo AS h
ON   a.en = h.enterprise_nbr
where (H.SURVEY_CODE = 'AGRI2003' or h.survey_code = 'AGRI2002')
and a.turnover >='4000000'
and REO_CODE IN (3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29)

Query 2:

SELECT count (distinct(a.en)) '4 mil completed response'
FROM  STATSHOFW7D33D.AGRIC2003.DBO.AGRIC2003 AS A
INNER JOIN SQL.sms_system.dbo.history_reo AS h
ON   a.en = h.enterprise_nbr
WHERE a.turnover >='4000000'
AND (H.SURVEY_CODE = 'AGRI2003' or h.survey_code = 'AGRI2002')
and (reo_code = 19 or
reo_code = 22 or
reo_code = 27)

Query 3:

SELECT count (distinct(a.en)) '4 mil investigation response'
FROM  STATSHOFW7D33D.AGRIC2003.DBO.AGRIC2003 AS A
INNER JOIN SQL.sms_system.dbo.history_reo AS h
ON   a.en = h.enterprise_nbr
WHERE a.turnover >='4000000'
AND (H.SURVEY_CODE = 'AGRI2003' or h.survey_code = 'AGRI2002')
and (reo_code in ('3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','28','29'))

Query 4:

SELECT count (distinct(a.en)) '4 mil extensions response'
FROM  STATSHOFW7D33D.AGRIC2003.DBO.AGRIC2003 AS A
INNER JOIN SQL.sms_system.dbo.history_reo AS h
ON   a.en = h.enterprise_nbr
WHERE a.turnover >='4000000'
AND (H.SURVEY_CODE = 'AGRI2003' or h.survey_code = 'AGRI2002')
and (reo_code = 6)

Query 5:

SELECT count (distinct(a.en)) '4 mil undelivered'
FROM  STATSHOFW7D33D.AGRIC2003.DBO.AGRIC2003 AS A
INNER JOIN SQL.sms_system.dbo.history_reo AS h
ON   a.en = h.enterprise_nbr
WHERE a.turnover >='4000000'
AND (H.SURVEY_CODE = 'AGRI2003' or h.survey_code = 'AGRI2002')
and (reo_code = 0 or
reo_code = 1 or
reo_code = 2)

The results of query 2 to 5 are supposed to add up to query 1. they don't add up because in the history_reo table we use a maint_date (records the date when the rec was saved) and for each of the queries (query 2 to 5) I need to extract the latest maint_date with the corresponding Reo_code

I was told to use the MAX function but it dosn't work!

HELP PLEASE>>>>>?????

You can't teach an old mouse new tricks.

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-26 : 09:29:30

quote:
because in the history_reo table we use a maint_date (records the date when the rec was saved) and for each of the queries (query 2 to 5) I need to extract the latest maint_date with the corresponding Reo_code

I don't see the maint_date used anywhere in any of the queries. How could maint_date be part of the problem?

The queries could be 'cleaned' up a bit by using the IN clause and use it consistently among all 5 queries. Query 3 codes the IN set as character type while it's coded as integer elsewhere. This won't change the result set, but I find it easier reading if there are more similarities than differences.

Nothing is shown about what the differences are in the resultset of queries 2 - 5 versus query 1, so it's hard to pinpoint the problem.

No information on how the MAX function fails or how it was applied.

It looks to me like the resultsets won't add up because the REO_CODE of query 1 spans (3 ..29). This doesn't match the REO_CODE conditions in queries 2 through 5.

I see query 2 through 5 REO_CODE spanning 0..18, with 6 repeated twice, 19, 22, 27, 28 and 29. Nowhere are results for 20, 21, 23, 24, 25, 26 tested.

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2003-11-27 : 02:32:23

Yes, The maint_date wasn't used. What I was trying to say is that each record has a maint_date and i need to used the greatest maint_date for each record.eg.

Enterprise_nbr--Reo_code--Maint_date--Turnover
EN1111--3--11/01--3000569
EN1487--8--15/01--2048726
EN2222--6--15/01--6548879
EN2222--13--2/02--6548879
EN2222--22--18/02--6548879
EN6666--5--19/01--5487236
EN6666--19--5/02--5487236
EN6666--27--23/02--5487236

Primary Key: Enterprise_nbr & Reo_code (Composite key)

Query 1 will pick all the distinct enterprises so (even if the enterprise has more than one Reo_code) - I get 4 enterprises in the example

Query 2 will give a count of all the enterprises with Reo_codes -19,22,27 (if one enetrprise has more that one Reo_code within these reo_codes it will be counted once. I get a count of 2 in the example

Query 3 & 4 & 5 will also give counts of enterprises with their ranges of reo_codes.

My problem is that i need to extract (for example) say for EN6666 or EN2222 - I need to extract only the Reo_code with the latest maint_date. Once I can get the latest maint_date then I will probably place it in a temp table and I can query the temp table for instance - I will have a table with the following:

Enterprise_nbr--Reo_code--Maint_date--Turnover
EN1111--3--11/01--3000569
EN1487--8--15/01--2048726
EN2222--22--18/02--6548879
EN6666--27--23/02--5487236

How can i do this?
I can do it in SAS but it's proving to be a bit of a mission in SQL

You can't teach an old mouse new tricks.

mkbosmans
Starting Member

15 Posts

Posted - 2003-11-27 : 06:09:44

You could do it like this.

SELECT reo1.Enterprise_nbr, reo1.Reo_code, reo1.Maint_date, reo1.Turnover
FROM history_reo AS reo1
INNER JOIN (SELECT Enterprise_nbr, MAX(Maint_date) AS Maint_date FROM history_reo) AS reo2
ON reo1.Enterprise_nbr=reo2.Enterprise_nbr AND reo1.Maint_date=reo2.Maint_date

But it's also possible with a subquery

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2003-11-28 : 00:55:46

I ran the following query:

SELECT count (reo1.Enterprise_nbr)--, reo1.Reo_code, reo1.Maint_date
FROM SQL.sms_system.dbo.history_reo AS reo1
INNER JOIN (SELECT Enterprise_nbr, MAX(Maint_date) AS Maint_date
FROM SQL.sms_system.dbo.history_reo) AS reo2
ON reo1.Enterprise_nbr=reo2.Enterprise_nbr AND 
reo1.Maint_date=reo2.Maint_date
INNER JOIN STATSHOFW7D33D.AGRI2002.DBO.AGRIC2002 AS A
ON   a.en = reo1.Enterprise_nbr
WHERE reo1.survey_code = 'Agri2002'
and (reo_code = 19 or
reo_code = 22 or
reo_code = 27)

and it's giving me the following err.msg:Server: Msg 8118, Level 16, State 1, Line 1
Column 'SQL.sms_system.dbo.history_reo.Enterprise_nbr' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

???

You can't teach an old mouse new tricks.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-11-28 : 04:45:59

this bit "SELECT Enterprise_nbr, MAX(Maint_date) AS Maint_date
FROM SQL.sms_system.dbo.history_reo"
needs to look like "SELECT Enterprise_nbr, MAX(Maint_date) AS Maint_date
FROM SQL.sms_system.dbo.history_reo GROUP BY Enterprise_nbr"

effectively you are saying, that for each "Enterprise_nbr" you want it's MAX(Maint_date)....MAX is an AGGREGATE FUNCTION...and when used like this needs to be 'assisted' by a GROUP BY STATEMENT...(as shown above)

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2003-11-28 : 07:26:52

[code]SELECT count (distinct(a.en)) '2002 COMPLETED Response'
FROM STATSHOFW7D33D.AGRI2002.DBO.AGRIC2002 AS A
INNER JOIN SQL.sms_system.dbo.history_reo AS h
ON a.en = h.enterprise_nbr
WHERE h.survey_code = 'Agri2002'
and (reo_code = 19 or
reo_code = 22 or
reo_code = 27)

SELECT count (reo1.Enterprise_nbr)--, reo1.Reo_code, reo1.Maint_date
FROM SQL.sms_system.dbo.history_reo AS reo1
INNER JOIN (SELECT Enterprise_nbr, MAX(Maint_date) AS Maint_date
FROM SQL.sms_system.dbo.history_reo GROUP BY Enterprise_nbr) AS reo2
ON reo1.Enterprise_nbr=reo2.Enterprise_nbr AND
reo1.Maint_date=reo2.Maint_date
INNER JOIN STATSHOFW7D33D.AGRI2002.DBO.AGRIC2002 AS A
ON a.en = reo1.Enterprise_nbr
WHERE reo1.survey_code = 'Agri2002'
and (reo_code = 19 or
reo_code = 22 or
reo_code = 27)[/code]

The first bit of code returns 16587 records and the second bit returns about 702 records - I don't think the second code is correct because the difference is just too great.
This is what I want to do:
It must search each group (grouped by enterprise_nbr) of records and keep the record with the max(maint_date) from the group and drop the rest and add that one to the count. If I requested records with only reo_code 22 then I must get records with the Max(maint_date) and the selected reo_code.
how do I do this?

You can't teach an old mouse new clicks.

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-28 : 09:32:29

Looks like the first query returns all records for the reo_code group and the second query returns only the records for MAX(maint_date). Isn't that correct and shouldn't the counts 16,587 and 702 be different?

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2003-12-01 : 08:10:57

The counts should be different but the difference CAN'T be that great.
The second bit CAN'T be less than 12 000 records (I can gurantee that much).
Can you return this:

Enterprise_nbr--Reo_code--Maint_date--Turnover
EN1111--3--11/01--3000569
EN1487--8--15/01--2048726
EN2222--6--15/01--6548879
EN2222--13--2/02--6548879
EN2222--22--18/02--6548879
EN6666--5--19/01--5487236
EN6666--19--5/02--5487236
EN6666--27--23/02--5487236

Like this?
Enterprise_nbr--Reo_code--Maint_date--Turnover
EN1111--3--11/01--3000569
EN1487--8--15/01--2048726
EN2222--22--18/02--6548879
EN6666--27--23/02--5487236

that's all that I'm trying to do
Drop * that <> maint_date for every enterprise_nbr

You can't teach an old mouse new clicks.

SamC
White Water Yakist

3467 Posts

Posted - 2003-12-01 : 08:58:45

quote:
SELECT count (distinct(a.en)) '2002 COMPLETED Response'
FROM STATSHOFW7D33D.AGRI2002.DBO.AGRIC2002 AS A
INNER JOIN SQL.sms_system.dbo.history_reo AS h
ON a.en = h.enterprise_nbr
WHERE h.survey_code = 'Agri2002'
and (reo_code = 19 or
reo_code = 22 or
reo_code = 27)

SELECT count (reo1.Enterprise_nbr)--, reo1.Reo_code, reo1.Maint_date
FROM SQL.sms_system.dbo.history_reo AS reo1
INNER JOIN (SELECT Enterprise_nbr, MAX(Maint_date) AS Maint_date
FROM SQL.sms_system.dbo.history_reo GROUP BY Enterprise_nbr) AS reo2
ON reo1.Enterprise_nbr=reo2.Enterprise_nbr AND
reo1.Maint_date=reo2.Maint_date
INNER JOIN STATSHOFW7D33D.AGRI2002.DBO.AGRIC2002 AS A
ON a.en = reo1.Enterprise_nbr
WHERE reo1.survey_code = 'Agri2002'
and (reo_code = 19 or
reo_code = 22 or
reo_code = 27)

The first query above has an INNER JOIN to AGRIC2002 . Could this be adding a lot of records? (I hadn't noticed there was an extra table join in the first query earlier.)

If this gives the correct count (16587) of all enterprise_nbr records,

SELECT count (distinct(a.en))  '2002 COMPLETED Response'
FROM  STATSHOFW7D33D.AGRI2002.DBO.AGRIC2002 AS A
INNER JOIN SQL.sms_system.dbo.history_reo AS h
ON   a.en = h.enterprise_nbr
WHERE h.survey_code = 'Agri2002'
and (reo_code = 19 or
reo_code = 22 or
reo_code = 27)

Then this should give the count of all MAX reo.maint_date records

SELECT count (distinct(a.en))  '2002 COMPLETED Response'
FROM  STATSHOFW7D33D.AGRI2002.DBO.AGRIC2002 AS A
INNER JOIN SQL.sms_system.dbo.history_reo AS h
INNER JOIN (
  SELECT enterprise_nbr, MAX(maint_date) as maint_date
  FROM QL.sms_system.dbo.history_reo
  GROUP BY enterprise_nbr
) Rmax ON rmax.maint_date = h.maint_date AND rmax.enterprise_num = h.enterprise_num
ON   a.en = h.enterprise_nbr
WHERE h.survey_code = 'Agri2002'
and (reo_code = 19 or
reo_code = 22 or
reo_code = 27)

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2003-12-01 : 09:23:49

[code]SELECT count (distinct(a.en)) '2002 COMPLETED Response'
FROM STATSHOFW7D33D.AGRI2002.DBO.AGRIC2002 AS A
INNER JOIN SQL.sms_system.dbo.history_reo AS h
INNER JOIN ( SELECT enterprise_nbr, MAX(maint_date) as maint_date
FROM SQL.sms_system.dbo.history_reo
GROUP BY enterprise_nbr)
Rmax ON rmax.maint_date = h.maint_date AND rmax.enterprise_NBR = h.enterprise_NBR
ON a.en = h.enterprise_nbr
WHERE h.survey_code = 'Agri2002'
and (reo_code = 19 or
reo_code = 22 or
reo_code = 27)[/code]

I changed the bit in red.
This query returns 715 records - it seems like these records all the the latest maint_date but 715 is just a bit too low and it's kind'a making me nervous - I'm going to go through the data to see if it's correct or not. One question - What is that RMAX supposed to be? a temp table?

Thanks for now

You can't teach an old mouse new clicks.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-12-01 : 09:29:52

In this case rmax is a made-up-name...it could just as easily have been a,b,c,d,x,etc....and it notes/names a 'derived table'...i.e. the results from the query in the preceeding brackets....
In order to be used...the derived table needs to be named....just to comply with the syntax requirements of SQL....it doesn't matter what the name is...as long as it's unique to the query.

A derived table....can be imagined to be a temp-table....however it only exists during the specific run of the overall query...whereas a true temp table, can exist and be used across queries (in one batch/procedure)...and even across procedures (if defined to be global temp tables)

SamC
White Water Yakist

3467 Posts

Posted - 2003-12-01 : 09:38:01

Richard,

I think the query is correct yielding 715 records. If you want a sanity check, run a query to find out how many unique enterprise_nbr are present.

SELECT count(*)
from SQL.sms_system.dbo.history_reo
group by enterprise_nbr

Whaddayaget?

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2003-12-01 : 09:58:56

I figured the first test I'm going to run is this: I'm going to check how many distinct records do I get get with reo_code (19, 22, 27)?

According to your query there's only 714 but on the database there's 16601 records.
Your query picks up all the distinct enterprises with the latest Maint_date within the specified Reo_codes - my query checks for distinct enterprise_nbr despite their maint_dates (so if EN1111 already exists in the count it won't be counted again even if a later maint_date is discovered) - We are supposed to get the same results but believe me when i say this, they are not the same

That last query you sent returns the number of times each enterprise was captured.

your code:

SELECT count (distinct(a.en))  '2002 COMPLETED Response'
FROM  STATSHOFW7D33D.AGRIC.DBO.AGRIC2002 AS A
INNER JOIN SQL.sms_system.dbo.history_reo AS h
INNER JOIN ( SELECT enterprise_nbr, MAX(maint_date) as maint_date
FROM SQL.sms_system.dbo.history_reo
GROUP BY enterprise_nbr)
Rmax ON rmax.maint_date = h.maint_date AND rmax.enterprise_NBR = h.enterprise_NBR
ON   a.en = h.enterprise_nbr
WHERE h.survey_code = 'Agri2002'
and (reo_code = 19 or
reo_code = 22 or
reo_code = 27)

my code:

Select count(distinct(a.en))
FROM  STATSHOFW7D33D.AGRIC.DBO.AGRIC2002 AS A
INNER JOIN SQL.sms_system.dbo.history_reo AS h
ON   a.en = h.enterprise_nbr
WHERE h.survey_code = 'Agri2002'
and (reo_code = 19 or
reo_code = 22 or
reo_code = 27)

You can't teach an old mouse new clicks.

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2003-12-01 : 10:14:40

[code]SELECT enterprise_nbr, MAX(maint_date) as maint_date
FROM SQL.sms_system.dbo.history_reo as h
inner join STATSHOFW7D33D.AGRIC.DBO.AGRIC2002 AS A
ON a.en = h.enterprise_nbr
where survey_code = 'agri2002'
and (reo_code = 19 or
reo_code = 22 or
reo_code = 27)
group BY enterprise_nbr
order by enterprise_nbr[/code]

I just ran this bit of code and for some reason it looks correct...?

You can't teach an old mouse new clicks.

SamC
White Water Yakist

3467 Posts

Posted - 2003-12-01 : 10:45:18

It's a valid query, what about the results look correct?

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2003-12-02 : 00:26:51

Yes, the results are correct

How can I get only the count from this query? I don't want it to return all 16000 records

You can't teach an old mouse new clicks.

SamC
White Water Yakist

3467 Posts

Posted - 2003-12-02 : 07:53:27

Lots of ways to count. Here's a count of the records returned from that query

SELECT COUNT(*) AS TOTAL
FROM (
SELECT enterprise_nbr, MAX(maint_date) as maint_date
group BY enterprise_nbr
) X

and here is a count of the distinct enterprise_nbr, less code, and may be what you need.

select count(distinct enterprise_nbr) as Total
FROM SQL.sms_system.dbo.history_reo as h
inner join STATSHOFW7D33D.AGRIC.DBO.AGRIC2002 AS A
ON a.en = h.enterprise_nbr
where survey_code = 'agri2002'
and (reo_code = 19 or
reo_code = 22 or
reo_code = 27)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources