محاسبه مجموع فیلدهایی با فرمت Text با محتوای ساعت و دقیقه

[bardia goharbin]

من یک فیلد دارم از نوع Char که در آن مقداری بصورت ساعت و دقیقه با مقادیری گوناگون ذخیره شده است، با چه دستورات TSQL میتوانم جمع این ستون را بصورت ساعت و دقیقه محاسبه کنم
البته مجموع مورد نیاز با در نظرگرفتن محاسبه 60 دقیقه و تبدیل آن به ساعت باشد. به مقادیر زیر توجه کنید

MyField
----------
100:40
10:10
20:30
----------
131:20

[یوسف زالی]

شما این کارو کن:
sum(Date2Minute(Times))// date2minute is a function you write to convert all hour and minute to minute only
بعد بیاید و دوباره به فرم ساعت:دقیقه نمایش بدید با یه تابع معکوس

[bardia goharbin]

دوست عزیز این فانکشن Date2Minute خطا میده مثل اینکه SQL یه همچین فانکشنی نداره
Date2Minute' is not a recognized built-in function name

[bardia goharbin]

راه حلتون منطقی و خوبه ولی دوست عزیز سئوال من این بود که آیا SQL خودش تابع یا فانکشنی برای این کار داره یا نه؟ خواستم اگر فانکشن آماده خود SQL داره از اون استفاده کنم و یا راه های دیگه رو پیشنهاد بدید

[یوسف زالی]

این جواب داد برام.
شما تست کن ببین کارتو راه میندازه:

select CAST(sum(cast(SUBSTRING(Times, 1, 2)as int))+sum(cast(SUBSTRING(Times, 4, 2)as int))/ 60 as varchar(6))
+':'
+case LEN(cast(sum(cast(SUBSTRING(Times, 4, 2)as int))% 60 as varchar(2)))when 1 then '0' else '' end
+cast(sum(cast(SUBSTRING(Times, 4, 2)as int))% 60 as varchar(2))


البته کد برای char 5 تایی هست.اگر از طول متغیر استفاده می کنید در substring از charindex استفاده کنید.

[محمد سلیم آبادی]

من یک فیلد دارم از نوع Char که در آن مقداری بصورت ساعت و دقیقه با مقادیری گوناگون ذخیره شده است، با چه دستورات TSQL میتوانم جمع این ستون را بصورت ساعت و دقیقه محاسبه کنم
البته مجموع مورد نیاز با در نظرگرفتن محاسبه 60 دقیقه و تبدیل آن به ساعت باشد. به مقادیر زیر توجه کنید

MyField
----------
100:40
10:10
20:30
----------
131:20

نظرت راجب این چیه مشکلت رو حل میکنه:

DECLARE @Sample TABLE
(MyField VARCHAR(50));

INSERT @Sample VALUES
('100:40'),
('10:10'),
('20:30');

SELECT CAST(SUM(h) + SUM(m) / 60 AS VARCHAR(50)) + ':' +
CAST(SUM(m) % 60 AS VARCHAR(50)) AS Total
FROM @Sample S
CROSS APPLY (
VALUES (
LEFT(MyField, CHARINDEX(':', MyField) - 1),
RIGHT(MyField, CHARINDEX(':', REVERSE(MyField)) - 1)
)
) D(hh, mm)
CROSS APPLY (
VALUES (
CAST (D.hh AS INTEGER),
CAST (D.mm AS INTEGER)
)
) T(h, m);

[محمد سلیم آبادی]

یا حتی ساده تر (تبدیل نوع داده رشته ای به عددی توسط اعداد خنثی، مثلا 1 در ضرب و 0 در جمع):

SELECT CAST(SUM(h * 1) + SUM(m * 1) / 60 AS VARCHAR(50)) + ':' +
CAST(SUM(m * 1) % 60 AS VARCHAR(50)) AS Total
FROM @Sample S
CROSS APPLY (
VALUES (
LEFT(MyField, CHARINDEX(':', MyField) - 1),
RIGHT(MyField, CHARINDEX(':', REVERSE(MyField)) - 1)
)
) D(h, m);

[یوسف زالی]

msalim جان من هم همین رو گفتم اما کد شما یه مشکل داره:
اگر دقیقه یک رقمی باشه 18:05 مثلا می شه 18:5

[bardia goharbin]

مرسی از تمام دوستان، همونطور که میدونیم راههای رسیدن به خدا زیاده، این راه رو هم خودم یافتم و با نتیجه دلخواه من 100% جوره


DECLARE @T TABLE (
c1 char(10) NOT NULL
);

