query and sub query - 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)

query and sub query

Author

Topic

imughal
Posting Yak Master

192 Posts

Posted - 2004-08-18 : 05:09:51

i have 2 tables processedfiles and unprocessedfile. Both table contains clientid, filename,uploaddate .

i have to list all upload date with their respective clients in given month and year.
on the basis of that recoed i have to list the unprocessed file date of espective client.

The structure of the data is

clientid Unprocessedfile processedfile
*** 30/7 [dd/mm] 2/8
5/8 5/8
6/8 8/8
18/8 20/8

first i have to list all clients from processed file then check unprocessed file data which is less then processed file.

i have develop a stored procedure which returns all processedfile data in correct order but when i apply sub query for unprocessed file data it return error. Subquery returned more than 1 value.

The store procedure is here.
Declare @monthno int
Declare @year int

set @monthno=8
Set @year=2004

select caclientsloginid,upload_date as 'Processed Send', noofmonths as 'No. of Months',
(
select upload_date
from fileinfo as B
where right(rtrim(filename),3)= 'zip' and
filename <> 'NULL' and
b.caclientsloginid=c.caclientsloginid and
b.upload_date < c.upload_date and
month(upload_date) = @monthno and
year(upload_date) = @year

)

from backpdfinfo as c
where filename <> 'NULL' and
month(upload_date) = @monthno and
year(upload_date) = @year
order by caclientsloginid

when i try sub query seprated with fixed value its return only 1 rec.

select upload_date
from fileinfo as B
where right(rtrim(filename),3)= 'zip' and
filename <> 'NULL' and
b.caclientsloginid=3773 and
b.upload_date < '2004-08-07' and
month(upload_date) = 8 and
year(upload_date) = 2004

Kindly tell me how to solve the sub query result. any idea.

thx
iffi

gates_micro
Starting Member

29 Posts

Posted - 2004-08-18 : 05:36:54

SELECT DISTINCT A.CLIENTID,A.FILENAME,A.UPLOADATE FROM
(SELECT CLIENTID,FILENAME,UPLOADATE FROM UNPROCESSEDFILE)A,
(SELECT CLIENTID,UPLOADATE FROM PROCESSEDFILE) B
WHERE A.UPLOADATE<B.UPLOADATE
AND A.CLIENTID=B.CLIENTID

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-18 : 08:28:45

Too idenitify your problem, run this:

The subquery column should show you where you have multiple records.
Don't forget that the way you have this set, the conditions in the sub that reference c.caclientsloginid and c.upload_date will change as those values change. Most likely the source of your problem.

Try out gates solution, I'm kinda confused on what you are trying to do.


Declare @monthno int
Declare @year int

set @monthno=8
Set @year=2004

select 
	caclientsloginid,
	upload_date as 'Processed Send', 
	noofmonths as 'No. of Months',
	(
	select 
		count(*)--upload_date
	from fileinfo as B
	where right(rtrim(filename),3)= 'zip' and
	filename <> 'NULL' and
	b.caclientsloginid=c.caclientsloginid and 
	b.upload_date < c.upload_date and
	month(upload_date) = @monthno and
	year(upload_date) = @year
	) as SubQuery
from backpdfinfo as c 
where filename <> 'NULL' and
month(upload_date) = @monthno and
year(upload_date) = @year 
order by caclientsloginid

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources