YTD for different dept - SQL Server Forums

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)

YTD for different dept

Author

Topic

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-12-22 : 11:22:43

Hi all I got help with this before, and I am trying to integrate a different depts stats into this but I keep getting an error message
I must be missing something cause when I execute this I get

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)
)

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-22 : 11:33:36

Give alias name for derived tables...and remove tablename after FROM keyword

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-22 : 11:38:46

[code]SELECT
DATEPART(mm,Date) AS [Month],
MonthITAuditCount,
MonthInternalApplicationsCount,
MonthMonitorCasinoIt_OpsProjCount,
MonthInternalDatabaseMaintenance_SupportCount,
YTDSecurityAdministrationCount,
YTDInvestigationsCount,
YTDDesktopSupportCount,
YTDProfessionalDevelopmentCount
FROM dbo.ITDepartment,--missed a comma here
(
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
--Also, Put a join condition if needed.
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]

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-22 : 11:41:59

SELECT
DATEPART(mm,Date) AS [Month],
MonthITAuditCount,
MonthInternalApplicationsCount,
MonthMonitorCasinoIt_OpsProjCount,
MonthInternalDatabaseMaintenance_SupportCount,
YTDSecurityAdministrationCount,
YTDInvestigationsCount,
YTDDesktopSupportCount,
YTDProfessionalDevelopmentCount
FROM dbo.ITDepartment,--missed a comma here
(
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-- Put a join condition if needed.
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

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-12-22 : 12:06:50

here is what I have there is no joins, but I'm still getting error messages

Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Line 30
Incorrect syntax near 't'.
Msg 102, Level 15, State 1, Line 43
Incorrect syntax near 't1'.

SELECT DATEPART(mm,Date) AS Month,
MonthITAudtiCount,
MonthInternalApplicationsCount,
MonthMonitorCasinoIt_OpsProjCount,
MonthInternalDatabaseMaintenance_SupportCount,
MonthSecurityAdminstrationCount,
MonthInvestigationsCount,
MonthDesktopSupportCount,
MonthProfessionalDevelopmentCount,
YTDITAudtiCount,
YTDInternalApplicationsCount,
YTDMonitorCasinoIt_OpsProjCount,
YTDSecurityAdminstrationCount,
YTDInvestigationsCount,
YTDDesktopSupportCount,
YTDProfessionalDevelopmentCount,
FROM dbo.ITDepartment,
(
SELECT DATEADD(mm,DATEDIFF(mm,0,[Start_Date]),0) AS Date,
       COUNT(ITAudti) AS MonthITAudtiCount,
       COUNT(InternalApplications) AS MonthInternalApplicationsCount,
       COUNT(MonitorCasinoIt_OpsProj) AS MonthMonitorCasinoIt_OpsProjCount,
       COUNT(InternalDatabaseMaintenance_Support) AS MonthInternalDatabaseMaintenance_SupportCount,
	COUNT(SecurityAdminstration) AS YTDSecuirtyAdminstrationCount,
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(ITAudti) AS MonthITAudtiCount,
       COUNT(InternalApplications) AS MonthInternalApplicationsCount,
       COUNT(MonitorCasinoIt_OpsProj) AS MonthMonitorCasinoIt_OpsProjCount,
       COUNT(InternalDatabaseMaintenance_Support) AS MonthInternalDatabaseMaintenance_SupportCount,
	COUNT(SecurityAdminstration) AS YTDSecuirtyAdminstrationCount,
COUNT(Investigations) AS YTDInvestigationsCount,
COUNT(DesktopSupport) As YTDDesktopSupportCount,
Count(ProfessionalDevelopment) As YTDProfessionalDevelopmentCount
FROM dbo.ITDepartment
WHERE [Start_Date] <DATEADD(mm,1,Date)
)t1

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-12-22 : 12:17:10

this code will give me total for each month but I need month and year

SELECT     SUM(TotalActiveSelfExclusions) AS TotalActiveExcl, SUM(QrtlyAdditions) AS TotalQrtly, SUM(SelfExclusionsHearings) AS TotalSelfExclusions, 
                      SUM(ExpiredSelfExclusions) AS TotalExpiredSelfExcl, SUM(LicenseHearing) AS TotalLicenseHearing, SUM(PermitHearing) AS TotalPermitHearings, 
                      SUM(Reinstated) AS TotalReinstated, SUM(Revoked) AS TotalRevoked, SUM(Requested) AS TotalRequested, SUM(Granted) AS TotalGranted, 
                      Start_Date, End_Date
FROM         AdministrativeHearing_Legal
GROUP BY Start_Date, End_Date
HAVING      (Start_Date = CONVERT(DATETIME, '2008-01-01 00:00:00', 102)) AND (End_Date = CONVERT(DATETIME, '2008-01-31 00:00:00', 102))

muzzettemm
Posting Yak Master

212 Posts

Posted - 2008-12-22 : 14:35:36

nevermind I figured out what was wrong with it, totally missed it..I need to pay better attention. VISKAH your the Best :)

SELECT DATEPART(mm,Date) AS Month,
MonthITAudtiCount,
MonthInternalApplicationsCount,
MonthMonitorCasinoIt_OpsProjCount,
MonthInternalDatabaseMaintenance_SupportCount,
MonthSecurityAdminstrationCount,
MonthInvestigationsCount,
MonthDesktopSupportCount,
MonthProfessionalDevelopmentCount,
YTDITAudtiCount,
YTDInternalApplicationsCount,
YTDMonitorCasinoIt_OpsProjCount,
YTDInternalDatabaseMaintenance_SupportCount,
YTDSecurityAdminstrationCount,
YTDInvestigationsCount,
YTDDesktopSupportCount,
YTDProfessionalDevelopmentCount
FROM 
(
SELECT DATEADD(mm,DATEDIFF(mm,0,[Start_Date]),0) AS Date,
      sum(ITAudti) AS MonthITAudtiCount,
       sum(InternalApplications) AS MonthInternalApplicationsCount,
       sum(MonitorCasinoIt_OpsProj) AS MonthMonitorCasinoIt_OpsProjCount,
      SUM(InternalDatabaseMaintenance_Support) AS MonthInternalDatabaseMaintenance_SupportCount,	
	sum(SecurityAdminstration) AS MonthSecurityAdminstrationCount,
	SUM(Investigations) AS MonthInvestigationsCount,
	SUM(DesktopSupport) AS MonthDesktopSupportCount,
	sum(ProfessionalDevelopment) AS MonthProfessionalDevelopmentCount

FROM ITDepartment
GROUP BY DATEADD(mm,DATEDIFF(mm,0,[Start_Date]),0)
)t
CROSS APPLY
(
SELECT SUM(ITAudti) AS YTDITAudtiCount,
       SUM(InternalApplications) AS YTDInternalApplicationsCount,
       SUM(MonitorCasinoIt_OpsProj) AS YTDMonitorCasinoIt_OpsProjCount,
       SUM(InternalDatabaseMaintenance_Support) AS YTDInternalDatabaseMaintenance_SupportCount,
	SUM(SecurityAdminstration) AS YTDSecurityAdminstrationCount,
SUM(Investigations) AS YTDInvestigationsCount,
SUM(DesktopSupport) As YTDDesktopSupportCount,
SUM(ProfessionalDevelopment) As YTDProfessionalDevelopmentCount
FROM ITDepartment
WHERE [Start_Date] <DATEADD(mm,1,Date)
)t1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources