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
Awarded to:
Skills Required
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.

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