J'ai une table d'user où datatables ont environ 20 ans. Je veux aller chercher datatables du CreatedOn
colonne CreatedOn
qui est (2014-07-08 17:44:00) et si datatables ne sont pas disponibles pour un mois, alors je veux 0 pour ce mois
Exemple
Month data year jan 34 2014 feb 56 2014 march 0 2014 apr 23 2014
J'utilise la requête suivante mais elle ne fonctionne pas
with cte(monno , monname ) as( select 1, 'Jan' union all select 2, 'Feb' union all select 3, 'Mar' union all select 4, 'Apr' union all select 5, 'May' union all select 6, 'Jun' union all select 7, 'Jul' union all select 8, 'Aug' union all select 9, 'Sep' union all select 10, 'Oct' union all select 11, 'Nov' union all select 12, 'Dec' ) select distinct monname,monno, count(CreatedOn) over(partition by datepart(year,CreatedOn),DATEPART(MONTH,CreatedOn)) from Consortingbutors_tbl a right join cte b on DATEPART(MONTH,CreatedOn) = b.monno and datepart(year,CreatedOn) = 2014 order by monno
S'il vous plaît, aidez-moi à sortir de ce problème.
Essaye ça
with cte(monno , monname ) as( select 1, 'Jan' union all select 2, 'Feb' union all select 3, 'Mar' union all select 4, 'Apr' union all select 5, 'May' union all select 6, 'Jun' union all select 7, 'Jul' union all select 8, 'Aug' union all select 9, 'Sep' union all select 10, 'Oct' union all select 11, 'Nov' union all select 12, 'Dec' ) select monname, monno, coalesce(cnt,0) as cnt from cte c outer apply ( select count(*) as cnt from Consortingbutors_tbl where CreatedOn >= DATEADD(mm,c.monno - 1,'20140101') And CreatedOn < DATEADD(mm,c.monno,'20140101') )t order by monno
Essayez cette façon de find le numéro du mois et le nom du mois
CREATE TABLE #TempMonth ( MonthNum int, MonthNames varchar(50) ) INSERT INTO #TempMonth SELECT STR(MONTH(DATEADD(mm, number, GETDATE())), 2) AS MonthNum, DATENAME(month, DATEADD(month, MONTH(DATEADD(mm, number, GETDATE())), 0) - 1) AS MonthNames FROM master.dbo.spt_values WHERE (name IS NULL) AND (number BETWEEN 0 AND 11) ORDER BY STR(MONTH(DATEADD(mm, number, GETDATE())), 2) SELECT * FROM #TempMonth select distinct MonthNames,MonthNum, count(CreatedOn) over(partition by datepart(year,CreatedOn),DATEPART(MONTH,CreatedOn)) from Consortingbutors_tbl a right join #TempMonth b on DATEPART(MONTH,CreatedOn) = b.MonthNum and datepart(year,CreatedOn) = 2014 order by MonthNum DROP Table #TempMonth