dirkdirden
Senor Membber
 Gender: Male
Location: United States |
XML slideshow......looks like it was done in flash but I could be mistaken cosidering I haven't used much flash or XML.
I have been useing alot of analysis server coding in MDX for the past few months. Not a big fan of it yet.
This is what I'm working on today. Very boring sql stuff.
-----------------------------------------------------------------
-- Queries to run after Matr weekly data load
--
-- These will check for:
-- Nulls in serial_nbr, ord_nbr length, screen_dtm is a date
-- check the type, disposition, damage pareto,
-- check cust_name and count the unknowns
--
------------ make sure a sn is provided
select count(*) from cwp_matr_screen_staging
where serial_nbr is null
Select * from cwp_matr_screen_staging
where serial_nbr is null
select count(*) from cwp_matr_receipts_staging
where serial_nbr is null
-- look for blank wrk ara
select count(*) from cwp_matr_screen_staging
where work_area is null
-- check the length of the order numbers for all CWP records (100*) -- answer SHOULD BE NINE
select len(control_nbr) from cwp_matr_receipts_staging
where ltrim(rtrim(control_nbr)) like '100%'
group by len(control_nbr)
--no control numbers should be greater the 11. numbers starting with "V" will be 10 characters long
SELECT LEN(control_nbr) A--------------------------------------------------------
-- make sure the table contains all valid types
select count(*) from dbo.cwp_matr_receipts_staging
where lower(board_cpu) not in ('cpu','board','epsd')
--Check for proper depot names in screening
Select * from cwp_matr_screen_staging
Where depot not in (
'EXEL_PNG',
'EXEL_DUB',
'DHL_BRAZIL',
'INGMCR',
'MOSCOW',
'CDW_INDIA',
'UPS_BRAZIL'
)
update cwp_matr_screen_staging
set depot = 'MOSCOW'
where depot = 'Moscow'
--Check for proper depot names in receipts
Select * from cwp_matr_receipts_staging
Where depot not in (
'EXEL_PNG',
'EXEL_DUB',
'DHL_BRAZIL',
'INGMCR',
'MOSCOW',
'CDW_INDIA',
'UPS_BRAZIL'
)
--uppdate depot name
update cwp_matr_receipts_staging
set depot = 'MOSCOW'
where depot = 'Moscow'
--- test to make sure every record has a screen dtm listed
select count(*) from cwp_matr_receipts_staging
where isdate(rcpt_date) = 0
select count(*) from cwp_matr_receipts_staging
where isdate(ship_date) = 0
--update cwp_matr_receipts_staging
--Set ship_date = rcpt_date
--where isdate (ship_date) = '0'
--- select * from cwp_matr_receipts where isdate(ship_date) = 0
select count(*) from dbo.cwp_matr_screen_staging
where isdate(test_date) = 0
Select 'Length', depot
FROM cwp_matr_receipts_staging
WHERE LEN(control_nbr) > '11'
-- total number counts by receipts per intel month
select t.IntelMonthDate, count(*)as QTY from cwp_matr_receipts r
inner join TimeDim t
on convert(varchar(25),t.IntelDate,102) = convert(varchar(25),r.rcpt_date,102)
group by t.IntelMonthDate
order by t.IntelMonthDate desc
-- total number counts by ships per intel month
select t.IntelMonthDate, count(*)as QTY from cwp_matr_receipts r
inner join TimeDim t
on convert(varchar(25),t.IntelDate,102) = convert(varchar(25),r.ship_date,102)
group by t.IntelMonthDate
order by t.IntelMonthDate desc
-- total number of test by intel month
select t.IntelMonthDate, count(*) from cwp_matr_screen r
inner join TimeDim t
on convert(varchar(25),t.IntelDate,102) = convert(varchar(25),r.test_date,102)
group by t.IntelMonthDate
order by t.IntelMonthDate desc
--- check to see the trend of the dispos over the last few months
select t.IntelMonthDate, final_dispo, count(*) as QTY from cwp_matr_receipts r
inner join TimeDim t
on convert(varchar(25),t.IntelDate,102) = convert(varchar(25),r.ship_date,102)
group by t.IntelMonthDate, final_dispo
order by t.IntelMonthDate desc,final_dispo
----- insert the dups into a tmp (checking for exact duplicates).
--(0 rows affected is the prefered out come) otherwise see the tmp table.
--drop table #tmp_dup
select record_id ,depot,board_cpu,rcpt_date,serial_nbr,part_nbr,cpu
_product,rtn_rsn,control_nbr,customer,cst_track_nb
r,
cst_fail_date,cst_fail_symptom,cst_pt_nbr,sspec,fp
o_atpo ,test_platform,final_dispo ,
comments,ship_date,create_by,created_date ,modified_by,modified_date into #tmp_dup
from cwp_matr_receipts_staging
group by record_id ,depot,board_cpu,rcpt_date,serial_nbr,part_nbr,cpu
_product,rtn_rsn,control_nbr,customer,cst_track_nb
r,
cst_fail_date,cst_fail_symptom,cst_pt_nbr,sspec,fp
o_atpo ,test_platform,final_dispo ,
comments,ship_date,create_by,created_date ,modified_by,modified_date
having count(*) > 1
--The next steps are used only if duplicates are found.
-- deletes from the table all duplicates
delete from dbo.cwp_matr_receipts_staging
from dbo.cwp_matr_receipts_staging r
inner join #tmp_dup d
on
r.record_id = d.record_id and
r.depot = d.depot and
r.board_cpu = d.board_cpu and
r.rcpt_date = d.rcpt_date and
r.serial_nbr = d.serial_nbr and
isnull(r.part_nbr,'') = isnull(d.part_nbr,'') and
isnull(r.cpu_product,'') = isnull(d.cpu_product,'')and
isnull(r.rtn_rsn,'') = isnull(d.rtn_rsn,'')and
isnull(r.control_nbr,'') = isnull(d.control_nbr,'')and
isnull(r.customer,'') = isnull(d.customer,'')and
isnull(r.cst_track_nbr,'') = isnull(d.cst_track_nbr,'')and
isnull(r.cst_fail_date,'') = isnull(d.cst_fail_date,'')and
isnull(r.cst_fail_symptom,'') = isnull(d.cst_fail_symptom,'')and
isnull(r.cst_pt_nbr,'') = isnull(d.cst_pt_nbr,'')and
isnull(r.sspec,'') = isnull(d.sspec,'')and
isnull(r.fpo_atpo,'') = isnull(d.fpo_atpo,'')and
isnull(r.test_platform,'') = isnull(d.test_platform,'')and
isnull(r.final_dispo,'') = isnull(d.final_dispo,'')and
isnull(r.comments,'') = isnull(d.comments,'')and
isnull(r.ship_date,'') = isnull(d.ship_date,'')and
isnull(r.create_by,'') = isnull(d.create_by,'')and
isnull(r.created_date,'') = isnull(d.created_date,'')and
isnull(r.modified_by,'') = isnull(d.modified_by,'')and
isnull(r.modified_date,'') = isnull(d.modified_date,'')
---- inserts back into the table the single instance of the record just removed
insert into dbo.cwp_matr_receipts_staging
select * from #tmp_dup
---- test for just dup on ser_nbr and ord_nbr
select depot,record_id, serial_nbr, control_nbr from cwp_matr_receipts_staging
group by depot,record_id, serial_nbr, control_nbr having count(*) > 1
Select *
from cwp_matr_receipts_staging
where serial_nbr = 'BQGN44201493'
--- ser_nbr and work_ara 2x
select depot, record_id, serial_nbr, work_area
from cwp_matr_screen_staging
group by depot, record_id, serial_nbr, work_area
having count(*) > 1
Select *
from cwp_matr_screen_staging ss
inner join cwp_matr_screen sr
on ss.serial_nbr = sr.serial_nbr
and ss.test_date = sr.test_date
select *
from cwp_matr_receipts_staging ss
inner join cwp_matr_receipts sr
on ss.serial_nbr = sr.serial_nbr
and ss.record_id = sr.record_id
--and ss.test_date = sr.test_date
and ss.rcpt_date = sr.rcpt_date
and ss.depot = sr.depot
order by ss.record_id
Select *
from cwp_matr_screen_staging ss
inner join cwp_matr_receipts_staging rs
on ss.serial_nbr = rs.serial_nbr
and ss.record_id is null
--Check for duplicates in the cwp_matr_screen table
select record_id, depot, serial_nbr, test_date, work_area, count(*) as qty
from cwp_matr_screen
group by record_id, depot, serial_nbr, test_date, work_area
having count(*) > 1
--See CWP remove duplicates from cwp_matr_screen to remove dups.
--Checking for existing serial numbers with matching rcpt_date
select S.*
from cwp_matr_receipts_staging S
inner join cwp_matr_receipts R
on S.serial_nbr = R.serial_nbr
and S.rcpt_date = R.rcpt_date
and S.record_id = R.record_id
--Select *
--from cwp_matr_receipts_staging (use a pivot table to see the results)
-- week by week comparison
Select *
from cwp_matr_receipts_staging r
inner join dbo.TimeDim tmd
on CONVERT(VARCHAR, rcpt_date, 101) = tmd.IntelDate
__________________
|
KMC Community Forums
Nov 17th, 2006 05:48 PM
Sounds like the same kinda stuff my dad does. Boring.
