Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Date field in Select & Group by


Date field in Select & Group by

Author
Message
sbrochu
sbrochu
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 9
Originally my dataset used to look like this:


Name Exe-Count Domain Last Logon User Start Date
C03702 21 HANOVER SLE609 2010-09-14 09:09:03.000
C03702 21 HANOVER SLE609 2010-09-13 10:35:24.000
C03702 21 HANOVER SLE609 2010-09-13 09:26:39.000
C03702 21 HANOVER SLE609 2010-09-09 10:15:38.000
C03712 20 HANOVER 2010-10-05 07:04:45.000
C03712 20 HANOVER 2010-10-01 06:58:42.000
C03712 20 HANOVER 2010-09-30 07:08:30.000
C03712 20 HANOVER 2010-09-29 06:59:11.000

Now I have it like this:
Name Exe-Count Domain Last Logon User
C03702 21 HANOVER SLE609
C03712 20 HANOVER
C03721 1 HANOVER JLK487
C03722 23 HANOVER SLL
C03727 21 HANOVER TDD488
C03728 21 HANOVER LXB888
C03733 1 HANOVER CAA326
C03742 31 HANOVER
C03753 13 HANOVER MAP129


As soon as I include Start Date field to select and groupby i get the data like first dataset above.
can some one help me with this please? Here is the script below.

use altiris
DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate = GETDATE() - 90
SET @EndDate = GETDATE()

select max(t.[Name]) as Asset,
max(t.execution)as [Execution Count],
t.[Domain] AS 'Domain',
t.[Last Logon User] AS 'Last Logon User',
t.[File Name],
t.[File Path],
t.[FileVersion],
t.[Policy Name]
--convert(char(10), t.[Start Date], 101) as [Start Date]
--t.[Start Date]

from

(SELECT
T0.[Name],
COUNT(*) OVER (PARTITION BY T0.[Name]) as Execution,
T1.[Domain] AS 'Domain',
T1.[Last Logon User] AS 'Last Logon User',
T2.[Start Date] AS 'Start Date',
T2.[File Name] AS 'File Name',
T2.[File Path] AS 'File Path',
T2.[FileVersion] AS 'FileVersion',
T2.[Policy Name] AS 'Policy Name'
FROM ( [vResourceEx] T0
INNER JOIN [Inv_AeX_AC_Identification] T1
ON
T0.[Guid] = T1.[_ResourceGuid] )
INNER JOIN [Evt_AeX_Application_Start] T2
ON
T1.[_ResourceGuid] = T2.[_ResourceGuid]
WHERE T2.[Policy Name] = 'Extra - All versions'
AND T2.[Start Date] BETWEEN @StartDate AND @EndDate
AND T0.[ResourceTypeGuid] = '493435f7-3b17-4c4c-b07f-c23e7ab7781f'
Group by
T0.[Name],
T1.[Domain],
T1.[Last Logon User],
T2.[Start Date],
T2.[File Name],
T2.[File Path],
T2.[FileVersion],
T2.[Policy Name])t

group by
t.[Name],
t.Execution,
t.[Domain],
t.[Last Logon User],
t.[File Name],
t.[File Path],
t.[FileVersion],
t.[Policy Name]
--t.[Start Date]
order by
t.[Name]
--t.[Start Date] desc
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6371 Visits: 25280
sbrochu

To help others help you, please post your table definition, sample data, required result and the code you have run following the directions contained in the first link in my signature block. For example:

CREATE TABLE #T ([Name] VARCHAR(10), ExeCount INT, Domain VARCHAR(10),
[Last Logon User] VARCHAR(10), [Start Date] DATETIME )
INSERT INTO #T
SELECT 'C03702', 21,'HANOVER','SLE609','2010-09-14 09:09:03.000'



If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
sbrochu
sbrochu
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 9
Ron,

Can you read my original post, I am not having trouble when I include "startdate" in my select statement. It gives me the first dataset with duplicates. I want it to return the second dataset with out duplicates. Please see my sample dataset from my original post.
Stefan_G
Stefan_G
Old Hand
Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)

Group: General Forum Members
Points: 389 Visits: 955
I totally agree with bitbucket.

Please read the first link in his signature.

Your need to make your question much clearer.

At a minimum you need to post sample data in the form of INSERT statements, and you need to post the expected result based on your sample data.
sbrochu
sbrochu
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 9
The "start date" field data is like this in the database.

StartDate Internal Name FileName
2010-09-13 09:22:21.000 SignIn.exe signin.exe
2010-09-13 09:23:53.000 EXTRA extra.exe
2010-09-13 09:23:53.000 EXTRA extra.exe
2010-09-13 11:33:41.000 ConsoleOne consoleone.exe
2010-09-13 14:57:25.000 MSACCESS msaccess.exe
2010-09-13 17:59:36.000 CSA csacontrol.exe
2010-09-14 07:54:24.000 SignIn.exe signin.exe
2010-09-14 07:55:43.000 EXTRA extra.exe
2010-09-14 07:55:43.000 EXTRA extra.exe
2010-09-14 08:35:30.000 Microsoft PowerPoint* powerpnt.exe


I was and am able to extract the "StartDate" field. but, with latest version of my script, the dataset changes when I include "StartDate" field. It's back to all duplicate records is what I am saying.
sbrochu
sbrochu
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 9
I want my dataset to be

Name Exe-Count Domain Last Logon User Start Date
C03702 21 HANOVER SLE609 2010-09-14 09:09:03.000
C03712 20 HANOVER 2010-10-05 07:04:45.000

Instead of
Name Exe-Count Domain Last Logon User Start Date
C03702 21 HANOVER SLE609 2010-09-14 09:09:03.000
C03702 21 HANOVER SLE609 2010-09-13 10:35:24.000
C03702 21 HANOVER SLE609 2010-09-13 09:26:39.000
C03702 21 HANOVER SLE609 2010-09-09 10:15:38.000
C03712 20 HANOVER 2010-10-05 07:04:45.000
C03712 20 HANOVER 2010-10-01 06:58:42.000
C03712 20 HANOVER 2010-09-30 07:08:30.000
C03712 20 HANOVER 2010-09-29 06:59:11.000
Oliiii
Oliiii
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 777
Well if you include the start date in the group by, it'll output a row per different start date.
So it's working the way it's supposed to work ...

To resolve your problem, remove start date from the group by and do a max(startdate) in your select.
sbrochu
sbrochu
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 9
It works thank you.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search