| Groups | Blog | Home |
sql server programming : Please help me with this query...thanks
[quoted text, click to view]
"Dandy WEYN" wrote: > You need to pivot the table > http://www.dandyman.net/sql/samplesByTopic.aspx?topicid=2&id=6 If you have pivoting with sql server on your mind perhaps you would like to check out the RAC utility for S2k. Be aware that RAC cuts to the chase: No more Cursors, No more Case, No more sql Code to put in your face:). RAC and QALite @ www.rac4sql.net
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
this will probably get you on the way
-- 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 [quoted text, click to view] "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 > > >
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 [quoted text, click to view] "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 > > >
Thanks a lot for your answer and an elegant way to solve my issue.
An additional question though, should I conclude from the answer that I'm "bound" to use SPs and cursors to get the job done. Isn't there a way to do some kind of self join operation... I'm asking since I'm not sure I'll be allowed to set up SPs or Views in the destination database. (I'll be using a vb.net app and ado (not ado.net) to execute the t-sql) Thanks again /Kim "Dandy WEYN" <no_spam_info@dandyman.net> skrev i meddelandet news:40390d9d$0$7043$ba620e4c@news.skynet.be... [quoted text, click to view] > 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 > > > > > > > >
I'm so sorry i didn't take a close look at your request
In fact the answer I provided you was totally wrong. try this select id2.id,id1.resourceid, id2.date, id2.quantity from tbl_resultset id1 join tbl_resultset id2 on id1.resourceid = id2.resourceid and id2.id > id1.id -- 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 [quoted text, click to view] "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 > > >
select * from TABLE a where a.DATE = ( select max(b.DATE) from TABLE b where
a.ID = b.ID ) [quoted text, click to view] "Kim" <kim@diz.se> wrote in message news:uEsYs2X%23DHA.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 > > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |