List all Canned Jobs with parts in a single spreadsheet

[PTS]

Howdy. No question here, just sharing on the off chance someone else could use this. It's been awhile since I've dabbled in SQL, so if any other IT folks are out there with some useful tricks, I'd love to hear em.

The shop owner wanted a list of all canned jobs with included parts (he's not currently using part kits) to make it easier to see at a glance which jobs needed tweaking. Since I didn't see any built-in reports that could do that, I went ahead and wrote up a script. Running this and exporting to Excel got the results he was looking for.

Code: Select all

select distinct sm.category.description as catdesc, sm.cannedjob.name, sm.cannedjob.cannedjobid, sm.cannedjob.description as jobdesc, sm.lineitem.description + ': ' + sm.partitem.partno as parts
into tempcj
from sm.cannedjob
inner join sm.category on sm.cannedjob.categoryid=sm.category.categoryid
inner join sm.lineitem on sm.cannedjob.cannedjobid=sm.lineitem.cannedjobid
inner join sm.partitem on sm.lineitem.lineitemid=sm.partitem.lineitemid
order by sm.category.description, sm.cannedjob.cannedjobid, sm.cannedjob.name, sm.cannedjob.description

select distinct catdesc, name, jobdesc, stuff((select ' , ' + cast(parts as varchar(max))
from tempcj 
where (cannedjobid = results.cannedjobid) 
for xml path ('')),1,2,'') as partnumber 
from tempcj results 
group by catdesc, name, jobdesc, cannedjobid

drop table tempcj

[Johnny5]

Errors out in SQL 2008 R2... below works:

CODE: select distinct sm.category.description as catdesc, sm.cannedjob.name, sm.cannedjob.cannedjobid, sm.cannedjob.description as jobdesc, sm.lineitem.description + ': ' + sm.partitem.partno as parts
into tempcj
from sm.cannedjob
inner join sm.category on sm.cannedjob.categoryid=sm.category.categoryid
inner join sm.lineitem on sm.cannedjob.cannedjobid=sm.lineitem.cannedjobid
inner join sm.partitem on sm.lineitem.lineitemid=sm.partitem.lineitemid
order by sm.category.description, sm.cannedjob.cannedjobid, sm.cannedjob.name, sm.cannedjob.description

select distinct catdesc, name, jobdesc, stuff((select ' , ' + cast(parts as varchar(max))
from tempcj
where (cannedjobid = results.cannedjobid)
for xml path ('')),1,2,'') as partnumber
from tempcj results
group by catdesc, name, jobdesc, cannedjobid

drop table tempcj

[Johnny5]

Oh - btw - very nice!