Solved

Dateadd and Merging Data

Posted on 2011-02-28
5
459 Views
Last Modified: 2012-05-11
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
Comment
Question by:halifaxman
  • 3
5 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 34996517
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
 
LVL 4

Expert Comment

by:musalman
ID: 34996529
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
 
LVL 25

Expert Comment

by:reb73
ID: 34996537
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
 

Author Comment

by:halifaxman
ID: 34996655
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
 
LVL 25

Accepted Solution

by:
reb73 earned 500 total points
ID: 34996726
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL query for highest sequence 4 59
SQL that will turn the rows in to columnsin date format 28 52
SQL Query help 3 24
disk usage reporting tools 27 48
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question