Linq/SQL 帮助比较上个月/下个月的唯一 ID 以计算连续数

Question

编辑 #2 * 随着高管们希望从这份报告中获得更多信息，这个问题已经演变。基本上我们有每月订阅。我们希望按月查看谁加入（通过查看第一笔付款的时间）然后谁没有付款（退出）但在前一个月付款。它已被修改为现在还显示某人是否已“重新激活”（退出然后回来）和“停用”。我通过编写一个相当复杂的程序解决了这个问题，但我有一种感觉，聪明的人可以告诉我一种更简单的方法（因为这非常慢）。

我需要在不久的将来按“标签”表中的字段过滤原始结果，该表具有“标签”列表、它们关联的 contactid 以及它们应用的日期。基本上，我需要通过联系人将联系人表连接到标签表中的日期，然后像下面的代码一样查询付款表。这样我就可以在整个程序中看到过去一个月的历史记录中被标记的每个人。

感谢您的帮助！

Tables
[Contacts]         [Added Tags]
*Contact Id         Contact Id
                    Tag   
[Payments]          Date Added
*Invoice Id
Contact Id
Date
Pay Type
Pay Amt

和代码

public static int CalculateMonthDifference(DateTime startDate, DateTime endDate)
{
int monthsApart = 12 * (startDate.Year - endDate.Year) + startDate.Month - endDate.Month;
return Math.Abs(monthsApart);
}

public static int WriteLinez(int contactId, int monthx, int yearx, int concmos, int concmosreact, bool firstmonth, bool lastmonth, bool reactivated, bool deactivated, StreamWriter sw)
{
Console.WriteLine(contactId + "," + monthx + "/1/" + yearx + "," + concmos +"," + concmosreact  + "," + firstmonth + "," + lastmonth+ "," + reactivated + "," + deactivated);
sw.WriteLine(contactId + "," + monthx + "/1/" + yearx + "," + concmos +"," + concmosreact  + "," + firstmonth + "," + lastmonth+ "," + reactivated + "," + deactivated);
return 1;
}
void Main()
{
        StreamWriter sw = new StreamWriter(@"c:\Users\eboney\Desktop\TestFile.txt");
        //var csvoutput = new wr
        sw.WriteLine("contactId,month,concmos,concmosreact,firstmonth,lastmonth,reactivated,deactivated");
        //AddedTags.Select (atc => atc.ContactId=p.ContactId);
        var query2 =
        from p in Payments//, atc in AddedTags
        where (p.Products == "c30" || p.Products == "c3") && p.PayType != "Credit" && p.PayType != "Adjustment"  
            && p.PayAmt > 0 
        group p by p.ContactId into contactGroup
        // p."Contact Id" ascending
        select new
        {
            Contact = contactGroup.Key,
            Dates = 
                from yg in contactGroup
                group yg.Date by yg.Date.Value into dateGroup
                select new
                {
                    Datex = dateGroup.Key
                }
        };



    //arrayquery.Dump();
query2.OrderByDescending (q => q.Contact);



foreach(var contact in query2)
{
    //declarations and reset variables
    int contactId = contact.Contact;
    int x = contact.Dates.Count();
    int prevmonthDiff = 0;
    int nextmonthDiff = 0;
    int concurrentmos = 1;
    int concurrentmosreact = 0;
    bool firstmo = true;
    bool lastmo = false;
    bool reactflag = false;
    reactflag = false;
    bool reactivated = false;
    bool deactivated = false;
    DateTime prevdate = new DateTime();
    DateTime currentdate = new DateTime();
    DateTime nextdate = new DateTime();
    int monthat = contact.Dates.ElementAt(0).Datex.Month;
    int yearat = contact.Dates.ElementAt(0).Datex.Year;
    //loop through and process dates
    for ( int i = 0; i < x; i++ )
    {
        firstmo = false;
        lastmo = false;
        reactivated = false;
        deactivated = false;

        currentdate = contact.Dates.ElementAt(i).Datex;
        ///// DUPE CHECK ////
        if (prevdate.Year == currentdate.Year && prevdate.Month == currentdate.Month) { continue; }

        //////// if its the ONLY date /////
        if (x == 1) { 
            WriteLinez(contactId, monthat, yearat, 1, 0, firstmo, lastmo, reactivated, deactivated,sw); 
            continue;
            }

        ////  = LAST date to process
        if ( i + 1 == x ) 
        { 
            lastmo = true; if (reactflag) { deactivated=true; } 
        }///// IF THERE IS Next MONTH
        else if ( i + 1 < x ) 
        {   
            //set the next date
            nextdate = contact.Dates.ElementAt( i + 1 ).Datex; 
            //calc diff to next month
            nextmonthDiff =  CalculateMonthDifference(  currentdate, nextdate );  
                    //Console.WriteLine(currentdate + "," + nextdate +"--- >" + nextmonthDiff);

            /*   IF Break in Next Month   */
            if ( nextmonthDiff > 1 ) { 
                lastmo = true; // set it as last month
                deactivated = true; 
                //reactflag = true; //set the flag for break in months
                } 
            /*  IF there is NO BREAK in next month */   
            if ( nextmonthDiff < 2 ) { 
                lastmo = false; 
                deactivated=false;
                }
        }
        if (i == 0) { WriteLinez(contactId, currentdate.Month, currentdate.Year, 1, 0, true, lastmo, false, false,sw); prevdate = currentdate; continue; }
        /////////// PREVIOUS MONTH procs/calcs ///////////////////
        prevmonthDiff = CalculateMonthDifference(prevdate,currentdate);
                //Console.WriteLine(currentdate + "," + prevdate +"--- >" + prevmonthDiff);
        /* IF CONCURRENT MONTHS */
        if ( prevmonthDiff == 1 ) { 
            firstmo = false; //make sure that the first month flag is reset
            if (reactflag) { //if a reactivation has not occurred
                concurrentmosreact += 1; }

                else {
                concurrentmos += 1; } //if reactivation has 
            if (reactivated == true ) { reactivated = false; }
            } 

        if ( prevmonthDiff != 1 )//if its been more than a month since last payment
            { 
            //firstmo = true; //reset the first month
            reactivated = true; //set reactivated
            reactflag = true;
            concurrentmosreact = 1;
            concurrentmos = 0;
            }
        //Console.Write(" Prevmonthdiff = "+prevmonthDiff+", Nextmonthdiff "+nextmonthDiff+"      ");   
        //Console.Write(prevmonthDiff + ", " + nextmonthDiff + ", ");
        WriteLinez(contactId, currentdate.Month, currentdate.Year, concurrentmos, concurrentmosreact, firstmo, lastmo, reactivated, deactivated,sw);

        //if ( nextmonthDiff > 1 ) { reactflag = true; }//set the flag for break in months
        prevdate = currentdate;

    }
}

sw.Close(); 

}

// Define other methods and classes here