Closed

Sql expert for 1 hour work

This project was awarded to sumon355 for $35 USD.

Get free quotes for a project like this
Employer working
Project Budget
$30 - $40 USD
Total Bids
5
Project Description

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.

Awarded to:
Skills Required

Browse Related Skills

Related Projects

Other things people do on Freelancer

Related Articles

Latest Articles

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online