Doesn't sort by date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Doesn't sort by date

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-01-28 : 10:29:30

Why doesn' this sp sort by Date_Logged_In

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER Procedure [dbo].[spRMU_CountNoDailyUsers]

AS
SELECT CONVERT(varchar, Log_DateTime, 103) AS Date_Logged_In, Log_Username as Username, COUNT(Log_Username) AS No_Logins
FROM tblUserLog
Where Log_Printed =0
GROUP BY CONVERT(varchar, Log_DateTime, 103) , CONVERT(varchar, Log_DateTime, 103), Log_Username
ORDER BY Date_Logged_In desc, No_Logins desc

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-28 : 10:32:31

quote:
Originally posted by Pinto

Why doesn' this sp sort by Date_Logged_In

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER Procedure [dbo].[spRMU_CountNoDailyUsers]

AS
SELECT CONVERT(varchar, Log_DateTime, 103) AS Date_Logged_In, Log_Username as Username, COUNT(Log_Username) AS No_Logins
FROM tblUserLog
Where Log_Printed =0
GROUP BY CONVERT(varchar, Log_DateTime, 103) , CONVERT(varchar, Log_DateTime, 103), Log_Username
ORDER BY ~~Date_Logged_In~~ Log_DateTime desc, No_Logins desc

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-01-28 : 10:34:13

I tried that and go this error msg when I pressed F5

Msg 8127, Level 16, State 1, Procedure spRMU_CountNoDailyUsers, Line 9
Column "tblUserLog.Log_DateTime" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-01-28 : 10:38:48

Got it working Thx

EZEK
Starting Member

8 Posts

Posted - 2008-01-28 : 10:39:53

u r grouping 2 times same field and converting them?

i think the problem is in the group by clause...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-28 : 10:40:57

harsh showed you the proper way.

ALTER Procedure [dbo].[spRMU_CountNoDailyUsers]
AS

SET NOCOUNT ON

SELECT		CONVERT(varchar, Log_DateTime, 103) AS Date_Logged_In,
		Log_Username as Username,
		COUNT(Log_Username) AS No_Logins
FROM		tblUserLog
Where		Log_Printed = 0
GROUP BY	CONVERT(varchar, Log_DateTime, 103),
		Log_Username
ORDER BY	CONVERT(varchar, Log_DateTime, 103),
		COUNT(Log_Username) desc

E 12°55'05.25"
N 56°04'39.16"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-01-28 : 10:58:55

But if I run this code in my sp the dates do not sort in the correct order

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-28 : 11:04:42

It will be a lot easier if you can show us some sample data & expected & current outputs.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-01-28 : 11:07:37

Table fields
Id Key 4
Log_Username Wilson
Log_Printed

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-01-28 : 11:07:37

Table fields
Id Key 4
Log_Username Wilson
Log_Printed

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-01-28 : 11:09:38

Table fields
Id Key 4
Log_Username Wilson
Log_Printed 0
Log_DateTime 28/01/2008 15:10:07

Expected results

28/01/08 Wilson 12
28/01/08 Jones 4
27/01/08 Wilson 3
27/01/08 Main 2
27/01/08 Jones 1
etc
etc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-28 : 11:10:04

Where is the Log_DateTime column? And what datatype does it have?

E 12°55'05.25"
N 56°04'39.16"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-01-28 : 11:14:16

Log_DateTime is shown above it is DateTime type

Every time a user logs onto my system an entry is made in the table. I want to count how many time a user logs each day and sort it by Log_DatetIme desc.

Thx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-28 : 11:25:06

So the DateTime has time information too?
And you are sorting by the converted datetime value which starts with month?

Try this

ALTER PROCEDURE	dbo.spRMU_CountNoDailyUsers
AS

SET NOCOUNT ON

SELECT		CONVERT(VARCHAR, Log_DateTime, 103) AS Date_Logged_In,
		Log_Username AS Username,
		COUNT(Log_Username) AS No_Logins
FROM		tblUserLog
WHERE		Log_Printed = 0
GROUP BY	DATEDIFF(DAY, '19000101', Log_DateTime),
		Log_Username
ORDER BY	DATEDIFF(DAY, '19000101', Log_DateTime) DESC

E 12°55'05.25"
N 56°04'39.16"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-01-28 : 11:26:41

I have this error when I compile it

Msg 8120, Level 16, State 1, Procedure spRMU_CountNoDailyUsers, Line 10
Column 'tblUserLog.Log_DateTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-01-28 : 11:31:34

I have changed it to this and it sorts ok but there is a line for each entry - it isn't grouping

