Solved

Dateadd and Merging Data

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

867 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

24 Experts available now in Live!

Get 1:1 Help Now