?
Solved

Dateadd and Merging Data

Posted on 2011-02-28
5
Medium Priority
?
468 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

719 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