ALTER PROCEDURE dbo.spRMU_CountNoDailyUsers
AS

SET NOCOUNT ON

SELECT CONVERT(VARCHAR, Log_DateTime, 103) AS Date_Logged_In,
Log_Username AS Username,
COUNT(Log_Username) AS No_Logins
FROM tblUserLog
WHERE Log_Printed = 0
GROUP BY DATEDIFF(DAY, '19000101', Log_DateTime),
Log_Username, Log_DateTime
ORDER BY DATEDIFF(DAY, '19000101', Log_DateTime)desc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-28 : 11:31:44

[code]ALTER PROCEDURE dbo.spRMU_CountNoDailyUsers
AS

SET NOCOUNT ON

SELECT CONVERT(VARCHAR(10), Date_Logged_In, 103) AS Date_Logged_In,
Username,
COUNT(*)
FROM (
SELECT DATEADD(DAY, DATEDIFF(DAY, '19000101', Log_DateTime), '19000101') AS Date_Logged_In,
Log_Username AS Username
FROM tblUserLog
WHERE Log_Printed = 0
) AS k
GROUP BY Date_Logged_In,
Username
ORDER BY Date_Logged_In DESC,
COUNT(*) DESC[/code]

E 12°55'05.25"
N 56°04'39.16"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-01-28 : 11:34:19

Date is showing as 31/10/20 for 31/10/2008 and it is still not sorting by date :-(

Here in UK I have to go home soon - so thanks for help and I'll catch up tomorrow....

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-28 : 13:55:27

I just ripped off the other examples and added a test table and it appears to be giving me the results I was expecting. I wouldn't think it would matter for this, but what version of SQL are you using?

DECLARE @tblUserLog TABLE (Log_UserName VARCHAR(50), Log_DateTime DATETIME, Log_Printed BIT)

INSERT @tblUserLog
SELECT 'Frank', GETDATE() - 100, 0
UNION ALL SELECT 'Frank' , GETDATE() - 30, 0
UNION ALL SELECT 'Frank' , GETDATE() - 30, 1
UNION ALL SELECT 'Frank' , GETDATE() - 30, 0
UNION ALL SELECT 'Frank' , GETDATE() - 10, 0
UNION ALL SELECT 'Frank' , GETDATE() - 10, 0
UNION ALL SELECT 'Frank' , GETDATE() - 1, 0
UNION ALL SELECT 'Bill' , GETDATE() - 30, 0
UNION ALL SELECT 'Bill' , GETDATE() - 10, 0
UNION ALL SELECT 'Bill' , GETDATE() - 10, 1
UNION ALL SELECT 'Bill' , GETDATE() - 10, 0
UNION ALL SELECT 'Bill' , GETDATE() - 10, 1
UNION ALL SELECT 'Bill' , GETDATE() - 10, 0
UNION ALL SELECT 'Bill' , GETDATE() - 1, 0

SELECT		
	CONVERT(VARCHAR, Log_DateTime, 103) AS Date_Logged_In,
	Log_Username AS Username,
	COUNT(Log_Username) AS No_Logins
FROM		
	@tblUserLog
WHERE		
	Log_Printed = 0
GROUP BY	
	DATEDIFF(DAY, '19000101', Log_DateTime),
	Log_Username,
	Log_DateTime
ORDER BY	
	DATEDIFF(DAY, '19000101', Log_DateTime) DESC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 02:12:38

I think you shold eliminate the third GROUP BY line...

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-29 : 03:57:26

quote:
Originally posted by Pinto

Why doesn' this sp sort by Date_Logged_In

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER Procedure [dbo].[spRMU_CountNoDailyUsers]

AS
SELECT CONVERT(varchar, Log_DateTime, 103) AS Date_Logged_In, Log_Username as Username, COUNT(Log_Username) AS No_Logins
FROM tblUserLog
Where Log_Printed =0
GROUP BY CONVERT(varchar, Log_DateTime, 103) , CONVERT(varchar, Log_DateTime, 103), Log_Username
ORDER BY Date_Logged_In desc, No_Logins desc

Use this

ALTER Procedure [dbo].[spRMU_CountNoDailyUsers]

AS
SELECT DATEADD(DAY,DATEDIFF(DAY,0,Log_DateTime),0) AS Date_Logged_In, Log_Username as Username, COUNT(Log_Username) AS No_Logins
FROM tblUserLog
Where Log_Printed =0
GROUP BY DATEADD(DAY,DATEDIFF(DAY,0,Log_DateTime),0) , Log_Username
ORDER BY Date_Logged_In desc, No_Logins desc

and let your front end application do the date formation

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources