Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
CREATE TABLE `ingress` (
`ing_index` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`mrn` varchar(9) DEFAULT NULL,
`originalvial` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`ing_index`)
) ENGINE=InnoDB AUTO_INCREMENT=187434 DEFAULT CHARSET=utf8;
CREATE TABLE `norminv` (
`pni_index` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`mrn` varchar(9) DEFAULT NULL,
`originalvial` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`pni_index`),
KEY `idx_mrn` (`mrn`)
) ENGINE=InnoDB AUTO_INCREMENT=171777 DEFAULT CHARSET=utf8;
CREATE TABLE `tracking` (
`tracking_index` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`fpni` bigint(20) unsigned DEFAULT NULL,
`f_ing_index` bigint(20) unsigned DEFAULT NULL,
`f_req_index` bigint(20) unsigned DEFAULT NULL,
`last_modified` datetime DEFAULT NULL,
`modified_by` varchar(20) DEFAULT NULL,
PRIMARY KEY (`tracking_index`)
) ENGINE=InnoDB AUTO_INCREMENT=71467 DEFAULT CHARSET=utf8;
DELIMITER $$
CREATE DEFINER=`tillabuser`@`%` PROCEDURE `MergeIngPendingToNorminv`()
BEGIN
UPDATE `til_lab`.`ingress`
SET location = 'freeze box'
WHERE
(location IS NULL OR location = '')
AND
(vialnum IS NOT NULL OR vialnum > 0);
INSERT INTO `til_lab`.`norminv`
(SELECT null, mrn, null, capcolor, datefrzn, cellid, vialnum,
amount, units, location, comments, originalvial
FROM `til_lab`.`ingress`
WHERE
mrn IS NOT NULL
AND
mrn <> ''
AND
cellid IS NOT NULL
AND
cellid <> ''
AND
vialnum IS NOT NULL
AND
dateVerified IS NULL
);
UPDATE ingress
SET dateVerified = CURDATE()
WHERE
mrn IS NOT NULL
AND
mrn <> ''
AND
cellid IS NOT NULL
AND
cellid <> ''
AND
vialnum IS NOT NULL
AND
dateVerified IS NULL;
END$$
DELIMITER ;
@grosscol

This comment has been minimized.

Copy link
Owner Author

@grosscol grosscol commented Jul 17, 2015

DECLARE @vials TABLE (Id INT IDENTITY(1,1), VialDetails NCHAR(1))
DECLARE @Batches TABLE (BatchId INT IDENTITY(1,1), VialId INT, BatchDetails NCHAR(1))

@grosscol

This comment has been minimized.

Copy link
Owner Author

@grosscol grosscol commented Jul 17, 2015

DECLARE @vials TABLE (VialId INT IDENTITY(1,1), VialDetails NCHAR(1))
DECLARE @Batches TABLE (BatchId INT IDENTITY(1,1), VialId INT, BatchDetails NCHAR(1))

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.