SQL Examples

_sql_sample.readme

--------------------------------------------
Examples of SQL code from my last job.
--------------------------------------------

While I did analytics / BI work, I also wrote a lot of SQL's
to audit data. Due to the nature of telecom work, we had
a few tools used by provisioning department that allowed
them to break business rules. When you're running numbers,
and find out things don't add up, you start digging. So,
I would come across this, and then get with the IT / IS
dept to try to resolve it. In some cases, they couldn't
do anything about it. So, I would create audits to proactively
monitor it and catch/resolve issues before they festered.

Statements below demonstrate JOINS, UNION, CASE, NOLOCK,
sub-queries...

We didn't have a reporting mirror or data warehouse, so I
had to run data off the production server, and part of my
data was aggregating it into metrics. So, using NOLOCK became
a habit to avoid locking up a table that was being written
to a lot. EG: CSR's hammering away on sales orders.

I would also try to run data and reports during off-hours
to avoid impacts to day-time operations. Most folks wanted
reports first thing in the morning, so I would set data
compilation / aggregation / etc to run around 4am, then 
have reports run from 6am to 7am. Call center ops started
at 8am.

It's really about load-balancing the server.

desc_table_audits.sql

/*

We had a massive "decription" table used for all descriptions:
product, feature, promotion, etc. This let us have language flags
for English / Spanish, and description type ID's to tell which
description it was for.

The debate over whether a monolith table like this is better then
a specialized description table per-component (EG: products having
their own description table) can be debated.

But, I didn't make the DB system; I was just working with it.

I would consistently need to audit the description table in
case something was added that I needed, or something I was using
was changed in it, or something in another system didn't have
a description.

The reason was because the database system didn't have solid
PK-FK'ing to ensure data integrity. Why? I don't know. Again,
I was a user of the database, not the designer.

I would find things violating PK / FK data integrity. I'd let
IS know. They were in charge of the database, including creating
and enforcing PH/FK rules. Since they didn't enforce them, I
just got in the habit of creating data audits to double-check things
and make sure nothing got messed up.

The queries below UNION into a massive single output. They check
each component I was responsible for in the description table,
use a CASE statement to add a flag on what issue is occurring,
then rolls it all up into a single output. This let me target-
audit issues in a single-glance output.

- Craig

*/
------------------------------------------------
/*

Query looks for anything active that uses a desc_id description where the description is either ...

a) missing (desc_id used isn't in MAINT_DESCRIPTION table)
b) inactive (record(s) in MAINT_DESCRIPTION are inactive)

It should be rare if impossible for this to occur through front-end systems.
However, back-end manipulations, like recycling old desc's or deactivating unused ones,
may create such instances.  So, it's best to test for it regularly.

*/

-------------------------------
--LSP's

--find lsp's assigned to missing or inactive desc's
select 'LSP_ID' as 'type', cast(pl.lsp_id as varchar) as 'id', pl.desc_id, md.description, md.status,
	case md.status
		when null then 'desc does not exist'
		when 'I' then 'desc is inactive'
		else 'unknown'
 	end as 'issue'
from prod_lsp				as pl 	(nolock)
	left join maint_description	as md 	(nolock) on pl.desc_id = md.desc_id
where pl.status = 'A'
	and isnull(md.status,'I') = 'I'		-- is null or 'I'nactive


-------------------------------
--PROMO PACKAGES

union --find promo's assigned to missing or inactive desc's (customer desc)
select 'PROMO_ID' as 'type', cast(mpp.promo_id as varchar) as 'id', md.desc_id, md.description, md.status,
	case md.status
		when null then 'cust desc does not exist'
		when 'I' then 'cust desc is inactive'
		else 'unknown'
 	end as 'issue'
from maint_promo_package		as mpp	(nolock)
	left join maint_description	as md 	(nolock) on mpp.desc_id = md.desc_id
where mpp.status_id = 29
	and isnull(md.status,'I') = 'I'		-- desc null or inactive


union --find promo's assigned to missing or inactive desc's (internal desc)
select 'PROMO_ID' as 'type', cast(mpp.promo_id as varchar) as 'id', md.desc_id, md.description, md.status,
	case md.status
		when null then 'internal desc does not exist'
		when 'I' then 'internal desc is inactive'
		else 'unknown'
 	end as 'issue'
from maint_promo_package		as mpp 	(nolock)
	left join maint_description	as md 	(nolock) on mpp.internal_desc_id = md.desc_id
where mpp.status_id = 29
	and isnull(md.status,'I') = 'I'		-- is null or 'I'nactive


-------------------------------
--PROMO DETAILS

union --find promo details assigned to missing or inactive desc's (customer desc)
select 'PROMO_DETAIL_ID' as 'type', cast(mpd.promo_detail_id as varchar) as 'id', md.desc_id, md.description, md.status,
	case md.status
		when null then 'cust desc does not exist'
		when 'I' then 'cust desc is inactive'
		else 'unknown'
 	end as 'issue'
from maint_promo_detail			as mpd	(nolock)
	left join maint_description	as md 	(nolock) on mpd.desc_id = md.desc_id
where mpd.status_id = 29
	and isnull(md.status,'I') = 'I'		-- is null or 'I'nactive


union --find promo details assigned to missing or inactive desc's (internal desc)
select 'PROMO_DETAIL_ID' as 'type', cast(mpd.promo_detail_id as varchar) as 'id', md.desc_id, md.description, md.status,
	case md.status
		when null then 'internal desc does not exist'
		when 'I' then 'internal desc is inactive'
		else 'unknown'
 	end as 'issue'
from maint_promo_detail			as mpd	(nolock)
	left join maint_description	as md 	(nolock) on mpd.internal_desc_id = md.desc_id
where mpd.status_id = 29
	and isnull(md.status,'I') = 'I'		-- is null or 'I'nactive

-------------------------------
--FEATURE / MRC

union --find promo details assigned to missing or inactive desc's (internal desc)
select 'MRC' as 'type', cast(mf.mrc as varchar) as 'id', md.desc_id, md.description, md.status,
	case md.status
		when null then 'desc does not exist'
		when 'I' then 'desc is inactive'
		else 'unknown'
 	end as 'issue'
from maint_features			as mf	(nolock)
	left join maint_description	as md 	(nolock) on mf.desc_id = md.desc_id
where mf.status = 'a'
	and isnull(md.status,'I') = 'I'		-- is null or 'I'nactive
	--these come up, but they've been hosed up for about a year now, and nobody's done anything
	--so I'm ignoring them
	and mf.mrc not in ('LSPP','UDPCP','UDPDP')

-------------------------------
--MESSAGE

union --find promo details assigned to missing or inactive desc's (internal desc)
select 'MSG_ID' as 'type', cast(mdm.msg_id as varchar) as 'id', md.desc_id, md.description, md.status,
	case md.status
		when null then 'desc does not exist'
		when 'I' then 'desc is inactive'
		else 'unknown'
 	end as 'issue'
from maint_desc_message			as mdm	(nolock)
	left join maint_description	as md 	(nolock) on mdm.desc_id = md.desc_id
where mdm.status_id = 29
	and isnull(md.status,'I') = 'I'		-- is null or 'I'nactive

-------------------------------
--BILL POST PROMO (BP_PROMOTIONS)

-- union --find promo details assigned to missing or inactive desc's (internal desc)
-- select 'BP_PROMOTIONS_ID' as 'type', cast(bpp.bp_promotions_id as varchar) as 'id', md.desc_id, md.description, md.status,
-- 	case md.status
-- 		when null then 'desc does not exist'
-- 		when 'I' then 'desc is inactive'
-- 		else 'unknown'
--  	end as 'issue'
-- from bp_promotions			as bpp	(nolock)
-- 	left join maint_description	as md 	(nolock) on bpp.desc_id = md.desc_id
-- where md.status = 'I'
-- 	or md.status is null

----------------------------------

/*

a final ORDER BY statement helps sift the union'ed
data by component and problem.

*/

order by 'type', 'id'

prep_temp_tables.sql

