Combining 4 Tables in A Command 1

[LisaStew]

Hello:

Even though this is a long post, I think the problem I have is fairly straightforward... Any help you can provide will be greatly appreciated!

I have four tables that I would like to combine in a Crystal Report (using a command - or perhaps something else would work better??). I am using Crystal Reports v10 (and could upgrade to v11 if needed).

Two of tables contain the *packaging* date and specific serial numbers that were packaged on that date. (The reason there are two tables is that IT recently archived this data and started a new table.) The other two tables contain the *shipping* date and the specific serial numbers that were shipped on that date. (Again, the reason there are two tables with this same information is that IT also archived the old data.) There is no overlap between these tables. In other words, the old data was archived on a date and the new tables were filled with new data the next date.

I am trying to use a command to combine these four tables. ****The overall goal is I want to see is a list of serial numbers that have been packaged in Q1'12 that haven't been shipped yet. ****

I have been able to create two separate commands that combine the two packaging tables and a second command that combines the two shipping tables... I do not know how to combine both of these commands and only show the serial numbers that haven't been shipped yet... I tried to do this in Crystal, and the report stalled and never did produce the desired results...

Here is the command I made to "combine" the two *packaging* tables:

SELECT `packaging_trac_archive1`.`id`, `packaging_trac_archive1`.`BarCode`, `packaging_trac_archive1`.`UpdateDate`
FROM `manufacturing`.`packaging_trac_archive` `packaging_trac_archive1`
WHERE (`packaging_trac_archive1`.`UpdateDate`>={ts '2012-01-01 00:00:00'} AND `packaging_trac_archive1`.`UpdateDate`<{ts '2012-04-01 00:00:01'})

UNION ALL

SELECT `packaging_trac1`.`idpackagingtrac`, `packaging_trac1`.`SerialNo`, `packaging_trac1`.`DateTime`
FROM `manufacturing`.`packaging_trac` `packaging_trac1`
WHERE (`packaging_trac1`.`DateTime`>={ts '2012-01-01 00:00:00'} AND `packaging_trac1`.`DateTime`<{ts '2012-04-01 00:00:01'})


Here is the command to combine the two *shipping* tables:


SELECT `shipping_trac1`.`SerialNo`, `shipping_trac1`.`DateTime`
FROM `manufacturing`.`shipping_trac` `shipping_trac1`
WHERE (`shipping_trac1`.`DateTime`>={ts '2012-01-01 00:00:00'} AND `shipping_trac1`.`DateTime`<{ts '2012-12-12 00:00:00'})

UNION ALL

SELECT `shipping_trac_archive1`.`Barcode`, `shipping_trac_archive1`.`ShipDate`
FROM `manufacturing`.`shipping_trac_archive` `shipping_trac_archive1`
WHERE (`shipping_trac_archive1`.`ShipDate`>={ts '2012-01-01 00:00:00'} AND `shipping_trac_archive1`.`ShipDate`<{ts '2012-12-12 00:00:01'})

Does anyone have any advice on how to combine these 4 tables to only show the serial numbers that haven't shipped yet?

 

[IanWaterman]

What is the relationship between the packaging and shipping tables

If you can add a field that relates the two you can the join the two tables on that field with a left outer.

In select expert add filter

isnull(Shipping.relatedfield)

This will bring back all records in packing with no corresponding record in shipping

NB
Using commands will be very slow as you will have to bring back all data to Crystal on local PC and filter there. You will be better off to produce either two views on database and link or faster still use a stored procedure to do the union and join all in one go.

Ian

 

[LisaStew]

Ian Waterman!

Thanks for your prompt reply - it is very much appreciated!

The related field is `packaging_trac1`.`SerialNo` and `packaging_trac_archive1`.`BarCode` (the two packaging tables)
and
`shipping_trac1`.`SerialNo` and `shipping_trac_archive1`.`Barcode` (the two shipping tables).

I have tried the suggested filter -- isnull(Shipping.SerialNumber) -- but it is taking hours to run...

I would love to try your other two suggestions, but I am not sure how to do views or a stored procedure in Crystal Reports... Any hints or re-directs will be very much appreciated.

Thanks ~ Lisa

 

[IanWaterman]

Lisa

You can only create views or stored procedures directly on the database and then use them within Crystal.

Try a single command

