Prev: SQL Job, DTS and RunAs (Impersonation)
Next: Access frontend with SQL Server backend: my solution for ODBCCall error
From: T-SQL help on 10 May 2010 02:29
Thank you Plamen, Dan,Crosan. I missed out on an important column in
my table, I apologize. Here is the updated requirement:

CREATE TABLE toll_drive
(
Amount INT,
Route_nme VARCHAR(30),
dte DATETIME
);
GO
INSERT INTO toll_drive SELECT 10,'Route1','JAN 1 2010';
INSERT INTO toll_drive SELECT 100,'Route2','JAN 11 2010';
INSERT INTO toll_drive SELECT 65,'Route1','JAN 30 2010';
INSERT INTO toll_drive SELECT 10,'Route2','JAN 31 2010';
INSERT INTO toll_drive SELECT 24,'Route1','FEB 07 2010';
INSERT INTO toll_drive SELECT 50,'Route2','MAR 03 2010';
INSERT INTO toll_drive SELECT 60,'Route1','MAR 30 2010';
INSERT INTO toll_drive SELECT 90,'Route2','MAR 31 2010';
INSERT INTO toll_drive SELECT 100,'Route1','APR 14 2010';
INSERT INTO toll_drive SELECT 200,'Route1','APR 24 2010';
GO

Amount Route Month
65 Route1 Jan 30 2010
10 Route2 Jan 31 2010
24 Route2 Feb 07 2010
60 Route1 Mar 30 2010
90 Route2 Mar 31 2010
200 Route1 Apr 24 2010

i.e I want the amount against the max date for a month for each Route.
Thus there
would be only one entry against a month and that too the value
against
the most latest date for each Route. Also, please note that dte is NOT
unique and the combination of Route&Dte is unique.

Joe,
This is a test table. Hence, I was casual about Keys and Constraints.


Thank you.

On May 7, 7:00 pm, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote:
> 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 Ratchevhttp://www.SQLStudio.com

From: Dan Guzman on 10 May 2010 08:40
> i.e I want the amount against the max date for a month for each Route.

Here's one solution.

SELECT
toll_drive.Amount,
toll_drive.Route_nme AS Route,
toll_drive.dte AS Month
FROM dbo.toll_drive
JOIN (
SELECT
Route_nme,
MAX(dte) AS max_dte,
DATEADD(month, DATEDIFF(month, '19000101', dte), '19000101') AS
Month
FROM dbo.toll_drive
GROUP BY
DATEADD(month, DATEDIFF(month, '19000101', dte), '19000101'),
Route_nme) AS RouteSummary
ON RouteSummary.Route_nme = toll_drive.Route_nme
AND RouteSummary.max_dte = toll_drive.dte
ORDER BY
Month,
Route;

--
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:38ac5e58-2797-42b6-b633-a3d4212bd619(a)u3g2000prl.googlegroups.com...
> Thank you Plamen, Dan,Crosan. I missed out on an important column in
> my table, I apologize. Here is the updated requirement:
>
> CREATE TABLE toll_drive
> (
> Amount INT,
> Route_nme VARCHAR(30),
> dte DATETIME
> );
> GO
> INSERT INTO toll_drive SELECT 10,'Route1','JAN 1 2010';
> INSERT INTO toll_drive SELECT 100,'Route2','JAN 11 2010';
> INSERT INTO toll_drive SELECT 65,'Route1','JAN 30 2010';
> INSERT INTO toll_drive SELECT 10,'Route2','JAN 31 2010';
> INSERT INTO toll_drive SELECT 24,'Route1','FEB 07 2010';
> INSERT INTO toll_drive SELECT 50,'Route2','MAR 03 2010';
> INSERT INTO toll_drive SELECT 60,'Route1','MAR 30 2010';
> INSERT INTO toll_drive SELECT 90,'Route2','MAR 31 2010';
> INSERT INTO toll_drive SELECT 100,'Route1','APR 14 2010';
> INSERT INTO toll_drive SELECT 200,'Route1','APR 24 2010';
> GO
>
> Amount Route Month
> 65 Route1 Jan 30 2010
> 10 Route2 Jan 31 2010
> 24 Route2 Feb 07 2010
> 60 Route1 Mar 30 2010
> 90 Route2 Mar 31 2010
> 200 Route1 Apr 24 2010
>
> i.e I want the amount against the max date for a month for each Route.
> Thus there
> would be only one entry against a month and that too the value
> against
> the most latest date for each Route. Also, please note that dte is NOT
> unique and the combination of Route&Dte is unique.
>
> Joe,
> This is a test table. Hence, I was casual about Keys and Constraints.
>
>
> Thank you.
>
> On May 7, 7:00 pm, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote:
>> 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 Ratchevhttp://www.SQLStudio.com
>
From: Plamen Ratchev on 10 May 2010 10:57
Here is my query adjusted to the new requirements:

