Query issue

building reports crystal reports
1

Hi, I have to build a query for a crystal report for some file tracking system. There are two tables table A & table B from where the information would be coming… Both tables have the same fields and structure. Fields required are file barcode, location & date of allocation. Now what happens, first time when a location is assigned to a file, file barcode, location & date of allocation are stored in table A (call it locationA, Date1). Suppose the file is moved to a new location B on Date2 then table A would now have locationB, Date1 and table B would have LocationA, Date2. Again if the file moves to location C on Date3, then table A would have locationC, Date1 and table B would have 2 records, LocationA, Date2 and LocationB and Date3. In orther words, table A would always have the latest file location with the first date of allocation(we call it the date of birth) and table B would have previous locations with the date of action i.e. the date on which the file was moved FROM this location.

Now, User wants to see the report based on date of allocation, i.e on Date1 he wants locationA, Date2 he wants locationB and so on. How can I build this query. Please help!!! :hb: :hb:

sarna (BOB member since 2012-09-15)

2

Hi Sara,

If you are using SQL Server as Database you can use temp tables and get the required functionality.

Created 2 tables in SQL Server 2008 : filesummary and filedetail with the following columns and data:

FileSummary

FileName FileLocation FileDate
A DDD 8/25/2012
B PPP 7/31/2012

FileDetail

FileName FileLocation FileDate
A AAA 8/28/2012
A BBB 9/1/2012
A CCC 9/5/2012
B ZZZ 8/1/2012

I have used the following code (SQL Server 2008) to get the data:

IF OBJECT_ID(‘Tempdb.dbo.#tempfiledata’) IS NOT NULL drop table #tempfiledata
IF OBJECT_ID(‘Tempdb.dbo.#tempfiledatafinal’) IS NOT NULL drop table #tempfiledatafinal

CREATE Table #tempfiledata
(

FName Varchar(50),
FLocation Varchar(50),
FDate Date
)

INSERT INTO #tempfiledata
select Filename, FileLocation, Filedate from filedetail
UNION
select Filename, FileLocation, Filedate from FileSummary
order by Filename, filedate

CREATE Table #tempfiledatafinal
(
Fname Varchar(50),
FLocation Varchar(50),
FDate Date,
Seq int
)
INSERT INTO #tempfiledatafinal
Select Fname, FLocation,FDate,ROW_NUMBER()OVER (Partition by Fname Order by Fdate) as Seq from #tempfiledata

Select A.FLocation, B.Fdate
from #tempfiledatafinal A
inner join #tempfiledatafinal B on A.Seq-1=B.Seq and A.Fname=B.Fname
UNION
select X.Filelocation, MAX (Y.FileDate) from FileSummary X
INNER JOIN Filedetail Y on X.FileName=Y.Filename
group by X.Filelocation

Thank you.
Regards
Manish

cr_manish :india: (BOB member since 2009-07-30)