Asp Forum - sql group by question - microsoft.public.sqlserver.programming

jobs

3/15/2007 2:46:00 PM

select WorkflowNo, JobNo, step, max(WFDateTime) from WorkFlowHistory
group by WorkFlowNo, JobNo, Step

produces this:

workflowno jobno step WFDateTime
34 512 1 2007-03-14 09:45:01.520
34 513 2 2007-03-14 09:54:01.300
34 514 3 2007-03-14 09:55:01.453
35 534 1 2007-03-14 15:07:01.197
35 535 2 2007-03-14 15:14:01.250
35 536 3 2007-03-14 15:15:01.330

That same table has column Status. How could I have reported the
associated Status for that Date?

Thanks for any help or information.

6 Answers

Jim Underwood

3/15/2007 3:06:00 PM

You could use a subquery to filter the date and only select rows where the
date is equal to the max date for your keys (or group by columns).
Something like this...

select WF.WorkflowNo
, WF.JobNo
, WF.step
, WF.Status
, WF.WFDateTime
from WorkFlowHistory as WF
where WF.WFDateTime =
(
select max(WF1.WFDateTime)
from WorkFlowHistory as WF1
where WF.JobNo = WF1.JobNo
and WF.step = WF1.step
and WF.WorkflowNo = WF1.WorkflowNo
)

"jobs" <jobs@webdos.com> wrote in message
news:1173969975.180505.201920@e65g2000hsc.googlegroups.com...
> select WorkflowNo, JobNo, step, max(WFDateTime) from WorkFlowHistory
> group by WorkFlowNo, JobNo, Step
>
> produces this:
>
>
> workflowno jobno step WFDateTime
> 34 512 1 2007-03-14 09:45:01.520
> 34 513 2 2007-03-14 09:54:01.300
> 34 514 3 2007-03-14 09:55:01.453
> 35 534 1 2007-03-14 15:07:01.197
> 35 535 2 2007-03-14 15:14:01.250
> 35 536 3 2007-03-14 15:15:01.330
>
> That same table has column Status. How could I have reported the
> associated Status for that Date?
>
> Thanks for any help or information.
>

jobs

3/15/2007 8:11:00 PM

Thank you! That worked nicely.. What if I want every step, even the
ones that don't have history yet and want there default status (if
missing) to come back as "NONE". Right now, I have you code in a view
that's reporting the first two steps, but not the 3rd which is yet to
be excuted does not:

select JobNo, JobName, Status from WorkFlowJobStatus_vw where
WorkFlowNo=38 order by Step

544 job1 DONE
545 Job2 ERROR

WorkflowDetail for this WorkFlowName, has a third Job

and I'd like it also report

544 job1 DONE
545 Job2 ERROR
Job3 NONE

Thanks again!

Jim Underwood

3/15/2007 8:21:00 PM

please post DDL, sample data for your scenario, and expected results. Other
wise I have to guess at some details.
For an explanation of what I am asking see this link:
http://www.aspfaq.com/etiquette.a...

If I understand correctly, you want to report on values for which no date
has been entered yet, meaning the only entry in the table has a null value
for the date? Or no row exists in the table yet?

"jobs" <jobs@webdos.com> wrote in message
news:1173989465.123346.233730@y80g2000hsf.googlegroups.com...
> Thank you! That worked nicely.. What if I want every step, even the
> ones that don't have history yet and want there default status (if
> missing) to come back as "NONE". Right now, I have you code in a view
> that's reporting the first two steps, but not the 3rd which is yet to
> be excuted does not:
>
> select JobNo, JobName, Status from WorkFlowJobStatus_vw where
> WorkFlowNo=38 order by Step
>
> 544 job1 DONE
> 545 Job2 ERROR
>
>
> WorkflowDetail for this WorkFlowName, has a third Job
>
> and I'd like it also report
>
> 544 job1 DONE
> 545 Job2 ERROR
> Job3 NONE
>
> Thanks again!
>

jobs

3/16/2007 12:20:00 PM

sorry. There are another two tables..

ActiveWorkFlow (will tie WorkflowNo to WorkFlowName so we can find
all related JobNames for that workflow)
WorkFlowNo
WorkFlowName

and

WorkFlowDetail
WorkFlowName
JobName

WorkFlowDetail has all the JobNames in a workflow. I;m looking for
the last status of every Job/Workflow combination (as the prior query
succeeded in doing) however, I also what jobnames not listed in
history for those WorkFlows... listing it with Status defaulting to
NONE.

Thanks for any help or information.

Jim Underwood

3/16/2007 1:19:00 PM

You should be able to accomplish that with a basic outer join to your view,
although if performance is poor you may have to tweak it a bit. The below
code is just an example, since you didn't provide actual DDL. I assume that
you will join on JobNo and WorkFlowNo, although you only list the name
columns in your table descriptions.

select WFD.JobNo
, WFD.JobName
, coalesce(wfh.Status, 'NONE')
from ActiveWorkFlow as AWF
inner join WorkFlowDetail WFD
on WFD.WorkFlowName = AWF.WorkFlowName
left outer join WorkFlowJobStatus_vw as WFH
on WFD.WorkFlowNo = WFH.WorkFlowNo
and WFD.JobNo = WFH..JobNo
where WorkFlowNo=38
order by Step

"jobs" <jobs@webdos.com> wrote in message
news:1174047603.681727.123850@e1g2000hsg.googlegroups.com...
> sorry. There are another two tables..
>
> ActiveWorkFlow (will tie WorkflowNo to WorkFlowName so we can find
> all related JobNames for that workflow)
> WorkFlowNo
> WorkFlowName
>
> and
>
> WorkFlowDetail
> WorkFlowName
> JobName
>
>
> WorkFlowDetail has all the JobNames in a workflow. I;m looking for
> the last status of every Job/Workflow combination (as the prior query
> succeeded in doing) however, I also what jobnames not listed in
> history for those WorkFlows... listing it with Status defaulting to
> NONE.
>
> Thanks for any help or information.
>

jobs

3/16/2007 2:59:00 PM

Thanks for help

WFD does not have a workflowno, just a workflowname and that's okay
since I have a function to get WFName from WFNo.

However, The below does not produce the desired result

select WFD.WorkflowName,WFH.WorkflowNo, WFH.JobNo ,
WFH.Step , WFD.JobName
, coalesce(WFH.Status, 'NONE')
from ActiveWorkFlow as AWF
inner join WorkFlowDetail WFD
on WFD.WorkFlowName = AWF.WorkFlowName
left outer join WorkFlowJobStatus_vw as WFH
on WFD.WorkFlowName = dbo.GetWorkFlowName_fn(WFH.WorkFlowNo)
where WFH.WorkFlowNo=38
order by WFH.Step

produces:

FirstWF 38 544 1 Gancho_file DONE
FirstWF 38 544 1 Gancho_file DONE
FirstWF 38 544 1 Gancho_file DONE
FirstWF 38 544 1 Gancho_file DONE
FirstWF 38 544 1 Gancho_FTP DONE
FirstWF 38 544 1 Gancho_FTP DONE
FirstWF 38 544 1 Gancho_FTP DONE
FirstWF 38 544 1 Gancho_FTP DONE
FirstWF 38 544 1 Gancho_venta DONE
FirstWF 38 544 1 Gancho_venta DONE
FirstWF 38 544 1 Gancho_venta DONE
FirstWF 38 544 1 Gancho_venta DONE
FirstWF 38 545 2 Gancho_file ERROR
FirstWF 38 545 2 Gancho_file ERROR
FirstWF 38 545 2 Gancho_file ERROR
FirstWF 38 545 2 Gancho_file ERROR
FirstWF 38 545 2 Gancho_FTP ERROR
FirstWF 38 545 2 Gancho_FTP ERROR
FirstWF 38 545 2 Gancho_FTP ERROR
FirstWF 38 545 2 Gancho_FTP ERROR
FirstWF 38 545 2 Gancho_venta ERROR
FirstWF 38 545 2 Gancho_venta ERROR
FirstWF 38 545 2 Gancho_venta ERROR
FirstWF 38 545 2 Gancho_venta ERROR

note: no step 3, also repeating items which the view was not doing.

select * from WorkFlowDetail where Workflowname='FirstWF'
step
FirstWF Gancho_file 3
FirstWF Gancho_FTP 2
FirstWF Gancho_venta 1

Thanks again ..

microsoft.public.sqlserver.programming

sql group by question

jobs

Jim Underwood

jobs

Jim Underwood

jobs

Jim Underwood

jobs

x Login to ForumsZone