Categories
MySQL

MySQL Commands Maintenance v2

Create an Archive table for unused assets

This SQL statement creates the archive table based on any primary locations that equal ’23 Banbury Road’ or ‘Acland’, it then pulls in the data from the other reference tables.

CREATE TABLE `_archive_asset_log_verbose` AS
SELECT
`x`.`autonum`,
`x`.`barcode`,
`asset_list`.`description`,
`asset_list`.`assetgroup`,
`locations`.`priloc` as `locpriloc`,
`locations`.`secloc` as `locsecloc`,
`locations`.`terloc` as `locterloc`,
`x`.`datedue`,
`x`.`user`,
`users`.`employeename`,
`x`.`pdaid`,
`pda`.`human` as `pda#`,
`pda`.`pdauid`,
`x`.`timecreated`,
`x`.`status`,
`x`.`actiontaken`,
`x`.`actionremaining`,
`x`.`checkrqd`,
`x`.`changedesc`,
`x`.`changegroup`,
`x`.`changeloc`,
`changeloc`.`priloc` as `changepriloc`,
`changeloc`.`secloc` as `changesecloc`,
`changeloc`.`terloc` as `changeterloc`,
`x`.`ts`

FROM (((((maintenancev2.asset_log as x
LEFT JOIN `users` on `x`.`user`=`users`.`autonum`)
LEFT JOIN `pda` on `x`.`pdaid`=`pda`.`autonum`)
LEFT JOIN `asset_list` on `x`.`barcode`=`asset_list`.`barcode`)
LEFT JOIN `locations` on `asset_list`.`loc`=`locations`.`autonum`)
LEFT JOIN `locations` as `changeloc` on `x`.`changeloc`=`changeloc`.`autonum`)

where `x`.`barcode` in
(select `barcode` from `asset_list` where `loc` in
(select `autonum` from `maintenancev2`.`locations`
where ((`priloc`=’23 Banbury Road’) or `priloc`=’Acland’)
)
);

Leave a Reply

Your email address will not be published. Required fields are marked *