Click here to Skip to main content
15,791,675 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
can you help me with a sql statements to find Appid count order by month wise and year wise but don't count existing Month & year appid
column name     TYPE   

ID                 int 
AppID           int 
Month           int 
Year            int 

ID Appid Month Year

207 396 Jan 2013
250 396 Feb 2013
283 396 Mar 2013
319 396 Apr 2013
365 396 May 2013
221 451 Feb 2013
262 451 Mar 2013
293 451 Apr 2013
329 451 May 2013
384 451 June 2013
354 609 May 2013
394 702 June 2013
353 707 May 2013

I want the output as below,
Month      count
January     1
February    1
March       0
April       0
May         2
June        1
Updated 19-Jun-13 23:14pm
joshrduncan2012 19-Jun-13 16:57pm    
What have you tried so far?
damodara naidu betha 20-Jun-13 6:06am    
Month is declare as int in the table .. but data of month is in varchar

Probably more complicated than it needs to be. Part of the reason is that Month and Year should be stored as a DATE or DATETIME Data Type for easier data manipulation.

Create the test data table
Create Table  tabledata (id int,appid int, Month int, Year Int)
insert into tabledata values(207,396,1,2013)
insert into tabledata values(250,396,2,2013)
insert into tabledata values(283,396,3,2013)
insert into tabledata values(319,396,4,2013)
insert into tabledata values(365,396,5,2013)
insert into tabledata values(221,451,2,2013)
insert into tabledata values(262,451,3,2013)
insert into tabledata values(293,451,4,2013)
insert into tabledata values(329,451,5,2013)
insert into tabledata values(384,451,6,2013)
insert into tabledata values(354,609,5,2013)
insert into tabledata values(394,702,6,2013)
insert into tabledata values(353,707,5,2013)

Execute the SELECT statement
Note: The Where clause with the DATEADD function is designed to handle data from multiple years not just 2013.
select year,month,count(distinct((year*100000)+(month*1000)+appid)) As [Count of AppIds] 
from tabledata t1 
where (select count(*) from tabledata t2 where
MONTH(DATEADD(m,-1,CAST(LTRIM(STR(t1.YEAR))+'-'+LTRIM(STR(t1.MONTH))+'-01' as Date)))=t2.month And
YEAR(DATEADD(m,-1,CAST(LTRIM(STR(t1.YEAR))+'-'+LTRIM(STR(t1.MONTH))+'-01' as Date)))=t2.year and
group by year,month 
select year,month, 0 As [Count of AppIds] from tabledata t3 where
(select count(*) from tabledata t4 where
MONTH(DATEADD(m,-1,CAST(LTRIM(STR(t3.YEAR))+'-'+LTRIM(STR(t3.MONTH))+'-01' as Date)))=t4.month And
YEAR(DATEADD(m,-1,CAST(LTRIM(STR(t3.YEAR))+'-'+LTRIM(STR(t3.MONTH))+'-01' as Date)))=t4.year and
t4.appid=t3.appid)>0 and 
((t3.year*100)+t3.month) NOT IN 
(select distinct ((t5.year*100)+t5.month)
from tabledata t5 
where (select count(*) from tabledata t6 where
MONTH(DATEADD(m,-1,CAST(LTRIM(STR(t5.YEAR))+'-'+LTRIM(STR(t5.MONTH))+'-01' as Date)))=t6.month And
YEAR(DATEADD(m,-1,CAST(LTRIM(STR(t5.YEAR))+'-'+LTRIM(STR(t5.MONTH))+'-01' as Date)))=t6.year and
order by year,month 

year	month	Count of AppIds
2013	1	1
2013	2	1
2013	3	0
2013	4	0
2013	5	2
2013	6	1
Share this answer

try this...

--create table with this structure.
  Create Table  tabledata (id int,appid int, Month varchar(10), Year Int);

-- insert data into the table
   insert into tabledata values(207,396,'January',2013)
   insert into tabledata values(250,396,'February',2013)
   insert into tabledata values(283,396,'March',2013)
   insert into tabledata values(319,396,'April',2013)
   insert into tabledata values(365,396,'May',2013)
   insert into tabledata values(221,451,'February',2013)
   insert into tabledata values(262,451,'March',2013)
   insert into tabledata values(293,451,'April',2013)
   insert into tabledata values(329,451,'May',2013)
   insert into tabledata values(384,451,'June',2013)
   insert into tabledata values(354,609,'May',2013)
   insert into tabledata values(394,702,'June',2013)
   insert into tabledata values(353,707,'May',2013)

-- Execute following query to get desired result set
SELECT ROW_NUMBER() OVER(PARTITION BY appid,Year order by appid) RowNum,
appid,Month,Year FROM tableData
SELECT  month,COUNT(appid) Count FROM (SELECT TblCTE.appid,A.month,
MONTH(CAST(A.Month + '1 2010' AS datetime)) MonthOrder FROM TblCTE
ON TblCTE.RowNum < 2  AND TblCTE.Month = A.Month) AS B
 GROUP BY B.Month , B.MonthOrder ORDER BY B.MonthOrder

    You will get required result set like this by executing above query.
  Month       Count
  January  1
  February 1
  March    0
  April    0
  May          2
  June     1
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900