sql query get all data from Jan to current month even if there are no records

小编典典

sql query get all data from Jan to current month even if there are no records

sql

I’m not good with sql so any help world be great

I have a SQL query that gets records who has register from Jan to current
month

My code example

SELECT DatePart(YEAR, p.createStamp) as TheYear, DatePart(MONTH, p.createStamp) as TheMonth ,  COUNT(p.pId) AS TOTALCOUNT 
FROM profile p with(nolock)
where DatePart(YEAR, p.createStamp) = DATEPART(YEAR, GETDATE())
GROUP BY YEAR(p.createStamp), MONTH(p.createStamp)
ORDER BY YEAR(p.createStamp), MONTH(p.createStamp)

how ever the query will bring back

February = 2, March = 3, April = 4 and May = 5

i would like to have it bring back Jan = 1 with a total count of 0 and June =
6 with the total count of 0 as well any ideas how to do this?

thank you.

阅读 187

2021-04-15

共1个答案

小编典典

Here is a loop to create the month/year combinations and use this as the base
of your query:

declare @startDate as datetime
set @startDate = '1/1/13'

declare @currentDate as datetime
set @currentDate = '6/6/13'

select
     month(@currentDate) as monthOfDate
    ,year(@currentDate) as yearOfDate
into #allDates
where 1=0

while (@startDate <= @currentDate)
begin
    insert into #allDates values (month(@startDate),year(@startDate))
    set @startDate = dateadd(m,1,@startDate)
end

select 
     _monthYear.yearofDate
    ,_monthYear.monthOfDate
    , COUNT(p.pId) as total
from #allDates _monthYear
left join profile p with(nolock)
    on month(p.createStamp) = _monthYear.monthOfDate
    and year(p.createStamp) = _monthYear.yearOfDate
group by
     _monthYear.yearofDate
    ,_monthYear.montOfDate

drop table #allDates

2021-04-15