In Progress

Sql expert for 1 hour work

Please note that I cannot use any functions or stored procedure, It has to be a query in sql server 2005

Query1

have table called AllSessions and there are two fields

SessionBegin and SessionEnd

It basically show when the users were logged in to my app. The session End might be null if they are currently logged in.

I want to write a query to get the number of users that were logged in during a time frame group by 15 minutes intervals

Attached is the data, the records are unique, [url removed, login to view]

Below is pseudo query I wrote, please note that I am adding 5 hours as the time on server is on UTC. I am adding 5 hours to convert to EST

I want something like

03/23 2013 6:am -20

03/23 2013 6:15 am -30

DECLARE @BeginDate AS DATE

DECLARE @EndDate AS DATE

DECLARE @BeginTime AS CHAR(2)

DECLARE @EndTime AS CHAR(2)

SET @BeginDate = '2013-02-22'

SET @EndDate = '2013-02-22'

SET @BeginTime = '06'

SET @EndTime = '11'

SELECT *

FROM AllSession

WHERE (SessionEnd < DATEADD(HOUR, 5, CAST(CAST(CAST(@BeginDate AS DATE) AS VARCHAR)

+ ' ' + @BeginTime + ':00:00:00' AS DATETIME))

AND SessionBegin DATEADD(HOUR, 5, CAST(CAST(CAST(@EndDate AS DATE) AS VARCHAR)

+ ' ' + @EndTime + ':00:00:00' AS DATETIME)))

GROUP BY DATEADD(hour, - 5, DATEADD(minute, - (1 * DATEDIFF(minute, 0, ?) % 15), DATEADD(minute, DATEDIFF(minute, 0,

?), 0)))

ORDER BY DATEADD(hour, - 5, DATEADD(minute, - (1 * DATEDIFF(minute, 0, ?) % 15), DATEADD(minute, DATEDIFF(minute, 0,

?), 0)))

Query 2

------------

I have the below querythat counts number of records in 15 minutes intervals.

It de ends when there are no longer any records

for eg if ther are no records after 11:45 am it stops , i want to show nulls till end of the @enddate time

DECLARE @BeginDate AS DATE

DECLARE @EndDate AS DATE

DECLARE @BeginTime AS CHAR(2)

DECLARE @EndTime AS CHAR(2)

SET @BeginDate = '2013-02-22'

SET @EndDate = '2013-02-22'

SET @BeginTime = '06'

SET @EndTime = '23'

SELECT

cast( dateadd(hour,-5,(dateadd(minute, -1 * datediff(minute, 0, DtComplete) % 15, dateadd(minute, datediff(minute, 0, DtComplete), 0)))) as nvarchar(30)) AS UsingCast

, count(*) count_total

, count( distinct code) count_codes

FROM [url removed, login to view]

WHERE

DtComplete BETWEEN

DATEADD(HOUR, 5, CAST(CAST(CAST(@BeginDate as DATE) AS VARCHAR) + ' ' + @BeginTime + ':00:00:00' AS DATETIME)) AND

DATEADD(HOUR, 5,CAST(CAST(CAST(@EndDate as DATE) AS VARCHAR) + ' ' + @EndTime + ':00:00:00' AS DATETIME))

group by dateadd(minute, -1 * datediff(minute, 0, DtComplete) % 15, dateadd(minute, datediff(minute, 0, DtComplete), 0))

I want it show like

11:45am -15

12:00-null

12:15-null

and so on till

10:45-null

I need this to be done in next 4 hours.

Skills: SQL

See more: work by the hour, sql get hour from datetime, sql get date, hour of code, by the hour, 1.5 hours in minutes, group de, c# expert 5-6 hours of work, set hour, hours work count, sql select select, hour time frame, sql select order, convert sql server stored procedure, set server 2013, convert sql code, convert stored procedure, convert code stored procedure, stored procedure expert, hour work, convert xls sql, users currently logged, sql code convert, convert sql query sql query, convert sql query sql

About the Employer:
( 0 reviews ) Ahmedabad, India

Project ID: #4368792

5 freelancers are bidding on average $46 for this job

sumon355

Hello, I am very much interested to do this work.

$35 USD in 0 days
(65 Reviews)
5.7
Reza2012

Hi expert is [url removed, login to view] send details. Let me starts. thanks

$35 USD in 1 day
(29 Reviews)
4.7
BrindabanB

Hi, I will be able to deliver in an hour you decide on the project. Looking forward to your reply. Thanks & regards, Brindaban

$40 USD in 0 days
(5 Reviews)
3.7
sveralex

Hello here is my bid

$80 USD in 2 days
(3 Reviews)
3.2
dushani84

I would like to work on this project.I have good experience in SQL.

$39 USD in 1 day
(0 Reviews)
0.0