Select P.* from
(
SELECT `packaging_trac_archive1`.`id`, `packaging_trac_archive1`.`BarCode`, `packaging_trac_archive1`.`UpdateDate`
FROM `manufacturing`.`packaging_trac_archive` `packaging_trac_archive1`
WHERE (`packaging_trac_archive1`.`UpdateDate`>={ts '2012-01-01 00:00:00'} AND `packaging_trac_archive1`.`UpdateDate`<{ts '2012-04-01 00:00:01'})
UNION ALL
SELECT `packaging_trac1`.`idpackagingtrac`, `packaging_trac1`.`SerialNo`, `packaging_trac1`.`DateTime`
FROM `manufacturing`.`packaging_trac` `packaging_trac1`
WHERE (`packaging_trac1`.`DateTime`>={ts '2012-01-01 00:00:00'} AND `packaging_trac1`.`DateTime`<{ts '2012-04-01 00:00:01'})
) P
left outer join
(
SELECT `shipping_trac1`.`SerialNo`, `shipping_trac1`.`DateTime`
FROM `manufacturing`.`shipping_trac` `shipping_trac1`
WHERE (`shipping_trac1`.`DateTime`>={ts '2012-01-01 00:00:00'} AND `shipping_trac1`.`DateTime`<{ts '2012-12-12 00:00:00'})
UNION ALL
SELECT `shipping_trac_archive1`.`Barcode`, `shipping_trac_archive1`.`ShipDate`
FROM `manufacturing`.`shipping_trac_archive` `shipping_trac_archive1`
WHERE (`shipping_trac_archive1`.`ShipDate`>={ts '2012-01-01 00:00:00'} AND `shipping_trac_archive1`.`ShipDate`<{ts '2012-12-12 00:00:01'})
) S
on
P.Barcode = s.serialNo
where s.serialNo is null

Might be a little faster

Ian

 

[lbass]

Assuming that Barcode and SerialNo are matching fields, as are the ID and IDPackagingtrac fields, you could use a command like the following.

SELECT
'Packaged' as type,
`packaging_trac_archive1`.`id`,
`packaging_trac_archive1`.`BarCode` as grpfield,
`packaging_trac_archive1`.`UpdateDate`
FROM `manufacturing`.`packaging_trac_archive` `packaging_trac_archive1`
WHERE (`packaging_trac_archive1`.`UpdateDate`>={ts '2012-01-01 00:00:00'} AND `packaging_trac_archive1`.`UpdateDate`<{ts '2012-04-01 00:00:01'})

UNION ALL

SELECT 'Packaged' as type,
`packaging_trac1`.`idpackagingtrac`,
`packaging_trac1`.`SerialNo`,
`packaging_trac1`.`DateTime`
FROM `manufacturing`.`packaging_trac` `packaging_trac1`
WHERE (`packaging_trac1`.`DateTime`>={ts '2012-01-01 00:00:00'} AND `packaging_trac1`.`DateTime`<{ts '2012-04-01 00:00:01'})

UNION ALL

SELECT 'Shipped' as type,
null,
`shipping_trac1`.`SerialNo`,
`shipping_trac1`.`DateTime`
FROM `manufacturing`.`shipping_trac` `shipping_trac1`
WHERE (`shipping_trac1`.`DateTime`>={ts '2012-01-01 00:00:00'} AND `shipping_trac1`.`DateTime`<{ts '2012-12-12 00:00:00'})

UNION ALL

SELECT 'Shipped' as type,
null,
`shipping_trac_archive1`.`Barcode`,
`shipping_trac_archive1`.`ShipDate`
FROM `manufacturing`.`shipping_trac_archive` `shipping_trac_archive1`
WHERE (`shipping_trac_archive1`.`ShipDate`>={ts '2012-01-01 00:00:00'} AND `shipping_trac_archive1`.`ShipDate`<{ts '2012-12-12 00:00:01'})

This would merge all fields in the same ordinal position in the Select statement. You could then insert a group on {command.grpfield}. Then go to report->selection formula->GROUP and enter:

distinctcount({command.type},{command.grpfield})=1

This assumes that all items are in the packaged tables but not necessarily in the shipped tables (but not vice versa).

A command is generally faster than linked tables. You should never link commands, as that would occur locally and slow the report.

-LB

 

[LisaStew]

Linda Bass:

This works phenomenal! THANK YOU!!!!

I did run into a hiccup though... The join between PACKAGING and SHIPPING data should be a LEFT OUTER JOIN... I didn't realize that this would happen, so I didn't note it in the first post. Pretty much if a serial number does NOT show up in either of the Packaging or Packaging Trac Archives for the noted time period (1/1/12 to 4/1/12 above), I don't care if/when it shipped (or not).

I thought maybe:
left outer join grpfield on `packaging_trac_archive1`.`BarCode` = `shipping_trac1`.`SerialNo`

...would do it instead of the middle "UNION ALL" but that resulted in an error.

Again the point of this query is to show which serial numbers/barcodes that were packaged in a certain time period did not ship in another time period.

Oh we are so close I can feel it

THANKS YOU! THANK YOU! THANK YOU! ~ Lisa

 

[IanWaterman]

I think my original command would do what you want. As its unions the shipping and packaging data separately and then left joins them.

Ian

 

[LisaStew]

Ian Waterman!

THANK YOU!!!! This worked just perfectly! I am sorry I didn't do it the first time, I just saw the second post and had wrongly assumed that the second response (from lbass) corrected something to your original response. I apologize.

Thanks and thanks and thanks again for your prompt response.

I am just learning commands and your info was dead-on. Hopefully others will benefit from this post as well

~ Lisa

 

[LisaStew]

Ian Waterman:

So everything is working absolutely fantastic with the command you created for me. Would it be possible to add ANOTHER LINK? This means we would be linking a total of 6 tables in one command....

The purpose of the second link is to determine the last known scan location. (Every time the product is scanned, there is a record inserted in the AMPLOCATION (was the AMPLOCATIONARCHIVE) containing the location where the product was scanned and a date/time stamp of the scan.)

The purpose of the original command you created was to list out product that was packaged, but not shipped. I want to add this *additional* link/command to see if the product was scanned at *another* location besides shipping after it was packaged... (This would mean the product did not follow proper procedures but still is in the building and would not be expected to be shipped yet...)

I thought if I could extract the Serial Number (then probably group on the Serial Number?) and calculate the MAXIMUM DATE/TIME from the AMPLOCATION and AMPLOCATION ARCHIVE tables, I could then see if the MAX DATE/TIME stamp matches the DATE/TIME stamp given in the above query (i.e. the date/time it was packaged). If not, I would know the amp was scanned at a different location (besides Shipping) after it was packaged.

Here is what I came up with so far... It doesn't calculate the MAX DATE/TIME stamp for each Serial Number Group... That is where I got stuck.... I also didn't know how to "link" the Serial Number from the above command with the Serial Number from this command and then show the MAX Date/Time associated with the Serial Number....

Select L.* from
(SELECT `amplocation1`.`SerialNo`, `amplocation1`.`DateTime`, `amplocation1`.`Location`
FROM `manufacturing`.`amplocation` `amplocation1`
WHERE `amplocation1`.`DateTime`>={ts '2011-10-01 00:00:00'}
ORDER BY `amplocation1`.`SerialNo`
UNION ALL
SELECT `amplocation_archive1`.`BarCode`, `amplocation_archive1`.`DateScanned`, `amplocation_archive1`.`Location`
FROM `manufacturing`.`amplocation_archive` `amplocation_archive1`
WHERE `amplocation_archive1`.`DateScanned`>={ts '2011-10-01 00:00:00'}
ORDER BY `amplocation_archive1`.`BarCode`)L

The final format of the report (after everything was linked) would be:
*SerialNo/Barcode* *Pkg Date (Product Not Shipped)* *Last Scan Date from AmpLocation tables*

Thanks again ~ Lisa

 

[IanWaterman]

Try this

Select P.* from
(
SELECT `packaging_trac_archive1`.`id`, `packaging_trac_archive1`.`BarCode`, `packaging_trac_archive1`.`UpdateDate`
FROM `manufacturing`.`packaging_trac_archive` `packaging_trac_archive1`
WHERE (`packaging_trac_archive1`.`UpdateDate`>={ts '2012-01-01 00:00:00'} AND `packaging_trac_archive1`.`UpdateDate`<{ts '2012-04-01 00:00:01'})
UNION ALL
SELECT `packaging_trac1`.`idpackagingtrac`, `packaging_trac1`.`SerialNo`, `packaging_trac1`.`DateTime`
FROM `manufacturing`.`packaging_trac` `packaging_trac1`
WHERE (`packaging_trac1`.`DateTime`>={ts '2012-01-01 00:00:00'} AND `packaging_trac1`.`DateTime`<{ts '2012-04-01 00:00:01'})
) P
left outer join
(
SELECT `shipping_trac1`.`SerialNo`, `shipping_trac1`.`DateTime`
FROM `manufacturing`.`shipping_trac` `shipping_trac1`
WHERE (`shipping_trac1`.`DateTime`>={ts '2012-01-01 00:00:00'} AND `shipping_trac1`.`DateTime`<{ts '2012-12-12 00:00:00'})
UNION ALL
SELECT `shipping_trac_archive1`.`Barcode`, `shipping_trac_archive1`.`ShipDate`
FROM `manufacturing`.`shipping_trac_archive` `shipping_trac_archive1`
WHERE (`shipping_trac_archive1`.`ShipDate`>={ts '2012-01-01 00:00:00'} AND `shipping_trac_archive1`.`ShipDate`<{ts '2012-12-12 00:00:01'})
) S
on
P.Barcode = s.serialNo
where s.serialNo is null

left outer join

(SELECT `amplocation1`.`SerialNo`, max(`amplocation1`.`DateTime`) as MaxDate, `amplocation1`.`Location`
FROM `manufacturing`.`amplocation` `amplocation1`
WHERE `amplocation1`.`DateTime`>={ts '2011-10-01 00:00:00'}
group BY `amplocation1`.`SerialNo`, `amplocation1`.`Location`
UNION ALL
SELECT `amplocation_archive1`.`BarCode`, max(`amplocation_archive1`.`DateScanned`), `amplocation_archive1`.`Location`
FROM `manufacturing`.`amplocation_archive` `amplocation_archive1`
WHERE `amplocation_archive1`.`DateScanned`>={ts '2011-10-01 00:00:00'}
group BY `amplocation_archive1`.`BarCode`, `amplocation_archive1`.`Location`)L

on
P.Barcode = L.serialNo
where s.serialNo is null or L.serialNo

YOu might need to play with the Whare clause to suit your needs

Ian