Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Date field in Select & Group by Expand / Collapse
Author
Message
Posted Monday, October 11, 2010 1:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 12, 2010 8:04 AM
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

Post #1002424
Posted Monday, October 11, 2010 2:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:34 AM
Points: 5,566, Visits: 24,728
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
Post #1002436
Posted Tuesday, October 12, 2010 6:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 12, 2010 8:04 AM
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.
Post #1002737
Posted Tuesday, October 12, 2010 6:47 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 8:40 AM
Points: 316, Visits: 908
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.
Post #1002752
Posted Tuesday, October 12, 2010 6:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 12, 2010 8:04 AM
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.
Post #1002759
Posted Tuesday, October 12, 2010 6:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 12, 2010 8:04 AM
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
Post #1002762
Posted Tuesday, October 12, 2010 7:05 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:20 AM
Points: 206, Visits: 763
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.
Post #1002770
Posted Tuesday, October 12, 2010 8:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 12, 2010 8:04 AM
Points: 7, Visits: 9
It works thank you.
Post #1002842
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse