نمایش نتایج 1 تا 8 از 8

نام تاپیک: محاسبه میزان مانده ماهانه و سالانه در یک کوئری

  1. #1
    کاربر دائمی
    تاریخ عضویت
    اردیبهشت 1389
    پست
    221
    تشکر کردن
    148
    7 بار تشکر شده در 7 پست

    محاسبه میزان مانده ماهانه و سالانه در یک کوئری

    سلام خدمت اساتید محترم

    من دو تا جدول دارم که با چند شرط خاص می خوام کوئری بگیرم و سرعت بازیابی و نمایش اطلاعات خیلی مهم است زیرا برنامه تحت شبکه است.
    دو جدول به نام TUser و TPardakht : فیلد UserId برای join دو جدول مشابه است.
    حالا می خوام لیست کاربران (نام و نام خانوادگی از جدول TUser) و جمع مبلغ پرداختی مثلا در ماه 8 از جدول TPardakht مربوط به هر کاربر و آخرین تاریخ پرداختی در ماه 8 هر کاربر در یک جدول نمایش داده شود که :
    1 - ممکن است در جدول TUser افرادی تعریف شده باشند که تا به حال هیچ پرداختی نداشته اند اما من می خواهم حداقل در جدول نام آنها را نشان دهد اما آخرین تاریخ پرداختی و مبلغ خالی باشد.
    2 - افرادی که در ماه 9 پرداخت داشته اند را کلا نشان ندهد.
    3 - جمع مبلغ پرداختی ماه 8 را می خواهم اما ستون بعدی فقط آخرین تاریخ پرداختی در ماه 8 را می خواهم. (بنابراین از group by نمیشه استفاده کرد)
    4 - جمع کل مبلغ پرداختی از ابتدا تا کنون


    تمامی اطلاعات می خواهم در یک دیتاتیبل ریخته شود.
    من با دیتاتیبل و گذاشتن حلقه های تو در تو توانستم خروجی بگیرم اما سرعت بسیار بسیار پایین است. اساتید چه پیشنهادی می دهید؟ حالا یا با استفاده از join و یا linq

    این کدها با حلقه های تو در تو:
    SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM TUser ORDER BY UserId ASC",                         conn = new SqlConnection(codes.ReturnConnectionstring()));
    DataTable dt = new DataTable();
    da.Fill(dt);
    for (int i = 0; i < dt.Rows.Count; i++)
    {
    SqlDataAdapter daC = new SqlDataAdapter("SELECT * FROM TPardakht " +
    "WHERE Month='9' AND UserId='" + dt.Rows[i]["UserId"].ToString() + "'",
    conn = new SqlConnection(codes.ReturnConnectionstring()));
    DataTable dtC = new DataTable();
    daC.Fill(dtC);


    //اگر کاربر پرداختی در ماه 9 نداشت اطلاعات ماه 8 را نشان بده
    if (dtC.Rows.Count == 0)
    {
    SqlDataAdapter daa = new SqlDataAdapter("SELECT * FROM TPardakht " +
    "WHERE Month='8' AND UserId='" + dt.Rows[i]["UserId"].ToString() + "'",
    conn = new SqlConnection(codes.ReturnConnectionstring()));
    DataTable dt2 = new DataTable();
    daa.Fill(dt2);


    string stLM = "", stLD = "", stLB = "";
    decimal dMande = 0;


    if (dt2.Rows.Count > 0)
    {
    decimal dM = 0;


    if (dt2.Rows.Count > 0)
    {
    dM = 0;
    for (int j = 0; j < dt2.Rows.Count; j++)
    {
    // جمع مبلغ پرداختی در ماه 8 و رشته فیلد توضیح هر کدام
    dM += decimal.Parse(dt2.Rows[j]["Bed"].ToString());
    stLB += dt2.Rows[j]["Babat"].ToString();


    }
    //آخرین تاریخ پرداختی در ماه 8
    stLD = dt2.Rows[0]["Date"].ToString();
    stLM = dM.ToString("N0");
    }
    //به دست آوردن جمع کل مبلغ پرداختی از اول تا کنون
    dMande = 0;
    SqlDataAdapter daM = new SqlDataAdapter("select isnull(sum(Bes),0)-isnull(sum(Bed),0) AS Mande from TPardakht " +
    "where YCode='" + dt.Rows[i]["Code"].ToString() + "'",
    conn = new SqlConnection(codes.ReturnConnectionstring()));
    DataTable dtM = new DataTable();
    daM.Fill(dtM);
    dMande = decimal.Parse(dtM.Rows[0][0].ToString());




    dtMandeList.Rows.Add("", dt.Rows[i]["UserId"].ToString(),
    dt.Rows[i]["Name"].ToString(),
    dMande, stLM, stLD, stLB, dt.Rows[i]["Code"].ToString());
    }
    }
    }

  2. #2
    مدیر بخش آواتار Mahmoud.Afrad
    تاریخ عضویت
    مرداد 1387
    پست
    3,209
    تشکر کردن
    58
    2,576 بار تشکر شده در 2,008 پست

    نقل قول: سریع ترین و بهترین روش بازگردانی کوئری با شرط های خواسته شده

    اسکریپت جداولی که دخیل هستند رو بزارید. و اینکه entity framework کار میکنید یا چیز دیگری؟

  3. کاربرانی که به خاطر مطلب مفید Mahmoud.Afrad از وی تشکر کرده‌اند:


  4. #3
    کاربر دائمی
    تاریخ عضویت
    اردیبهشت 1389
    پست
    221
    تشکر کردن
    148
    7 بار تشکر شده در 7 پست

    نقل قول: سریع ترین و بهترین روش بازگردانی کوئری با شرط های خواسته شده

    تعداد فیلد های جدول ها خیلی زیاد هستند برای اسکریپت گذاشتن.
    خیر آشنایی با روش
    entity framework ندارم. از linq to sql و یا همون datatable و dataadapter استفاده کردم که مستقیم به sql وصل می شود.
    ممنون میشم راهنمایی بفرمایید چون خیلی وقته کارم گیره.
    با سپاس فراوان

  5. #4
    مدیر بخش آواتار Mahmoud.Afrad
    تاریخ عضویت
    مرداد 1387
    پست
    3,209
    تشکر کردن
    58
    2,576 بار تشکر شده در 2,008 پست

    نقل قول: سریع ترین و بهترین روش بازگردانی کوئری با شرط های خواسته شده

    نقل قول نوشته شده توسط tara1367 مشاهده تاپیک
    تعداد فیلد های جدول ها خیلی زیاد هستند برای اسکریپت گذاشتن.
    خیر آشنایی با روش
    entity framework ندارم. از linq to sql و یا همون datatable و dataadapter استفاده کردم که مستقیم به sql وصل می شود.
    ممنون میشم راهنمایی بفرمایید چون خیلی وقته کارم گیره.
    با سپاس فراوان
    اسکریپت از قسمتهای حداقلی که میتونید بزارید! اسکریپت بگیرید و اجزای غیرضروری رو حذف کنید و اینجا قرار بدید.

    چرا در مورد جداول توضیح نمیدید؟!! با داشتن تاریخ (Date) چرا ماه رو جدا هم ذخیره میکنید؟ تاریخ رو میلادی ذخیره میکنید یا شمسی؟ Code چیه؟ ماه های 8 و 9 از کجا میان و آیا ممکنه تغییر کنند یا نه؟
    کدهایی که گذاشتید ado.net هستند نه linqtosql. کمی دقت کنید که از کدام تکنولوژی استفاده میکنید.
    برای اینگونه موارد باید از یکی از انواع joinها استفاده کنید تا بتونید اطلاعات رو از چند جدول مرتبط بیرون بکشید (خودتون گفتید فیلد مشترک برای join استفاده خواهد شد ولی اصلا از join استفاده نکردید) در اینصورت نیازی به حلقه نیست؛ میتونم بگم با یک کوئری همه این کارها رو میشه انجام داد. موارد مورد نیاز join و گروهبندی براساس userid و month و استفاده از تابع sum و تفریق برای محاسبه مانده، استفاده از تابع Max (یا Top و یا تابعی دیگر) برای بدست آوردن آخرین تاریخ. برای پیوند توضیحات "بابت" هم میتونید از for xml در sql و یا از متد string.join در linqtosql استفاده کنید.

  6. کاربرانی که به خاطر مطلب مفید Mahmoud.Afrad از وی تشکر کرده‌اند:


  7. #5
    کاربر دائمی
    تاریخ عضویت
    اردیبهشت 1389
    پست
    221
    تشکر کردن
    148
    7 بار تشکر شده در 7 پست

    نقل قول: سریع ترین و بهترین روش بازگردانی کوئری با شرط های خواسته شده

    ممنون از راهنمایی و وقتی که گذاشتین. جواب به سوالاتتون:

    اسکریپت جدول TUser:

    CREATE TABLE [dbo].[TUser](
    [Code] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NULL,
    [Fname] [nvarchar](60) NULL,
    [Lname] [nvarchar](120) NULL,
    CONSTRAINT [PK_TUser] PRIMARY KEY CLUSTERED
    (
    [Code] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    و اسکریپت جدول TPardakht:

    CREATE TABLE [dbo].[TPardakht](
    [Code] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NULL,
    [Name] [nvarchar](120) NULL,
    [Date] [nvarchar](15) NULL,
    [Bed] [decimal](18, 0) NULL,
    [Bes] [decimal](18, 0) NULL,
    [Mande] [decimal](18, 0) NULL,
    [Babat] [nvarchar](max) NULL,
    [WaysOfPay] [nvarchar](70) NULL,
    [Month] [nvarchar](2) NULL,
    [Year] [nvarchar](4) NULL,
    [DocNo] [decimal](18, 0) NULL,
    [OtherH] [nvarchar](max) NULL,
    [HCode] [int] NULL,
    [HName] [nvarchar](100) NULL,
    [Control] [nvarchar](1) NULL,
    [YFname] [nvarchar](50) NULL,
    [YLname] [nvarchar](70) NULL,
    [YCode] [int] NULL,
    CONSTRAINT [PK_TPardakht] PRIMARY KEY CLUSTERED
    (
    [Code] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

    بنده از تکنولوژی ado.net استفاده می کنم.
    اما عرض کردم برای تست از linq to sql هم استفاده کردم دیدم سرعتش مثل همینه.
    از join هم استفاده کردم اما بزرگترین مساله اینه که نمی خوام اسم کابرانی را که در ماه مثلا 9 پرداخت داشته اند را نشون بده.
    ضمنا به این دلیل از فیلد ماه جدا استفاده کردم چون ممکن است پرداخت مربوط به ماه 8 مثلا در تاریخ 1395/09/01 انجام شود ولی می خواهند جزو ماه 8 باشد. ماه 8 و 9 در اینجا مثال است بعد خودم چایگزین متغیر می کنم.

    راه های مختلفی تست کردم اما چون موارد خواسته شده همه قرار است در یک گریدویو نمایش داده شود و هر کدام از فیلدها یک جور محاسبه می شود سرعت بسیار پایین می آید.
    باز ممنون میشم یک راهنمایی بفرمایید همچنین در مورد string.join توضیح دهید.

    با سپاس فراوان
    آخرین ویرایش به وسیله Mahmoud.Afrad : شنبه 25 دی 1395 در 20:09 بعد از ظهر

  8. #6
    کاربر دائمی
    تاریخ عضویت
    اردیبهشت 1389
    پست
    221
    تشکر کردن
    148
    7 بار تشکر شده در 7 پست

    نقل قول: سریع ترین و بهترین روش بازگردانی کوئری با شرط های خواسته شده

    یک تکه کد نوشتم که مقدار زیادی از کارم رو راه انداخت با سرعت بالا فقط با بازیابی دو تا از فیلدها مشکل دارم:

    SqlDataAdapter da = new SqlDataAdapter("SELECT TOP (100) PERCENT '' AS Row, dbo.TUser.UserId, " +
    "dbo.TUser.Fname + ' ' + dbo.TUser.Lname AS Name, 0 AS Mandeh, ISNULL(SUM(dbo.TPardakht.Bed), 0) AS LastP, " +
    "ISNULL(MAX(dbo.TPardakht.Date), N'') AS LastD, ISNULL(MAX(dbo.TPardakht.Babat), N'') AS LastT, " +
    "ISNULL(dbo.TPardakht.YCode, N'') AS YCode " +
    "FROM dbo.TUser LEFT OUTER JOIN dbo.TPardakht ON dbo.TUser.Code = dbo.TPardakht.YCode AND " +
    "dbo.TPardakht.Month = '" + stMonth + "' AND dbo.TPardakht.Control = 'P' AND dbo.TPardakht.Year = '" + stYear + "' AND " +
    "dbo.TUser.Vaziat = 'Y' " +
    "GROUP BY dbo.TPardakht.YCode, dbo.TUser.UserId, dbo.TUser.Fname, dbo.TUser.Lname " +
    "ORDER BY dbo.TUser.UserId, Name",
    conn = new SqlConnection(codes.ReturnConnectionstring()));
    DataTable dt = new DataTable();
    da.Fill(dt);
    dgvMande.DataSource = dt;

    در حال حاضر تمامی فیلدهای مورد نظر در یک ماه خاص مثلا ماه 8 نشان داده می شود ولی:
    یک اینکه می خوام فیلد بابت بیاد سطرهای مورد نظر مربوط به بابت رو در قالب یک رشته نشون بده. از for xml استفاده کردم ولی ارور داد.
    و دوم اینکه این ها اطلاعات پرداختی مربوط به 1 ماه است اگر بخوام یکی از ستون های گریدویو جمع پرداخت کل سال مربوط به هر کاربر را هم محاسبه کند فکر کنم باید از select تو در تو استفاده کنم اما نمی دونم چه جوری.
    یعنی در واقع در دستور select بعد از آخرین فیلد این select رو هم اضافه کردم اما نمی دونم باید کجاش شرط بذارم. اینجوری میاد آخرین فیلد اضافه شده را برابر با کل مانده حساب کل کاربران میذاره. اما من تک تک کاربران رو می خوام:
       (SELECT ISNULL(SUM(Bes) - SUM(Bed), 0) FROM dbo.TPardakht) AS Mand


    ممنون میشم اگر کسی می دونه راهنمایی کنه.
    آخرین ویرایش به وسیله Mahmoud.Afrad : شنبه 25 دی 1395 در 20:08 بعد از ظهر

  9. #7
    مدیر بخش آواتار Mahmoud.Afrad
    تاریخ عضویت
    مرداد 1387
    پست
    3,209
    تشکر کردن
    58
    2,576 بار تشکر شده در 2,008 پست

    نقل قول: سریع ترین و بهترین روش بازگردانی کوئری با شرط های خواسته شده

    ;with cteMonthlyTotal
    as
    (
    select
    TPardakht.UserId , TPardakht.Month ,
    SUM(TPardakht.Bed) as bedForMonth,
    SUM(TPardakht.Bes) as besForMonth,
    STUFF((SELECT ';' + TP.Babat
    FROM TPardakht TP
    WHERE TP.UserId = TPardakht.UserId and tp.Month = TPardakht.Month
    FOR XML PATH('')), 1, 1, '') AS Babat,
    TPardakht.Year
    from TPardakht
    group by TPardakht.UserId , TPardakht.Month , TPardakht.Year
    ),
    cteYearlyTotal
    as
    (
    select
    cteMonthlyTotal.UserId, cteMonthlyTotal.Month, cteMonthlyTotal.bedForMonth, cteMonthlyTotal.besForMonth,
    (cteMonthlyTotal.bedForMonth-cteMonthlyTotal.besForMonth) as MonthlyMmande ,
    cteMonthlyTotal.Babat,
    yy.Year , yy.BedForYear , yy.BesForYear
    from cteMonthlyTotal
    join
    (
    select UserId , Year , SUM(Bed) as BedForYear , SUM(Bes) as BesForYear
    from TPardakht
    group by UserId , Year
    ) yy
    on yy.UserId=cteMonthlyTotal.UserId and yy.Year=cteMonthlyTotal.Year
    group by cteMonthlyTotal.UserId , cteMonthlyTotal.Month , cteMonthlyTotal.bedForMonth , cteMonthlyTotal.besForMonth , cteMonthlyTotal.Babat , yy.Year , yy.BedForYear , yy.BesForYear
    ),
    cteResult
    as
    (
    select TUser.UserId , TUser.Fname , TUser.Lname,
    cteYearlyTotal.Month, cteYearlyTotal.bedForMonth, cteYearlyTotal.besForMonth, cteYearlyTotal.MonthlyMmande, cteYearlyTotal.Babat,
    cteYearlyTotal.Year, cteYearlyTotal.BedForYear, cteYearlyTotal.BesForYear, (cteYearlyTotal.BedForYear-cteYearlyTotal.BesForYear) as YearlyMende
    from TUser
    left join cteYearlyTotal
    on TUser.UserId = cteYearlyTotal.UserId
    WHERE TUser.UserId NOT IN (select DISTINCT TPardakht.UserId from TPardakht where TPardakht.Month=9)
    --AND TPardakht.Month = ' stMonth '
    --AND TPardakht.Control = 'P'
    --AND TPardakht.Year = 'stYear '
    --AND TUser.Vaziat = 'Y'
    )
    select *
    from cteResult

    بهینه تر هم شاید بشه نوشت. روش فکر کنید.

    نکته:
    نوع ستونهاتون رو هم اصلاح کنید. مثلا نوع ستون Month میتونه tinyint باشه که فقط یک بایت اشغال میکنه . Year میتونه smallint باشه . یا ستونهایی که داده غیرفارسی(یونیکد) در خود ذخیره میکننده نیاز نیست nvarchar باشه و میتونه varchar یا داده مناسب دیگری انتخاب بشه.

  10. کاربرانی که به خاطر مطلب مفید Mahmoud.Afrad از وی تشکر کرده‌اند:


  11. #8
    کاربر دائمی
    تاریخ عضویت
    اردیبهشت 1389
    پست
    221
    تشکر کردن
    148
    7 بار تشکر شده در 7 پست

    نقل قول: سریع ترین و بهترین روش بازگردانی کوئری با شرط های خواسته شده

    واقعا زدن فقط دکمه تشکر برای وقتی که گذاشتین کافی نیست. خیلی خیلی لطف کردید. کارم رو راه انداخت. انشالله همیشه موفق باشید و به درجات عالی علم برسید.

تاپیک های مشابه

  1. تاخیر Post شدن در یک کوئری یا جدول
    نوشته شده توسط dkhatibi در بخش برنامه نویسی در Delphi
    پاسخ: 10
    آخرین پست: چهارشنبه 12 دی 1386, 21:39 بعد از ظهر
  2. شمارش رکوردهای که از تاریخ ثبت آنها یک ماه گذشته است در یک کوئری
    نوشته شده توسط davoodmz در بخش برنامه نویسی در Delphi
    پاسخ: 3
    آخرین پست: پنجشنبه 23 فروردین 1386, 20:00 بعد از ظهر
  3. جمع فیلدها در یک کوئری و جمع نهایی در فیلدی مجازی
    نوشته شده توسط davoodmz در بخش برنامه نویسی در Delphi
    پاسخ: 8
    آخرین پست: یکشنبه 19 فروردین 1386, 15:14 بعد از ظهر
  4. نمایش دو تیبل در یک کوئری
    نوشته شده توسط iran400 در بخش SQL Server
    پاسخ: 14
    آخرین پست: پنجشنبه 02 شهریور 1385, 15:56 بعد از ظهر
  5. چمارش اعداد در یک کوئری یا فرم با یک شرط خاص
    نوشته شده توسط saras در بخش Access
    پاسخ: 2
    آخرین پست: پنجشنبه 25 خرداد 1385, 13:38 بعد از ظهر

برچسب های این تاپیک

قوانین ایجاد تاپیک در تالار

  • شما نمی توانید تاپیک جدید ایجاد کنید
  • شما نمی توانید به تاپیک ها پاسخ دهید
  • شما نمی توانید ضمیمه ارسال کنید
  • شما نمی توانید پاسخ هایتان را ویرایش کنید
  •