Sunday, 28 August 2016

Difference between DateTime and DateTime2 DataType

Difference between DateTime and DateTime2 DataType

DateTime2 is the new Data Type introduced in Sql Server 2008 for storing Date

and Time value. As per MSDN, Microsoft Suggests to use this new Data Type 

for new work instead of DateTime.


Following table summarizes some of the major difference between this
new DateTime2 and the old DateTime Data Type.
DateTimeDateTime2[(n)]
Min Value1753-01-01 00:00:000001-01-01 00:00:00
Max Value9999-12-31 23:59:59.9979999-12-31 23:59:59.9999999
Storage Size8 Bytes6 to 8 bytes
Note: Parameter n is optional and if it is not specified then fractional
seconds precision is 7 digit and it can be from 0 to 7 digit. For fractional seconds
precision <3, takes 6 bytes For fractional seconds precision 3 or 4 it will take
7 bytes For fractional seconds precision >4 it will take 8 bytes
UsageDeclare @now datetimeDeclare @now datetime2(7)
ComplianceIs not an ANSI/ISO compliantIs an ANSI/ISO compliant
Current Date and Time functionGetDate() – It returns DB Current Date and Time of DateTime Data Type
Example: SELECT GETDATE()
Result: 2011-09-16 13:23:18.767
SYSDATETIME()– It returns DB Current Date and Time of DateTime2 Data Type
Example:SELECT SYSDATETIME()
Result: 2011-09-16 13:23:18.7676720
+/- daysWORKS
Example: 
DECLARE
 @nowDateTimeDATETIME = GETDATE() SELECT@nowDateTime + 1
Result: 2011-09-17 13:44:31.247
FAILS – Need to use only DateAdd function
Example: 
DECLARE
 @nowDateTime2 DATETIME2=
SYSDATETIME()
SELECT
 @nowDateTime2+1
ResultMsg 206, Level 16, State 2, Line 2
Operand type clash: datetime2 is incompatible with int

Wednesday, 24 August 2016

Multiple Pivots in Sql Server

CREATE TABLE TblPivot
(
Time_Dt Date,
Workflow_Id int not null identity(1,1),
Completed_No int,
Average Numeric(6,2)
)


insert into TblPivot
(Time_Dt,Completed_No,Average) values
('2016-08-24',null,1.5)

insert into TblPivot
(Time_Dt,Completed_No,Average) values
('2016-08-24',null,1.6)


insert into TblPivot
(Time_Dt,Completed_No,Average) values
('2016-08-24',null,2.6)

insert into TblPivot
(Time_Dt,Completed_No,Average) values
('2016-08-24',100,2.9)






Select Time_Dt,sum(Coalesce(([10004]),0)) Completed,
sum(Coalesce(([1]),0)) Workflow_1,
sum(Coalesce(([2]),0)) Workflow_2,
sum(Coalesce(([3]),0)) Workflow_3,
sum(Coalesce(([4]),0)) Workflow_4
from 
(SELECT
*
 FROM
 (
    SELECT
Time_Dt,Workflow_Id,
case when Completed_No is not null then 10000+Workflow_Id end Count_Workflow_Id,
Completed_No,Average
 FROM TblPivot
 ) AS P
 

PIVOT
(
  Sum(Completed_No) FOR Count_Workflow_Id IN ([10004])
) AS pv1
 
-- For ItemName
PIVOT
(
 Sum(Average) FOR  Workflow_Id in ([1], [2], [3],[4])
) AS pv2 )a group by Time_Dt


--

Regards,
Ashif Ahamed 
 


"We can't solve problems by using the same kind of thinking we used when we created them - Albert Einstein"

Sunday, 21 August 2016

Base Query to Date Difference Between Overlapping and Non-Overlapping Dates

CREATE TABLE dbo.Projects

( projectid  INT          NOT NULL,

  title      VARCHAR(100) NOT NULL,

  start_date DATE         NOT NULL,

  end_date   DATE         NOT NULL);

Go

INSERT INTO dbo.Projects(projectid, title, start_date, end_date) VALUES

  (1, 'Project 1', '20100212', '20100220'),

  (2, 'Project 2', '20100214', '20100312'),

  (3, 'Project 3', '20100124', '20100201'),

  (4, 'Project 4', '20100401', '20100410'),

  (5, 'Project 5', '20100420', '20100425'),

  (6, 'Project 6', '20100501', '20100509'),

  (7, 'Project 7', '20100409', '20100415'),

  (8, 'Project 8', '20100414', '20100421'),

  (9, 'Project 9', '20100605', '20100610'),

  (10, 'Project 10', '20100614', '20100621'),

  (11, 'Project 11', '20100601', '20100625');

Go

והשליפה הרקורסיבית:

With T1 As

(Select    Row_Number() Over(Order By Start_date) N,

        *

From    Projects),

T2 As

(Select 1 N,

        start_date,

        end_date

From    T1

Where    N=1

Union All

Select    T2.N+1,

        T2.start_date,

        Case When T1.end_date>=T2.end_date Then T1.end_date Else T2.end_date End

From    T1

Inner Join T2

        On T2.End_date >= T1.start_date

Where    T1.N=T2.N+1

Union All

Select    T2.N+1,

        T1.start_date,

        T1.end_date

From    T1

Inner Join T2

        On T2.End_date < T1.start_date

Where    T1.N=T2.N+1

)

Select    start_date,

        Max(end_date) end_date

From    T2

Group By start_date;

הפלט המתקבל:

start_date    end_date

2010-01-24    2010-02-01

2010-02-12    2010-03-12

2010-04-01    2010-04-25

2010-05-01    2010-05-09

2010-06-01    2010-06-25