I don't even know what this might be called ...

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

SQL Server 2005 Forums

Transact-SQL (2005)

I don't even know what this might be called ...

Author

Topic

rhaydin
Starting Member

11 Posts

Posted - 2009-05-22 : 05:50:49

Hi there,

I'd really appreciate any help on this.
I don't even know what to call it so I've made a picture.

I'm familiar with SQL but I've never really made a query like this before.
I have an idea how I might do it by using variables but I've never used these in SQL. I'm quite proficient at Visual Basic so I understand most programming concepts. So please don't hold back.

Even if you could just point me in the right direction.

Thanks in advance,
Jamie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-22 : 05:56:15

PIVOT


DECLARE @sample TABLE
(
	TSORef		varchar(10),
	tsoType		CHAR(10),
	dateReceived	datetime
)

INSERT INTO @sample
SELECT	'PG0005', 'S', '2009-05-14 14:51:25'	UNION ALL
SELECT	'PG0005', 'D', '2009-05-14'

SELECT	TSORef, [S] AS [Date when new], [D] AS [Date launched]
FROM	@sample s
	pivot
	(
		MAX(dateReceived)
		FOR	tsoType IN ([S], [D])
	) p
/*
TSORef     Date when new                                          Date launched                                          
---------- ------------------------------------------------------ ------------------------------------------------------ 
PG0005     2009-05-14 14:51:25.000                                2009-05-14 00:00:00.000

(1 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

rhaydin
Starting Member

11 Posts

Posted - 2009-05-22 : 06:25:45

Khtan,

I'm sitting here with a huge grin on my face at the fact that there's people like yourself out there who take the time out to help people like me !
Thanks very much indeed ! -

Regards,
Jamie

rhaydin
Starting Member

11 Posts

Posted - 2009-05-22 : 07:18:20

Hey again,

I've started trying to make this code work for me in Access and was wondering :

Will this method work from within MS-Access ? - Just in case I spend ages trying to make it and realised that Access doesn't support some of the concepts.

Cheers again,
Jamie

kishore_pen
Starting Member

49 Posts

Posted - 2009-05-22 : 08:31:17

which ver. of access you are using ?

rhaydin
Starting Member

11 Posts

Posted - 2009-05-22 : 09:28:28

Hi there,

I'm using Access 2003 SP2.

Cheers,
Jamie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-22 : 09:29:40

Sorry . . I am not familiar with access. The fact that you posted in a SQL Server 2005 forum, the query i posted will works on SQL Server 2005 and above. I doubt it will work in Access

KH
[spoiler]Time is always against us[/spoiler]

rhaydin
Starting Member

11 Posts

Posted - 2009-05-22 : 09:34:25

I'm working from access but my tables are linking to an SQL server and are not actually stored in an access file.
It's a great start anyway as I know I need to look up pivoting queries and that I might actually be able to use variables too !

I'm on the right track thanks to your post so cheers.
Jamie

Wozer
Starting Member

29 Posts

Posted - 2009-05-22 : 11:20:43

What are you trying to get it to do in access? Are you trying to make a report with it? I have some limited knowledge of access and might be able to help

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-22 : 12:00:25

Simple answer:

You can create a VIEW on the database that uses khtan code.

Then query the view from access.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

rhaydin
Starting Member

11 Posts

Posted - 2009-05-27 : 04:36:32

Hey there,

I've started reading up on pivoting in SQL so hopfully I'll get on top of this soon.
Thanks for all your help !!

Charlie:
I'm snookered with creating a view as I've only got read access to the database !

Wozer:
Yea, I'm creating some queries to be used with reporting.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-27 : 04:46:01

In Access, you can do the same thing with the TRANSFORM statement.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-27 : 04:48:34

TRANSFORM MAX(dateReceived)
SELECT TSORef, dateReceived, currentStatus FROM Table1
PIVOT currentStatus IN ("new", "launched")

http://office.microsoft.com/en-us/access/HP010322771033.aspx

E 12°55'05.63"
N 56°04'39.26"

rhaydin
Starting Member

11 Posts

Posted - 2009-05-27 : 05:04:45

Hey Peso,

Thanks for that ! - This is excellent stuff and gets me back on the road !

Cheers again,
Jamie

rhaydin
Starting Member

11 Posts

Posted - 2009-05-27 : 09:50:51

Back again ..
Got my code working, this is it :

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;

I just got that working and now I've hit the next problem.
I need to create another field that is the difference between 'ro completed' and 'new'.

If this field were stored in a table then it would be easy for me to just reference the actualy values from the table, but I've not idea how to do it now that these dates are only displayed as fields from the results of my query when, in the table they are really just values.

Hope this makes sense any maybe somebody will be ok to lend another hand !

Cheers,
Jamie

rhaydin
Starting Member

11 Posts

Posted - 2009-05-28 : 10:36:19

Got this sorted, for anybody who needs to know :

You can use the pivot query as a source for a new query and then your pivoted values are now seen as fields which can have calculations performed on them.

Cheers,
JP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources