Solved

Dateadd and Merging Data

Posted on 2011-02-28
5
455 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now