Newbe needing help finding no activity

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

General SQL Server Forums

New to SQL Server Programming

Newbe needing help finding no activity

Author

Topic

Bill Z
Starting Member

27 Posts

Posted - 2012-04-11 : 14:15:20

I am so new at this that I’m still crying where the doctor swatted me on the rear.

Here is my task: I have 2 tables. 1 that has all of my GL # and their descriptions and 1 that has all of the GL detail since 1991 (2.4 million lines). The GL # is the common key,

I have to identify what GL # have not had any activity since 07/01/2009.

I don’t know where to start. Do I count detail with same GL # that have a date > ‘06/30/2009’ and list GL#s with count greater than 0? How do I do it?

Bill Zaffos

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-11 : 15:10:13

I think your thought process is correct. One way would be as follows:

SELECT
	[GL #]
FROM
	GLDetailTable
GROUP BY
	[GL #]
HAVING
	MAX(ActivityDate) < '20090701';

This would not list GL # for which there was no activity at all. If you want to get those as well, you will need to do a select from the GL# table with a left join to the GLDetailTable.

quote:
I am so new at this that I’m still crying where the doctor swatted me on the rear.

[LIKE]

Bill Z
Starting Member

27 Posts

Posted - 2012-04-11 : 16:22:20

[quote]Originally posted by sunitabeck

I think your thought process is correct. One way would be as follows:

SELECT
	[GL #]
FROM
	GLDetailTable
GROUP BY
	[GL #]
HAVING
	MAX(ActivityDate) < '20090701';

This would not list GL # for which there was no activity at all. If you want to get those as well, you will need to do a select from the GL# table with a left join to the GLDetailTable.

Can you turn some of this around and then check to see if MAX was zero? MAX(ActivityDate) > '07/01/2009', and MAX < 1

It is things like this that has me stumped.

Bill Zaffos

Xiez
Starting Member

13 Posts

Posted - 2012-04-11 : 17:19:20

In his example, the 'MAX' keyword, is (semi)meaningless, so don't get too hung up on it. Yes, it is needed, but only because you need it to return only 1 value per GL#. If you didn't put max there, and there were 20 update records for 1 GL#, it wouldn't know which one to pull. So, the answer is no, adding "AND MAX < 1" wouldn't do anything.

What are you looking for the max of? You need to specify a field. If you mean you're looking for only the records that have been updated more than 0 times, then his query should already do that for you.

If I'm misunderstanding, please let me know and I'll try to help :)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-11 : 17:59:20

quote:

Can you turn some of this around and then check to see if MAX was zero? MAX(ActivityDate) > '07/01/2009', and MAX < 1

You cannot use MAX the way you are trying to use it as MAX < 1. In addition, if there was no activity at all, I assume those GL#s would not even be in the activity table. So you will need to pick up those GL#s from the GL table. So you could do one of the following. In the example, I am creating two temp tables just so I can demonstrate what I am trying to do.

-- Test data
CREATE TABLE #tmpGL(GL INT);
CREATE TABLE #tmpActivity(GL INT, ActivityDate DATETIME);

INSERT INTO #tmpGL VALUES (1),(2),(3),(4),(5);
INSERT INTO #tmpActivity VALUES
(1,'20090101'),(1,'20090901'),
(2,'20090102'),
(3,'20090904'),(3,'20091111');


-- this would not retrieve 4 and 5
SELECT
	GL
FROM
	#tmpActivity
GROUP BY
	GL
HAVING
	MAX(ActivityDate) < '20090701';

-- this would retrieve 4 and 5 along with 2
SELECT
	g.GL
FROM
	#tmpGL g
	LEFT JOIN #tmpActivity a ON a.GL = g.GL
GROUP BY
	g.GL
HAVING
	MAX(ActivityDate) < '20090701'
	OR MAX(ActivityDate) IS NULL;

-- so would this.
SELECT DISTINCT
	g.GL
FROM
	#tmpGL g
WHERE
	NOT EXISTS 
	(
		SELECT * FROM #tmpActivity a 
		WHERE a.GL = g.GL AND a.ActivityDate >= '20090701'
	);

-- cleanup	
DROP TABLE #tmpGL;
DROP TABLE #tmpActivity;

Bill Z
Starting Member

27 Posts

Posted - 2012-04-12 : 12:55:55

Thanks for these suggestions. In an attempt to not be confusing, I will restate the original problem amplifying some detail.

Again, Here is my task: I have 2 tables. 1 that has all of my GL # and their descriptions and 1 that has all of the GL detail since 1991 (2.4 million lines). The GL # is the common key,

I have to identify what GL # have NOT had any activity since 07/01/2009 to present. Anything with any activity from today back to 07/01/2009 should NOT be included in the result list.

The following kind of works but it gives me all GL #s with the max date of each group. I now want to list only the GL#s that only have dates older than 07/01/2009.

Select glt_gl_key,max(glt_date) maxdt
from glt_trns_dtl
group by glt_gl_key ;

I tried to add the additional lines but couldn’t get it to work yet. I get an error on the unload statement.

into temp oldnum1;
select * from oldnum1
where maxdt < '07/01/2009'
Unload to old_gl_nums.lst ;

How do you measure success? Money? Career? Health? Stuff? Relationships? Success and Purpose in Life go hand in hand. If you want to fulfill YOUR purpose in life you need to find out what it is.

I do this by accomplishing GOD’S purpose for my life.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2012-04-12 : 13:38:35

Select distinct yourGLnumber
Into #GLlist
from YourTable1

Delete from dbo.#GLlist
From dbo.#GLlist inner join YourTable2 on dbo.#GLlist.yourGLnumber
= YourTable2.yourGLnumber

Where Youractivitydate > ‘06/30/2009’

Select YourTable1.yourGLnumber ,ext,ext
from
dbo.#GLlist inner join YourTable1 on dbo.#GLlist.yourGLnumber = YouYourTable1.yourGLnumberrTable1.yourGLnumber

drop table dbo.#GLlist

This is where YourTable1 is the master and yourtable2 contains the transactions

Jim
Users <> Logic

Bill Z
Starting Member

27 Posts

Posted - 2012-04-24 : 15:06:48

Thanks all for these great suggestions. It seems that my version of Informix 9.40.HC7 just isn't going to execute these commands that you suggest. I can make the necessary adjustments for my tables and fields but Informix just doesn't like many of these key words that the other SQL servers accept.

I have noticed that I'm not the only one with Informix challenges.

Thanks again. I'll somehow close this out.

_______________________________________________________________________________
How do you measure success? Money? Career? Health? Stuff? Relationships? Success and Purpose in Life go hand in hand. If you want to fulfill YOUR purpose in life you need to find out what it is.

I do this by accomplishing GOD’S purpose for my life.

Bill Z
Starting Member

27 Posts

Posted - 2012-05-03 : 14:08:38

I did it and this is how!!!!

select glk_gr,
glk_key,
glk_grp_part01,
glk_grp_part02,
glk_title_dl
from glk_key_mstr
where glk_gr = 'NL'
into temp temp01 ;

select glt_gl_gr,
glt_gl_key,
glt_gl_obj,
glt_type
from glt_trns_dtl
where glt_gl_gr = 'NL'
and glt_date > '06/30/2008'
and glt_entry_date > '06/30/2008'
into temp temp02 ;

unload to gls_with_no_activity.txt
select glk_key,
glk_grp_part01,
glk_grp_part02,
glk_title_dl
from temp01 LEFT OUTER JOIN temp02
ON glk_key = glt_gl_key
where glt_gl_obj is null ;

drop table temp01 ;
drop table temp02 ;

_______________________________________________________________________________
How do you measure success? Money? Career? Health? Stuff? Relationships? Success and Purpose in Life go hand in hand. If you want to fulfill YOUR purpose in life you need to find out what it is.

I do this by accomplishing GOD’S purpose for my life.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources