SQL Server Count rows by monthend - year to date falling between two dates

I'm trying to create a query that can return counts of rows that fall between two dates as of the last day of the month prior and a count for the current month (MTD) at the time the query is run. For example let's say I need this report today. I can manually create a report using a query for each month of the year (past conditions, not future). January, February, March, April, etc. Where I use date1< End of Month (eom) and date2>eom.

However, I'd like to create a query that automatically gives me the counts of rows where date1 is before the eom and date2 is after the eom (or date2 is null). I thought maybe there was someway to use systemdate and dateadd to count backwards, but I don't know how to put this together to provide all of the last 12 months as of the last day of each month. The last day of each month is not a column stored directly in the tables I need to pull from.

There is a date dimension table, but I'm not sure how to include that with the tables with dates I'm pulling from, if that is necessary. The less than and greater than dates indicate the "as of date" that I need to pull the counts for over a period of several months or year to date.

Select count(column1) as "September Count"
From table a
 Left Outer Join a table b on a.pk=b.pk
Where a.date1 <= '2023-09-30 00:00:00'
 AND b.date2 > '2023-09-30 00:00:00'
        OR date2 is null)

UNION ALL

Select count(a.column1) as "August Count"
,b.otherstuff as "Important"
,a.OtherStuff as "Also Important"
From table a
Left Outer Join table2 b on a.pk=b.pk
Where a.date1 <= '2023-08-31 00:00:00'
 AND (b.date2 > '2023-08-31 00:00:00'
        OR b.date2 is null)

etc.....

Where the results should be something like this:

Please let me know if I need to provide more details. The database is proprietary, but I could post more fake information for explanations if necessary. You're help is greatly appreciated!

I know I can create a query for each month and union them together, but I would like a better way to do this that can be more automated and less manual.

Where the results should be something like this:
| August Count | September Count | Important | Also Important |
| ------------ | ------------ | ------------ | ------------ |
| 46890 | 39640 | Stuff | Other Stuff |

Count	Month	Important	Also Important
46890	August	Stuff	Other Stuff
39640	September	Stuff 2	2 Other Stuff

Please let me know if I need to provide more details. The database is proprietary, but I could post more fake information for explanations if necessary. You're help is greatly appreciated!

Sample Data
| Date | EventDate2 | Number | Rep, | Event Descr | Add by |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| 2023-07-26 | 2023-08-05 | 22E16587 | "Doe, Jane" | Event Type | Pill'ar |
| 2023-01-17 | 2023-09-12 | 23E13561 | "Rushing Jr" | Event Type | Pill'ar |
| 2023-01-14 | 2023-09-29 | 23E16154 | "Smith,Mike" | Event Type2 | Server |
| 2023-02-09 | 2023-08-16 | 23E21553 | "Doe, Ryan " | Event Type | Pill'ar |
| 2022-02-16 | 2023-09-27 | 23E22373 | NULL, | Event Type | Pill'ar |
| 2023-02-25 | 2023-10-02 | 23E23905 | "Smith,Mike" | Event Type | Pill'ar |
| 2023-03-11 | 2023-10-02 | 23E24451 | "Smith,Mike" | Event Type | Server |
| 2023-04-11 | 2023-10-02 | 23E24476 | "Smith,Mike", | Event Type | Server |
| 2023-05-12 | 2023-10-02 | 23E24778 | "Smith,Mike", | Event Type | Server |
| 2023-06-12 | 2023-09-22 | 23E24830 | NULL, | Event Type | Server |

Here's something that more closely resembles the existing query but only with one month at a time:

Select 
a.StatusDescr as "Status"
,Count(distinct a.CaseNbr) as "September Count"
From CRM a
 Inner JOIN Event b on a.AcctID=b.AcctID
 Left Outer Join Meeting c on a.AcctID=c.AcctID
 Left Outer Join Party e on a.AcctID=e.AcctID
 Left Outer Join Service g on a.AcctID=g.AcctID
 LEFT OUTER JOIN Closure h on a.AcctID=h.AcctID
 Inner JOIN User f on b.Event_User=f.Name
  Where  
    b.eventdate<= '2023-09-30 00:00:00'
   AND h.closedate > '2023-09-30 00:00:00'
    (a.AcctType='SMMS'
    or a.AcctType='SGHOV'
    or a.AcctType='SMXD')
   AND (a.LocID='219' 
    or a.LocID='200' 
    or a.LocID='260')
   AND (b.EventTYPE='1252'       
    OR b.EventTYPE='1225'
    OR b.EventCd='SMRESP')
   AND b.DeletedFlag='No'
   AND h.CurrentFlag='yes'
   AND a.statusdescr='open'
 GROUP BY a.statusdescr asc;

I'd just identify the time period you're interested in, and group your data over the date, when filtering by those dates.

What follows is complete baloney data spread out over 180 days before and after the current date:

drop table if exists #tab
create table #tab
(
    SK int identity(1,1),
    Dt date,
    Value int
)

insert into #Tab
(
    Dt,
    Value
)
select top 100000
    dateadd(day, checksum(newid()) % 180, getdate()),
    abs(checksum(newid()))
from sys.all_objects a, sys.all_objects b

Next, you want to find all the rows that are in the current month and the preceding month. You can do this a number of ways; you could do it with variables (as I've done) or with inline statements. You could check the year and month values of your date column, but that gets a little tricky around end of years since you have to backdate your year if you're running this in Januaray. So I think the simplest approach is what I've shown here, and you can just get all rows between the start of last month, and the end of this month.

declare 
    @Now date = getdate(),
    @StartOfPreviousMonth date,
    @EndOfPreviousMonth date,    
    @StartOfCurrentMonth date,
    @EndOfCurrentMonth date

select
    @EndOfCurrentMonth = eomonth(getdate()),
    @StartOfCurrentMonth = datefromparts(year(@Now), month(@Now), 1),
    @StartOfPreviousMonth = dateadd(month, -1, @StartOfCurrentMonth),
    @EndOfPreviousMonth = eomonth(@StartOfPreviousMonth)

select
    StartOfCurrentMonth = @StartOfCurrentMonth,
    EndOfCurrentMonth = @EndOfCurrentMonth,
    StartOfPreviousMonth = @StartOfPreviousMonth,
    EndOfPreviousMonth = @EndOfPreviousMonth

select
    _Year = year(dt),
    _Month = month(dt),
    Ct = count(1)
from #tab
where Dt between @StartOfPreviousMonth and  @EndOfCurrentMonth
group by year(Dt), month(dt)

To your final point about formatting, as a rule, I'm not a fan of pivoting data like this onto a single row. If the column name has the month in it, you can't easily re-run the same query, and have an app know what field to look for (i.e, does your app have to look for ALL possible month names for the columns?). I'd suggest just leaving it as two separate rows. But if that's not possible, I'd suggest maybe naming the fields "CurrentMonthTotal" and "PreviousMonthTotal", because at least that way the columns are predictably named.

So if you want to munge them onto the same row, maybe try something like this:

select
    PreviousMonthCt = max(case when _Month = month(@StartOfPreviousMonth) then Ct else null end),
    CurrentMonthCt =  max(case when _Month = month(@StartOfCurrentMonth) then Ct else null end)
from
(
    select
        _Year = year(dt),
        _Month = month(dt),
        Ct = count(1)
    from #tab
    where Dt between @StartOfPreviousMonth and  @EndOfCurrentMonth
    group by year(Dt), month(dt)
) a

SQL Server Count rows by monthend - year to date falling between two dates

1条答案

相关问题

热门标签

最新问答