INSERT INTO @T(c1)
SELECT '100:40' UNION ALL
SELECT '10:10' UNION ALL
SELECT '20:30' UNION ALL
SELECT '20:20';

select
replace(str(sum(convert(int,PARSENAME(replace(c1,' :','.'),2))) +
sum(convert(int,PARSENAME(replace(c1,':','.'),1)))/60) + ':' +
str(sum(convert(int,PARSENAME(replace(c1,':','.'), 1)))%60),' ','')Total from @t

[محمد سلیم آبادی]

msalim جان من هم همین رو گفتم
فکر می کنم عاملی که باعث سوء تفاهم شده این بود که کل پست اول رو بصورت نقل قل گرفتم و سپس پاسخ دادم و این برداشت می تونه ازش شده باشه که راه حل موجود در پست های پیشین صحیح نیستند. ولی اینطور نبوده، واقعیتش هر سه کوئری که در این تاپیک وجود داره از یک روش کاملا یکسان پیروی میکنند. یعنی تفکیک دو مقدار توسط تابع (شما Substring من Left/Right و ایشان Parsname) سپس تبدیل آن به عدد و در پایان بدست آوردن حاصل جمع.
اما نکته ای که من سعی داشتم بهش اشاره کنم این بود که نیازی نیست که مجموع دقایق که در دو جا مورد استفاده قرار گرفته در هر دو مورد دوباره محاسبه بشه.

یعنی کوئری موجود در پست قبلی میتونست خواناتر و ساده تر بشه بوسیله یک عبارت جدولی یا تکنیک Apply ای که در پست های قبلیم نشون دادم (بدون توجه به نتیجه چرا که من نتیجه NULL رو بعد از اجرای کوئری پست قبلی بدست اوردم). مثلا توسط CTE (یا Derived Table ) راه حل پست قبلی به این شکل در میاد:

;with cte as
(select PARSENAME(replace(c1,' :','.'),2) as v1,
PARSENAME(replace(c1,':','.'),1) as v2
from @t)

select
replace(str(sum(convert(int,v1)) +
sum(convert(int,v2))/60) + ':' +
str(sum(convert(int,v2))%60),' ','')Total
from cte


حد اقل مزیت هایی که میتونه داشته باشه اینه که راحتر میشه کد رو اصلاح، توسعه و ویرایش کرد. و از دوبار محاسبه شدن یک مقدار یکسان نیز جلوگیری به عمل آمده است.


اما کد شما یه مشکل داره:
اگر دقیقه یک رقمی باشه 18:05 مثلا می شه 18:5 من پستهای این تاپیک رو دوباره مطالعه کردم ولی همچنین درخواستی رو از طرف ایجاد کننده تاپیک مشاهده نکردم. کاربر تمام مقادیر مرتب به دقیقه را بصورت دو رقمی نمایش داده بود و در مورد این موضوع بنظر نمیرسه که چیزی گفته باشه. یا حد اقل من متوجه نشدم؟
به هر حال اینم میتونه یک جور نیاز مساله باشه. که روشی که من برای حل این موضوع استفاده خواهم کرد بشکل زیر هست:

RIGHT('00' + value, 2)

البته قابل ذکر هست تا زمانی که نیازهای مساله و داده های محیط عملیاتی بطور کامل و دقیق با جزئیات کافی بیان نشن امکان ارسال و پیشنهاد یک راه حل دقیق امکان پذیر نیست... .
امید وارم متوجه منظورم شده باشید و مشکل مطرح کننده سوال نیز بطور کامل حل شده باشد.

[محمد سلیم آبادی]

آخرین پست صفحه قبل نیز خوانده شود.
و همچنین راه حل دوم در اینجا
http://puresql.blogsky.com/1392/05/13/post-14/%D9%85%D8%AD%D8%A7%D8%B3%D8%A8%D9%87-%D9%85%D8%AC%D9%85%D9%88%D8%B9-%D8%B3%D8%A7%D8%B9%D8%A7%D8%AA-%D9%88-%D8%AF%D9%82%DB%8C%D9%82%D9%87-%D9%85%D9%82%D8%A7%D8%AF%DB%8C%D8%B1-%D8%B1%D8%B4%D8%AA%D9%87-%D8%A7%DB%8C

[hassanzarabi]

با سلام

از استاد عزیز جناب آقای محمد سلیم آبادی (http://barnamenevis.org/member.php?108959-%D9%85%D8%AD%D9%85%D8%AF-%D8%B3%D9%84%DB%8C%D9%85-%D8%A2%D8%A8%D8%A7%D8%AF%DB%8C) بابت این مطالب بسیار ممنونم خیلی استفاده کردم