Showing posts with label mytable. Show all posts
Showing posts with label mytable. Show all posts

Friday, February 24, 2012

How to get last records in grouped query.

Hi,
I want to create query where I could group records by quarters, and get
the last record in each group.

e.g
Create Table MyTable
(
Value [float],
date[datetime]
)
Insert into MyTable (value, [date]) values (100, '1-1-2000')
Insert into MyTable (value, [date]) values (110, '1-2-2000')
Insert into MyTable (value, [date]) values (120, '1-3-2000')
Insert into MyTable (value, [date]) values (130, '1-4-2000')
Insert into MyTable (value, [date]) values (140, '1-5-2000')
Insert into MyTable (value, [date]) values (150, '1-6-2000')
Insert into MyTable (value, [date]) values (160, '1-7-2000')

Now I would like to get this data grouped by quarter, and get the last
value from each quarter. So here I would like to get result like this
(120, q1 -2000)
(150, q2 -2000)
(160, q3 -2000)
I know how to create aggregate functions but I have problem with getting
that last record from each group.

*** Sent via Developersdex http://www.developersdex.com ***I have solution unless somebody has better one:
Create temp table with dates only, grouped by quarter and do right join
query on MyTable.

Create Table #temp1
(
IdTemp1 int not null Primary key identity ,
[date] datetime
)
Insert into #temp1
(
[date]
)

Select
Max(date)
from MyTable
group by Year([date]), DatePart (quarter,[date])
order by Year([date]), DatePart (quarter,[date])

select * from MyTable right join #temp1 on mytable.date = #temp1.date

*** Sent via Developersdex http://www.developersdex.com ***|||SELECT A.Value, B.Yr, B.Qtr
FROM MyTable as A
JOIN (SELECT Yr = datepart(year, date),
Qtr = datepart(quarter, date),
max(date) as LastDate
FROM MyTable
GROUP BY datepart(year, date),
datepart(quarter, date)) as B
ON A.date = B.LastDate

Roy Harvey
Beacon Falls, CT

On 23 Jan 2007 06:57:11 GMT, schapopa <schapopawrote:

Quote:

Originally Posted by

>Hi,
>I want to create query where I could group records by quarters, and get
>the last record in each group.
>
>e.g
>Create Table MyTable
>(
>Value [float],
>date[datetime]
>)
>Insert into MyTable (value, [date]) values (100, '1-1-2000')
>Insert into MyTable (value, [date]) values (110, '1-2-2000')
>Insert into MyTable (value, [date]) values (120, '1-3-2000')
>Insert into MyTable (value, [date]) values (130, '1-4-2000')
>Insert into MyTable (value, [date]) values (140, '1-5-2000')
>Insert into MyTable (value, [date]) values (150, '1-6-2000')
>Insert into MyTable (value, [date]) values (160, '1-7-2000')
>
>Now I would like to get this data grouped by quarter, and get the last
>value from each quarter. So here I would like to get result like this
>(120, q1 -2000)
>(150, q2 -2000)
>(160, q3 -2000)
>I know how to create aggregate functions but I have problem with getting
>that last record from each group.
>
>
>
>*** Sent via Developersdex http://www.developersdex.com ***

Sunday, February 19, 2012

How to get from smalldatetime -field only date?

Hi!
I'm using MSDE 2000 SP4. I need to get only date from smalldatetime
using SQL statement like "SELECT MyDate FROM MyTable.".
Like this way I'll get MyDate as "27.11.2005 0:00:00" when using finnish
regional settings, but how to get just "27.11.2005" without that empty time?
I think this is quite easy to solve.
Thanks in advance!
Mika
You have to use the CONVERT Function to dispaly it in the proper way:
Select CONVERT(VARCHAR(10),'27.11.2005 0:00:00',103)
HTH, jens Suessmeyer.
|||Jens kirjoitti:
> You have to use the CONVERT Function to dispaly it in the proper way:
> Select CONVERT(VARCHAR(10),'27.11.2005 0:00:00',103)
> HTH, jens Suessmeyer.
>
Nice, thanks Jens!
Is there a link where those different form numbers like 103 is
described? Looks like 104 is like finnish form of date, which I just
guessed.
|||Look int he BOL which is delivered within SQL Server or use the online
BOL:
http://msdn.microsoft.com/library/en...ca-co_2f3o.asp
HTH, Jens Suessmeyer.