Help me to write a query

Question

Post reply

Help me to write a query

lulu2792

Newbie

Points: 9
More actions
April 27, 2010 at 8:39 pm

#218269

My question is at here:
http://stackoverflow.com/questions/2726369/help-to-the-way-to-write-a-query-for-the-requirement
Please help me. Thanks.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

You'll (hopefully) find your answer over at stackoverflow... 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

lmu92 (4/28/2010)
You'll (hopefully) find your answer over at stackoverflow... 😉

LOL, Lutz 😀

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 3

People here may not have, or may not want, StackOverflow logins to answer your question. If you want an answer here, ask the question here. Otherwise, wait patiently over there.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

LutzM SSC Guru Points: 107049 More actions · Answer 4

Grant Fritchey (4/28/2010)
People here may not have, or may not want, StackOverflow logins to answer your question. If you want an answer here, ask the question here. Otherwise, wait patiently over there.

That's the "more polite version" of what I was trying to tell... Thanx for the better wording, Grant! 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 5

lmu92 (4/28/2010)
Grant Fritchey (4/28/2010)
People here may not have, or may not want, StackOverflow logins to answer your question. If you want an answer here, ask the question here. Otherwise, wait patiently over there.
That's the "more polite version" of what I was trying to tell... Thanx for the better wording, Grant! 😉

Really, I thought yours was more polite. Mine was just wordier.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Please post your question here. You will get a better response in that case.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

LutzM SSC Guru Points: 107049 More actions · Answer 7

Did you notice? Based on the last login info the OP obviously lost interest or found an answer...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 8

No question, No answer. I think that was OP's first post.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 9

It was answered over there.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Grant Fritchey (4/29/2010)
It was answered over there.

It would have been nice to have had it posted it here along with the answer.

Glad it was answered:rolleyes:

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 11

The question was;

I need to write a SQL-Server query but I don't know how to solve. I have a table RealtimeData with data:
Time | Value
4/29/2009 12:00:00 AM | 3672.0000
4/29/2009 12:01:00 AM | 3645.0000
4/29/2009 12:02:00 AM | 3677.0000
4/29/2009 12:03:00 AM | 3634.0000
4/29/2009 12:04:00 AM | 3676.0000 // is EOD of day "4/29/2009"
4/30/2009 12:00:00 AM | 3671.0000
4/30/2009 12:01:00 AM | 3643.0000
4/30/2009 12:02:00 AM | 3672.0000
4/30/2009 12:03:00 AM | 3634.0000
4/30/2009 12:04:00 AM | 3632.0000
4/30/2009 12:05:00 AM | 3672.0000 // is EOD of day "4/30/2009"
5/1/2009 12:00:00 AM | 3673.0000
5/1/2009 12:01:00 AM | 3642.0000
5/1/2009 12:02:00 AM | 3672.0000
5/1/2009 12:03:00 AM | 3634.0000
5/1/2009 12:04:00 AM | 3635.0000 // is EOD of day "5/1/2009"
I want to get the EOD's data of days which exist in table. (EOD = end of day). With the my sample's data, I will need to reture a table like following:
Time | Value
4/29/2009 | 3676.0000
4/30/2009 | 3672.0000
5/1/2009 | 3635.0000
Note: I write the comment so that you can know where is EOD. And SQL Server is version 2005.
Note: the data in RealtimeData table is very large with more than 400.000 rows. Please help me to write in optimization.
Please help me to solve my problem. Thanks.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 12

Solution according to me is...

;With wcte as (

Select vTime,vValue,Row_Number() over (partition by Convert(DateTime,Convert(varchar(10),vTime,110)) order by vTime Desc) rno

from @vTable

)Select vTime,vValue from wcte where rno = 1

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

ranjitpullat Valued Member Points: 67 More actions · Answer 13

Try this one

;WITH mas as(

select Max(mas.Time) as [Time]

from RealtimeData mas

GROUP BY convert(nvarchar,mas.Time,103)

)

select * from mas

CROSS APPLY

(

SELECT Value from RealtimeData sub

where sub.Time = mas.Time

) as sub

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 14

Try this one
;WITH mas as(
select Max(mas.Time) as [Time]
from RealtimeData mas
GROUP BY convert(nvarchar,mas.Time,103)
)
select * from mas
CROSS APPLY
(
SELECT Value from RealtimeData sub
where sub.Time = mas.Time
) as sub

This question was not originally posted here. Sorry for confusion.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]