Date field in Select & Group by

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Date field in Select & Group by

Date field in Select & Group by

Rate Topic

Display Mode

Topic Options

Author

Message

sbrochu

Posted Monday, October 11, 2010 1:51 PM

Forum 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

bitbucket-25253

Posted Monday, October 11, 2010 2:23 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:16 PM
Points: 5,101, Visits: 20,201

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

sbrochu

Posted Tuesday, October 12, 2010 6:38 AM

Forum 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

Stefan_G

Posted Tuesday, October 12, 2010 6:47 AM

SSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 5:31 AM
Points: 275, Visits: 751

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

sbrochu

Posted Tuesday, October 12, 2010 6:54 AM

Forum 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

sbrochu

Posted Tuesday, October 12, 2010 6:57 AM

Forum 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

Oliiii

Posted Tuesday, October 12, 2010 7:05 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:38 PM
Points: 194, Visits: 685

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

sbrochu

Posted Tuesday, October 12, 2010 8:07 AM

Forum 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 »

Permissions