SELECT amount, route_nme, dte
FROM toll_drive AS A
WHERE dte = (SELECT MAX(B.dte)
FROM toll_drive AS B
WHERE B.route_nme = A.route_nme
AND B.dte < DATEADD(MONTH,
DATEDIFF(MONTH, '20010101',
A.dte), '20010201'));

--
Plamen Ratchev
http://www.SQLStudio.com
From: --CELKO-- on 10 May 2010 15:11
>> By definition of ISO 8601 you should be using YYYYMMDD. <<

Dead wrong again. The only ISO-8601 date format allowed in Standard
SQL has the dashes. Would you like me to do that cut & paste again?

5.3. Literals

<date literal> ::= DATE <date string>

<date string> ::= <quote> <unquoted date string> <quote>

<unquoted date string> ::= <date value>

<date value> ::=
<years value> <minus sign> <months value> <minus sign> <days value>

<years value> ::= <datetime value>
<months value> ::= <datetime value>
<days value> ::= <datetime value>

<datetime value> ::= <unsigned integer>

===========

The reason for having only one format is to keep things simple and
portable. I personally would like the 'T' format because it is one
string without any spaces, it is easy to read, and Standard.
From: Tony Rogerson on 10 May 2010 15:27
It would appear you have the memory of a gold fish as for the how many times
is it now you need to be reminded?

YYYY-MM-DD is not consistent in the SQL Server product.

Do you really want people getting or not getting their drugs because they
followed your advice?

ISO 8601 allows YYYY-MM-DD and YYYMMDD, the ISO 8601 standard states that
YYYY-MM-DD is the "display friendly" version.

Now, to ISO SQL, you are correct the only date literal allowed is YYYY-MM-DD
which should be prefixed with the DATE operator.

SQL Server nor most other products have the DATE operator...

> <date literal> ::= DATE <date string>

So we cannot implement the ISO SQL standard YYYY-MM-DD we must use dialect;
unfortunately in SQL Server dialect we are subject to regional settings and
the inconsistency with YYYY-MM-DD outside the centre of the universe -
Texas, but you not all this already!

Seriously --CELKO-- why are you so dam arrogant on this particular issue?
You just will not accept the evidence of the inconsistency, countless
product experts have shown you in black and white.

I look forward to the day (will be in the next few months) when this NNTP
forum is closed down and we go to the moderated Microsoft ones - you will
not be able to get away with the behavior you've been getting away with for
the past decade on here - won't be long now!

--ROGGIE--

"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:49095a91-0cef-4909-b403-d5e1de0b7149(a)d19g2000yqf.googlegroups.com...
>>> By definition of ISO 8601 you should be using YYYYMMDD. <<
>
> Dead wrong again. The only ISO-8601 date format allowed in Standard
> SQL has the dashes. Would you like me to do that cut & paste again?
>
> 5.3. Literals
>
> <date literal> ::= DATE <date string>
>
> <date string> ::= <quote> <unquoted date string> <quote>
>
> <unquoted date string> ::= <date value>
>
> <date value> ::=
> <years value> <minus sign> <months value> <minus sign> <days value>
>
> <years value> ::= <datetime value>
> <months value> ::= <datetime value>
> <days value> ::= <datetime value>
>
> <datetime value> ::= <unsigned integer>
>
> ===========
>
> The reason for having only one format is to keep things simple and
> portable. I personally would like the 'T' format because it is one
> string without any spaces, it is easy to read, and Standard.

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: SQL Job, DTS and RunAs (Impersonation)
Next: Access frontend with SQL Server backend: my solution for ODBCCall error