Date field in Select & Group by

  • Originally my dataset used to look like this:

    NameExe-Count DomainLast Logon UserStart Date

    C0370221HANOVERSLE6092010-09-14 09:09:03.000

    C0370221HANOVERSLE6092010-09-13 10:35:24.000

    C0370221HANOVERSLE6092010-09-13 09:26:39.000

    C0370221HANOVERSLE6092010-09-09 10:15:38.000

    C0371220HANOVER2010-10-05 07:04:45.000

    C0371220HANOVER2010-10-01 06:58:42.000

    C0371220HANOVER2010-09-30 07:08:30.000

    C0371220HANOVER2010-09-29 06:59:11.000

    Now I have it like this:

    NameExe-Count DomainLast Logon User

    C0370221HANOVERSLE609

    C0371220HANOVER

    C037211HANOVERJLK487

    C0372223HANOVERSLL

    C0372721HANOVERTDD488

    C0372821HANOVERLXB888

    C037331HANOVERCAA326

    C0374231HANOVER

    C0375313HANOVERMAP129

    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

  • 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[/url]
    Before posting a performance problem please read[/url]

  • 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.

  • 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.

  • The "start date" field data is like this in the database.

    StartDate Internal Name FileName

    2010-09-13 09:22:21.000SignIn.exesignin.exe

    2010-09-13 09:23:53.000EXTRAextra.exe

    2010-09-13 09:23:53.000EXTRAextra.exe

    2010-09-13 11:33:41.000ConsoleOneconsoleone.exe

    2010-09-13 14:57:25.000MSACCESSmsaccess.exe

    2010-09-13 17:59:36.000CSAcsacontrol.exe

    2010-09-14 07:54:24.000SignIn.exesignin.exe

    2010-09-14 07:55:43.000EXTRAextra.exe

    2010-09-14 07:55:43.000EXTRAextra.exe

    2010-09-14 08:35:30.000Microsoft 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.

  • 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

  • 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.

  • It works thank you.

Viewing 8 posts - 1 through 7 (of 7 total)

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