November 30, 2011 at 12:21 am
Hi All,
I've a query related to dynamic date and year :
select bug_id,
category,
count(*) Total_bugs,
SUM(CASE when bug_date >= '10/1/2011' and bug_date <= '10/31/2011' Then 1 else 0 end) OCT_11,
SUM(CASE when bug_date >= '9/1/2011' and bug_date<= '9/30/2011' Then 1 else 0 end) SEP_11,
FROM AA_BUG_TBL
GROUP BY BUG_ID,BUG_CATEGORY
In the above query,Can we write one sum statement to get sum of bugs for a particular month and particular year dynamically?
for ex:2 bugs between 10/1/2011 and 10/31/2011 then 2 under oct_11
5 bugs between 09/01/11 and 09/30/2011 then 5 under sep_11
In this case we need to calculate correct no of days for feb
Thanks,
Mahender.
November 30, 2011 at 1:18 am
rameshk_adusumilli (11/30/2011)
Hi All,I've a query related to dynamic date and year :
select bug_id,
category,
count(*) Total_bugs,
SUM(CASE when bug_date >= '10/1/2011' and bug_date <= '10/31/2011' Then 1 else 0 end) OCT_11,
SUM(CASE when bug_date >= '9/1/2011' and bug_date<= '9/30/2011' Then 1 else 0 end) SEP_11,
FROM AA_BUG_TBL
GROUP BY BUG_ID,BUG_CATEGORY
In the above query,Can we write one sum statement to get sum of bugs for a particular month and particular year dynamically?
for ex:2 bugs between 10/1/2011 and 10/31/2011 then 2 under oct_11
5 bugs between 09/01/11 and 09/30/2011 then 5 under sep_11
In this case we need to calculate correct no of days for feb
Thanks,
Mahender.
here's a start:
sum(case when month(bug_date) = 9 and year(bug_date) = 2011 then 1 else 0 end ) sep_11,
I'm not clear on exactly what you mean by "dynamically." Can you explain?
thanks
November 30, 2011 at 2:27 am
This is a bit of a wild stab in the dark, so probably not quite what you're looking for.
BEGIN TRAN
--Standard TestEnvironment of 1,000,000 rows of random-ish data
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS bug_id,
CHAR((ABS(CHECKSUM(NEWID())) % 3) + 65) + CHAR((ABS(CHECKSUM(NEWID())) % 3) + 70) AS category,
DATEADD(DAY,((ABS(CHECKSUM(NEWID())) % 333) + 1),CAST('2011' AS DATETIME)) AS bug_date
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
DECLARE @SQL VARCHAR(MAX)
PRINT '========== COUNT OVER =========='
SET STATISTICS TIME ON
SELECT @SQL = ISNULL(@SQL,'') + '''' + category + ''', ' +
CONVERT(VARCHAR(6),perMonth) + ' AS ' + theMonth + ', '
FROM (SELECT DISTINCT category,
COUNT(*) OVER (PARTITION BY DATEADD(MONTH, DATEDIFF(MONTH, 0, bug_date), 0), category) AS perMonth,
UPPER(SUBSTRING(DATENAME(MONTH,DATEADD(MONTH, DATEDIFF(MONTH, 0, bug_date), 0)),1,3))+'_'+
SUBSTRING(CONVERT(VARCHAR(4),DATEPART(YEAR,DATEADD(MONTH, DATEDIFF(MONTH, 0, bug_date), 0))),3,2) AS theMonth
FROM #testEnvironment) a
ORDER BY theMonth
EXEC('SELECT ' + @SQL + 'COUNT(*) AS totalBugs FROM #testEnvironment')
SET STATISTICS TIME OFF
PRINT REPLICATE('=',80)
ROLLBACK
If this is not what you were after, please read this article[/url] about the best way to provide us with readily consumable sample data and DDL scripts which will in turn allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.
November 30, 2011 at 2:43 am
You will have to use a Dynamic Cross-Tab to solve your issue
Go through the article below for more information
http://www.sqlservercentral.com/articles/Crosstab/65048/
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply