• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 483
  • Last Modified:

Dateadd and Merging Data

I have the following query below - this is against a SQL 2005 database

DECLARE @start DATETIME
DECLARE @end DATETIME

SET @start = DATEADD(hour, 7,
                     DATEADD(day, -5,
                             CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 120), 120)))
    + 0.0208334
SET @end = DATEADD(hour, 7,
                   DATEADD(day, -4,
                           CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 120), 120)))
    + 0.0208334    

SELECT  CONVERT(VARCHAR, DateTime, 103) AS Eventdate,
        MIN(DateTime) [DateTime],
        MAX(DateTime) LoginDateTime,
        DATEDIFF(s, MIN(DateTime), MAX(DateTime)) Duration,
        -1 AS Reasoncode,
        pe.FirstName,
        pe.LastName
FROM    t_Agent_Event_Detail ae
        LEFT OUTER JOIN im_awdb.dbo.t_Agent ag ON ae.SkillTargetID = ag.SkillTargetID
        LEFT OUTER JOIN im_awdb.dbo.t_Person pe ON ag.PersonID = pe.PersonID
        LEFT OUTER JOIN im_awdb.dbo.t_Agent_Team_Member tm ON ag.SkillTargetID = tm.SkillTargetID
        LEFT OUTER JOIN im_awdb.dbo.t_Agent_Team at ON tm.AgentTeamID = at.AgentTeamID
        LEFT OUTER JOIN im_awdb.dbo.t_Reason_Code rc ON ae.ReasonCode = rc.ReasonCode
WHERE   ae.DateTime >= @start
        AND ae.DateTime < @end
        AND ag.Description = 'LH'
        AND pe.LastName = 'Smihth'
GROUP BY CONVERT(VARCHAR, DateTime, 103),
        pe.FirstName,
        pe.LastName

The query looks at data from 7:30 am to 7:30 am the next day. In the above example this is the 23rd Feb 7:30 am to 24th Feb 7:30 am

The query produces the following output below


Eventdate      DateTime      Login                        DateTime                             Duration          Reasoncode      FirstName                LastName
23/02/2011      2011-02-23 20:51:14.000      2011-02-23 22:53:58.000      7364             -1                      Robert                Smith
24/02/2011      2011-02-24 07:00:10.000      2011-02-24 07:00:10.003      0             -1                      Robert                Smith

I have 2 questions

1. How would I get the get the data varaible @start and @end to show 2011-02-23 07:30:00.000 and  2011-02-24 07:30:00.000 without adding the + 0.0208334   to the end?
2. I would like to the query to display the following result below (I need to keep all the fields in the query and cannot remove any)

Eventdate      DateTime      Login                        DateTime                             Duration          Reasoncode      FirstName                LastName
23/02/2011      2011-02-23 20:51:14.000      2011-02-24 07:00:10.003      36536             -1                      Robert                Smith

Any help would be much appreciated
0
Mark Wilson
Asked:
Mark Wilson
  • 3
1 Solution
 
reb73Commented:
A1: Something similar but using pure datetime calculations -

SET @start = DATEADD(Minute, 450, DATEADD(Day, 0, DATEDIFF(Day, 0, GETDATE())))
SET @end = DATEADD(Day, 1, @start)

A2: Change the group by as follows

GROUP BY --CONVERT(VARCHAR, DateTime, 103),
        pe.FirstName,
        pe.LastName

0
 
musalmanERP ConsultantCommented:
1) Are you interested in following format ?
2011-02-28 16:29:27.000

try
SELECT CONVERT(VARCHAR(16), GETDATE(), 120) + ':00.000'

2) try my query as you required for all dates...
0
 
reb73Commented:
P.S: The calculation for the first field in the results will have to change as well (overlooked it!). You could change it as follows -

SELECT  CONVERT(VARCHAR, Min(DateTime), 103) AS Eventdate
               .....
0
 
Mark WilsonBI DeveloperAuthor Commented:
Thanks for the answers

Reb Q1 - this is now a lot better thanks

Q2

A2: Change the group by as follows

GROUP BY --CONVERT(VARCHAR, DateTime, 103),
        pe.FirstName,
        pe.LastName

This the same grouping as in the original query

SELECT  CONVERT(VARCHAR, Min(DateTime), 103) AS Eventdate - I have now changed to this

I am still getting the same results as before
 




0
 
reb73Commented:
No, the grouping is altered (see the two hypen marks prefixing/commenting out the first group by field)

To make it clearer, here's the complete revised query -

 
DECLARE @start DATETIME
DECLARE @end DATETIME

SET @start = DATEADD(Minute, 450, DATEADD(Day, 0, DATEDIFF(Day, 0, GETDATE())))
SET @end = DATEADD(Day, 1, @start)

SELECT  CONVERT(VARCHAR, Min(DateTime), 103) AS Eventdate,
        MIN(DateTime) [DateTime],
        MAX(DateTime) LoginDateTime,
        DATEDIFF(s, MIN(DateTime), MAX(DateTime)) Duration,
        -1 AS Reasoncode,
        pe.FirstName,
        pe.LastName
FROM    t_Agent_Event_Detail ae
        LEFT OUTER JOIN im_awdb.dbo.t_Agent ag ON ae.SkillTargetID = ag.SkillTargetID
        LEFT OUTER JOIN im_awdb.dbo.t_Person pe ON ag.PersonID = pe.PersonID
        LEFT OUTER JOIN im_awdb.dbo.t_Agent_Team_Member tm ON ag.SkillTargetID = tm.SkillTargetID
        LEFT OUTER JOIN im_awdb.dbo.t_Agent_Team at ON tm.AgentTeamID = at.AgentTeamID
        LEFT OUTER JOIN im_awdb.dbo.t_Reason_Code rc ON ae.ReasonCode = rc.ReasonCode
WHERE   ae.DateTime >= @start
        AND ae.DateTime < @end
        AND ag.Description = 'LH'
        AND pe.LastName = 'Smihth'
GROUP BY
        pe.FirstName,
        pe.LastName

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now