RE: Unknown command format

[Ray Thompson]

First simplify the query. Your where clause has some redundant conditions.
Try the following:

SELECT format(theDate,'mmmm yyyy') AS THE_DATE,

       theMonth,

       theYear,

       SUM(Views) AS Total_Views,

       SUM(Visitors) AS Total_Visitors,

       SUM(Visits) AS Total_Visits,

       SUM(NewVisitors) AS

       Total_NewVisitors,

       SUM(RetVisitors) AS Total_RetVisitors

FROM nustatsl.times

WHERE TheMonth = 04

  AND TheYear in (03,04)

GROUP BY format(theDate,'mmmm yyyy'),

      theMonth,

      theYear

ORDER BY theMonth,

         theYear DESC

OK, now what is not working? Are you getting an error? Incorrect data?

  _____  

From: Andrew Scott [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 21, 2004 7:58 PM
To: SQL
Subject: Unknown command format

Hi people, I have the following query below that works in our development
SQL Server, but not out production server.

SELECT     format(theDate, 'mmmm yyyy') AS THE_DATE, theMonth, theYear,
SUM(Views) AS Total_Views, SUM(Visitors) AS Total_Visitors, SUM(Visits)
                      AS Total_Visits, SUM(NewVisitors) AS
Total_NewVisitors, SUM(RetVisitors) AS Total_RetVisitors
FROM         nustatsl.times
WHERE     (TheMonth = 04) AND (TheYear = 03) OR
                      (TheMonth = 04) AND (TheYear = 03) OR
                      (TheMonth = 04) AND (TheYear = 04)
GROUP BY format(theDate, 'mmmm yyyy'), theMonth, theYear
ORDER BY theMonth, theYear DESC

Can anyone explain to me why this is so, and how can I fix this:-)

Regards
Andrew Scott
Technical Consultant

NuSphere Pty Ltd
Level 2/33 Bank Street
South Melbourne, Victoria, 3205

Phone: 03 9686 0485  -  Fax: 03 9699 7976   

  _____  

From: Bradford T Comer [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 21 April 2004 8:26 PM
To: SQL
Subject: RE: TSQL Assistance

Disregard I believe I got it licked with a LEFT OUTER JOIN.

Thanks
Brad
-----Original Message-----
From: Bradford T Comer [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 21, 2004 4:48 AM
To: SQL
Subject: TSQL Assistance

Can anyone tell me how to use a column from a secondary table in a order by
clause?

----- TBL_A ----
ID int PK NOT NULL
Name Varchar(255)  NULL
SortOrder int DEFAULT(9999) NOT NULL

----- TBL_B ----
ID int PK NOT NULL
Name Varchar(255)  NULL
TBL_A_ID int DEFAULT(0) NOT NULL

---- QUERY ----
SELECT B.*
FROM TBL_B B
WHERE
    ( B.NAME LIKE '%John%' )
        AND
    ( B.TBL_A_ID = TBL_A.ID)
ORDER BY
    TBL_A.SortOrder DESC/ASC

There will be instances where TBL_B will NOT have a record for TBL_A, in
these cases the value of TBL_A_ID will default to 0 - FWIW

I am stumped!

Thanks
Brad
  _____

  _____

---

[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]

---