Help with returning first or last date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with returning first or last date

Author

Topic

cjones1234
Starting Member

3 Posts

Posted - 2015-03-18 : 04:25:07

Hi,

I'm looking for some help with creating a report in "Trac" using SQL. Basically we have a database running in Trac that manages a workflow, and goes through various stages Eg In Work, Review, etc. Now the nature of this means jobs can go in and out of the same state more than once. Therefore in my report I need to be able to return either the first date (or the last date) for any given state.

For example, when work is started it goes into Initial Layout during this phase of the design it can go on and off hold, so when I produce a report for metrics I want to see only the first time it went into the state Initial Layout.
Similar example for Layout Approval, but in this instance I would want the last recorded date it went into this state.

At the moment my report is returning multiple lines for the same "ticket" entry in the database, as it is listing each occurance any of the fields change.

Any help greatly appreciated ....
Colin

Here's my code:

select
t.id as ticket,
t.type as type,
a.value as Project_site,
t.component as Layout_Site,
(CASE WHEN b.value ISNULL THEN ' ' ELSE b.value END) as Project,
(CASE WHEN c.value ISNULL THEN ' ' ELSE c.value END) as Assy_number,
(CASE WHEN d.value ISNULL THEN ' ' ELSE d.value END) as Assy_name,
(CASE WHEN e.value ISNULL THEN ' ' ELSE e.value END) as Detail_number,
(CASE WHEN f.value ISNULL THEN ' ' ELSE f.value END) as ECO,
(CASE WHEN g.value ISNULL THEN ' ' ELSE g.value END) as CAD_package,
(CASE WHEN h.value ISNULL THEN ' ' ELSE h.value END) as Design_engineer,
(CASE WHEN i.value ISNULL THEN ' ' ELSE i.value END) as Layout_engineer,
(CASE WHEN j.value ISNULL THEN ' ' ELSE j.value END) as Board_Technology,

t.time AS created,
(CASE WHEN x.time ISNULL THEN ' ' ELSE date(x.time, 'unixepoch') END) as assigned,
(CASE WHEN w.time ISNULL THEN ' ' ELSE date(w.time, 'unixepoch') END) as Initial_layout,
(CASE WHEN r.time ISNULL THEN ' ' ELSE date(r.time, 'unixepoch') END) as Initial_layout_on_Hold,
(CASE WHEN y.time ISNULL THEN ' ' ELSE date(y.time, 'unixepoch') END) as Layout_Approval,
(CASE WHEN u.time ISNULL THEN ' ' ELSE date(u.time, 'unixepoch') END) as Create_DataPack,
(CASE WHEN v.time ISNULL THEN ' ' ELSE date(v.time, 'unixepoch') END) as PDM_Approval,
(CASE WHEN z.time ISNULL THEN ' ' ELSE date(z.time, 'unixepoch') END) as closed_date,
(CASE WHEN s.time ISNULL THEN ' ' ELSE date(s.time, 'unixepoch') END) as WIP

from ticket t
left outer join ticket_custom a on (t.id = a.ticket and a.name = 'site')
left outer join ticket_custom b on (t.id = b.ticket and b.name = 'project')
left outer join ticket_custom c on (t.id = c.ticket and c.name = 'assm_number')
left outer join ticket_custom d on (t.id = d.ticket and d.name = 'assm_name')
left outer join ticket_custom e on (t.id = e.ticket and e.name = 'detail_number')
left outer join ticket_custom f on (t.id = f.ticket and f.name = 'eco')
left outer join ticket_custom g on (t.id = g.ticket and g.name = 'cadpackage')
left outer join ticket_custom h on (t.id = h.ticket and h.name = 'design_engineer')
left outer join ticket_custom i on (t.id = i.ticket and i.name = 'layout_engineer')
left outer join ticket_custom j on (t.id = j.ticket and j.name = 'technology')

left outer join ticket_change x on (t.id = x.ticket and x.newvalue = 'assigned')
left outer join ticket_change w on (t.id = w.ticket and w.newvalue = "'Initial Layout'")
left outer join ticket_change r on (t.id = r.ticket and r.newvalue = "'Initial Layout on Hold'")
left outer join ticket_change y on (t.id = y.ticket and y.newvalue = "'Layout Approval'")
left outer join ticket_change u on (t.id = u.ticket and u.newvalue = "'Design Datapack'")
left outer join ticket_change v on (t.id = v.ticket and v.newvalue = "'PDM Approvals'")
left outer join ticket_change s on (t.id = s.ticket and s.newvalue = "'Work In Progress'")

inner join ticket_change z on (t.id = z.ticket and z.newvalue ='closed')

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-03-18 : 06:12:39

So you want to return the first date for state Initial Layout
and the last date for Layout Approval, assuming these field values remain stamped? are these 2 fields to be used for the start/last date result? and all other dates not to be returned or are you referring to date parameters where you can select start and last dates.

We are the creators of our own reality!

cjones1234
Starting Member

3 Posts

Posted - 2015-03-19 : 08:29:52

quote:
Originally posted by sz1

So you want to return the first date for state Initial Layout
and the last date for Layout Approval, assuming these field values remain stamped? are these 2 fields to be used for the start/last date result? and all other dates not to be returned or are you referring to date parameters where you can select start and last dates.

We are the creators of our own reality!

The work flow in our Trac database can move in and out of various states, and the date is automatically stamped. It is not over written. So when I run a report just now, I get multiple entries for the same task because it reports every instance of that task when multiple data is recorded in these date fields. Basically can SQL look at these dates for the same state and then only return the first or last depending on how I define it? thanks

Kristen
Test

22859 Posts

Posted - 2015-03-19 : 08:52:02

quote:
Originally posted by cjones1234

Basically can SQL look at these dates for the same state and then only return the first or last depending on how I define it?

Yes. The way I would do it is to replace the TABLE JOIN with a JOIN to a sub-query which used the ROW_NUMBER() function and from that select the first / last row (ordered by date/whatever)

Kristen
Test

22859 Posts

Posted - 2015-03-19 : 08:59:10

Something like this maybe. CTE might be a better way to do it though ...


JOIN
(
SELECT DataColumn1, DataColumn2, ... ,
ROW_NUMBER() OVER(PARTITION BY ticket ORDER BY time) AS RowNoFirst,
ROW_NUMBER() OVER(PARTITION BY ticket ORDER BY time DESC) RowNoLast
FROM ticket_change
) AS X
on t.id = x.ticket
and x.newvalue = 'assigned'
AND RowNoFirst = 1 -- ... or RowNoLast=1

cjones1234
Starting Member

3 Posts

Posted - 2015-03-19 : 12:00:46

quote:
Originally posted by Kristen

Something like this maybe....

Thanks for your help Kristen in replying. I'm no software/ database expert, more of a hardware engineer, so struggling along trying to make the code to work.

Gave your idea to a fellow colleague and he reckons its the right approach, just need to get it working with teh rest of the SQL code to create the report I need ....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources