Combine Columns within SQL 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

General SQL Server Forums

New to SQL Server Programming

Combine Columns within SQL Query

Author

Topic

wrathyimp
Starting Member

14 Posts

Posted - 2011-08-18 : 05:19:53

Hi,

I have a sql view below:
MS SQL Syntax (Toggle Plain Text)

SELECT b.Stk AS secode,
b.TktE AS Ticket,
CAST(a.Last / 10 AS integer(10, 3)) AS Last,
CASE WHEN CONVERT(integer, a.Last) <> 0 THEN (CONVERT(integer, a.Last) - CONVERT(integer, a.Ref)) / 10 ELSE (CONVERT(integer, a.Ref) - CONVERT(integer, a.Prev)) / 10 END AS CHANGE,
CAST(a.High / 10 AS integer(10, 3)) AS High,
CAST(a.Low / 10 AS integer(10, 3)) AS Low,
CAST(a.Vol AS integer) * 100 AS Vol,
a.Deal AS Trades,
CAST(a.Amt AS integer) AS Value,
a.Prev,
a.Ref,
CAST(a.Bid / 10 AS integer(10, 3)) AS Bid,
CAST(a.Ask / 10 AS integer(10, 3)) AS Ask,
CAST(ASCII(LEFT(a.DatP, 1)) AS Integer) + '1922' AS Year,
CAST(ASCII(RIGHT(LEFT(a.DatP, 2), 1)) AS Integer) - '48' AS Month
CAST(ASCII(RIGHT(a.DatP, 1)) AS Integer) - '48' AS Day,

FROM StkQt AS a INNER JOIN
StockList AS b ON a.Row = b.Row

I need to merge the last three (year,month,day) columns as Date; within the same query.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-18 : 06:03:48

see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339

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

wrathyimp
Starting Member

14 Posts

Posted - 2011-08-18 : 06:37:33

Khtan, thanks for the link,
But i didnt get it.
Which part?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-18 : 07:02:12

anyone, take your pick
- DateSerial
- MDate

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

wrathyimp
Starting Member

14 Posts

Posted - 2011-08-18 : 07:39:05

Sorry to be asking such question, as i am new to sql commands.
Where to add the function MDate in the above mentioned query?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-18 : 07:42:43

[code]
select . . .
dbo.MDate
(
CAST(ASCII(LEFT(a.DatP, 1)) AS Integer) + '1922' , -- Year
CAST(ASCII(RIGHT(LEFT(a.DatP, 2), 1)) AS Integer) - '48' , -- Month
CAST(ASCII(RIGHT(a.DatP, 1)) AS Integer) - '48' -- Day
) AS theDATE
from . . .
[/code]

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

wrathyimp
Starting Member

14 Posts

Posted - 2011-08-18 : 08:06:35

Getting Error Invalid Object Name 'dbo.MDate'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-18 : 08:13:55

you need the create the MDate function from the link that I posted

go to that link, copy the text starting from create function . . and then paste into a new query window and execute

I have copied the function over here


create function MDate (@Year int, @Month int, @Day int)
returns datetime
as
begin
    return dateadd(month,(12*@Year)-22801+@Month,@Day-1)
end

copy and paste the code in BLUE in your query window and execute to create the MDate function

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

wrathyimp
Starting Member

14 Posts

Posted - 2011-08-18 : 08:24:11

this is my query:

CREATE FUNCTION MDate(@Year int, @Month int, @Day int) RETURNS datetime AS BEGIN RETURN dateadd(month, (12 * @Year) - 22801 + @Month,
@Day - 1) END SELECT b.Stk AS ksecode, b.TkrE AS Ticker, CAST(a.Last / 10 AS integer(10, 3)) AS Last, CASE WHEN CONVERT(integer, a.Last)
<> 0 THEN (CONVERT(integer, a.Last) - CONVERT(integer, a.Ref)) / 10 ELSE (CONVERT(integer, a.Ref) - CONVERT(integer, a.Prev))
/ 10 END AS Change, CAST(a.[Open] / 10 AS Integer(10, 3)) AS [Open], CAST(a.High / 10 AS integer(10, 3)) AS High,
CAST(a.Low / 10 AS integer(10, 3)) AS Low, CAST(a.Vol AS integer) * 100 AS Vol, a.Deal AS Trades, CAST(a.Amt AS integer) AS Value,
a.Prev, a.Ref, CAST(a.Bid / 10 AS integer(10, 3)) AS Bid, CAST(a.Ask / 10 AS integer(10, 3)) AS Ask,
dbo.MDate(CAST(ASCII(LEFT(a.DatP, 1)) AS Integer) + '1922', CAST(ASCII(RIGHT(LEFT(a.DatP, 2), 1)) AS Integer) - '48',
CAST(ASCII(RIGHT(a.DatP, 1)) AS Integer) - '48') AS theDATE
FROM StockQuotation AS a INNER JOIN
StockList AS b ON a.Row = b.Row

Its not working, giving syntax error:
Incorrect Syntax near keyword SELECT

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-18 : 08:29:35

you got it all wrong

1. open a new query window
2. copy & paste the following text in blue


create function MDate (@Year int, @Month int, @Day int)
returns datetime
as
begin
    return dateadd(month,(12*@Year)-22801+@Month,@Day-1)
end

3. execute

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

wrathyimp
Starting Member

14 Posts

Posted - 2011-08-18 : 08:51:49

Great Thanks

wrathyimp
Starting Member

14 Posts

Posted - 2011-08-18 : 09:13:27

One more quesetion related to the this sql query, i need to generate a excel from this query result.

How can i have a command to export results to excel in the query.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-18 : 09:19:18

refer to this thread

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources