Need Query help
in [Programming SQL]
|
Prev: SQL Job, DTS and RunAs (Impersonation)
Next: Access frontend with SQL Server backend: my solution for ODBCCall error
From: T-SQL help on 7 May 2010 07:03 Hello, [SQL2000] CREATE TABLE toll_drive ( Amount INT, dte DATETIME ); GO INSERT INTO toll_drive SELECT 10,'JAN 1 2010'; INSERT INTO toll_drive SELECT 100,'JAN 11 2010'; INSERT INTO toll_drive SELECT 65,'JAN 30 2010'; INSERT INTO toll_drive SELECT 10,'JAN 31 2010'; INSERT INTO toll_drive SELECT 24,'FEB 07 2010'; INSERT INTO toll_drive SELECT 50,'MAR 03 2010'; INSERT INTO toll_drive SELECT 60,'MAR 30 2010'; INSERT INTO toll_drive SELECT 90,'MAR 31 2010'; INSERT INTO toll_drive SELECT 100,'APR 14 2010'; GO I am looking for resultset: Amount Month 10 Jan 31 2010 24 Feb 07 2010 90 Mar 31 2010 100 Apr 14 2010 i.e I want the amount against the max date for a month. Thus there would be only one entry against a month and that too the value against the most latest date. Thank you.
From: Dan Guzman on 7 May 2010 08:02 > i.e I want the amount against the max date for a month. Thus there > would be only one entry against a month and that too the value against > the most latest date. Assuming dte is unique within a given month, one method: SELECT Amount, dte FROM dbo.toll_drive WHERE dte IN ( SELECT MAX(dte) FROM dbo.toll_drive GROUP BY DATEADD(month, DATEDIFF(month, '19000101', dte), '19000101') ); If dte is non-unqiue, you'll need to provide criteria to break ties. -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ "T-SQL help" <dinesh.vanchi(a)gmail.com> wrote in message news:c2682ae0-2528-453b-af78-b92ed7a04cbe(a)h20g2000prn.googlegroups.com... > Hello, > > [SQL2000] > > CREATE TABLE toll_drive > ( > Amount INT, > dte DATETIME > ); > GO > INSERT INTO toll_drive SELECT 10,'JAN 1 2010'; > INSERT INTO toll_drive SELECT 100,'JAN 11 2010'; > INSERT INTO toll_drive SELECT 65,'JAN 30 2010'; > INSERT INTO toll_drive SELECT 10,'JAN 31 2010'; > INSERT INTO toll_drive SELECT 24,'FEB 07 2010'; > INSERT INTO toll_drive SELECT 50,'MAR 03 2010'; > INSERT INTO toll_drive SELECT 60,'MAR 30 2010'; > INSERT INTO toll_drive SELECT 90,'MAR 31 2010'; > INSERT INTO toll_drive SELECT 100,'APR 14 2010'; > GO > > > I am looking for resultset: > > Amount Month > 10 Jan 31 2010 > 24 Feb 07 2010 > 90 Mar 31 2010 > 100 Apr 14 2010 > > i.e I want the amount against the max date for a month. Thus there > would be only one entry against a month and that too the value against > the most latest date. > > Thank you.
From: Plamen Ratchev on 7 May 2010 10:00 Here is another solution: SELECT amount, dte FROM toll_drive AS A WHERE dte = (SELECT MAX(B.dte) FROM toll_drive AS B WHERE B.dte < DATEADD(MONTH, DATEDIFF(MONTH, '20010101', A.dte), '20010201')); -- Plamen Ratchev http://www.SQLStudio.com
From: --CELKO-- on 8 May 2010 15:32 Did you mean to post a real table, with keys and all that RDBMS stuff? Do you know about ANSI/ISO Standard date format and INSERT INTO statements versus dialect? CREATE TABLE Toll_Trips (trip_date DATE NOT NULL PRIMARY KEY, toll_amt INTEGER NOT NULL); INSERT INTO Toll_Trips (toll_amt, trip_date) VALUES (10, '2010-01-01'), (100, '2010-01-11'), (65, '2010-01-30'), (10, '2010-01-31'), (24, '2010-03-07'), (50, '2010-03-04'), (60, '2010-03-30'), (90, '2010-03-31'), (100, '2010-04-14'); Just for fun, untested: SELECT T1.trip_date, T1.toll_amt FROM Toll_Trips AS T1 WHERE trip_date IN (SELECT MAX(trip_date) OVER (PARTITION BY DATEPART(YEAR, trip_date), DATEPART(MONTH, trip_date)) FROM Toll_Trips AS T2); The idea is that the subquery expression will be computed once since it is not correlated to the outer query
From: Tony Rogerson on 8 May 2010 16:07
> (65, '2010-01-30'), (10, '2010-01-31'), Why are you using the "display friendly" version of ISO 8601 when we do not do display nor formatting in the back end? By definition of ISO 8601 you should be using YYYYMMDD. A quick check of books online will confirm that is achievable. Also, why aren't you using a Calendar table instead of the proprietary DATEPART function? --ROGGIE-- "--CELKO--" <jcelko212(a)earthlink.net> wrote in message news:febaf01b-c4f8-4463-a831-670bbb941fba(a)37g2000yqm.googlegroups.com... > Did you mean to post a real table, with keys and all that RDBMS stuff? > Do you know about ANSI/ISO Standard date format and INSERT INTO > statements versus dialect? > > CREATE TABLE Toll_Trips > (trip_date DATE NOT NULL PRIMARY KEY, > toll_amt INTEGER NOT NULL); > > INSERT INTO Toll_Trips (toll_amt, trip_date) > VALUES (10, '2010-01-01'), (100, '2010-01-11'), > (65, '2010-01-30'), (10, '2010-01-31'), > (24, '2010-03-07'), (50, '2010-03-04'), > (60, '2010-03-30'), (90, '2010-03-31'), > (100, '2010-04-14'); > > Just for fun, untested: > > SELECT T1.trip_date, T1.toll_amt > FROM Toll_Trips AS T1 > WHERE trip_date > IN (SELECT MAX(trip_date) > OVER (PARTITION BY DATEPART(YEAR, trip_date), > DATEPART(MONTH, trip_date)) > FROM Toll_Trips AS T2); > > The idea is that the subquery expression will be computed once since > it is not correlated to the outer query > |