Early in the days of bendustries.co, one of my first posts was an introduction to SQL Server Studio Manager and the basics of SQL queries, using a basic ship-to report as an example. Since then, I’ve expanded a bit on what I run as a ‘typical’ ship-to report. Aside from that, I think the time is near right to throw this into PSyOPs as a simple report that can be run. This will free up some of my time in no longer being the only person that can hunt down accounts by obscure information. Things like ‘I think their address was…700-something? I think there was a 700 in it somewhere.’
Thinking along those lines, let me pull back the curtain on how I develop the queries that go into the PSyOPs Framework.
What we started with was very basic:
select Customer.Account, Customer.Department, Customer.DepartmentDescription,
CustomerShipTo.Name, CustomerShipTo.Address1, CustomerShipTo.Address2,
CustomerShipTo.City, CustomerShipTo.State, CustomerShipTo.Zip,
Customer.Phone
from Customer left join CustomerShipTo on Customer.Id = CustomerShipTo.CustomerId
where Customer.Name like '%bendustries%'
Not bad for a basic ship-to, but often what I’m looking for is taxability, route codes, sub lists, etc.
So, some of these are easy – sub lists are simply adding Customer.SubListId. Similarly, if you need to know if they are NED (or USA Express, if you swing that way), CustomerShipTo.DropShipFlag is all you need. Odds are good these will go hand in hand – if they’re drop-ship flagged, you’re probably using a sub list to handle what would normally fill from your own warehouse.
But, let’s get a little more useful and add salesperson, too. For that, we’ll go ahead and toss a CustomerSalesPerson.UserId into the query. While we’re at it, let’s throw [dbo].[User].Name in there, as well. As I mentioned in the Customer by Contract Report post, we put User in square brackets because ‘User’ is a reserved keyword in SQL. I noticed when I was adding these in myself earlier today that it wouldn’t work unless I prepended [dbo] (DataBaseObject) to the string, as well. There are some SQL hackers that write everything this way. It might be more ‘proper’, but I’m not picky. Lack of formal education may help in this regard.
Now we’re up to a query that looks like this:
select CustomerSalesPerson.UserId,[dbo].[User].Name, Customer.Account, Customer.Department, Customer.DepartmentDescription,
CustomerShipTo.Name, CustomerShipTo.Address1, CustomerShipTo.Address2,
CustomerShipTo.City, CustomerShipTo.State, CustomerShipTo.Zip,
Customer.Phone
from Customer left join CustomerShipTo on Customer.Id = CustomerShipTo.CustomerId
where Customer.Name like ‘%bendustries%’
And…we haven’t added CustomerSalesPerson nor User to our select list. Easily enough done:
from (Customer left join CustomerShipTo on Customer.Id = CustomerShipTo.CustomerId)
left join
(CustomerSalesPerson left join [User] on CustomerSalesPerson.UserId = [User].Id)
on Customer.Id = CustomerSalesPerson.CustomerId
where Customer.Name like '%bendustries%'
The last time the customer ordered for a particular department might be useful, too, no? CustomerTotal.LastOrderDate will do that…but that’s not in our select list, either. So:
from ((Customer left join CustomerShipTo on Customer.Id = CustomerShipTo.CustomerId)
left join
(CustomerSalesPerson left join [User] on CustomerSalesPerson.UserId = [User].Id)
on Customer.Id = CustomerSalesPerson.CustomerId)
left join CustomerTotal on Customer.Id = CustomerTotal.CustomerId
where Customer.Name like '%bendustries%'
Ah, but I wanted if they’re taxable and their district, as well. Customer.Region, CustomerPricing.Taxable, CustomerPricing.TaxDistrict does that, and back to the select list, which has gotten a bit unwieldy. Let’s rework it just a little:
from ((CustomerShipTo left join CustomerPricing
on CustomerShipTo.CustomerId = CustomerPricing.CustomerID)
right join
(Customer left join CustomerTotal
on Customer.Id = CustomerTotal.CustomerId)
on CustomerShipTo.CustomerId = Customer.Id)
left join
(CustomerSalesPerson left join [User] on CustomerSalesPerson.UserId = [User].Id)
on Customer.Id = CustomerSalesPerson.CustomerId
where Customer.Name like '%bendustries%'
Wait, this isn’t quite it, either. I have some accounts with two salespeople assigned, and I get duplicate rows on those. Also, I don’t need to see the department if it’s been closed. That we do a little differently, though. For that, we’ll add it to the where clause, since these are part of our selection criteria. The where clause then becomes:
where Customer.Name like '%bendustries%'
and CustomerSalesPerson.UserType = 'primary'
and CustomerPricing.OEExempt = ''
The two new tables introduced in our where clause already exist in our select list, so we’re good on that point. Incidentally, you can put where limits on a query without selecting the field or even any field from a given table, if you put it in the list of tables your select query pulls from.
However, in the interest of staying on track, this is what the query I leave open in SSMS looks like today:
select CustomerSalesPerson.UserId,[dbo].[User].Name, Customer.Account, Customer.Phone, Customer.Name, Customer.Department, Customer.DepartmentDescription,
CustomerShipTo.Name, CustomerShipTo.Address1, CustomerShipTo.Address2,
CustomerShipTo.City, CustomerShipTo.State, CustomerShipTo.Zip, Customer.Region,
CustomerPricing.Taxable, CustomerPricing.TaxDistrict, Customer.ManifestRoute,
CustomerShipTo.DropShipFlag, Customer.SubListID, Customer.Sort, CustomerTotal.LastOrderDate
from ((CustomerShipTo left join CustomerPricing
on CustomerShipTo.CustomerId = CustomerPricing.CustomerID)
right join
(Customer left join CustomerTotal
on Customer.Id = CustomerTotal.CustomerId)
on CustomerShipTo.CustomerId = Customer.Id)
left join
(CustomerSalesPerson left join [User] on CustomerSalesPerson.UserId = [User].Id)
on Customer.Id = CustomerSalesPerson.CustomerId
where
Customer.Account = ' 412597'
--and Customer.DepartmentDescription like '%%'
--Customer.Name like '%%'
--CustomerShipTo.Name like '%%'
--CustomerShipTo.City = ''
--and CustomerShipTo.State = ''
--and CustomerShipTo.Address2 like '%700%'
--Customer.OrderCode = ''
--Customer.Email like '%%'
and CustomerSalesPerson.UserType = 'primary'
and CustomerPricing.OEExempt = ''
Notice the eight lines commented out in my where clause. These are the magic – I can limit my criteria on any field in any of the six tables I’m selecting from. In a given day, I might run this 3 or 4 times based on different limits. These are the most common criteria I use. To change criteria, I only need to ensure I’m using ‘and’ appropriately to join multiple criteria.
Notice also that I mix exact and inexact matches – I use leading spaces on Customer.Account to ensure exact matches, while in Customer.Name, I frequently search for partial names, and so a like search with wildcards makes more sense.
Running this gives us results that look something like this:
Click for full size
Looking at the results, though, the Customer.Salesperson name is eating up a lot of space. You can’t see it in this screen shot, but to fit all the columns into a single view, I had to squish the Object Explorer pane down to nearly nothing on a 1080p monitor. Names can be handy to have, but not necessarily as the second column, so we’ll rearrange the select query like so:
select CustomerSalesPerson.UserId, Customer.Account, Customer.Phone, Customer.Name, Customer.Department, Customer.DepartmentDescription,
CustomerShipTo.Name, CustomerShipTo.Address1, CustomerShipTo.Address2,
CustomerShipTo.City, CustomerShipTo.State, CustomerShipTo.Zip, Customer.Region,
CustomerPricing.Taxable, CustomerPricing.TaxDistrict, Customer.ManifestRoute,
CustomerShipTo.DropShipFlag, Customer.SubListID, Customer.Sort, CustomerTotal.LastOrderDate, [dbo].[User].Name
Which gives us:
Click for full size
Much better – rearrange columns as you see fit to move important data to where it makes the most sense. Also note I appear to have made a mistake on one of the setups – the Georgia office still has the address from the California department I copied. This makes it incredibly easy to catch and fix such mistakes. When I set up an account with more than a couple departments, or add departments to an existing account, I’ll give it 15 minutes to let EBS SQL sync from DDMS, and run this to check the setup. Occasionally, I’ll realize I’ve changed an address on an incorrect department. Assuming I catch the mistake quickly enough, I’ll run a quick query to get the old address from here and restore it in DDMS. This is much, much easier than pulling it from a backup.
Here I’ve given a wordy version of how I develop queries in SSMS with an eye to putting the query into PSyOPs. With a bit of patience, it’s relatively easy to put together queries for nearly everything, once you’ve hunted down the tables that hold the data you need. In the next post, I’ll put this query into the Customer Reports screen, showing how that happens as I go. And as I do, I’ll post up the resulting files if you just want it done and ready to go.
Recent Comments