December 15, 2008 at 5:38 am
I am getting Error While Executing Below Query in SQL 2005
Select BusinessGroup,EmployeeName,ReportingTo,Location
From tbl_pm_employee
Inner join tbl_CNF_BusinessGroups
on tbl_CNF_BusinessGroups.BusinessGroupID=tbl_pm_employee.BusinessGroupID
Inner Join tbl_PM_location
on tbl_pm_location.locationid=tbl_pm_employee.locationid
Where UserName IN ( select max(Logdate) As LogDate
from tbl_PBN_debugging
group by UserName
Having DATEDIFF(dd, MAX(Logdate), GETDATE()) >= 90)
The Error Is : Conversion failed when converting datetime from character string.
Can any one help me out Actually i want list of people who have not logged in for last 90 days.
Plz Help me
December 15, 2008 at 5:48 am
OK the error is because of the where clause.
You are basically saying WHERE Username IN (datefield)
you can't compare those fields.
Use this:
select max(Logdate) As LogDate ,UserName
from tbl_PBN_debugging
group by UserName
Having DATEDIFF(dd, MAX(Logdate), GETDATE()) >= 90
as part of an inner join and then join on UserId and that will restrict by users that you want
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
December 15, 2008 at 6:24 am
sunilibn (12/15/2008)
Can any one help me out Actually i want list of people who have not logged in for last 90 days.
In addition to correcting the error pointed out by Chris, you might also wish to amend the logic in your WHERE clause to something like this - not because it's faster, or correct, but because it reads easier!
SELECT BusinessGroup, EmployeeName, ReportingTo, Location
FROM tbl_pm_employee e
INNER JOIN tbl_CNF_BusinessGroups g
ON g.BusinessGroupID=e.BusinessGroupID
INNER JOIN tbl_PM_location l
ON l.locationid=e.locationid
WHERE NOT EXISTS (SELECT 1 FROM tbl_PBN_debugging WHERE UserName = e.UserName AND DATEDIFF(dd, Logdate, GETDATE()) < 91)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 15, 2008 at 10:49 pm
Thanks For Your Help but ..
As i can not make a joins on UserID because UserId filed is not present in table tbl_PBN_debugging.
In tbl_PBN_debugging there is only UserName and LogDate fileds.
Also now as per Christopher Stobbs says. i hav tried the below query but still error because. of UserName.
i guess.
Select BusinessGroup,EmployeeName,ReportingTo,Location
From tbl_pm_employee E
Inner join tbl_CNF_BusinessGroups G
on G.BusinessGroupID=E.BusinessGroupID
Inner Join tbl_PM_location L
on L.locationid=E.locationid
Where UserName IN ( select max(Logdate) As LogDate,UserName
from tbl_PBN_debugging
group by UserName
Having DATEDIFF(dd, MAX(Logdate), GETDATE()) >= 90)
Here The Error Is: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
As m not getting wht to use in WHERE .........IN ( select max(Logdate) As LogDate,UserName
from tbl_PBN_debugging
group by UserName
Having DATEDIFF(dd, MAX(Logdate), GETDATE()) >= 90) insted of UserName..
Sunil
December 16, 2008 at 1:21 am
Sunil, did you try my code?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 16, 2008 at 3:56 am
Hey Chris!!!
Hope you Doing Well..
Nways..
Yes i have tried your Code but it is giving 948 rows and which is Incorrect.
As if i only execute the query :
select max(Logdate) As LogDate,UserName
from tbl_PBN_debugging
group by UserName
Having DATEDIFF(dd, MAX(Logdate), GETDATE()) >= 90
Then it's returns 440 Rows.
How come the 948 Users haven't login in last 90 Days?
December 16, 2008 at 4:29 am
Hi Sunil
Give this a try...SELECT DISTINCT UserName
FROM tbl_PBN_debugging
WHERE DATEDIFF(dd, Logdate, GETDATE()) < 91
...how many rows do you get?
This is "the number of users who have logged on within 90 days"
What's the total number of users who have logged on at any time?
SELECT DISTINCT UserName
FROM tbl_PBN_debugging
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 16, 2008 at 4:40 am
SELECT DISTINCT UserName
FROM tbl_PBN_debugging
WHERE DATEDIFF(dd, Logdate, GETDATE()) < 91
...how many rows do you get?
I hope tbl_PBN_debugging table contains only one row per user. LogDate field gets updated upon successful login.
If he is recording each login date in that table (to maintain login history), that would give incorrect results...
December 16, 2008 at 4:59 am
ps (12/16/2008)
SELECT DISTINCT UserName
FROM tbl_PBN_debugging
WHERE DATEDIFF(dd, Logdate, GETDATE()) < 91
...how many rows do you get?
I hope tbl_PBN_debugging table contains only one row per user. LogDate field gets updated upon successful login.
How do you know this? Sunil hasn't stated it - are you working on the same system?
If he is recording each login date in that table (to maintain login history), that would give incorrect results...
Possibly, depends on the values of Logdate - but stripping time off the current date isn't a priority at this time.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 16, 2008 at 5:14 am
How do you know this? Sunil hasn't stated it - are you working on the same system?
No, I'm not working on same system. It was a concern intended for him to check this possiblity as well.
No offence 🙂
December 16, 2008 at 5:27 am
ps (12/16/2008)
How do you know this? Sunil hasn't stated it - are you working on the same system?
No, I'm not working on same system. It was a concern intended for him to check this possiblity as well.
No offence 🙂
None taken, buddy, and a good point.
Here's a little sample data
DROP TABLE #tbl_PBN_debugging
CREATE TABLE #tbl_PBN_debugging (LogDate DATETIME,UserName VARCHAR(20))
INSERT INTO #tbl_PBN_debugging (LogDate, UserName)
SELECT GETDATE()-0, 'Sunil' UNION ALL -- today (within the last 1 day, today)
SELECT GETDATE()-1, 'Sunil' UNION ALL -- yesterday (within the last 2 days, yesterday and today)
SELECT GETDATE()-90, 'Sunil' UNION ALL
SELECT GETDATE()-92, 'Sunil' UNION ALL
SELECT GETDATE()-90, 'Pradeep' UNION ALL -- within the last 91 days
SELECT GETDATE()-91, 'Pradeep'
Running this - "users who haven't logged on in the last 90 days"...
select max(LogDate) As LogDate,UserName
from #tbl_PBN_debugging
group by UserName
Having DATEDIFF(dd, MAX(LogDate), GETDATE()) >= 90
returns this...LogDate UserName
----------------------- --------
2008-09-17 12:17:59.010 Pradeep
And running this - "users who have logged on in the last 90 days"...SELECT DISTINCT UserName
FROM #tbl_PBN_debugging
WHERE DATEDIFF(dd, LogDate, GETDATE()) < 90 Returns this...UserName
--------
Sunil
The first query can be modified to return only the username...
SELECT UserName
FROM #tbl_PBN_debugging
GROUP BY UserName
HAVING DATEDIFF(dd, MAX(LogDate), GETDATE()) >= 90
So we have more than one method of determining which users have logged on in the last n days - what we really need to know now is whether or not one or more of the JOINed tables are one-to-many.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 16, 2008 at 5:41 am
Yes.. the first query when grouped by username gives correct result along with max(logdate).
I guess he got quite a few ways of pulling what he wants!
December 16, 2008 at 11:45 pm
Folks,
As i don't want the users who loged in last 90 days. I want the uesr list who did not loged in in last 90 days.
Also the tbl_PBN_debugging table contains many rows per user as When user loged inside the system the entry is thr in table and the other filed name is LogId.
December 16, 2008 at 11:59 pm
SELECT UserName
FROM #tbl_PBN_debugging
GROUP BY UserName
HAVING DATEDIFF(dd, MAX(LogDate), GETDATE()) >= 90
Did you run this query written by Chris? what was the output?
December 17, 2008 at 2:24 am
sunilibn (12/16/2008)
Folks,As i don't want the users who loged in last 90 days. I want the uesr list who did not loged in in last 90 days.
Also the tbl_PBN_debugging table contains many rows per user as When user loged inside the system the entry is thr in table and the other filed name is LogId.
In that case, take your original query
Select BusinessGroup,EmployeeName,ReportingTo,Location
From tbl_pm_employee
Inner join tbl_CNF_BusinessGroups
on tbl_CNF_BusinessGroups.BusinessGroupID=tbl_pm_employee.BusinessGroupID
Inner Join tbl_PM_location
on tbl_pm_location.locationid=tbl_pm_employee.locationid
Where UserName IN ( select max(Logdate) As LogDate
from tbl_PBN_debugging
group by UserName
Having DATEDIFF(dd, MAX(Logdate), GETDATE()) >= 90)
and replace the part in bold with
WHERE NOT EXISTS (
SELECT 1
FROM #tbl_PBN_debugging
WHERE UserName = tbl_pm_employee.UserName
AND DATEDIFF(dd, LogDate, GETDATE()) < 90
)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply