September 6, 2012 at 7:43 am
I have two tables
Table1
recordid description
1001 ABC
1002 DEF
Table2
id Status time recordid
1 NEW 2hr 1001
2 PEND 1hr 1001
3 NEW 5hr 1002
i want a report that is like
recordid description status-new-time status-pend-time
how can i do this???
September 6, 2012 at 7:53 am
qamar 52306 (9/6/2012)
I have two tablesTable1
recordid description
1001 ABC
1002 DEF
Table2
id Status time recordid
1 NEW 2hr 1001
2 PEND 1hr 1001
3 NEW 5hr 1002
i want a report that is like
recordid description status-new-time status-pend-time
how can i do this???
If Status & recordid together are unique in Table2, you can use this:
SELECT r.recordid
, r.description
, sn.time AS status-new-time
, sp.time AS status-pend-time
FROM Table1 r
LEFT JOIN Table2 sn ON r.recordid=s.recordid AND Status='NEW'
LEFT JOIN Table2 sp ON r.recordid=s.recordid AND Status='PEND'
Otherwise, I think alias sn & sp should be converted to subqueries.
September 6, 2012 at 7:58 am
I'd guess you'd want something like a PIVOT.
Something like
SELECT T1.recordid,
T1.description,
MAX(Case WHEN T2.Status = 'New' THEN T2.time END) AS NEwStatus,
MAX(Case WHEN T2.Status = 'PEND' THEN T2.time END) AS PendStatus
FROM Table1 AS T1 JOIN Table2 AS T2 ON T1.recordid = T2.recordid
GROUP BY T1.recordid, T1.description
September 6, 2012 at 8:08 am
Or, Using the actual PIVOT statement.
SELECT recordId,
[description], [NEW], [PEND]
FROM ( SELECT T1.recordId, T1.description, T2.Status, T2.Time
FROM Table1 AS T1 JOIN Table2 AS T2 ON T1.recordid = T2.recordid ) F
PIVOT ( MAX(Time) FOR Status in ([NEW], [PEND]) ) AS P;
September 6, 2012 at 8:17 am
Thanks ryan.mcatee brother, it solved my problem
thanks for quick response
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply