|
mrtweaver
Yak Posting Veteran
67 Posts |
 Posted - 2009-08-14 : 12:04:18
|
| Attached is a picture of the data which is output when my stored proceedure is ran. My code from my stored proceedure follows that. What I am trying to do is be able to filter the output data in a kind of dynamic way. Here is the varing ways in which MGMT want to be able to view the data.1. By machine number.2. By Shift (so entire production floor)3. By Mechanic (this way they can see where all the mechanic has loged in)4. By type of page ( there are 4 basic types of pages, Respond, SetUp, Maintenance, Bypass)Some of these can be used with others, Such as you may want to see all shifts for a particular machine, or you might want to see all machines on a particular shift or all machines from all shifts. This one will need further filtering because in this report they will want to limit what type of pages they see.I can send over via variables, strings and such to change the conditions of the various select statements. But I dont think this is the best approach.In SQL what would be considered a Numerical Wildcard?Ok now for the pictures and such. Hope they help.---------------------------------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>ALTER PROCEDURE [dbo].[Prod_mdw] @ReportStart datetime, @ReportStop datetime, @shift int, @mechid int, @machine intASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.SET NOCOUNT ON; declare @tblTemp table(machine int, shift int, jobtask bigint, start datetime, stop datetime, mechpg int, mech1 int, mech2 int, proddate datetime, verb1 varchar(10)) declare @tablea table(machine int, shift int, jobtask bigint, start datetime, stop datetime, mechpg int, mech1 text, mech2 text, proddate datetime, verb1 varchar(10), mechid1 int, mechid2 int) declare @tableb table (machine int, shift int, jobtask bigint, start datetime, stop datetime, mechpg int, mech1 text, mech2 text, proddate datetime, verb1 varchar(10), mechid1 int, mechid2 int) declare @tablec table (machine int, shift int, jobtask bigint, start datetime, stop datetime, mechpg int, mech1 text, mech2 text, proddate datetime, verb1 varchar(10), mechid1 int, mechid2 int)insert into @tblTemp (machine,shift,jobtask,start,stop,mechpg,mech1,mech2,proddate, verb1)SELECT t1.MACHINE, t1.SHIFT, t1.JOBTASK, t1.START, t1.STOP, t1.MECHPG, t1.MECH1, t1.MECH2, t1.Prod_date, CASE WHEN mech1 = 0 AND mechpg = 1 THEN 'LiteOn' WHEN mech1 > 0 AND mechpg = 1 THEN 'MechResp' WHEN mech1 > 0 AND mechpg = 0 AND status = 'byps' THEN 'Bypass' ELSE status END AS verb1FROM dbo.Prodtrack as t1WHERE (t1.Prod_date >= @ReportStart and t1.Prod_date <= @ReportStop) AND ((t1.MECHPG = 1) OR (t1.MECHPG = 0 AND t1.MECH1 <> 0))-- Equivalent to Mech Page Init Routineinsert into @tablea(machine,shift,jobtask,start,stop,mechpg,mech1,mech2,proddate,verb1,mechid1,mechid2)SELECT y.MACHINE, y.SHIFT, y.jobtask, y.START, y.stop, y.MECHPG, CASE WHEN y.mech1 = 0 THEN '' END AS mech1, CASE WHEN y.mech2 = 0 THEN '' END AS mech2, y.Prod_date, CASE WHEN y.mechpg = 0 THEN 'pageinit' END AS verb1,y.mech1,y.mech2 FROM @tblTemp as x INNER JOIN dbo.Prodtrack as y ON x.MACHINE = y.MACHINE AND x.SHIFT = y.SHIFT AND x.START = y.STOPWHERE y.MECHPG = 0 AND y.MECH1 = 0 AND y.STATUS <> 'lgin' and (y.Prod_date >= @ReportStart and y.Prod_date <= @ReportStop)-- This is the mechanic retreive name routineinsert into @tableb (machine,shift,jobtask,start,stop,mechpg,mech1,mech2,proddate,verb1,mechid1,mechid2)SELECT p.MACHINE, p.shift, p.jobtask, p.start, p.stop, p.mechpg, a.ProdEmpFirstName + ' ' + a.ProdEmpLastName AS mech1, b.ProdEmpFirstName + ' ' + b.ProdEmpLastName AS mech2,p.prod_date, CASE WHEN p.operator != 0 AND p.mech1 != 0 THEN 'Respond' WHEN p.jobtask = 9999999999 AND p.operator = 0 AND p.mech1 <> 0 THEN 'Maint.' ELSE 'Setup' END AS verb1,p.mech1,p.mech2 FROM dbo.Prodtrack as p INNER JOIN dbo.Prodtrack AS q ON p.MACHINE = q.MACHINE AND p.Prod_date = q.Prod_date AND p.START = q.STOP LEFT OUTER JOIN dbo.ProdEmployee AS a ON a.ProdEmployeeID = p.MECH1 LEFT OUTER JOIN dbo.ProdEmployee AS b ON b.ProdEmployeeID = p.MECH2 WHERE p.MECH1 <> 0 AND q.MECH1 = 0 and (p.Prod_date >= @ReportStart and p.Prod_date <= @ReportStop)-- This is the routine that provides the LOGOUT indicationinsert into @tablec (machine,shift,jobtask,start,stop,mechpg,mech1,mech2,proddate,verb1,mechid1,mechid2)SELECT m.machine, m.shift, m.jobtask, m.stop, m.start, m.mechpg, CASE WHEN m.mech1 = 0 THEN '' ELSE '' END AS mech1, CASE WHEN m.mech2 = 0 THEN '' ELSE '' END AS mech2,m.prod_date, CASE WHEN m.mechpg = 0 AND m.mech1 = 0 THEN 'Logout' ELSE 'END' END AS verb1,m.mech1,m.mech2 FROM dbo.Prodtrack as m INNER JOIN @tblTemp as n ON m.machine = n.MACHINE AND m.shift = n.SHIFT AND m.start = n.STOPWHERE ((m.mechpg <> 1 AND m.mech1 = 0) OR (m.status = 'lgof' AND m.logged <> N' qc ')) and (m.Prod_date >= @ReportStart and m.Prod_date <= @ReportStop)-- This merges all the data back togetherSELECT MACHINE, shift, jobtask, START, mech1, mech2, verb1, Proddate,mechid1,mechid2FROM @tableaUNION ALLSELECT MACHINE, shift, jobtask, START, mech1, mech2, verb1, Proddate,mechid1,mechid2FROM @tablebUNION ALLSELECT MACHINE, shift, jobtask, START, mech1, mech2, verb1, Proddate,mechid1,mechid2FROM @tablecorder by machine,startEND-------------------------------------------------------------Ok I can not seem to get it to upload the JPG file I have that shows my output data. If you are interested in that either A: tell me how to add it in this posting or B: Let me know you are interested and i will send it via email. |
|
Filtering Stored Proceedures