Re: Please help me with this query...thanks

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Dandy WEYN (no_spam_info_at_dandyman.net)
Date: 02/22/04 

Date: Sun, 22 Feb 2004 21:10:38 +0100

You need to pivot the table

http://www.dandyman.net/sql/samplesByTopic.aspx?topicid=2&id=6 

--
Dandy  Weyn, Belgium
MCSE, MCSA, MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql
"Kim" <kim@diz.se> wrote in message
news:uEsYs2X#DHA.3568@TK2MSFTNGP10.phx.gbl...
> How can I do this in one query
>
> I've got a table like this
>
> ID RESOURCEID DATE QUANTITY
>
> The Id is unique (but just a counter not of interest here), there may be
one
> resourceid for each date and then an asscoiated quantity.
> I want to select all resourceid (distinct) and I just want just the
> resourceid and the date and quantity associated  with that post that is
less
> than today but nearast in time.
>
> To examplify
>
> if some records look like like this
>
> ID     RESOURCEID      DATE              QUANTITY
> 1         1                          2003-12-01     1.3
> 2         1                          2004-01-25     1.4
> 3         2                          2002-01-01     1.7
> 4         2                          2003-07-27     2.8
>
> I want my select statement to return only the two following post
>
> ID     RESOURCEID      DATE              QUANTITY
> 2         1                      2004-01-25         1.4
> 4         2                      2003-07-27         2.8
>
> That is one post for each resource and the post should be the one were the
> date is less than today
>
> I want to do this without using stored procedures or view, since I'm not
> allowed to create this kind if objects in the database.
> I thought that perhaps using a #temp table would be a solution but I just
> can't get it to work as I want
>
> Thanks in advance
>
>
>

Relevant Pages

  • Re: Have Collection Exclude DCs
    ... select ResourceId, ResourceType, Name, SMSUniqueIdentifier, ... > 5 Primary Domain Controller ... Kim Oppalfens ...
    (microsoft.public.sms.admin)
  • Re: Please help me with this query...thanks
    ... Check my SQL Server resource pages (currently under construction) ... "Kim" wrote in message ... > The Id is unique, there may be one> resourceid for each date and then an asscoiated quantity. ...
    (microsoft.public.sqlserver.programming)