SQL Server - Stored Procedure issue to get Matrix type of data

Need help, struggling to do this please

开发者_C百科

Got the following fields in table

Package_Name
Package_StartTime
Package_Endtime

What I require is:

Based on data range give me how much time each package took time to execute

like

Package Name - 21 Sept    22 Sept    23 Sept
ABC             3 mins     4 mins      2 mins

This way I want to see the execution time pattern of the packages.

---

For every package, this query will calculate execution time in minutes for every day between StartTime and EndTime.

Solution:

CREATE TABLE dbo.Package
(
    Name NVARCHAR(10) PRIMARY KEY
    ,StartTime DATETIME NOT NULL
    ,EndTime DATETIME NOT NULL
);

INSERT  dbo.Package 
SELECT  'A', '2011-01-01T00:02:00', '2011-01-01T00:05:00'
UNION ALL
SELECT  'B', '2011-01-01T23:50:00', '2011-01-02T00:04:00'
UNION ALL
SELECT  'C', '2011-01-01T23:50:00', '2011-01-01T23:59:00'
UNION ALL
SELECT  'D', '2011-01-02T22:10:00', '2011-01-05T01:00:00';

WITH PivotSource
AS
(
    SELECT  a.Name
            ,CONVERT(VARCHAR(25),b.DayStartTime,112) [Day]
            ,DATEDIFF(MINUTE,b.DayStartTime,b.DayEndTime) DayMinutes
    FROM    dbo.Package a
    CROSS APPLY
    (
        SELECT  v.number
                ,CONVERT(VARCHAR(25),DATEADD(DAY,v.number,a.StartTime),112) IntermediateTime
                ,CASE 
                    WHEN v.number=0 THEN a.StartTime
                    ELSE CONVERT(VARCHAR(25),DATEADD(DAY,v.number,a.StartTime),112)
                END DayStartTime
                ,CASE 
                    WHEN CONVERT(VARCHAR(25),a.EndTime,112)=CONVERT(VARCHAR(25),DATEADD(DAY,v.number,a.StartTime),112) THEN a.EndTime
                    ELSE CONVERT(VARCHAR(25),DATEADD(DAY,v.number,a.StartTime),112)+' 23:59:59.999'
                END DayEndTime          
        FROM    master.dbo.spt_values v
        WHERE   v.type = 'P'
        AND     v.number <= DATEDIFF(DAY,a.StartTime,a.EndTime)
    ) b
)
SELECT  pvt.*
FROM    PivotSource cte
PIVOT( SUM(cte.DayMinutes) FOR cte.[Day] IN ([20110101],[20110102],[20110103],[20110104],[20110105]) ) pvt

DROP TABLE dbo.Package;

Results:

Name       20110101    20110102    20110103    20110104    20110105
---------- ----------- ----------- ----------- ----------- -----------
A          3           NULL        NULL        NULL        NULL
B          10          4           NULL        NULL        NULL
C          9           NULL        NULL        NULL        NULL
D          NULL        110         1440        1440        60

---

Try this to get the data

select package_Name as Package,Package_StartTime as Start,
        DateDiff(s,Package_StartTime,Package_EndTime) as Time
from table
where Package_StartTime >= @StartOfRange and Package_EndTime <= @EndOfRange

You could format the seconds to time inside SQL if you want using

cast((DateDiff(s,Package_StartTime,Package_EndTime) / 60) as varchar(2)) + 
  ':' + cast((DateDiff(s,Package_StartTime,Package_EndTime) % 60) as varchar(2))

This will return a result set looking like this...

 Package     Start       Time 
  ABC         9/21/2011   360 
  ABC         9/22/2011   570 
  etc.

or

 Package     Start       Time 
  ABC         9/21/2011   6:00 
  ABC         9/22/2011   9:30 
  etc.

Depending on how you get the Time column

If you are using SQL 2005 and above, you can use PIVOT to spread this across columns... Here is a good link with an example of how to use PIVOT

Quick PIVOT example, not tested, but hopefully gets you started.

select * from PackageTableAbove
pivot (max (Time) for Start in ([9/21/2011],[9/22/2011],
                 [9/23/2011],[9/24/20111])) as PackageTimePerDay

If you don't want to hard-code the dates, you can look at using dynamic SQL to build the above statement. I am using Max(Time) under the assumption each package runs once per day.