/*

I was mostly working with product & customer data.
I was outside the IS / IT dept as an analyst attached to
marketing / sales, so had to do the best I could with
what I had. IS / IT was hammered with internal software
fixes, usually for billing system, so I had to find ways
to organize data and prep for daily report runs.

I wasn't allowed access to IS / IT tools, like SSRS,
so a lot of what I did was orchastrated through an MS
Access database which I used VBA to automate SQL Server
T-SQL's through. The MS Access database was task-scheduled
to fire off at certain times of the day, and run specific
SQL statements through ADO.

SQL below was a temp table refresh I'd run daily
(eg: 4am before call center got slammed) to aggregate
data for a lot of daily report runs that happened laster.

In a perfect world, a lot of these would have been
created as pre-optimized views in SQL Server. But,
again, IS / IT didn't have time to do it, and I
didn't have user privs to do that. I was stuck with
some read/write privs. So, had to do the best I could
with what I had.

*/

------------------------------------
/*

If SQL Server wants to return record counts by default,
have to disable that for VBA / ADO to run T-SQL's against
it in batch format. This is one of those things you
learn by experience when outputs fail, and you spend
time tracking down the issue.

*/

-- set nocount in case running through VBA/ADO as batch
set nocount on

------------------------------------

-- purge temp tables if they exist
-- these should, since I'm making them perma-temps for
-- other queries to use in other sessions

--use tempdb

if object_id('tempdb..cos_abbr') is not null 
	drop table tempdb..cos_abbr
if object_id('tempdb..active_exch_npanxx') is not null 
	drop table tempdb..active_exch_npanxx
if object_id('tempdb..active_exch_npanxx_svc_product') is not null 
	drop table tempdb..active_exch_npanxx_svc_product
if object_id('tempdb..lsp_main_state') is not null 
	drop table tempdb..lsp_main_state
if object_id('tempdb..cust_vitals_active_suspend') is not null 
	drop table tempdb..cust_vitals_active_suspend

-------------------------------------
/*

In marketing / sales reports, we used short-hand abbr's
for class of service a lot. Created a temp table for it,
b/c IS wouldn't add it as a new field to the reference
table. Again.. had to find work-arounds to do the best
I could with what I had.

While it seems inefficient to re-make the same table
over and over again daily, I did so in case someone
purged the temp tables in the server. I didn't want
to assume the temp table would exist. It's such a
simple temp table to make, I decided to just
purge and make instead of trying to elaborately
test if it existed before making.

*/

--use sage

-- precompile class of service abbreviation reference

select cos_id, cos_desc,
	case cos_desc
		when 'LOCAL' 				then 'L'
		when '1 WAY METRO' 			then '1M'
		when '2 WAY METRO' 			then '2M'
		when 'REGIONAL' 			then 'R'
		when '1 WAY METRO REGIONAL' then '1MR'
		when '2 WAY METRO REGIONAL' then '2MR'
		when 'LD ONLY' 				then 'LD'
		else 'X'					--unknown
	end as 'cos'
into tempdb..cos_abbr
from maint_cos

-------------------------------------
/*

NPA-NXX is the area code + 3 digits of phone numbers.

EG: 123-456-7890 .. npa-nxx = 123-456

The telecom industry updates area codes + 3 digits all
the time as numbers are shiftd around, cities grow,
metro and local calling scopes change, etc.

Keeping up with all of this was a part-time job
in and of itself. We'd get a LERG, which was an
updated npa-nxx "bible" every few months, and I'd
import it and update our systems with it. I'd then
have checks-n-balances to make sure everything was
updated and nothing was going wonky.

Data audits were a main function for me. A lot of
the tools we had built for this had massive gaps in
them making them unreliable. IS dept was slammed
working on billing systems and such, so didn't have
time to update our tools. So, again, do the best
you can with what you have by finding work-arounds.
Most of my work-arounds were finding out what caused
issues, creating data audits to catch it all, and
proactively updating, cleaning or correcting things
instead of reactively waiting for things to blow up.

NPA-NXX going smoothly was a very critical part
of my job, because it was the bread-n-butter of billing.
IE: NPA-NXX callscope was used to determine local vs.
long distance billing. So, I created tools to update
it smoothly, then monitor it to make sure no issues
happened.

*/
-- precompile an npa-nxx exchange ref table
-- to use for orig/term side data
-- also isolates current/latest spec_serv_code

