Need help in getting correct closed count of tickets in SQL Server 2014

Question

question

maddy2119 asked Dec 21, '20

Need help in getting correct closed count of tickets in SQL Server 2014

Hi Geeks,

I am using sql server 2014, and trying to get output with count of OPEN and CLOSED tickets from one table which contains 'CreatedDate' and 'Closeddate' columns.

I am using below query and ended up getting output as shown below.

SELECT CONVERT(VARCHAR(3), DATEFROMPARTS(2020, MONTH(CreatedDate), 1), 7) AS MONTH,
  count(*) as 'OPEN', count(*) as 'CLOSED' 
from dbo.SPSActivityClassBase where year(createddate)=2020 
group by MONTH(CreatedDate) order by max(createddate);

But actual Closed count is different. Please help me out.

Thanks in Advance

Maddy

script sql2014

1608547495203.png (7.9 KiB)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2020-12-21T11:44:04Z

Kev Riley answered Dec 21, '20

You need 2 queries, one that works out the OPEN data and one for the CLOSED, then full join them together.

Here's an example. You could also use a 'calendar' table too, to fill in any gaps if there were months without any opens or closes

declare @YourTable table (
    id int not null,
    CreatedDate date,
    ClosedDate date)

insert into @YourTable (id, CreatedDate, ClosedDate) select 1, '1 jan 2020','15 jan 2020'
insert into @YourTable (id, CreatedDate, ClosedDate) select 2, '1 jan 2020','15 feb 2020'
insert into @YourTable (id, CreatedDate, ClosedDate) select 3, '1 feb 2020','15 feb 2020'
insert into @YourTable (id, CreatedDate, ClosedDate) select 4, '1 feb 2020','15 apr 2020'
insert into @YourTable (id, CreatedDate, ClosedDate) select 5, '1 mar 2020','15 mar 2020'
insert into @YourTable (id, CreatedDate, ClosedDate) select 6, '1 apr 2020','15 jul 2020'

select * from @YourTable

select
    coalesce(Openings.Month, Closings.Month), 
    isnull(Openings.NumberOfTickets,0) as [OPEN], 
    isnull(Closings.NumberOfTickets,0) as [CLOSED]
from 
(
select
    month(CreatedDate) as MonthNum ,datename(month, CreatedDate) as [Month], count(*) as NumberOfTickets
from @YourTable group by month(CreatedDate), datename(month, CreatedDate)
) Openings
full join
(
select
    month(ClosedDate) as MonthNum,datename(month, ClosedDate) as [Month], count(*) as NumberOfTickets
from @YourTable group by month(ClosedDate), datename(month, ClosedDate)
) Closings on Closings.MonthNum = Openings.MonthNum
order by coalesce(Openings.MonthNum, Closings.MonthNum)

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

maddy2119 commented · Dec 21, 2020 at 01:34 PM

Thanks a lot Kev. it worked and I got desired output :)

0 ·

maddy2119 commented · Jan 08, 2021 at 10:37 AM

Hi Kev, Happy New Year. With the below query I can get Month format (Jan to Dec) for the year 2020 with open and closed count of tickets. Now I would like to get result for last 12 months irrespective of year. For example if I execute on March 2021, I need results from April 2020 to March 2021. Could you please help me out.

Thanks in Advance

Maddy

select 
    coalesce(Openings.Month, Closings.Month) as 'MONTH', 
    isnull(Openings.OPENED,0) as [OPEN], 
     isnull(Closings.CLOSED,0)as [CLOSED]
from 
(
SELECT month(createddate) as Monthname, CONVERT(VARCHAR(3), DATEFROMPARTS(2020, MONTH(CreatedDate), 1), 7) AS MONTH,
  count(*) as 'OPENED' from dbo.SPSActivityClassBase where year(createddate)=2020 group by MONTH(CreatedDate) 
  ) openings
  full join
  (
  SELECT month(closeddate) as Monthname, CONVERT(VARCHAR(3), DATEFROMPARTS(2020, MONTH(ClosedDate), 1), 7) AS MONTH,
  count(*) as 'CLOSED' from dbo.SPSActivityClassBase where year(closeddate)=2020 group by MONTH(ClosedDate)
  ) Closings on Closings.Month = Openings.Month
  order by  coalesce(Openings.MonthName, Closings.MonthName)

0 ·

Answer 2 · 2021-01-08T12:19:30Z

Kev Riley answered Jan 8, '21

Adding this as another answer as it wouldn't fit in the comments.

First lets create a calendar table that lists all the months that we are interested in.

Assuming we are running this today, 8 Jan 2021, I want 12 months data from 1 Feb 2020 to 31 Jan 2021

declare @Date datetime
set @date = getdate() -- start at today

--so if today is 8 Jan 2021, I want 12 months data from 1 Feb 2020 to 31 Jan 2021
declare @start12months date
set @start12months = dateadd(month, -11, dateadd(month, datediff(month, 0, @date),0) )

declare @end12months date
set @end12months = dateadd(day, -1, dateadd(month, datediff(month, 0, @date)+1,0))


select @start12months, @end12months;
---------- ----------
2020-02-01 2021-01-31

(1 row affected)

--now build a 12 row table of month 'slots'
declare @monthslots table (id int not null, firstofmonth date, MonthDesc varchar(50));
with cte_tally as 
(
select top 10000
    ROW_NUMBER() over(order by sc1.column_id) N
from
    sys.columns sc1,sys.columns sc2
)

insert into @monthslots (id, firstofmonth, MonthDesc)
select
    N, dateadd(month,N-1,@start12months), datename(month, dateadd(month,N-1,@start12months)) + ' ' + datename(year, dateadd(month,N-1,@start12months))
from cte_tally
where dateadd(month,N-1,@start12months) <= @end12months

select * from @monthslots

id          firstofmonth MonthDesc
----------- ------------ --------------------------------------------------
1           2020-02-01   February 2020
2           2020-03-01   March 2020
3           2020-04-01   April 2020
4           2020-05-01   May 2020
5           2020-06-01   June 2020
6           2020-07-01   July 2020
7           2020-08-01   August 2020
8           2020-09-01   September 2020
9           2020-10-01   October 2020
10          2020-11-01   November 2020
11          2020-12-01   December 2020
12          2021-01-01   January 2021

(12 rows affected)

Now back to my example query that I originally created. If we tweak this so that the [Month] output column is actually Month and Year, we can then join that onto the 12 month table that we've just created.

declare @YourTable table (
    id int not null,
    CreatedDate date,
    ClosedDate date)

insert into @YourTable (id, CreatedDate, ClosedDate) select 1, '10 dec 2019','15 jan 2020'
insert into @YourTable (id, CreatedDate, ClosedDate) select 2, '11 jan 2020','15 feb 2020'
insert into @YourTable (id, CreatedDate, ClosedDate) select 3, '4 feb 2020','15 feb 2020'
insert into @YourTable (id, CreatedDate, ClosedDate) select 4, '6 feb 2020','15 apr 2020'
insert into @YourTable (id, CreatedDate, ClosedDate) select 5, '5 mar 2020','15 mar 2020'
insert into @YourTable (id, CreatedDate, ClosedDate) select 6, '10 apr 2020','15 jul 2020'

--select * from @YourTable

select
    monthslots.MonthDesc,
    isnull(Openings.NumberOfTickets,0) as [OPEN], 
    isnull(Closings.NumberOfTickets,0) as [CLOSED]
from @monthslots monthslots
left join
    (
    select
        datename(month, CreatedDate) + ' ' + datename(year, CreatedDate) as [Month], count(*) as NumberOfTickets
    from @YourTable group by datename(month, CreatedDate) + ' ' + datename(year, CreatedDate) 
    ) Openings
    on monthslots.MonthDesc = Openings.Month
left join
    (
    select
        datename(month, ClosedDate) + ' ' + datename(year, ClosedDate) as [Month], count(*) as NumberOfTickets
    from @YourTable group by datename(month, ClosedDate) + ' ' + datename(year, ClosedDate)
    ) Closings 
    on monthslots.MonthDesc = Closings.Month
order by monthslots.id

And that gives the full 12 months data:

MonthDesc                                          OPEN        CLOSED
-------------------------------------------------- ----------- -----------
February 2020                                      2           2
March 2020                                         1           1
April 2020                                         1           1
May 2020                                           0           0
June 2020                                          0           0
July 2020                                          0           1
August 2020                                        0           0
September 2020                                     0           0
October 2020                                       0           0
November 2020                                      0           0
December 2020                                      0           0
January 2021                                       0           0

(12 rows affected)

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

maddy2119 commented · Jan 11, 2021 at 12:51 PM

Hi Kev, sorry for late response...It worked as expected and happy with the results :) Thank you so Much.

0 ·

question