Renaming selected results in a query

Im trying to select some fields from the inner join of a couple table and rename the results. After that I need to check for duplicates of some of these fields. I've tried the following as a stored procedure but get no result.help


CREATE PROCEDURE dbo.PendingClaims
CREATE PROCEDURE dbo.PendingClaims
@StartPeriod datetime,
@EndPeriod datetime
AS

select ClaimStatus.suffix,ClaimStatus.seqno,ClaimStatus.g roupNo,ClaimStatus.memberId,ClaimStatus.depseq,Pay mentHist.serviceFrom,PaymentHist.paidDate,Provider .firstname,BenefitLine.charge, Provider.lastname,sum(BenefitLine.majorPaid+Benefi tLine.basicPaid)as totalpaid,ClaimStatus.JulianDate into #Dup

From
{oj((dbo.ClaimStatus ClaimStatus Inner Join dbo.PaymentHist PaymentHist On
PaymentHist.julianDate= ClaimStatus.julianDate AND
PaymentHist.suffix = ClaimStatus.suffix AND
PaymentHist.seqno = ClaimStatus.seqno)
INNER JOIN dbo.BenefitLine BenefitLine ON
PaymentHist.julianDate= BenefitLine.julianDate AND
PaymentHist.suffix = BenefitLine.suffix AND
PaymentHist.seqno = BenefitLine.seqno AND
PaymentHist.adjNo = BenefitLine.adjNo AND
PaymentHist.payNo = BenefitLine.payNo)
INNER JOIN dbo.PROVIDER Provider ON
PaymentHist.userId = Provider.userid AND
PaymentHist.addJulianYear = Provider.addJulianYear AND
PaymentHist.addJulianDay = Provider.addJulianDay AND
PaymentHist.addNo = Provider.addJulianNo
}
where
PaymentHist.paidDate >= @StartPeriod and
PaymentHist.paidDate<=@EndPeriod
Group by ClaimStatus.JulianDate,ClaimStatus.suffix,ClaimSta tus.seqno,ClaimStatus.groupNo,ClaimStatus.memberId ,ClaimStatus.depseq,PaymentHist.serviceFrom,Paymen tHist.paidDate,Provider.firstname,BenefitLine.char ge, Provider.lastname

select a.* from #Dup a, #Dup b where
a.JulianDate = b.JulianDate

drop table #Dup
GO

Where do you not get a result? The insert statement looks good apart from

PaymentHist.paidDate >= @StartPeriod and
PaymentHist.paidDate<=@EndPeriod

which should better look like

Convert(varchar(10), PaymentHist.paidDate, 112) >= Convert(varchar(10), @StartPeriod, 112) and
Convert(varchar(10), PaymentHist.paidDate, 112)<=Convert(varchar(10), @EndPeriod, 112)

I convert because I believe that paidDate is in datetime format and the params as well. If this is the case the paidDate looks s.th. like this

09/10/2003 18:38:25.793

and that would (with a high probability) never match your params

Im not getting a reply after I select * from the new table i create i.e #Dup. I know the date isnt a problem. when I run this query in query analyzer I do get results but when I incorporate it into a Crystal report as a stored procedure no result is returned......any other suggestions?

This looks like two things were mixed up. The query above has a "{oj" in it which indicates that it is in the SQL window of CR. As far as I remember CR uses it's own date stuff, f.e. SerializeDate(), for comparing dates.

It is not in the SQL window of CR. I went to Enterprise Manager of MS SQL Server and created a stored procedure. Instead of incorporating a table in Crystal I try to use the result of the Stored Procedure.....But it is not returning any fields. As I said when I run it in query analyser i get results but none when I try to incorporate in Crystal. I know the date isnt a problem. Any other solutions?

Well, well, if I give up the idea that the query has a pb then the only difference between SQL Analyzer and CR is that CR might use ODBC for the connection. As it is possible to create a connection with same name for File, System, and User DSN maybe that's a point to check.
Then you may try your SP in a Pass-Through Query in Access using once the CR ODBC and once a brandnew connection (created in Access).
Good luck!

Thanks m.lazor I'll try that I just hope it works.