Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I have the below code from SQL. How do I translate this into the QS Data Load Editor?
select a.Product_Name,
a.Manufacturer_Name,
a.SerialNumber,
a.Location_Name,
a.InventoryDate,
b.DateCollected,
b.FormName,
b.CollectedBy,
from msgat.dbo.assetsummary a join msgat.dbo.publisheddatasummary b
on a.serialnumber=b.serialnumber
where b.DateCollected = (select max(DateCollected)
from msgat.dbo.publisheddatasummary b WHERE a.SerialNumber = b.SerialNumber)
and(DateDiff(Day, b.DateCollected, a.InventoryDate) <>0
or DateDiff(Month, b.DateCollected, a.InventoryDate) <>0
or DateDiff(Year, b.DateCollected, a.InventoryDate) <>0)
and a.status<>'Disposed'
and a.serialnumber is not null
and ((a.LocationLineage LIKE '161%')
OR (a.LocationLineage LIKE '372%')
OR (a.LocationLineage LIKE '371%')
OR (a.LocationLineage LIKE '082%')
OR (a.LocationLineage LIKE 'SE2%')
OR (a.LocationLineage LIKE '154-1100%'))
ORDER BY SerialNumber
Have you tried following
LOAD
Product_Name,
Manufacturer_Name,
SerialNumber,
Location_Name,
InventoryDate,
DateCollected,
FormName,
CollectedBy,
SQL
select a.Product_Name,
a.Manufacturer_Name,
a.SerialNumber,
a.Location_Name,
a.InventoryDate,
b.DateCollected,
b.FormName,
b.CollectedBy,
from msgat.dbo.assetsummary a join msgat.dbo.publisheddatasummary b
on a.serialnumber=b.serialnumber
where b.DateCollected = (select max(DateCollected)
from msgat.dbo.publisheddatasummary b WHERE a.SerialNumber = b.SerialNumber)
and(DateDiff(Day, b.DateCollected, a.InventoryDate) <>0
or DateDiff(Month, b.DateCollected, a.InventoryDate) <>0
or DateDiff(Year, b.DateCollected, a.InventoryDate) <>0)
and a.status<>'Disposed'
and a.serialnumber is not null
and ((a.LocationLineage LIKE '161%')
OR (a.LocationLineage LIKE '372%')
OR (a.LocationLineage LIKE '371%')
OR (a.LocationLineage LIKE '082%')
OR (a.LocationLineage LIKE 'SE2%')
OR (a.LocationLineage LIKE '154-1100%'))
ORDER BY SerialNumber;