SQL Query - Please Help

Question

Tables-

1. Tblassets (History table)

srno	assignto	status	devicetype	subdevicetype	SerialNumber	installedby	installeddate
1	A	S1	Desktop	Monitor	123ser	D	1-jan-2017
2	C	S2	Laptop	Laptop	456dty	D	2-jan-2017
3	D	S4	Desktop	Monitor	789juk	D	3-jan-2017
4	E	S5	Laptop	Laptop	456dty	D	4-jan-2017

 

2. Tbllotenter (master table)

srno	devicetype	subdevicetype	serialnumber	createdon
1	Laptop	Laptop	789juk	3-dec-2016
2	Laptop	Laptop	456dty	3-dec-2016
3	Desktop	Monitor	123ser	3-dec-2016

 

Step-2 –

Load all details – where srno is greater in tblasset table –

Output as –

Logic – For SerialNumber - 789juk, there is one record in tblasset table, with srno=3.

For SerialNumber -456dty, there are two records and max srno is = 4.

For each serial number, find the largest srno in tblasset table and fetch those records.

devicetype	subdevicetype	serialnumber	assignto	Status	srnoOfAssetTable
Laptop	Laptop	789juk	D	S4	3
Laptop	Laptop	456dty	E	S5	2
Desktop	Monitor	123ser	A	S1	4

Query-

Select a.srno, a assignto ,a.Device, a.SubDevice, a.SerialNumber,a.Status

From

(

Select asset.srno, asset.assignto, asset.status, asset.devicetype, asset.subdevice, asset.installedby, asset.installeddate, lotentry.serialnumber, lotentry.PONO, lotentry.hostname,

Row_number()over (partition by lotentry.serialnumber order by asset.dtcreatedby desc)rn

From tblassets asset, tblSerialNumberMaster lotentry

Where asset.serialnumber = lotentryserialnumber

)a

Where rn = 1

Step-2-

Load this data in gridview.

Against each record, user will enter price, remarks and price – multiple times.

3. Transactiondetails (Users insert data)

Srno	assignto	devicetype	subdevicetype	serialnumber	price	remarks	filename	createdon
1	D	Laptop	Laptop	789juk	30	test	F	1-feb-2017
2	E	Laptop	Laptop	456dty	44	test	G	1-feb-2017
3	A	Desktop	Monitor	123ser	55	test	G	1-feb-2017
4	A	Desktop	Monitor	123ser	62	test	g	1-feb-2017
5	E	Laptop	Laptop	456dty	41	test	G	1-feb-2017
6	D	Laptop	Laptop	789juk	34	test	F	1-feb-2017

Again-

Step -4 –

Load these records on gridview such that –

Srno	assignto	devicetype	subdevicetype	serialnumber	price	remarks	filename	createdon
4	A	Desktop	Monitor	123ser	62	test	g	1-feb-2017
5	E	Laptop	Laptop	456dty	41	test	G	1-feb-2017
6	D	Laptop	Laptop	789juk	34	test	F	1-feb-2017

 

Eg – For SerialNumber -123ser, there are two entries in the table, (srno – 3,4). Latest entry is srno-4; so load the record srno-4;

Same, for SerialNumner - 789juk, there are two entries in table ,(srno – 1,6). Latest entry is srno – 6; so load the record srno -5;

In short, I have to display two different records in two different gridviews.

Please help me out in Step-4