Something is so wrong but I just cant see it

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Something is so wrong but I just cant see it

Author

Topic

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-12-01 : 12:13:38

Hi all I got help with this select statement, I needed to be able to carry a running total for each field. Everytime i run this SQL gives me an error message that says . Can anyone tell me what I'm doing wrong pls, thank you

Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 23
Incorrect syntax near ')'.

SELECT DATEPART(mm,Date) AS Month,
MonthITAuditCount,
MonthInternalApplicationsCount,
MonthMonitorCasinoIt_OpsProjCount,
MonthInternalDatabaseMaintenance_SupportCount,
YTDSecurityAdministrationCount,
YTDInvestigationsCount,
YTDDesktopSupportCount,
YTDProfessionalDevelopmentCount
FROM dbo.ITDepartment
(
SELECT DATEADD(mm,DATEDIFF(mm,0,[Start_Date]),0) AS Date,
       COUNT(ITAudit) AS MonthITAuditCount,
       COUNT(InternalApplications) AS MonthInternalApplicationsCount,
       COUNT(MonitorCasinoIt_OpsProj) AS MonthMonitorCasinoIt_OpsProjCount,
       COUNT(InternalDatabaseMaintenance_Support) AS MonthInternalDatabaseMaintenance_SupportCount,
	COUNT(SecurityAdministration) AS YTDSecuirtyAdministrationCount,
COUNT(Investigations) AS YTDInvestigationsCount,
COUNT(DesktopSupport) As YTDDesktopSupportCount,
Count(ProfessionalDevelopment) As YTDProfessionalDevelopmentCount
FROM dbo.ITDepartment
GROUP BY DATEADD(mm,DATEDIFF(mm,0,[Start_Date]),0)
)
CROSS APPLY
(
SELECT COUNT(ITAudit) AS MonthITAuditCount,
       COUNT(InternalApplications) AS MonthInternalApplicationsCount,
       COUNT(MonitorCasinoIt_OpsProj) AS MonthMonitorCasinoIt_OpsProjCount,
       COUNT(InternalDatabaseMaintenance_Support) AS MonthInternalDatabaseMaintenance_SupportCount,
	COUNT(SecurityAdministration) AS YTDSecuirtyAdministrationCount,
COUNT(Investigations) AS YTDInvestigationsCount,
COUNT(DesktopSupport) As YTDDesktopSupportCount,
Count(ProfessionalDevelopment) As YTDProfessionalDevelopmentCount
FROM dbo.ITDepartment
WHERE [Start_Date] <DATEADD(mm,1,Date)
)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-01 : 12:22:38

You are missing a table alias name for the "from" derived table.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 12:34:55

[code]SELECT DATEPART(mm,Date) AS Month,
MonthITAuditCount,
MonthInternalApplicationsCount,
MonthMonitorCasinoIt_OpsProjCount,
MonthInternalDatabaseMaintenance_SupportCount,
YTDSecurityAdministrationCount,
YTDInvestigationsCount,
YTDDesktopSupportCount,
YTDProfessionalDevelopmentCount
FROM (
SELECT DATEADD(mm,DATEDIFF(mm,0,[Start_Date]),0) AS Date,
COUNT(ITAudit) AS MonthITAuditCount,
COUNT(InternalApplications) AS MonthInternalApplicationsCount,
COUNT(MonitorCasinoIt_OpsProj) AS MonthMonitorCasinoIt_OpsProjCount,
COUNT(InternalDatabaseMaintenance_Support) AS MonthInternalDatabaseMaintenance_SupportCount,
COUNT(SecurityAdministration) AS YTDSecuirtyAdministrationCount,
COUNT(Investigations) AS YTDInvestigationsCount,
COUNT(DesktopSupport) As YTDDesktopSupportCount,
Count(ProfessionalDevelopment) As YTDProfessionalDevelopmentCount
FROM dbo.ITDepartment
GROUP BY DATEADD(mm,DATEDIFF(mm,0,[Start_Date]),0)
)t
CROSS APPLY
(
SELECT COUNT(ITAudit) AS MonthITAuditCount,
COUNT(InternalApplications) AS MonthInternalApplicationsCount,
COUNT(MonitorCasinoIt_OpsProj) AS MonthMonitorCasinoIt_OpsProjCount,
COUNT(InternalDatabaseMaintenance_Support) AS MonthInternalDatabaseMaintenance_SupportCount,
COUNT(SecurityAdministration) AS YTDSecuirtyAdministrationCount,
COUNT(Investigations) AS YTDInvestigationsCount,
COUNT(DesktopSupport) As YTDDesktopSupportCount,
Count(ProfessionalDevelopment) As YTDProfessionalDevelopmentCount
FROM dbo.ITDepartment
WHERE [Start_Date] <DATEADD(mm,1,Date)
)t1
[/code]

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-12-01 : 13:36:21

still getting an error message,

msg 156, level 15, state 1, line 12
incorrect sytax near the keyword select
msg 102, level 15 state 1 line 23
incorrect syntx near ')'
msg 102 level 15, state 1, line 36
incorrect syntax near t1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 23:47:26

quote:
Originally posted by muzzettemm

still getting an error message,

msg 156, level 15, state 1, line 12
incorrect sytax near the keyword select
msg 102, level 15 state 1 line 23
incorrect syntx near ')'
msg 102 level 15, state 1, line 36
incorrect syntax near t1

its working for me. can you show your code just in case you're using some thing different?
also are you using sql 2005?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 02:36:56

First try using the AS keyword for aliasing the tables.
All tools do not accept the AS keyword missing.

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-02 : 04:03:27

quote:
Originally posted by Peso

First try using the AS keyword for aliasing the tables.
All tools do not accept the AS keyword missing.

E 12°55'05.63"
N 56°04'39.26"

I expect SQL Server to force the usage of AS alias
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/09/should-alias-names-be-preceded-by-as-part-2.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources