Results 1 to 7 of 7

Thread: tricky query

  1. #1
    Join Date
    Mar 2004
    Posts
    162

    Unanswered: tricky query

    Hi,

    I need to return a number of records at specifik days, i do it with this query;

    SELECT LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10) AS Days, COUNT(LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10))
    AS Numbers_total, COUNT(LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10)) AS Numbers_In
    FROM tb_SecurityLog
    WHERE (CONVERT(varchar, CLF_LogGenerationTime, 120) BETWEEN @fyear + @fmonth + @fday AND @tyear + @tmonth + @tday) AND
    (SL_PolicyName LIKE N'%')
    GROUP BY LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10)
    ORDER BY LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10)

    i also need to have a criteria at that second COUNT and if the criteria is not met that row should not be counted, is this possible at all?

    //Mr
    Last edited by mrpcguy; 11-16-05 at 05:01.

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Re

    could U paste the DDL,some sample data and ur criteria,pls
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Mar 2004
    Posts
    162
    the output looks like this
    date,rows_total
    2005-11-01,100
    2005-11-02,200
    2005-11-03,300
    2005-11-04,200
    2005-11-05,250

    and what i wont is
    date,rows_total,rows_out
    2005-11-01,100,50
    2005-11-02,200,10
    2005-11-03,300,0
    2005-11-04,200,15
    2005-11-05,250,0
    and that "rows_out" colum should be based at that second COUNT with criteria "where SL_PolicyName LIKE N'_out%'"
    the definition of coulms is datetime (8) and nvarchar(254)

    //Mr
    Last edited by mrpcguy; 11-16-05 at 06:05.

  4. #4
    Join Date
    Jun 2003
    Posts
    269

    Re

    ------try that
    SELECT days,count(Numbers_total),count(Numbers_In)
    from (
    SELECT LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10) AS days,
    COUNT(LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10))
    AS Numbers_total,
    --COUNT(LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10))

    (select COUNT(LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10)) from #t t
    where t.SL_PolicyName=t1.SL_PolicyName and t.SL_PolicyName like N'%_out%'
    GROUP BY LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10),SL_PolicyName ) as Numbers_In
    FROM #t t1
    WHERE
    --(CONVERT(varchar, CLF_LogGenerationTime, 120) BETWEEN '20051115' AND '20051117') AND
    (SL_PolicyName LIKE N'%')
    GROUP BY LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10),SL_PolicyName
    )
    as tm GROUP BY days
    ORDER BY days
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  5. #5
    Join Date
    Mar 2004
    Posts
    162
    Hi and thx alot for your time... I can't make this to work, it complains at the temp table #t (invalid object)...

    //Mr

  6. #6
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up Re

    Sorry I forgot to put , DDL of #t,here we go,

    drop table #t
    go
    create table #t
    (
    SL_PolicyName varchar(200),
    CLF_LogGenerationTime datetime
    )
    go

    -----insert some sample data

    insert into #t
    select 'Policey_in1',getdate()
    union
    select 'Policey_in2',getdate()
    union
    select 'Policey_in3',getdate()
    union
    select 'Policey_out',getdate()
    union
    select 'Policey_out1',getdate()+1
    union
    select 'Policey_out2',getdate()
    go


    ---select query


    SELECT days,count(Numbers_total),count(Numbers_In)
    from (
    SELECT LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10) AS days,
    COUNT(LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10))
    AS Numbers_total,
    (select COUNT(LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10)) from #t t
    where t.SL_PolicyName=t1.SL_PolicyName and t.SL_PolicyName like N'%_out%'
    GROUP BY LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10),SL_PolicyName ) as Numbers_In
    FROM #t t1
    WHERE
    (CONVERT(varchar, CLF_LogGenerationTime, 120) BETWEEN '2005-11-15' AND '2005-11-18') AND
    (SL_PolicyName LIKE N'%')
    GROUP BY LEFT(CONVERT(varchar, CLF_LogGenerationTime, 120), 10),SL_PolicyName
    )
    as tm GROUP BY days
    ORDER BY days
    Last edited by mallier; 11-16-05 at 09:49.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  7. #7
    Join Date
    Mar 2004
    Posts
    162
    now i manage to make it work, thx alot...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •