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

General SQL Server Forums

New to SQL Server Programming

Meaning

Author

Topic

mukhan85
Starting Member

46 Posts

Posted - 2008-06-12 : 14:40:36

hey, can anybody help me to understad that code:
SELECT companies.Contact_Company AS am_label1,
ISNULL(Incident_CNT,0) AS NumberOfIncidents
FROM (
SELECT DISTINCT Contact_Company FROM HPD_Help_Desk
) companies LEFT OUTER JOIN (
SELECT COUNT(*) AS Incident_CNT, Contact_Company AS am_label1
FROM HPD_Help_Desk
WHERE Status< 5
AND DATEADD ("s", submit_date, '1/1/1970') >= CAST(CONVERT(char(8),GETDATE(),112) AS datetime)
GROUP BY Contact_Company
) IncidentsByCompany
ON companies.Contact_Company = IncidentsByCompany.Contact_Company

Thank you.

ramu_anu
Starting Member

3 Posts

Posted - 2008-06-12 : 16:39:31

You are selecting contact_company , incident count from HPD_Help_Desk where companies status > 5 and/or(left outer join) submitted date
is greater than todays date

(SELECT DISTINCT Contact_Company FROM HPD_Help_Desk) companies sub query table 1 is having left outer join with

below sub qry table 2 (SELECT COUNT(*) AS Incident_CNT, Contact_Company AS am_label1
FROM HPD_Help_Desk
WHERE Status< 5
AND DATEADD ("s", submit_date, '1/1/1970') >= CAST(CONVERT(char(8),GETDATE(),112) AS datetime)
GROUP BY Contact_Company ) IncidentsByCompany

possibly you are adding submit_date as number of seconds to '1/1/1970'--DATEADD ("s", submit_date, '1/1/1970'

select CAST(CONVERT(char(8),GETDATE(),112) AS datetime) gives 8 character date format with 112 style

use select CONVERT(varchar(10),GETDATE(),101) , select CONVERT(varchar(10),GETDATE(),112) you will know the style difference

ramu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources