sql query count failed sessions per user and 7 day period.

Posted on 2013-09-24
Medium Priority
Last Modified: 2013-09-26

I need to count the  failed sessions per CustomerName for this week or 7 day period.

Customer table.
DeviceId, Custname,,,,,,,,,,,,

session table
Sessions_Id, DeviceId, on_date_time, Off_Date_Time, Complete,,,,,,,,,,,,,

if complete is false the count it.

output should for be distinct   " custname   -  'Number of false completes""
Question by:jamppi
LVL 25

Expert Comment

ID: 39519795
This should work:
SELECT C.DeviceID, C.Custname, COUNT(*)
FROM Customer C INNER JOIN session S
ON C.DeviceID = S.DeviceID AND S.Complete = FALSE
GROUP BY C.DeviceID, C.Custname

Open in new window

I have included DeviceID in case you have two customers with the same name
LVL 49

Accepted Solution

PortletPaul earned 1600 total points
ID: 39519843
While custname might not be unique, a customer might use more than one device, so I've not included deviceid. Heer's a 7 day period approach.
      , COUNT(*)
FROM Customer C
INNER JOIN session S
        ON C.DeviceID = S.DeviceID AND S.Complete = FALSE

WHERE on_date_time >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -7) -- 7 day period


Open in new window


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

623 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