A nice little trick for doing UNION queries

To do a UNION query, you need the two queries to line up column by column.  When you are working on this, you should only do a TOP 3 * for each query, otherwise there you can't see everything without scrolling around.  It may seem obvious in retrospect.

Career notes

Last updated 2/10/17

Started on May 21, 2012 at Libman

Libman owns an Australian subsidiary, Sabco.  They use our Syspro infrastructure via Citrix.  Our hardware is in use at almost all hours.

Maintained the 100+ reports, hundreds of labels (although most do not have db connections).

Cneated/maintained several existing desktop applications (about ten).  Migrated several (3 or 4) programs from VB6 to .NET.

Upgraded reports and applications to Syspro 7

Canada Customs report has a lookup table.  We did not want to write a separate application to maintain this, so I wrote a report that showed the contents, of the table, and also allowed the user to enter new data into the lookup table via the same stored procedure that rendered it.

Syspro, Avantis, Wonderware, ADSI (shipping logistics 3^rd party software. Worked with them to gradually give more functionality to their program and less reliance on Fedex and UPS software) Bartender, OnBase document management system, Citrix

Created barcodes for all the sales orders and invoices for OnBase to “Index” automatically, instead of by hand.

ChequeCombine

Combines cheque image file (front and back) into one image, and gives the combined file a name that can automatically be indexed by OnBase.

LibmanWinService

an elegant service.  This has a table of stored procedures that are run at specified intervals.  If a stored procedure detects that a condition is met it emails a report to a list of people specified in the stored procedure.  This is used to notify clients that a shipment is on its way.

 SiloBobImport, .Net program that reads .csv files generated by the silo, imports them to a database, and renders appropriate reports.

Full Truck paperwork, combines three separate reports into one report using sub-reports. This report has a dozen or so parameters.

ShippingLabel and ASNLabel

These click-once programs read data from Syspro tables, show them on the screen, allow user to make edits then render them to the label printer using Bartender.  A flexible platform that allows easy configuration for creating labels for multiple vendors.

ShipNumHandler parses the .csv file produced by “Don’s program” for the Bill of lading number

Some labels can be rendered by Don’s program, but some go to ASNLabel via ShipNumHandler.

Don’s program instead of invoking Bartender would invoke ShipNumHandler, which would present the Bill of lading number to the user to edit or automatically print in the ASNLabel program.

Order Entry Metrics report (SQL triggers used triggers to get the needed data).

ConnectShipExport this small program that takes Syspro data, presents it to the user for modification, and then renders it to ADSI so that all these orders can be shipped.

Warehouse Loading app this Microsoft Access program tracks sales orders as they are being picked by forklift drivers.  An additional benefit is that I created reports on what has recently been picked in case trucks need to be rescheduled earlier than anticipated or in case products need to be given to another customer.

SabcoNT, a small program on Citrix that creates a file for supply chain transfer info.

Web application using MVC to maintain the CanadaCustoms lookup data with parent/child relationship Tariff categories and associated stock codes and their abbreviations.

Gap report
Some of the easier things are sometimes the most effective. Before I arrived, they would index invoices by hand.  Then I added a bar-code and it made this task much easier.
Then I wrote a report that showed if there were any consecutive invoice numbers missing.  This was really effective at showing if there were any missing documents that had not been scanned.
You could see the difference by looking at the gaps in the invoices before and after this report had been implemented.
By the way, this involved querying the OnBase database, which had an unusual structure.


Three web forms applications:

Vendor agreements.  Able to add rows, "expire" rows, edit rows.
TL Tracking.

LTL Tracking. Data comes from shipping info.  You can edit each row and add comments.

Many reports
It's not just about report creation, but there are always tasks where you need to investigate why the report is showing a certain value.  One time I spent several hours investigating why two dates were off by exactly one year.  Eventually, I discovered that the data had been entered twice, once in the right place and once in the wrong place with different journal numbers,.

My Major System

0 – Sow
1 - Tea
2 - Knee
3 - Ma
4 - Row
5 - Law
6 - Shoe
7 - Key
8 - Fee
9 - Pea
10 - Toes
11 - Tot
12 - Tuna
13 - Dime
14 - Tire
15 - Till (cash register)
16 - Touché
17 - Tick
18 - Teef (false Teeth)
19 - Tape
20 - Nose
21 - Nut
22 - Nun
23 - Gnome
24 - Nero
25 - Nail
26 - Nacho
27 - Nook
28 - Nife (Knife)
29 - Nob (knob)
30 - Moose
31 - Mat
32 - Moon
33 - Mummy
34 - Mir (Space station)
35 - Mail
36 - Mash (tool to mash potatoes)
37 - Mickey (Mouse)
38 - Movie
39 - Map
40 - Rose
41 - Rat
42 - Rain
43 - Ram (Horns)
44 - Rear (Bumper)
45 - Rail
46 - Roach
47 - Rake
48 - Roof
49 - Rope
50 - Louse
51 - Light
52 - Lion
53 - Lamp
54 - Lure (fish with hooks)

55 - Lilly
56 – Leech

57 - Leek
58 - Leaf
59 - Lip
60 - Chess
61 - Chat
62 - Chin
63 - Chime


------ rest of numbers -----

0 – Sow 5 – Law 10 – Doze 15 – Dual 20 – Noose 25 – Nail
1 – Dye 6 – Shoe 11 – Dad 16 – Dash 21 – Net 26 – Notch
2 – Knee 7 – Cow 12 – Dune 17 – Duck 22 – Nun 27 – Neck
3 – Ma 8 – Fee 13 – Dim 18 – Dove 23 – Gnome 28 – Knife
4 – Row 9 – Bay 14 – Deer 19 – Dab 24 – Nero 29 – Nip

30 – Mice 35 – Mail 40 – Rose 45 – Rail 50 – Lasie 55 – Lily
31 – Mud 36 – Mash 41 – Rat 46 – Rush 51 – Loot 56 – Leech
32 – Moon 37 – Make 42 – Rain 47 – Wreck 52 – Lane 57 – Leak
33 – Mum 38 – Movie 43 – Ram 48 – Roof 53 – Lame 58 – Lava
34 – Mower 39 – Map 44 – Rear 49 – Rope 54 – Lure 59 – Lip


60 – Chess 65 – Chill 70 – Case 75 – Cool 80 – Fuss 85 – Fall
61 – Chat 66 – Cha cha 71 – Cat 76 – Cash 81 – Foot 86 – Fish
62 – Chin 67 – Chalk 72 – Can 77 – Coke 82 – Fan 87 – Fog
63 – Chime 68 – Chief 73 – Comb 78 – Cave 83 – Foam 88 – Fife
64 - Chair 69 – Chip 74 – Car 79 – Cub 84 – Fur 89 – Fab


90 – Bus 95 – Ball
91 – Bat 96 – Bush
92 – Bun 97 – Book
93 – Beam 98 – Beef
94 – Beer 99 – Bib