SQL Server Performance

Data extraction day wise

Discussion in 'SQL Server 2005 General Developer Questions' started by rajneesh, Feb 27, 2007.

  1. rajneesh New Member

    Hi,

    I need to extract the date in day wise format like

    Year 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 .............. 31

    2005 X X X X X X X X X XX XX XX XX XX XX XX............... XX
    2006 X X X X X X X X X XX XX XX XX XX XX XX............... XX

    Does anybody have idea how to do that?

    Regards
    Rajneesh
    rajneesh, Feb 27, 2007
    #2

  2. ranjitjain New Member

    check datepart(yy,date) and datepart(dd,date) functions in BOL.
    You can use them to group by in select query and output the result.
    Still I think you need to do some formatting at front end
    else can also look at pivot queries/Cross-Tab queries.
    ranjitjain, Feb 27, 2007
    #2

  3. dineshasanka Moderator

    use pivot queries.

    if you can post your tabel stucture, we can give your the query

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
    dineshasanka, Feb 27, 2007
    #2

  4. rajneesh New Member

    Hi,

    Below is the table structure

    Companyname
    Co_create_date
    Address
    City
    State
    Companyid


    I need the count of distinct company name in day wise report

    Regards
    Rajneesh
    rajneesh, Feb 27, 2007
    #2

  5. dineshasanka Moderator


    SELECT *
    FROM
    (SELECT CAST(MONTH(Co_create_date) AS VARCHAR(4)) AS SaleMonth,
    CAST(year(Co_create_date) AS VARCHAR(4)) AS SaleYear

    FROM Person.Address) p
    PIVOT
    (
    COUNT (SaleMonth)
    FOR SaleMonth IN
    ( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12] )
    ) AS pvt



    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
    dineshasanka, Feb 27, 2007
    #2

  6. dineshasanka Moderator

    couldn't tell you,
    replcace table name at Person.Address

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
    dineshasanka, Feb 27, 2007
    #2

  7. Adriaan New Member

    SELECT COUNT(DISTINCT Companyname), Co_create_date
    FROM tbl
    GROUP BY Co_create_date
    Adriaan, Feb 27, 2007
    #2

  8. dineshasanka Moderator

    I have done it for month, you can cnage it for date by replcaying MONTH to DAY

    USE AdventureWorks
    GO
    SELECT *

    FROM ( SELECT CAST(DAY(ModifiedDate) AS VARCHAR(4)) AS SaleDay,
    CAST(year(ModifiedDate) AS VARCHAR(4)) AS SaleYear
    FROM Person.Address ) p PIVOT ( COUNT(SaleDay) FOR SaleDay IN ( [1],[2],[3],[4],[5],[6],[7],[8],[9],
    [10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],
    [22],[23],[24],[25],[26],[27],[28],[29],[30],[31] ) ) AS pvt



    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
    dineshasanka, Feb 27, 2007
    #2

Share This Page