SQL Server Performance Forum – Threads Archive
Data extraction day wise
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…………… XX2006 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
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.
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/
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
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/
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/
SELECT COUNT(DISTINCT Companyname), Co_create_date
FROM tbl
GROUP BY Co_create_date
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/
]]>