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
  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.
  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/
  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
  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/
  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/
  7. Adriaan New Member

    SELECT COUNT(DISTINCT Companyname), Co_create_date
    FROM tbl
    GROUP BY Co_create_date
  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/

Share This Page