select ms.stateid, ms.state, me.exchange_id, me.exchange, menn.exchange_npa_nxx_id, 
	menn.npa, menn.nxx, menn.ocn_no as 'ocn', right(menn.spec_serv_code, 1) as 'code',
	me.provider_id, menn.npa + menn.nxx as 'npanxx'
into tempdb..active_exch_npanxx
from maint_exchange_npa_nxx		as menn	(nolock)
	inner join maint_exchange	as me	(nolock) on menn.exchange_id = me.exchange_id
	inner join maint_state		as ms	(nolock) on me.stateid = ms.stateid
where menn.status = 'a'
	and me.status = 'a'
	and ms.status = 'a'

--debug
--select * from tempdb..active_exch_npanxx

-------------------------------------

-- precompile a serviceable npa-nxx exchange ref table
-- to use for making sure product has or is missing serviceables

select distinct m.*
into tempdb..active_exch_npanxx_svc_product
from tempdb..active_exch_npanxx		as m 	(nolock)
	inner join prod_exchange_to_lsp as petl	(nolock) on m.exchange_npa_nxx_id = petl.exchange_npa_nxx_id
	inner join lnk_state_to_ocn	as lsto	(nolock) on m.stateid = lsto.stateid
												and m.ocn = lsto.ocn_no
where petl.status = 'a'							-- active on product
	and lsto.status_id = 29						-- only on serviceable state-to-ocn combo's
	and m.code in ('N','O','J')					-- only serviceable codes (N/O = local , J = metro)
	and petl.lsp_id not in ('LSP046','LSP048')	-- ignore LD-only LSP's

--debug
--select * from tempdb..active_exch_npanxx

-------------------------------------
/*

While my job initially started out as product
data development and maintenance, when folks realized
I did a good job, was responsive to requests, and 
got things done, I started getting tapped for more
info requests.

My duties expanded into sales and marketing analysis,
and even the billing director was often asking me for
info regarding customer billing.

So, I rolled-up key customer data, then created automated
reports to satisfy info requests from marketing, sales,
billing, etc.

This query below was also used to make sure customers were
on correct products. Provisioning dept had a tool that let
them do whatever they wanted, which could be very dangerous.
The intent was it gave them the power to setup customers
and provision the service to them from other telecoms.

The problem is that human beings are not 100% fool-proof.
I found some residential customers had business products,
and business customers had residential products. IE: that
wasn't supposed to happen or be possible.

Aftering doing a large audit, I found everyone "crossing
the streams" between res to bus and vice-versa, then estimated
how long it had been going on and how much net loss we had
due to it. It was costing us $1M/yr having customers on
the wrong products.

This was a large cleanup effort, because we had to notify
customers before moving them in order to comply with PUC
regulations. IT dept also worked more checks-n-balances
into the Provisioning system to try to prevent it from
happening going forward, but I still ran the audits to
make sure. Every now and then someone in IT / IS or 
Billing would run an update to the system back-end that
would break something, so my audit would catch it and
help us proactively deal with it instead of wating
for it to become an issue.

*/


-- precompile active/suspended cust vitals for cust counts and other analysis
select distinct a.cust_no, a.cust_id, b.billitem_id, c.wtn, left(c.wtn,6) as 'npanxx',
	c.exchange_id, d.lsp_id, b.servicetype, a.sourcecode, a.status, 
	DATEDIFF(m, ISNULL(d.updatedate, d.entrydate), GETDATE()) AS 'plan_tenure_mos'
into tempdb..cust_vitals_active_suspend
from sage_customer			as a	(nolock)
	inner join sage_billing_item	as b 	(nolock) on a.cust_id = b.cust_id
	inner join sage_wtn		as c	(nolock) on b.billitem_id = c.billitem_id
	inner join sage_bi_service	as d 	(nolock) on b.billitem_id = d.billitem_id
where a.status in ('a','s')				--active / suspends cust's only
	and b.status in ('a','s')			--active / suspends bi's only
	and b.billitem_type = 'wtn'			--exclude 800, calling card, etc
	and d.status = 'a'					--active / current lsp on billitem/wtn

--debug
--select * from tempdb..cust_vitals_active_suspend

-------------------------------------
-------------------------------------
/*

The below SQL is interesting, b/c it demonstrates
how ambiguous working with telecom data can be.
There are callscope maps, but they don't cleanly
segregate areas based on geographic features. So,
you have to find interesting ways to organize the
data.

*/

/*
Since some LATA's cross state lines, we end up with some LSP's with exchanges
from multiple states.  However, those exchanges in other states still count as
exchanges from the main state.  So, to figure out which main state LSP's
fall in, we count up the npa-nxx's on them by state.  Usually, the state
with the most npa-nxx's is what state the LSP is for.  There are a few
exceptions, though, and those are covered by a CASE statement overriding
the state for that LSP.

When running reports where LSP-to-STATE is needed, you should use this
to create the #lsp_st temp table to pull up lsp's state instead of 
relying on PETL > MENN > ME > MS association, which will bring back
multiple states for some LSP's, fluffing up numbers.

(I used to do this via an MS Access DB, but it got annoying
having to copy in that table and VLOOKUP off it in Excel all the time.
It's much easier to just bring in this sql and make a temp table to
hook to during querying.)

*/

----------------------------------------------------------------------
-- ISOLATE LSP-TO-STATE REF
----------------------------------------------------------------------

-- destroy temp table if exists
if object_id('tempdb..#cmc_temp') is not null 
	drop table #cmc_temp

----------------------------

-- isolate states on each lsp based on exch npa-nxx counts
select distinct petl.lsp_id, menn.state, count(menn.state) as exch_ct
into #cmc_temp
from prod_exchange_to_lsp			as petl	(nolock)
	inner join tempdb..active_exch_npanxx	as menn	(nolock) on petl.exchange_npa_nxx_id = menn.exchange_npa_nxx_id
where petl.status = 'a' 
group by petl.lsp_id, menn.state
having petl.lsp_id not in ('','lsp046','lsp048')
--order by petl.lsp_id, menn.state

----------------------------

-- isolate lsp-to-state using max exch count or override
select a.lsp_id, 

	-- some LSP's need to override state, since they have more 
	-- exch's in one state than another, but actually count as the other state
	case
		when a.lsp_id = 'LSP128' then 'MO'	-- has more KS than MO, but counts as border-crossing MO LSP
		when a.lsp_id = 'LSP2121' then 'WI'	-- has more IL than WI, but counts as border-crossing WI LSP
		else a.state
	end as 'st'
into tempdb..lsp_main_state
from #cmc_temp as a
	inner join 
	(
		select lsp_id, max(exch_ct) as 'exch_ct'
		from #cmc_temp
		group by lsp_id
	) as b on a.lsp_id = b.lsp_id
		  and a.exch_ct = b.exch_ct
order by a.lsp_id

----------------------------

-- pull our list to dump into xl files as ref
--select * from tempdb..lsp_main_state

----------------------------
----------------------------

/*
I experimented with creating PK's and FK's on
certain temp tables to speed up performance,
but didn't find performance boosts. So, didn't
bother bogging the server down with index creations.
*/

/*
-- create keys & index on various temp tables
use tempdb

-- active_exch_npa_nxx
CREATE UNIQUE INDEX pk_index 
	ON active_exch_npanxx (exchange_npa_nxx_id);
ALTER TABLE active_exch_npanxx --WITH NOCHECK 
	ADD CONSTRAINT pk_exchange_npa_nxx_id PRIMARY KEY (exchange_npa_nxx_id)
ALTER TABLE active_exch_npanxx --WITH NOCHECK 
	ADD CONSTRAINT fk_stateid FOREIGN KEY (stateid)
ALTER TABLE active_exch_npanxx --WITH NOCHECK 
	ADD CONSTRAINT fk_exchange_id FOREIGN KEY (exchange_id)
ALTER TABLE active_exch_npanxx --WITH NOCHECK 
	ADD CONSTRAINT fk_provider_id FOREIGN KEY (provider_id)

*/