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: --CELKO-- on 10 May 2010 18:14 >> 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! << Funny, I set my installations to handle it without any problems. I see that the new DATE data types are using it, too. Can you see the writing on the wall? Or your display screen, as it were? You seem to feel that Redmond is the center of the universe, or is it just the UK dialect? I live in a world where ISO does matter. I am not as provincial as you are. "Caesar: Pardon him, Theodotus. He is a barbarian and thinks the customs of his tribe and island are the laws of nature." - Caesar and Cleopatra; George Bernard Shaw 1898
From: TheSQLGuru on 10 May 2010 18:29 No, what Tony and all the other's on this forum feel is that (as you have been told COUNTLESS times) THIS IS A MICROSOFT SQL SERVER SPECIFIC FORUM!!! -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "--CELKO--" <jcelko212(a)earthlink.net> wrote in message news:0a35d634-4fa7-4db6-a94e-e735593ca3c7(a)t17g2000yqg.googlegroups.com... >> 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! << Funny, I set my installations to handle it without any problems. I see that the new DATE data types are using it, too. Can you see the writing on the wall? Or your display screen, as it were? You seem to feel that Redmond is the center of the universe, or is it just the UK dialect? I live in a world where ISO does matter. I am not as provincial as you are. "Caesar: Pardon him, Theodotus. He is a barbarian and thinks the customs of his tribe and island are the laws of nature." - Caesar and Cleopatra; George Bernard Shaw 1898
From: Tony Rogerson on 11 May 2010 00:26 > Funny, I set my installations to handle it without any problems. I see When Austin Texas is the centre of your universe you'll have no problem. When all you deal with is theory using the ISO SQL Standard you'll have no problem because the ISO SQL has no concept of regionalisation. When ignorance is first nature to you you'll have no problem. It's seriously unprofessional when you know something is not consistent across all set up configurations to still be using and recommending it to people. Indeed your quote is ironic, "Caesar: Pardon him, Theodotus. He is a barbarian and thinks the customs of his tribe and island are the laws of nature." - Caesar and Cleopatra; George Bernard Shaw 1898" because it absolutely describes your own behavior here! Dozens of experts cannot be wrong and its been dozens of experts who have been telling you this fact of inconsistency for many years now --CELKO-- --ROGGIE "--CELKO--" <jcelko212(a)earthlink.net> wrote in message news:0a35d634-4fa7-4db6-a94e-e735593ca3c7(a)t17g2000yqg.googlegroups.com... >>> 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! << > > Funny, I set my installations to handle it without any problems. I see > that the new DATE data types are using it, too. Can you see the > writing on the wall? Or your display screen, as it were? > > You seem to feel that Redmond is the center of the universe, or is it > just the UK dialect? I live in a world where ISO does matter. I am not > as provincial as you are. > > "Caesar: Pardon him, Theodotus. He is a barbarian and thinks the > customs of his tribe and island are the laws of nature." - Caesar and > Cleopatra; George Bernard Shaw 1898
From: T-SQL help on 11 May 2010 23:12
On May 10, 5:40 pm, "Dan Guzman" <guzma...(a)nospam- online.sbcglobal.net> wrote: > > 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 MVPhttp://weblogs.sqlteam.com/dang/ > > "T-SQL help" <dinesh.van...(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- Hide quoted text - > > - Show quoted text - Thank you very much Dan, Plamen! |