asked on
ASKER
SELECT * FROM OPENQUERY([LNK-SERVER01], 'SELECT
Company,
Incident_Number,
CASE Priority
WHEN 0 THEN ''Critical''
WHEN 1 THEN ''High''
WHEN 2 THEN ''Medium''
ELSE ''Low''
END,
Assigned_Group,
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS Submit_Date,
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((last_resolved_date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS last_resolved_date
FROM HPD_TAB_INF
WHERE ASSIGNED_SUPPORT_COMPANY = ''Company - OPT''
AND ASSIGNED_SUPPORT_ORGANIZATION in (''OPER'', ''INF'',''SEC MANAG'')
AND STATUS <>6
AND SERVICE_TYPE=0
AND to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''YYYY-MM-DD HH24:MI'') >=''2016-05-23''
AND to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''YYYY-MM-DD HH24:MI'') < ''2016-05-24''
ORDER BY SUBMIT_DATE
')
ASKER
SELECT * FROM OPENQUERY([LNK-SERVER01], 'SELECT
Company,
Incident_Number,
CASE Priority
WHEN 0 THEN ''Critical''
WHEN 1 THEN ''High''
WHEN 2 THEN ''Medium''
ELSE ''Low''
END,
Assigned_Group,
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS Submit_Date,
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((last_resolved_date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS last_resolved_date
FROM HPD_TAB_INF
WHERE ASSIGNED_SUPPORT_COMPANY = ''Company - OPT''
AND ASSIGNED_SUPPORT_ORGANIZATION in (''OPER'', ''INF'',''SEC MANAG'')
AND STATUS <>6
AND SERVICE_TYPE=0
AND Submit_Date BETWEEN SYSDATE-1 and SYSDATE
ORDER BY SUBMIT_DATE
')
ASKER
ASKER
SELECT * FROM OPENQUERY([LNK-SERVER01], 'SELECT
Company,
Incident_Number,
CASE Priority
WHEN 0 THEN ''Critical''
WHEN 1 THEN ''High''
WHEN 2 THEN ''Medium''
ELSE ''Low''
END,
Assigned_Group,
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS Submit_Date,
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((last_resolved_date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS last_resolved_date
FROM HPD_TAB_INF
WHERE ASSIGNED_SUPPORT_COMPANY = ''Company - OPT''
AND ASSIGNED_SUPPORT_ORGANIZATION in (''OPER'', ''INF'',''SEC MANAG'')
AND STATUS <>6
AND SERVICE_TYPE=0
AND Submit_Date BETWEEN TO_DATE(TO_CHAR(SYSDATE-1, ''YYYY-MM-DD'')) and TO_DATE(TO_CHAR(SYSDATE, ''YYYY-MM-DD''))
ORDER BY SUBMIT_DATE
')
Never apply function to the table column in the where clause, you should do it the the values that you compare it to otherwise the possible existing indexes will not be used, which will make your query slower. Also use 'YYYY-MM-DD' format in strings when comparing, it is universally recognized.
ASKER
ASKER
ASKER
SELECT * FROM OPENQUERY([LNK-SERVER01], 'SELECT
Company,
Incident_Number,
CASE Priority
WHEN 0 THEN ''Critical''
WHEN 1 THEN ''High''
WHEN 2 THEN ''Medium''
ELSE ''Low''
END,
Assigned_Group,
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS Submit_Date,
to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((last_resolved_date-3600*3)/60/60/24),''DD-MM-YYYY HH24:MI'') AS last_resolved_date
FROM HPD_TAB_INF
WHERE ASSIGNED_SUPPORT_COMPANY = ''Company - OPT''
AND ASSIGNED_SUPPORT_ORGANIZATION in (''OPER'', ''INF'',''SEC MANAG'')
AND STATUS <>6
AND SERVICE_TYPE=0
AND to_char(to_date(''01-JAN-1970'',''DD-MON-YYYY'')+((Submit_Date-3600*3)/60/60/24),''YYYY-MM-DD HH24:MI'') BETWEEN TO_CHAR(SYSDATE-1, ''YYYY-MM-DD'') and TO_CHAR(SYSDATE, ''YYYY-MM-DD'')
ORDER BY SUBMIT_DATE
')
ASKER
ASKER
ASKER
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
TRUSTED BY
Open in new window