Checklist for Analyzing Slow-Running Queries?
# Use SQL Server Profiler to help identify the slow query or queries. For more information. Use the sys.dm_exec_query_stats and sys.dm_exec_requests dynamic management views to find similar queries that collectively consume a large number of resources.
# How do I analyze the performance of a slow-running query? After you have identified the slow-running query or queries, you can further analyze query performance by producing a Showplan, which can be a text, XML, or graphical representation of the query execution plan that the query optimizer generates. (You can produce a Showplan using Transact-SQL SET options, SQL Server Management Studio, or SQL Server Profiler.)
# Are suitable indexes available? Would adding one or more indexes improve query performance?
# Are there any data or index hot spots? Consider using disk striping. Disk striping can be implemented by using RAID (redundant array of independent disks) level 0, where data is distributed across multiple disk drives.
# Is the query optimizer provided with the best opportunity to optimize a complex query?
# If you have a large volume of data, do you need to partition it? Data manageability is the main benefit of partitioning, but if your tables and indexes on them are partitioned similarly, partitioning can also improve query performance. For more information, see Understanding Partitioning and Tuning the Physical Database Design.
Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts
Schedule a Job in SQL Server Management Studio
To create and attach a schedule to a job
1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Expand SQL Server Agent, expand Jobs, right-click the job you want to schedule, and click Properties.
3. Select the Schedules page, and then click New.
4. In the Name box, type a name for the new schedule.
5. Clear the Enabled check box if you do not want the schedule to take effect immediately following its creation.
6. For Schedule Type, select one of the following:
* Click Start automatically when SQL Server Agent starts to start the job when the SQL Server Agent service is started.
* Click Start whenever the CPUs become idle to start the job when the CPUs reach an idle condition.
* Click Recurring if you want a schedule to run repeatedly. To set the recurring schedule, complete the Frequency, Daily Frequency, and Duration groups on the dialog.
* Click One time if you want the schedule to run only once. To set the One time schedule, complete the One-time occurrence group on the dialog.
To attach a schedule to a job
1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Expand SQL Server Agent, expand Jobs, right-click the job that you want to schedule, and click Properties.
3. Select the Schedules page, and then click Pick.
4. Select the schedule that you want to attach, and then click OK.
5. In the Job Properties dialog box, double-click the attached schedule.
6. Verify that Start date is set correctly. If it is not, set the date when you want for the schedule to start, and then click OK.
7. In the Job Properties dialog box, click OK.
Where is SQL Server Agent? By default, it is tuned off.
To start the SQL Server Agent service
1.On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.
2.In SQL Server Configuration Manager, expand Services, and then click SQL Agent.
3.In the results pane, right-click any instance, and then click Start. A green arrow on the icon next to the SQL Server Agent and on the toolbar indicates that SQL Server Agent started successfully.
4.Click OK.
Can't do it? You might be using express version. Only in full version you can turn it on.
http://technet.microsoft.com/en-us/library/ms191439.aspx
http://msdn.microsoft.com/en-us/library/ms191454.aspx
1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Expand SQL Server Agent, expand Jobs, right-click the job you want to schedule, and click Properties.
3. Select the Schedules page, and then click New.
4. In the Name box, type a name for the new schedule.
5. Clear the Enabled check box if you do not want the schedule to take effect immediately following its creation.
6. For Schedule Type, select one of the following:
* Click Start automatically when SQL Server Agent starts to start the job when the SQL Server Agent service is started.
* Click Start whenever the CPUs become idle to start the job when the CPUs reach an idle condition.
* Click Recurring if you want a schedule to run repeatedly. To set the recurring schedule, complete the Frequency, Daily Frequency, and Duration groups on the dialog.
* Click One time if you want the schedule to run only once. To set the One time schedule, complete the One-time occurrence group on the dialog.
To attach a schedule to a job
1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Expand SQL Server Agent, expand Jobs, right-click the job that you want to schedule, and click Properties.
3. Select the Schedules page, and then click Pick.
4. Select the schedule that you want to attach, and then click OK.
5. In the Job Properties dialog box, double-click the attached schedule.
6. Verify that Start date is set correctly. If it is not, set the date when you want for the schedule to start, and then click OK.
7. In the Job Properties dialog box, click OK.
Where is SQL Server Agent? By default, it is tuned off.
To start the SQL Server Agent service
1.On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.
2.In SQL Server Configuration Manager, expand Services, and then click SQL Agent.
3.In the results pane, right-click any instance, and then click Start. A green arrow on the icon next to the SQL Server Agent and on the toolbar indicates that SQL Server Agent started successfully.
4.Click OK.
Can't do it? You might be using express version. Only in full version you can turn it on.
http://technet.microsoft.com/en-us/library/ms191439.aspx
http://msdn.microsoft.com/en-us/library/ms191454.aspx
Various Sizes And Lengths In Database
But, it is suggested to use 30 as maximum column name length in SQL Server.
Feature, SQL Server 2000, Oracle 9i Database
database name length, 128, 8
column name length, 128, 30
index name length, 128, 30
table name length, 128, 30
view name length, 128, 30
stored procedure name length, 128, 30
max columns per index, 16, 32
max char() size 8000, 2000
max varchar() size, 8000, 4000
max columns per table, 1024, 1000
max table row length, 8036, 255000
max query size, 16777216, 16777216
recursive subqueries, 40, 64
constant string size in SELECT, 16777207, 4000
constant string size in WHERE, 8000, 4000
http://www.mssqlcity.com/Articles/Compare/sql_server_vs_oracle.htm
Delete or Select Records Which Is In Or Not In A View
MySQL
Delete records in a table based on the condition that keys of that table is in a another view or is not in the view will not work in MySQL, because either IN or NOT IN needs to be followed by a string delimited by comma. Sure, you can program it to turn a view into a string by insert comma in it. Following is an example which does not work in MySQL:
DELETE FROM tab
WHERE tab.id IN
( SELECT tab.id
FROM tab
WHERE ...
)
An easier way is to use INNER JOIN method to accomplish the task. Here is how to do this:
DELETE tab.* FROM tab
INNER JOIN
( SELECT tab.id
FROM tab
WHERE ...
) AS vCondition
ON vCondition.joinKey = tab.joinKey
Please remember between DELETE and FROM you would need to insert what is going to be deleted. In this case, it is tab.*, represents all fields in the records which meets the condition.
For NOT IN condition, you would need to make sure what is going to be joined would be opposite to NOT IN condition:
DELETE tab.* FROM tab
INNER JOIN
( SELECT tab.id
FROM tab
WHERE tab.id NOT IN
( SELECT tab.id
FROM tab
WHERE ...
)) AS vCondition
ON vCondition.joinKey = tab.joinKey
Don't ask why this time NOT IN works. If you do, please ask MySQL developers directly why they make life so hard. At the end, it is free. So, would you please shut up?
T-SQL
Delete or select records in a table based on the condition that keys of that table is in a another view or is not in the view will work in SQL Server. However, if that is dynamic T-SQL, it won't work. Following is the solution:
SELECT @Columns = COALESCE( @Columns + ',[' + CAST(LTRIM(RTRIM(Pivot_Column)) AS VARCHAR) + ']'
, '[' + CAST(LTRIM(RTRIM(Pivot_Column)) AS VARCHAR)+ ']'
)
FROM
( SELECT DISTINCT Pivot_Column
FROM Temp_Transform_Input
) AS Base_Q
SET @Query = ' SELECT *
INTO Temp_Transform_Result
FROM
( SELECT *
FROM Temp_Transform_Input
) AS Inner_Q
PIVOT
( SUM(Content_Column)
FOR Pivot_Column IN (' + @Columns + ')
) AS Pivot_Q
'
EXECUTE(@Query)
http://www.electrictoolbox.com/article/mysql/cross-table-delete/
Delete records in a table based on the condition that keys of that table is in a another view or is not in the view will not work in MySQL, because either IN or NOT IN needs to be followed by a string delimited by comma. Sure, you can program it to turn a view into a string by insert comma in it. Following is an example which does not work in MySQL:
DELETE FROM tab
WHERE tab.id IN
( SELECT tab.id
FROM tab
WHERE ...
)
An easier way is to use INNER JOIN method to accomplish the task. Here is how to do this:
DELETE tab.* FROM tab
INNER JOIN
( SELECT tab.id
FROM tab
WHERE ...
) AS vCondition
ON vCondition.joinKey = tab.joinKey
Please remember between DELETE and FROM you would need to insert what is going to be deleted. In this case, it is tab.*, represents all fields in the records which meets the condition.
For NOT IN condition, you would need to make sure what is going to be joined would be opposite to NOT IN condition:
DELETE tab.* FROM tab
INNER JOIN
( SELECT tab.id
FROM tab
WHERE tab.id NOT IN
( SELECT tab.id
FROM tab
WHERE ...
)) AS vCondition
ON vCondition.joinKey = tab.joinKey
Don't ask why this time NOT IN works. If you do, please ask MySQL developers directly why they make life so hard. At the end, it is free. So, would you please shut up?
T-SQL
Delete or select records in a table based on the condition that keys of that table is in a another view or is not in the view will work in SQL Server. However, if that is dynamic T-SQL, it won't work. Following is the solution:
SELECT @Columns = COALESCE( @Columns + ',[' + CAST(LTRIM(RTRIM(Pivot_Column)) AS VARCHAR) + ']'
, '[' + CAST(LTRIM(RTRIM(Pivot_Column)) AS VARCHAR)+ ']'
)
FROM
( SELECT DISTINCT Pivot_Column
FROM Temp_Transform_Input
) AS Base_Q
SET @Query = ' SELECT *
INTO Temp_Transform_Result
FROM
( SELECT *
FROM Temp_Transform_Input
) AS Inner_Q
PIVOT
( SUM(Content_Column)
FOR Pivot_Column IN (' + @Columns + ')
) AS Pivot_Q
'
EXECUTE(@Query)
http://www.electrictoolbox.com/article/mysql/cross-table-delete/
Add A Linked Server In SQL Server
The system stored procedure sp_addlinkedserver can be used to created a linked server represent a remote SQL Server. This also can be used when SQL query needs to access multiple databases located in different servers.
Only following parameters are essential:
EXEC sp_addlinkedserver @server = 'RemoteComputerName' -- Remote computer name.
, @srvproduct = '' -- Can be nothing.
, @provider = 'SQLNCLI' -- SQL Server Driver, if do not know, use this.
, @datasrc = 'ServerName' -- Server name and instance.
;
If the remote SQL Server does not have an instance name, then the @datasrc parameter needs only to contain the remote server name and not the instance. There is not place for your nominated name.
To establish the access:
EXEC sp_addlinkedsrvlogin 'RemoteComputerName', 'true';
This would created linked server for all database users. When use it, just like this:
SELECT *
FROM RemoteComputerName.DatabaseName.dbo.TableName
GO
http://msdn.microsoft.com/en-us/library/ms190479.aspx
http://sqlserverplanet.com/dba/using-sp_addlinkedserver/
http://blogs.msdn.com/b/sql_protocols/archive/2006/08/10/694657.aspx
Only following parameters are essential:
EXEC sp_addlinkedserver @server = 'RemoteComputerName' -- Remote computer name.
, @srvproduct = '' -- Can be nothing.
, @provider = 'SQLNCLI' -- SQL Server Driver, if do not know, use this.
, @datasrc = 'ServerName' -- Server name and instance.
;
If the remote SQL Server does not have an instance name, then the @datasrc parameter needs only to contain the remote server name and not the instance. There is not place for your nominated name.
To establish the access:
EXEC sp_addlinkedsrvlogin 'RemoteComputerName', 'true';
This would created linked server for all database users. When use it, just like this:
SELECT *
FROM RemoteComputerName.DatabaseName.dbo.TableName
GO
http://msdn.microsoft.com/en-us/library/ms190479.aspx
http://sqlserverplanet.com/dba/using-sp_addlinkedserver/
http://blogs.msdn.com/b/sql_protocols/archive/2006/08/10/694657.aspx
Stored Procedure: Pivot With Dynamic Columns
Following is a stored procedure in aims to improve Microsoft built-in function of PIVOT in SQL Server. To use PIVOT, one must know exact number and name of columns after pivot, which does not meet the real world’s need. What we need is the pivot function works with dynamic pivot columns. To use this stored procedure, you would need to create a temporary table first with name of “Temp_Transform_Input”. Following is the structure of the this table:
Column 1, can be any name and data type. It would remain intact as it was after pivot.
Column 2, must be named as “Pivot_Column” and should be VARCHAR data type. Its contents after automatically distinct would be pivoted as column titles after execute this procedure.
Column 3, must be named as “Content_Column” and should be INT data type. It would be summed up after pivot. If the purpose is to count, put 1 here for every record.
To use it, just type EXECUTE P_Transform GO. The result will be automatically saved into 7 temporary tables. You do not need to manage the garbage collection for these two temporary tables, this procedure will manage them for you.
Why use temporary table instead of use Multi-statement Table-Valued Function as output method? Since you are dealing with the dynamic columns, it is impossible to declare the return table in Multi-statement Table-Valued Function.
CREATE PROCEDURE P_Transform
AS
BEGIN
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Row_Largest'
)
DROP TABLE dbo.Temp_Transform_Row_Largest
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Row_Sum'
)
DROP TABLE dbo.Temp_Transform_Row_Sum
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Column_Largest'
)
DROP TABLE dbo.Temp_Transform_Column_Largest
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Column_Sum'
)
DROP TABLE dbo.Temp_Transform_Column_Sum
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Both_Largest'
)
DROP TABLE dbo.Temp_Transform_Both_Largest
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Both_Sum'
)
DROP TABLE dbo.Temp_Transform_Both_Sum
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Result'
)
DROP TABLE dbo.Temp_Transform_Result
DECLARE @FirstColumn VARCHAR(128)
DECLARE @Summary VARCHAR(512)
DECLARE @Columns VARCHAR(MAX)
DECLARE @Query VARCHAR(MAX)
SELECT @FirstColumn = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Temp_Transform_Input'
AND ORDINAL_POSITION = 1
SET @Summary =
'
SELECT ' + @FirstColumn + '
, MAX(Content_Column) AS Largest
INTO Temp_Transform_Row_Largest
FROM Temp_Transform_Input
GROUP BY ' + @FirstColumn + '
ORDER BY MAX(Content_Column) DESC
, ' + @FirstColumn + '
'
EXECUTE(@Summary)
SET @Summary =
'
SELECT ' + @FirstColumn + '
, SUM(Content_Column) AS Total
INTO Temp_Transform_Row_Sum
FROM Temp_Transform_Input
GROUP BY ' + @FirstColumn + '
ORDER BY SUM(Content_Column) DESC
, ' + @FirstColumn + '
'
EXECUTE(@Summary)
SET @Summary =
'
SELECT Pivot_Column
, MAX(Content_Column) AS Largest
INTO Temp_Transform_Column_Largest
FROM Temp_Transform_Input
GROUP BY Pivot_Column
ORDER BY MAX(Content_Column) DESC
, Pivot_Column
'
EXECUTE(@Summary)
SET @Summary =
'
SELECT Pivot_Column
, SUM(Content_Column) AS Total
INTO Temp_Transform_Column_Sum
FROM Temp_Transform_Input
GROUP BY Pivot_Column
ORDER BY SUM(Content_Column) DESC
, Pivot_Column
'
EXECUTE(@Summary)
SET @Summary =
'
SELECT ' + @FirstColumn + '
, Pivot_Column
, MAX(Content_Column) AS Largest
INTO Temp_Transform_Both_Largest
FROM Temp_Transform_Input
GROUP BY ' + @FirstColumn + '
, Pivot_Column
ORDER BY MAX(Content_Column) DESC
, Pivot_Column
'
EXECUTE(@Summary)
SET @Summary =
'
SELECT ' + @FirstColumn + '
, Pivot_Column
, SUM(Content_Column) AS Total
INTO Temp_Transform_Both_Sum
FROM Temp_Transform_Input
GROUP BY ' + @FirstColumn + '
, Pivot_Column
ORDER BY SUM(Content_Column) DESC
, ' + @FirstColumn + '
, Pivot_Column
'
EXECUTE(@Summary)
SELECT @Columns = COALESCE( @Columns + ',[' + CAST(LTRIM(RTRIM(Pivot_Column)) AS VARCHAR) + ']'
, '[' + CAST(LTRIM(RTRIM(Pivot_Column)) AS VARCHAR)+ ']'
)
FROM
(
SELECT DISTINCT Pivot_Column
FROM Temp_Transform_Input
) AS Base_Q
SET @Query =
'
SELECT *
INTO Temp_Transform_Result
FROM
(
SELECT *
FROM Temp_Transform_Input
) AS Inner_Q
PIVOT
(
SUM(Content_Column)
FOR Pivot_Column IN (' + @Columns + ')
) AS Pivot_Q
'
EXECUTE(@Query)
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Input'
)
DROP TABLE dbo.Temp_Transform_Input
END
GO
It is very important the length of each contents in Pivot_Column must be less 30 characters, because the contents will become the column name after transpose. Else, it won't work properly.
http://www.paladn.com/component/content/article/40-sql-server/122-udf-forms.html
Column 1, can be any name and data type. It would remain intact as it was after pivot.
Column 2, must be named as “Pivot_Column” and should be VARCHAR data type. Its contents after automatically distinct would be pivoted as column titles after execute this procedure.
Column 3, must be named as “Content_Column” and should be INT data type. It would be summed up after pivot. If the purpose is to count, put 1 here for every record.
To use it, just type EXECUTE P_Transform GO. The result will be automatically saved into 7 temporary tables. You do not need to manage the garbage collection for these two temporary tables, this procedure will manage them for you.
Why use temporary table instead of use Multi-statement Table-Valued Function as output method? Since you are dealing with the dynamic columns, it is impossible to declare the return table in Multi-statement Table-Valued Function.
CREATE PROCEDURE P_Transform
AS
BEGIN
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Row_Largest'
)
DROP TABLE dbo.Temp_Transform_Row_Largest
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Row_Sum'
)
DROP TABLE dbo.Temp_Transform_Row_Sum
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Column_Largest'
)
DROP TABLE dbo.Temp_Transform_Column_Largest
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Column_Sum'
)
DROP TABLE dbo.Temp_Transform_Column_Sum
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Both_Largest'
)
DROP TABLE dbo.Temp_Transform_Both_Largest
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Both_Sum'
)
DROP TABLE dbo.Temp_Transform_Both_Sum
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Result'
)
DROP TABLE dbo.Temp_Transform_Result
DECLARE @FirstColumn VARCHAR(128)
DECLARE @Summary VARCHAR(512)
DECLARE @Columns VARCHAR(MAX)
DECLARE @Query VARCHAR(MAX)
SELECT @FirstColumn = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Temp_Transform_Input'
AND ORDINAL_POSITION = 1
SET @Summary =
'
SELECT ' + @FirstColumn + '
, MAX(Content_Column) AS Largest
INTO Temp_Transform_Row_Largest
FROM Temp_Transform_Input
GROUP BY ' + @FirstColumn + '
ORDER BY MAX(Content_Column) DESC
, ' + @FirstColumn + '
'
EXECUTE(@Summary)
SET @Summary =
'
SELECT ' + @FirstColumn + '
, SUM(Content_Column) AS Total
INTO Temp_Transform_Row_Sum
FROM Temp_Transform_Input
GROUP BY ' + @FirstColumn + '
ORDER BY SUM(Content_Column) DESC
, ' + @FirstColumn + '
'
EXECUTE(@Summary)
SET @Summary =
'
SELECT Pivot_Column
, MAX(Content_Column) AS Largest
INTO Temp_Transform_Column_Largest
FROM Temp_Transform_Input
GROUP BY Pivot_Column
ORDER BY MAX(Content_Column) DESC
, Pivot_Column
'
EXECUTE(@Summary)
SET @Summary =
'
SELECT Pivot_Column
, SUM(Content_Column) AS Total
INTO Temp_Transform_Column_Sum
FROM Temp_Transform_Input
GROUP BY Pivot_Column
ORDER BY SUM(Content_Column) DESC
, Pivot_Column
'
EXECUTE(@Summary)
SET @Summary =
'
SELECT ' + @FirstColumn + '
, Pivot_Column
, MAX(Content_Column) AS Largest
INTO Temp_Transform_Both_Largest
FROM Temp_Transform_Input
GROUP BY ' + @FirstColumn + '
, Pivot_Column
ORDER BY MAX(Content_Column) DESC
, Pivot_Column
'
EXECUTE(@Summary)
SET @Summary =
'
SELECT ' + @FirstColumn + '
, Pivot_Column
, SUM(Content_Column) AS Total
INTO Temp_Transform_Both_Sum
FROM Temp_Transform_Input
GROUP BY ' + @FirstColumn + '
, Pivot_Column
ORDER BY SUM(Content_Column) DESC
, ' + @FirstColumn + '
, Pivot_Column
'
EXECUTE(@Summary)
SELECT @Columns = COALESCE( @Columns + ',[' + CAST(LTRIM(RTRIM(Pivot_Column)) AS VARCHAR) + ']'
, '[' + CAST(LTRIM(RTRIM(Pivot_Column)) AS VARCHAR)+ ']'
)
FROM
(
SELECT DISTINCT Pivot_Column
FROM Temp_Transform_Input
) AS Base_Q
SET @Query =
'
SELECT *
INTO Temp_Transform_Result
FROM
(
SELECT *
FROM Temp_Transform_Input
) AS Inner_Q
PIVOT
(
SUM(Content_Column)
FOR Pivot_Column IN (' + @Columns + ')
) AS Pivot_Q
'
EXECUTE(@Query)
IF EXISTS( SELECT *
FROM SYS.TABLES
WHERE NAME = 'Temp_Transform_Input'
)
DROP TABLE dbo.Temp_Transform_Input
END
GO
It is very important the length of each contents in Pivot_Column must be less 30 characters, because the contents will become the column name after transpose. Else, it won't work properly.
http://www.paladn.com/component/content/article/40-sql-server/122-udf-forms.html
Pivot Large Table With Dynamic Columns
In general, it is recommended to use SUM(CASE) GROUP BY method to pivot rows into columns. For details why recommended such, please refer to http://koncordpartners.blogspot.com/2010/03/pivot-turning-rows-into-columns.html. However, in two circumstances SUM(CASE) GROUP BY method does not work:
1. The column numbers are too many. By using SUM(CASE) GROUP BY method, one must know every column’s name.
2. The column names are unknown when generate queries. Usually this could be happened with column names are result from another SELECT statement.
In fact, these two circumstances do always emerge together. In this situation, built-in PIVOT function in Oracle and SQL Server is the only option. In Ms Access, it is TRANSFORM, which is even better in comparison to Oracle and SQL Server, because it accept the list of column names directly from the SELECT statement. Following example is using T-SQL, and the scenario is to draw a matrix table summarizing the total air ambulance flights between airports within a state for past ten year. The involved records are around 500,000.
The PIVOT function is easy:
SELECT *
FROM
(
SELECT sendport AS Sending_Airport
, receiveport AS Receiving_Airport
, 1 AS InterimCounter
FROM dw_Legs
) AS Inner_Q
PIVOT
(
SUM(InterimCounter)
FOR Receiving_Airport IN ([The list of receiving airport])
) AS Pivot_Q
The problem is “The list of receiving airport” must be selected from airport table, and very unfortunately, T-SQL does not accept it as an outcome of SELECT statement. So, dynamic query is the only option left. We human just make our life so miserable by disallow this and disallow that.
Another issue is not all airports receive planes from every rest airports, which means “The list of receiving airport” should not be directly from airport table, rather is should be from the intermediate queries. So, for presentation purpose, a temporary table is the best solution, which is also in accordance with the design of SQL Server.
Step 1
CREATE VIEW V_Airport_Matrix
AS
SELECT sendport AS Sending_Airport
, receiveport AS Receiving_Airport
, 1 AS InterimCounter
FROM dw_Legs
GO
Step 2
Prepare dynamic queries:
DECLARE @columns VARCHAR(MAX)
SELECT @columns = COALESCE(@columns + ',[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR) + ']', '[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR)+ ']')
FROM
(
SELECT DISTINCT Receiving_Airport
FROM V_Airport_Matrix
) AS base_Q
Why LTRIM(RTRIM()) is needed? Because the length of the VARCHAR string becomes critical since VARCHAR(MAX) does have limit. And very often, VARCHAR(MAX) does not meet our need here. And, the text, ntext, and image data types are invalid for local variables. Following is the send part of Step 2:
DECLARE @query VARCHAR(MAX)
SET @query =
'
SELECT *
FROM
(
SELECT *
FROM V_Airport_Matrix
) AS Inner_Q
PIVOT
(
SUM(InterimCounter)
FOR Receiving_Airport IN (' + @columns + ')
) AS Pivot_Q
'
What about your query does exceed the VARCHAR(MAX) limit? You will need to shorten the query by cutting them into pieces:
DECLARE @columns VARCHAR(MAX)
SELECT @columns = COALESCE(@columns + ',[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR) + ']', '[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR)+ ']')
FROM
(
SELECT DISTINCT Receiving_Airport
FROM V_Airport_Matrix
WHERE Receiving_Airport LIKE 'A%'
OR Receiving_Airport LIKE 'B%'
OR Receiving_Airport LIKE 'C%'
OR Receiving_Airport LIKE 'D%'
OR Receiving_Airport LIKE 'E%'
OR Receiving_Airport LIKE 'F%'
OR Receiving_Airport LIKE 'G%'
OR Receiving_Airport LIKE 'H%'
OR Receiving_Airport LIKE 'I%'
OR Receiving_Airport LIKE 'J%'
OR Receiving_Airport LIKE 'K%'
OR Receiving_Airport 'L%'
) AS base_Q
Then, execute one by one before union together.
Step 3
To execute the queries:
EXECUTE(@query)
http://blog-mstechnology.blogspot.com/2010/06/t-sql-pivot-operator-with-dynamic.html
1. The column numbers are too many. By using SUM(CASE) GROUP BY method, one must know every column’s name.
2. The column names are unknown when generate queries. Usually this could be happened with column names are result from another SELECT statement.
In fact, these two circumstances do always emerge together. In this situation, built-in PIVOT function in Oracle and SQL Server is the only option. In Ms Access, it is TRANSFORM, which is even better in comparison to Oracle and SQL Server, because it accept the list of column names directly from the SELECT statement. Following example is using T-SQL, and the scenario is to draw a matrix table summarizing the total air ambulance flights between airports within a state for past ten year. The involved records are around 500,000.
The PIVOT function is easy:
SELECT *
FROM
(
SELECT sendport AS Sending_Airport
, receiveport AS Receiving_Airport
, 1 AS InterimCounter
FROM dw_Legs
) AS Inner_Q
PIVOT
(
SUM(InterimCounter)
FOR Receiving_Airport IN ([The list of receiving airport])
) AS Pivot_Q
The problem is “The list of receiving airport” must be selected from airport table, and very unfortunately, T-SQL does not accept it as an outcome of SELECT statement. So, dynamic query is the only option left. We human just make our life so miserable by disallow this and disallow that.
Another issue is not all airports receive planes from every rest airports, which means “The list of receiving airport” should not be directly from airport table, rather is should be from the intermediate queries. So, for presentation purpose, a temporary table is the best solution, which is also in accordance with the design of SQL Server.
Step 1
CREATE VIEW V_Airport_Matrix
AS
SELECT sendport AS Sending_Airport
, receiveport AS Receiving_Airport
, 1 AS InterimCounter
FROM dw_Legs
GO
Step 2
Prepare dynamic queries:
DECLARE @columns VARCHAR(MAX)
SELECT @columns = COALESCE(@columns + ',[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR) + ']', '[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR)+ ']')
FROM
(
SELECT DISTINCT Receiving_Airport
FROM V_Airport_Matrix
) AS base_Q
Why LTRIM(RTRIM()) is needed? Because the length of the VARCHAR string becomes critical since VARCHAR(MAX) does have limit. And very often, VARCHAR(MAX) does not meet our need here. And, the text, ntext, and image data types are invalid for local variables. Following is the send part of Step 2:
DECLARE @query VARCHAR(MAX)
SET @query =
'
SELECT *
FROM
(
SELECT *
FROM V_Airport_Matrix
) AS Inner_Q
PIVOT
(
SUM(InterimCounter)
FOR Receiving_Airport IN (' + @columns + ')
) AS Pivot_Q
'
What about your query does exceed the VARCHAR(MAX) limit? You will need to shorten the query by cutting them into pieces:
DECLARE @columns VARCHAR(MAX)
SELECT @columns = COALESCE(@columns + ',[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR) + ']', '[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR)+ ']')
FROM
(
SELECT DISTINCT Receiving_Airport
FROM V_Airport_Matrix
WHERE Receiving_Airport LIKE 'A%'
OR Receiving_Airport LIKE 'B%'
OR Receiving_Airport LIKE 'C%'
OR Receiving_Airport LIKE 'D%'
OR Receiving_Airport LIKE 'E%'
OR Receiving_Airport LIKE 'F%'
OR Receiving_Airport LIKE 'G%'
OR Receiving_Airport LIKE 'H%'
OR Receiving_Airport LIKE 'I%'
OR Receiving_Airport LIKE 'J%'
OR Receiving_Airport LIKE 'K%'
OR Receiving_Airport 'L%'
) AS base_Q
Then, execute one by one before union together.
Step 3
To execute the queries:
EXECUTE(@query)
http://blog-mstechnology.blogspot.com/2010/06/t-sql-pivot-operator-with-dynamic.html
Using Ms Access to Update Reporting Services Database
It is strange to use legacy Ms Access to update table in reporting services database. But sometimes it happens in market. This example is because the reporting services database was first built by Ms Access as local database, then developed into SQL Server database. During the transit period the updating does still using Ms Access.
A bunch of independent queries as well as internally called queries organized into Macros. When this macro is executed, a huge reporting services table had been generated within the Ms Access.
Next step is to export this table into SQL Server by using Import & Export Data (32-bit) of SQL Server 2008. Before that, make sure the existing table in SQL Server is being changed name, else the new records will be simply append to the end of original table.
In Import & Export Data (32-bit) Date source is Microsoft Access, file name is the Access file. Leave User name and Password blank. Next, in Destination select SQL Server Native Client 10.0 and make a right selection in Server name. In Authentication tick Use Windows Authentication before make a right selection in Database. Next, select Copy data from one or more tables or views. The rest steps are easy.
A bunch of independent queries as well as internally called queries organized into Macros. When this macro is executed, a huge reporting services table had been generated within the Ms Access.
Next step is to export this table into SQL Server by using Import & Export Data (32-bit) of SQL Server 2008. Before that, make sure the existing table in SQL Server is being changed name, else the new records will be simply append to the end of original table.
In Import & Export Data (32-bit) Date source is Microsoft Access, file name is the Access file. Leave User name and Password blank. Next, in Destination select SQL Server Native Client 10.0 and make a right selection in Server name. In Authentication tick Use Windows Authentication before make a right selection in Database. Next, select Copy data from one or more tables or views. The rest steps are easy.
Copy Only the Structure of A Table
This old little trick had been asked for so many times. So, it might be a good itea to post it on the blog.
In Oracle:
CREATE TABLE NEW_TAB AS
SELECT *
FROM OLD_TAB
WHERE 0 = -1
In SQL Server:
SELECT *
INTO NEW_TAB
FROM OLD_TAB
WHERE 0 = -1
The key point is WHERE condition. Because this condition is never to be met, so there is no records actually being selected. Someone might like WHERE 1 = 2, however, 1 or 2 can be referred to column one and column two. If by chance the value in column one just equals to column two, haha, you will get some data.
In Oracle:
CREATE TABLE NEW_TAB AS
SELECT *
FROM OLD_TAB
WHERE 0 = -1
In SQL Server:
SELECT *
INTO NEW_TAB
FROM OLD_TAB
WHERE 0 = -1
The key point is WHERE condition. Because this condition is never to be met, so there is no records actually being selected. Someone might like WHERE 1 = 2, however, 1 or 2 can be referred to column one and column two. If by chance the value in column one just equals to column two, haha, you will get some data.
A Key Point in Microsoft ODBC Setting
In Microsoft SQL Server DSN Configuration window, select “Change the default database to” is very important. Else, even test shows no error, database linkage may still have problem.
Contain Data Layout in SQL Part for Reporting Services
Business Intelligence in both ORACLE and SQL Server has offered standard tools and settings, such as cube and dimensions. However, for complicated reports, these tools may not be very useful. The orthodox approach to this issue is to have a customized OLAP database built. In some case, even with OLAP database, complicated queries still needed. This issue does also cause the further problem of data layout in report. Here is an approach to deal with these issues. Basically, this approach does contain the task of data layout in SQL part to avoid complex programming in Stored Procedures.
Let us say a report is required to present a daily summary of patient movement within a State, with certain criteria. It requires 5 columns: Date, Sending Facility, Receiving Facility, total transports between these two facilities in that day [Transports], and the Daily Subtotal for whole State. Indeed, Daily Subtotal and Transports can share same column. However, for better visual effect, it is required to have separate columns. GROUP BY and other analytic functions can be used to generate numbers for Transports and Daily Subtotal. However, for the sake of data layout in report, it is suggested only GROUP BY is used. As at today, almost all other analytic functions just generate undesired data layout, for instance, extra but unneeded columns from PIVOT.
The data was not located in a single table, so joins are necessary. Since the selection criteria (WHERE Conditions) are not strictly have one-to-one relationship, the joins therefore are Outer Joins to ensure needed records won’t be excluded. Here is FROM clause:
FROM dbo.transfer t
LEFT JOIN dbo.healthcare_facility s ON s.healthcare_facility_id = t.sending_facility_id
LEFT JOIN dbo.healthcare_facility r ON r.healthcare_facility_id = t.receiving_facility_id
LEFT JOIN ……
The sending facility and receiving facility indeed is in same table. So, it had been outer joined twice with flag as s and r.
Because of one-to-more relationship, it is for sure multiple rows will be generated for some records. So, mechanism to distinct records is needed. Unfortunately, DISTINCT function does not work with GROUP BY. So, an inner query is used strictly only selecting the essential fields together with DISTINCT:
( SELECT DISTINCT DATEADD(HH, 12, DATEDIFF(DD, 0, t.add_datetime)) Entry_Date
, m.mt_number [MT#]
, s.healthcare_facility_name Sending_Facility
, r.healthcare_facility_name Receiving_Facility
FROM dbo.transfer t
LEFT JOIN dbo.healthcare_facility s ON s.healthcare_facility_id = t.sending_facility_id
LEFT JOIN dbo.healthcare_facility r ON r.healthcare_facility_id = t.receiving_facility_id
LEFT JOIN …
WHERE …
) AS Distinct_Q
Do not forget to add “AS [name_of_inner_query]” at the end of inner query, which is required by Transact-SQL. DATEDIFF(DD, 0, t.add_datetime) is used here to uniform the time during the date enabling the use of GROUP BY. DATEADD(HH, 12, …) will be explained late. If Distinct_Q contains less fields needed by next step, it is neccessary to join back to retrieve the missing fields.
Next step is to use GROUP BY:
( SELECT Entry_Date
, Sending_Facility
, Receiving_Facility
, COUNT(*) Daily_Transports
FROM
(…) AS Distinct_Q
GROUP BY Entry_Date
, Sending_Facility
, Receiving_Facility
) AS Detail_Q
So far, we generate all essential parts of the report except the Daily Subtotal. We can use same structure for Daily Subtotal. Since inner query Distinct_Q used twice, it is worthwhile to move this temporary resultset to the head with WITH:
WITH Distinct_Q
AS
(…
)
(SELECT DATEADD(HH, -1, Entry_Date) E_Date
, COUNT(*) Daily_Subtotal
FROM Distinct_Q
GROUP BY Entry_Date
) AS Daily_Subtotal_Q
Note, DATEADD(HH, -1, Entry_Date) is used again here.
We can use UNION to put two temporary resultsets Detail_Q and Daily_Subtotal_Q together:
SELECT Entry_Date [Entry Date]
, Transfer_Status [Transfer Status]
, Sending_Facility [Sending Facility]
, Receiving_Facility [Receiving Facility]
, CAST(Daily_Transports AS VARCHAR(3)) Transports
, '' [Daily Subtotal]
FROM
(…) AS Detail_Q
UNION
SELECT E_Date [Entry Date]
, '' [Transfer Status]
, 'Daily Subtotal' [Sending Facility]
, '' [Receiving Facility]
, '' Transports
, CAST(Daily_Subtotal AS VARCHAR(3)) [Daily Subtotal]
FROM
(…) AS Daily_Subtotal_Q
The purpose of CAST(… AS VARCHAR(3)) used here is to avoid the empty string '' automatically turns to 0 after UNION. Field [Daily Subtotal] in Detail_Q is string data type and Daily_Subtotal_Q is number. So does for field [Transports].
The final step to to add ORDER BY at the end:
ORDER BY [Entry Date] DESC
, [Transfer Status]
, [Sending Facility]
, [Receiving Facility]
, [Transports]
, [Daily Subtotal]
GO
It is time to explain why using DATEADD() function above. Since [Entry Date] is to be sorted DESCENT-ly, one hour difference as result of two DATEADD() functions could place row of ‘Daily Subtotal’ at the end of every rows for that day.
Here is completed SQL script:
WITH Distinct_Q
AS
( SELECT DISTINCT DATEADD(HH, 12, DATEDIFF(DD, 0, t.add_datetime)) Entry_Date
, m.mt_number [MT#]
, s.healthcare_facility_name Sending_Facility
, r.healthcare_facility_name Receiving_Facility
FROM dbo.transfer t
LEFT JOIN dbo.healthcare_facility s ON s.healthcare_facility_id = t.sending_facility_id
LEFT JOIN dbo.healthcare_facility r ON r.healthcare_facility_id = t.receiving_facility_id
LEFT JOIN …
WHERE …
)
SELECT Entry_Date [Entry Date]
, Transfer_Status [Transfer Status]
, Sending_Facility [Sending Facility]
, Receiving_Facility [Receiving Facility]
, CAST(Daily_Transports AS VARCHAR(3)) Transports
, '' [Daily Subtotal]
FROM
( SELECT Entry_Date
, Sending_Facility
, Receiving_Facility
, COUNT(*) Daily_Transports
FROM Distinct_Q
GROUP BY Entry_Date
, Sending_Facility
, Receiving_Facility
) AS Detail_Q
UNION
SELECT E_Date [Entry Date]
, '' [Transfer Status]
, 'Daily Subtotal' [Sending Facility]
, '' [Receiving Facility]
, '' Transports
, CAST(Daily_Subtotal AS VARCHAR(3)) [Daily Subtotal]
FROM
(SELECT DATEADD(HH, -1, Entry_Date) E_Date
, COUNT(*) Daily_Subtotal
FROM Distinct_Q
GROUP BY Entry_Date
) AS Daily_Subtotal_Q
ORDER BY [Entry Date] DESC
, [Transfer Status]
, [Sending Facility]
, [Receiving Facility]
, [Transports]
, [Daily Subtotal]
GO
This blog is to illustrate how to manipulate the data layout in report simply by use SQL. You can also refer to http://koncordpartners.blogspot.com/2009/08/practical-case-of-transpose-query.html.
http://koncordpartners.blogspot.com/2010/12/special-function-of-order-by-clause-in.html
Let us say a report is required to present a daily summary of patient movement within a State, with certain criteria. It requires 5 columns: Date, Sending Facility, Receiving Facility, total transports between these two facilities in that day [Transports], and the Daily Subtotal for whole State. Indeed, Daily Subtotal and Transports can share same column. However, for better visual effect, it is required to have separate columns. GROUP BY and other analytic functions can be used to generate numbers for Transports and Daily Subtotal. However, for the sake of data layout in report, it is suggested only GROUP BY is used. As at today, almost all other analytic functions just generate undesired data layout, for instance, extra but unneeded columns from PIVOT.
The data was not located in a single table, so joins are necessary. Since the selection criteria (WHERE Conditions) are not strictly have one-to-one relationship, the joins therefore are Outer Joins to ensure needed records won’t be excluded. Here is FROM clause:
FROM dbo.transfer t
LEFT JOIN dbo.healthcare_facility s ON s.healthcare_facility_id = t.sending_facility_id
LEFT JOIN dbo.healthcare_facility r ON r.healthcare_facility_id = t.receiving_facility_id
LEFT JOIN ……
The sending facility and receiving facility indeed is in same table. So, it had been outer joined twice with flag as s and r.
Because of one-to-more relationship, it is for sure multiple rows will be generated for some records. So, mechanism to distinct records is needed. Unfortunately, DISTINCT function does not work with GROUP BY. So, an inner query is used strictly only selecting the essential fields together with DISTINCT:
( SELECT DISTINCT DATEADD(HH, 12, DATEDIFF(DD, 0, t.add_datetime)) Entry_Date
, m.mt_number [MT#]
, s.healthcare_facility_name Sending_Facility
, r.healthcare_facility_name Receiving_Facility
FROM dbo.transfer t
LEFT JOIN dbo.healthcare_facility s ON s.healthcare_facility_id = t.sending_facility_id
LEFT JOIN dbo.healthcare_facility r ON r.healthcare_facility_id = t.receiving_facility_id
LEFT JOIN …
WHERE …
) AS Distinct_Q
Do not forget to add “AS [name_of_inner_query]” at the end of inner query, which is required by Transact-SQL. DATEDIFF(DD, 0, t.add_datetime) is used here to uniform the time during the date enabling the use of GROUP BY. DATEADD(HH, 12, …) will be explained late. If Distinct_Q contains less fields needed by next step, it is neccessary to join back to retrieve the missing fields.
Next step is to use GROUP BY:
( SELECT Entry_Date
, Sending_Facility
, Receiving_Facility
, COUNT(*) Daily_Transports
FROM
(…) AS Distinct_Q
GROUP BY Entry_Date
, Sending_Facility
, Receiving_Facility
) AS Detail_Q
So far, we generate all essential parts of the report except the Daily Subtotal. We can use same structure for Daily Subtotal. Since inner query Distinct_Q used twice, it is worthwhile to move this temporary resultset to the head with WITH:
WITH Distinct_Q
AS
(…
)
(SELECT DATEADD(HH, -1, Entry_Date) E_Date
, COUNT(*) Daily_Subtotal
FROM Distinct_Q
GROUP BY Entry_Date
) AS Daily_Subtotal_Q
Note, DATEADD(HH, -1, Entry_Date) is used again here.
We can use UNION to put two temporary resultsets Detail_Q and Daily_Subtotal_Q together:
SELECT Entry_Date [Entry Date]
, Transfer_Status [Transfer Status]
, Sending_Facility [Sending Facility]
, Receiving_Facility [Receiving Facility]
, CAST(Daily_Transports AS VARCHAR(3)) Transports
, '' [Daily Subtotal]
FROM
(…) AS Detail_Q
UNION
SELECT E_Date [Entry Date]
, '' [Transfer Status]
, 'Daily Subtotal' [Sending Facility]
, '' [Receiving Facility]
, '' Transports
, CAST(Daily_Subtotal AS VARCHAR(3)) [Daily Subtotal]
FROM
(…) AS Daily_Subtotal_Q
The purpose of CAST(… AS VARCHAR(3)) used here is to avoid the empty string '' automatically turns to 0 after UNION. Field [Daily Subtotal] in Detail_Q is string data type and Daily_Subtotal_Q is number. So does for field [Transports].
The final step to to add ORDER BY at the end:
ORDER BY [Entry Date] DESC
, [Transfer Status]
, [Sending Facility]
, [Receiving Facility]
, [Transports]
, [Daily Subtotal]
GO
It is time to explain why using DATEADD() function above. Since [Entry Date] is to be sorted DESCENT-ly, one hour difference as result of two DATEADD() functions could place row of ‘Daily Subtotal’ at the end of every rows for that day.
Here is completed SQL script:
WITH Distinct_Q
AS
( SELECT DISTINCT DATEADD(HH, 12, DATEDIFF(DD, 0, t.add_datetime)) Entry_Date
, m.mt_number [MT#]
, s.healthcare_facility_name Sending_Facility
, r.healthcare_facility_name Receiving_Facility
FROM dbo.transfer t
LEFT JOIN dbo.healthcare_facility s ON s.healthcare_facility_id = t.sending_facility_id
LEFT JOIN dbo.healthcare_facility r ON r.healthcare_facility_id = t.receiving_facility_id
LEFT JOIN …
WHERE …
)
SELECT Entry_Date [Entry Date]
, Transfer_Status [Transfer Status]
, Sending_Facility [Sending Facility]
, Receiving_Facility [Receiving Facility]
, CAST(Daily_Transports AS VARCHAR(3)) Transports
, '' [Daily Subtotal]
FROM
( SELECT Entry_Date
, Sending_Facility
, Receiving_Facility
, COUNT(*) Daily_Transports
FROM Distinct_Q
GROUP BY Entry_Date
, Sending_Facility
, Receiving_Facility
) AS Detail_Q
UNION
SELECT E_Date [Entry Date]
, '' [Transfer Status]
, 'Daily Subtotal' [Sending Facility]
, '' [Receiving Facility]
, '' Transports
, CAST(Daily_Subtotal AS VARCHAR(3)) [Daily Subtotal]
FROM
(SELECT DATEADD(HH, -1, Entry_Date) E_Date
, COUNT(*) Daily_Subtotal
FROM Distinct_Q
GROUP BY Entry_Date
) AS Daily_Subtotal_Q
ORDER BY [Entry Date] DESC
, [Transfer Status]
, [Sending Facility]
, [Receiving Facility]
, [Transports]
, [Daily Subtotal]
GO
This blog is to illustrate how to manipulate the data layout in report simply by use SQL. You can also refer to http://koncordpartners.blogspot.com/2009/08/practical-case-of-transpose-query.html.
http://koncordpartners.blogspot.com/2010/12/special-function-of-order-by-clause-in.html
Date Format
People in United States use date format of mm/dd/yyyy. British, Australia and New Zealand use dd/mm/yyyy. Format of yyyy-mm-dd is an ISO 8601 international standard, and is favored by European countries and all Chinese spoken countries. Canada is THE country currently using all of these three formats. Format of yyyy-mm-dd is official format for Canada. Because they are a Commonwealth member country, they also use British format. And because they are heavily influenced by United States, in day-to-day life Canadians use American format most.
It is for sure IT industry should use yyyy-mm-dd format, not because it is an ISO 8601 standard, but also it is only meaningful format when you conduct textual sorting. For instance, when you use date as part of file name, it can be directly sorted by Windows Explorer.
In Microsoft Excel, if yyyy-mm-dd format is needed, please go to Format Cells, chose Locale as Chinese (Hong Kong S.A.R). It can be found in Type.
Sunday is traditionally regarded as the first day of the week. Different industries may have their own standard. However, the International Organization for Standardization (ISO) specifies that the week begins with Monday, which is also the standard for IT industry. So, day 1 in a week is Monday. The week ends at 23:59:59 of Sunday. Beware, Microsoft Excel's function weekday() does use Sunday as day 1 in a week. In addition, in Microsoft SQL Server DATEPART(WEEKDAY, dateToCheck) does also generate 2 for Monday.
Look at following three times:
Time 1: 2000-01-01 11:59:59 AM
Time 2: 2000-01-01 12:00:00
Time 3: 2000-01-01 12:00:01 PM
How many seconds between Time 1 and Time 2 above? How many seconds between Time 2 and Time 3? Are you sure? Would you think again, please? We human being just would like to confuse ourselves. Then look at these:
Time a: 2000-01-01 11:59:59 PM
Time b: 2000-01-01 0:00:00
Time c: 2000-01-01 0:00:01 AM
Time d: 2000-01-01 0:59:59 AM
Time e: 2000-01-01 1:00:00 PM
Time e: 2000-01-01 1:00:01 AM
So, if it is possible, please use military time (24 hour time system) instead of 12 hour time system.
Look at following three times:
Time 4: 2000-01-01 00:00:00
Time 5: 2000-01-01 24:00:00
Time 6: 2000-01-02 00:00:00
Are they at same time? Indeed, the format of Time 5 would never exist in any official document, except in some railway time tables, where 24:00:00 used only to describe the arrival time. Time 4 and Time 6 are often confused, though there is 24 hours difference. However, for the purpose of reporting period ending time, it is suggested to use format either as 1999-12-31 24:00:00 or 1999-12-31 23:59:59, but not 2000-01-01 00:00:00. This is particularly important for week report, because the reader could not tell from the glance at the date. For instance, 2010-04-12 00:00:00, how can you directly tell it is one second from 2010-04-11 00:00:01 or 2010-04-12 23:59:59? No, both are incorrect. The correct answer is it is one second from 2010-04-12 00:00:01 or 2010-04-11 23:59:59.
When the first week starts in a year? ISO 8601 provides the first week in a year starts from a Monday of a week with includes the first Thursday in that year. Therefore, the first week must include the January 4th. This approach does indeed emphasis the majority of the days in first week are in new year. This approach can also be used to determine the first week of a fiscal year as well as the first week of a month.
http://en.wikipedia.org/wiki/Date_and_time_notation_by_country
http://en.wikipedia.org/wiki/Calendar_date
http://en.wikipedia.org/wiki/ISO_8601
http://24hourtime.info/the-24-hour-time-system/
http://www.cl.cam.ac.uk/~mgk25/iso-time.html
http://en.wikipedia.org/wiki/12-hour_clock
It is for sure IT industry should use yyyy-mm-dd format, not because it is an ISO 8601 standard, but also it is only meaningful format when you conduct textual sorting. For instance, when you use date as part of file name, it can be directly sorted by Windows Explorer.
In Microsoft Excel, if yyyy-mm-dd format is needed, please go to Format Cells, chose Locale as Chinese (Hong Kong S.A.R). It can be found in Type.
Sunday is traditionally regarded as the first day of the week. Different industries may have their own standard. However, the International Organization for Standardization (ISO) specifies that the week begins with Monday, which is also the standard for IT industry. So, day 1 in a week is Monday. The week ends at 23:59:59 of Sunday. Beware, Microsoft Excel's function weekday() does use Sunday as day 1 in a week. In addition, in Microsoft SQL Server DATEPART(WEEKDAY, dateToCheck) does also generate 2 for Monday.
Look at following three times:
Time 1: 2000-01-01 11:59:59 AM
Time 2: 2000-01-01 12:00:00
Time 3: 2000-01-01 12:00:01 PM
How many seconds between Time 1 and Time 2 above? How many seconds between Time 2 and Time 3? Are you sure? Would you think again, please? We human being just would like to confuse ourselves. Then look at these:
Time a: 2000-01-01 11:59:59 PM
Time b: 2000-01-01 0:00:00
Time c: 2000-01-01 0:00:01 AM
Time d: 2000-01-01 0:59:59 AM
Time e: 2000-01-01 1:00:00 PM
Time e: 2000-01-01 1:00:01 AM
So, if it is possible, please use military time (24 hour time system) instead of 12 hour time system.
Look at following three times:
Time 4: 2000-01-01 00:00:00
Time 5: 2000-01-01 24:00:00
Time 6: 2000-01-02 00:00:00
Are they at same time? Indeed, the format of Time 5 would never exist in any official document, except in some railway time tables, where 24:00:00 used only to describe the arrival time. Time 4 and Time 6 are often confused, though there is 24 hours difference. However, for the purpose of reporting period ending time, it is suggested to use format either as 1999-12-31 24:00:00 or 1999-12-31 23:59:59, but not 2000-01-01 00:00:00. This is particularly important for week report, because the reader could not tell from the glance at the date. For instance, 2010-04-12 00:00:00, how can you directly tell it is one second from 2010-04-11 00:00:01 or 2010-04-12 23:59:59? No, both are incorrect. The correct answer is it is one second from 2010-04-12 00:00:01 or 2010-04-11 23:59:59.
When the first week starts in a year? ISO 8601 provides the first week in a year starts from a Monday of a week with includes the first Thursday in that year. Therefore, the first week must include the January 4th. This approach does indeed emphasis the majority of the days in first week are in new year. This approach can also be used to determine the first week of a fiscal year as well as the first week of a month.
http://en.wikipedia.org/wiki/Date_and_time_notation_by_country
http://en.wikipedia.org/wiki/Calendar_date
http://en.wikipedia.org/wiki/ISO_8601
http://24hourtime.info/the-24-hour-time-system/
http://www.cl.cam.ac.uk/~mgk25/iso-time.html
http://en.wikipedia.org/wiki/12-hour_clock
Subscribe to:
Posts (Atom)
Posts
