End of Month

[sunny27]

Hi, I am wondering if anyone here can help!

We have an Student table that has StudentGUID,StudentNumber,EffectiveDate and StudentStatus 

I need to somehow work out how many Students were Pending at the end of each month and if the Student is Pending and Approved in the same month then it should be considered as Approved at the end month. A Student Status is changed when he joins the school something like Approved,Closed,Pending,Revoked,Regular.There is EffectiveDate which is latest date for all the status. 

So for an example, if a Student status is pending on May 2nd, 2017 and Approved on May20th, 2017 - this would not be included. If a Student status is pending on April 2nd 2017, and Approved on May 5th 2017, this would be included for April as it went over the end of the month mark.

If a Student status is pending on April 2nd 2017, and Approved on 8th Jul 2017 it would be included for April, May and June.

Thanks for any help

[v-piga-msft]

Hi @sunny27,

It seems that you want work out how many Students were Pending at the end of each month and if the Student is Pending and Approved in the same month then it should be considered as Approved at the end month.

If it is convenient, could you share some data sample and your desired output so that we could help further on it?

Best Regards,

Cherry

[sunny27]

Thanks for Responding, Below is the sample data.

StudentGUID	StudentNumber	StudentStatusID	Status	EffectiveDate
C0912B29-47FE-4D76-9E7B-2524C2E3F62D	22727	10	Declined	4/18/2013
E5D680EE-FD21-4130-9558-252F707D3125	30542	4	Pending	1/21/2015
65E959E9-9BBF-45A4-9878-25300937F568	29301	4	Approved	12/1/2016
3D9923CD-9A08-44FA-BB6E-2531B9EACCB5	28505	3	Revoked	11/8/2012
D7294F81-14AB-418F-B8DD-25359F4808A7	28251	4	Approved	6/14/2013
5EF2B303-2909-4A10-8A78-253698631037	26918	3	Revoked	2/9/2010
ABA2E14D-D0C9-47CB-B588-2536D75C6945	30736	1	NPR	4/26/2018
1071A47A-1B74-4251-9F3D-2538DEB69C7B	28251	2	Pending	5/22/2012
5F4E599E-AD15-404F-A718-254A21972938	29301	2	Pending	6/24/2014
D510C3B3-50B0-4E1D-B3DD-2558D6E9C3E4	27740	3	Revoked	5/30/2011
4FFA9BBA-7305-4DAF-BC57-25599A3DE0A4	27336	3	Revoked	9/14/2010
B4287B50-758A-49C5-949E-255B50B15067	30542	2	Approved	8/1/2017
138697DC-C3AF-41C0-9A14-255C115787C7	28426	10	Declined	1/15/2013
DE09F26A-936F-44B4-A574-256547858D6E	28505	1	NPR	4/22/2013
C75BEDF8-9E17-4D73-9F1E-256F3EEAE89E	23333	4	Approved	5/22/2006