Need a fresh approach to something quite simple

Question

Post reply

Need a fresh approach to something quite simple

schwizla

SSCrazy

Points: 2668
More actions
November 2, 2006 at 9:23 am

#114909

Hello ppl,
I've been trying to have a go now for a few days but have now given up. I hope your brilliant minds can enlighten me here. Its quite a simple thing really which is why its more frustrating! Query as follows:
SELECT citrixfarm.SDB_SESSION.SESSIONSTART,
           citrixfarm.LU_APPNAME.APPNAME
FROM   citrixfarm.SDB_SESSION
INNER JOIN
citrixfarm.LU_APPNAME
ON
citrixfarm.SDB_SESSION.FK_APPNAMEID = citrixfarm.LU_APPNAME.PK_APPNAMEID
WHERE citrixfarm.LU_APPNAME.APPNAME IN ('DESKTOP', '20 DESKTOP')
This returns
2004-04-05 14:40:23.000     20 DESKTOP
2004-04-05 14:47:36.000     20 DESKTOP
2004-04-07 10:03:01.000     DESKTOP
2004-04-07 11:33:22.000     20 DESKTOP
2004-04-13 10:38:26.000     DESKTOP
2004-04-13 12:00:53.000     20 DESKTOP etc etc (750000 row table)
I want it to diplay
Day          Count of Desktop and 20 Desktop
10/10/06    200
11/10/06    230
12/10/06    245

heres where the problem lies as the datetime needs to be truncated and I have done so by modifying the select staement like this:
SUBSTRING(cast(citrixfarm.SDB_SESSION.SESSIONSTART as varchar(25)),1,11)
which returns Mar 26 2006 etc
so now I have:
SELECT DISTINCT(SUBSTRING(cast(citrixfarm.SDB_SESSION.SESSIONSTART as varchar(25)),1,11))

FROM    citrixfarm.SDB_SESSION
INNER JOIN
        citrixfarm.LU_APPNAME
ON citrixfarm.SDB_SESSION.FK_APPNAMEID =    citrixfarm.LU_APPNAME.PK_APPNAMEID
WHERE
         citrixfarm.LU_APPNAME.APPNAME IN ('DESKTOP', '20 DESKTOP')
GROUP BY citrixfarm.SDB_SESSION.SESSIONSTART
ORDER BY citrixfarm.SDB_SESSION.SESSIONSTART
so I need a count of the simlar rows and this also involves using the distict, count and order by commands but I am failing to get my head around this and also I am now getting
Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

I am sure I have approached this all wrong...PLEASE HELP!!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 1

This may do the trick

SELECT convert(varchar(10), citrixfarm.SDB_SESSION.SESSIONSTART, 103),

count(*) mycount

FROM citrixfarm.SDB_SESSION

INNER JOIN

citrixfarm.LU_APPNAME

ON

citrixfarm.SDB_SESSION.FK_APPNAMEID = citrixfarm.LU_APPNAME.PK_APPNAMEID

WHERE citrixfarm.LU_APPNAME.APPNAME IN ('DESKTOP', '20 DESKTOP')

GROUP BY convert(varchar(10), citrixfarm.SDB_SESSION.SESSIONSTART, 103)

Will1922 SSCertifiable Points: 7189 More actions · Answer 2

Try this:

SELECT CONVERT(CHAR(8),citrixfarm.SDB_SESSION.SESSIONSTART,101), COUNT(*)

FROM citrixfarm.SDB_SESSION

INNER JOIN

citrixfarm.LU_APPNAME

ON

citrixfarm.SDB_SESSION.FK_APPNAMEID = citrixfarm.LU_APPNAME.PK_APPNAMEID

WHERE citrixfarm.LU_APPNAME.APPNAME IN ('DESKTOP', '20 DESKTOP')

GROUP BY CONVERT(CHAR(8),citrixfarm.SDB_SESSION.SESSIONSTART,101)

Live to Throw
Throw to Live
Will Summers

Will1922 SSCertifiable Points: 7189 More actions · Answer 3

I guess Daryl beat me to the Post Reply button.

Live to Throw
Throw to Live
Will Summers

schwizla SSCrazy Points: 2668 More actions · Answer 4

I AM NOT WORTHY!

Thank you so much guys. AMAZING response time to my very frustrating problem. This forum is by far the best one out there.

Please continue to keep up the good work, it is very much apprecited

Pedro R. Lopez SSC Eights! Points: 902 More actions · Answer 5

siz if you want more performance use this in your group by and select

dateadd

(d,datediff(d,0,citrixfarm.SDB_SESSION.SESSIONSTART),0)

this way is faster since no convertion is needed.

hope you have and index for the datetime field.

Pedro R. Lopez
http://madurosfritos.blogspot.com/[/url]

schwizla SSCrazy Points: 2668 More actions · Answer 6

Hey Pedro,

thats shaved off two seconds! great!

But now I'm getting

2006-05-05 00:00:00.000

2004-07-20 00:00:00.000 etc

How do I trim the trailing zero's without converting it? Is there a function to trim or reformat a date variable?

Will1922 SSCertifiable Points: 7189 More actions · Answer 7

CONVERT(CHAR(10), expression, 21) will convert the date to yyy-mm-dd

Live to Throw
Throw to Live
Will Summers

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Let the application worry about that... unless you have loads of different reports using that query. You could centralize the code. The convert in the select part only shouldn't slow it down much.

Pedro R. Lopez SSC Eights! Points: 902 More actions · Answer 9

Agree with Ninja, but if you can;t after you get your resut do a

convert(varchar(10), datevalue , 103)

as in previous post, but after you get the reults, if not you query will slow down again.

SELECT convert(varchar(10), Yourdatefield, 103), a.*

FROM (YOUR QUERY) a

Pedro R. Lopez
http://madurosfritos.blogspot.com/[/url]

schwizla SSCrazy Points: 2668 More actions · Answer 10

Thanks every1 for the input,

Ninja, tru the convert doesnt slow it down that much. I just wanted to know if there was another way to trim down the date field without resorting to convert.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

The convert in the select is very minor because it won't affect the query plan (data access wise). It'll take a few cpu cycles to do the converts, but I wouldn't expect it to be very big (few ms at most).

A for the trimming part, it can only be executed on a string. And since datetime is a numeric you have no choice but to do conversions.