Add a custom/calculated field to a cross-tab query

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

Other Forums

MS Access

Add a custom/calculated field to a cross-tab query

Author

Topic

rhaydin
Starting Member

11 Posts

Posted - 2009-05-28 : 05:04:16

Hi there,

I've created a cross-tab (privot) query.

Among the data are two dates. These dates have been pivoted up to read horizontally. They are usually under one field (date) but this field holds dates for various changes and my query shows which dates these changes happened.

Now I need to calculated the number of days between the dates.

Usually, I could create a function that references table fields, but as these dates are not actually stored as separated fields/colums in the table (the reason I transformed them) I can not find a way to work with them.

So, I had :

REF--- Date_Modified--- Status
1----- 01/01/02--- Accepted
1----- 01/02/02--- New
2----- 01/01/02--- Accepted
2----- 01/02/02--- New

and I've transformed to look like this :

Ref--- Accepted--- New
1--- 01/01/02--- 01/02/02
2--- 01/01/02--- 01/02/02

I need to create another field called "difference" that shows the number of days in-between the Accepted and New fields.

How can I do this ?

Cheers very much,
Jamie

Code:

TRANSFORM Max(dbo_tsoHistoryView.dateReceived) AS MaxOfdateReceived

SELECT dbo_tsoHistoryView.TSORef, dbo_tsoHistoryView.title

FROM dbo_tsoHistoryView

WHERE (((dbo_tsoHistoryView.currentStatus)='ro accepted' Or (dbo_tsoHistoryView.currentStatus)='new') AND ((dbo_tsoHistoryView.TSORef) In (SELECT dbo_tsohistoryview.TSORef FROM

dbo_tsohistoryview WHERE lcase(dbo_tsohistoryview.currentStatus) = 'new') And (dbo_tsoHistoryView.TSORef) In (SELECT dbo_tsohistoryview.TSORef FROM dbo_tsohistoryview WHERE

lcase(dbo_tsohistoryview.currentStatus) = 'ro accepted')))

GROUP BY dbo_tsoHistoryView.TSORef, dbo_tsoHistoryView.title

PIVOT dbo_tsoHistoryView.currentStatus;

rhaydin
Starting Member

11 Posts

Posted - 2009-05-28 : 06:23:24

It's ok, I found out how to do this.
Just build another query using the pivot query as the source and then make calculated fields from there !

Cheers,
Jamie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources