none
חישוב RRS feed

  • שאלה

  • שלום ,

    יש לי טבלה כזו:

    שעה        שורה    הזמנה

    12          0         123

    12:05     1         123

    13:00      2            123

    13:05       99999               123

    אני רוצה לקבל :

    שעה  סיום   שעת התחלה      שורה    הזמנה

      13:05       12:00          0         123

      13:05       12:05     1             123

      13:05       13:00      2            123

    13:05          12:00       99999                123

    איך ניתן לפתור את הבעיה

    יום חמישי 19 יולי 2012 11:07

תשובות

  • היי נורית,

    למען האמת קצת היה קשה להבין אילו נתונים את רוצה לשלוף, אם אכן הצלחתי להבין אותך כהלכה, מצורף סקריפט שאמור לעזור לך. במידה והבנתי אותך בצורה לא נכונה, אשמח לקבל הסבר מפורט יותר ולנסות לענות שוב.... 

    יוסי חקיקת 

    CREATE TABLE #Tmp (RowTime TIME, Row INT, Invitation INT)
    
    INSERT INTO #Tmp (RowTime, Row,Invitation)
    VALUES
    
    ('12:00',0, 123),
    ('12:05',1, 123),
    ('13:00',2, 123),
    ('13:05',99999, 123)
    
    SELECT 
    	Drived.MaxTime,
    	t.RowTime,
    	t.Row,
    	t.Invitation 
    FROM 
    	(
    		SELECT MAX(RowTime) AS MaxTime,Invitation FROM #Tmp GROUP BY Invitation
    	) AS 
    	Drived 
    INNER JOIN 
    	#Tmp AS t ON  t.Invitation = Drived.Invitation

    יום חמישי 19 יולי 2012 11:54
  • הי נורית,

    אני לא בטוח אם כבר קיבלת תשובה לשאלה שלך.

    אם לא, אז הנה הפתרון שלי...

    -- Setup
    
    CREATE TABLE
    	dbo.OrderDetails
    (
    	LineTime	TIME(0)	NOT NULL ,
    	Locator		INT		NOT NULL ,
    	LineNumber	INT		NOT NULL ,
    	OrderNumber	INT		NOT NULL
    );
    GO
    
    
    INSERT INTO
    	dbo.OrderDetails
    (
    	LineTime ,
    	Locator ,
    	LineNumber ,
    	OrderNumber
    )
    VALUES
    	('12:00' , 0 , 5 , 1) ,
    	('12:05' , 1234 , 1 , 1) ,
    	('12:12' , 5678 , 2 , 1) ,
    	('12:30' , 9101 , 3 , 1) ,
    	('12:40' , 1011 , 4 , 1) ,
    	('12:50' , 99999 , 4 , 1) ,
    	('13:00' , 0 , 1 , 1) ,
    	('13:05' , 99999 , 1 , 1);
    GO
    
    
    -- Solution - SQL Server 2012
    
    SELECT
    	TotalTime	= CAST (DATEADD (MINUTE , DATEDIFF (MINUTE , FIRST_VALUE (LineTime) OVER (PARTITION BY OrderNumber ORDER BY Locator ASC , LineTime ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) , LAST_VALUE (LineTime) OVER (PARTITION BY OrderNumber ORDER BY Locator ASC , LineTime ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)) , '00:00:00') AS TIME(0)) ,
    	LineTime	= LineTime ,
    	Locator		= Locator ,
    	LineNumber	= LineNumber ,
    	OrderNumber	= OrderNumber
    FROM
    	dbo.OrderDetails
    ORDER BY
    	OrderNumber	ASC ,
    	LineTime	ASC;
    GO
    
    
    -- Solution - SQL Server 2008/2008R2
    
    SELECT
    	TotalTime	= CAST (DATEADD (MINUTE , DATEDIFF (MINUTE , CalculatedOrderDetails.MinOrderTime , CalculatedOrderDetails.MaxOrderTime) , '00:00:00') AS TIME(0)) ,
    	LineTime	= CalculatedOrderDetails.LineTime ,
    	Locator		= CalculatedOrderDetails.Locator ,
    	LineNumber	= CalculatedOrderDetails.LineNumber ,
    	OrderNumber	= CalculatedOrderDetails.OrderNumber
    FROM
    	(
    		SELECT
    			MinOrderTime	=
    				(
    					SELECT TOP (1)
    						MinOrderTimes.LineTime
    					FROM
    						dbo.OrderDetails AS MinOrderTimes
    					WHERE
    						MinOrderTimes.OrderNumber = OrderDetails.OrderNumber
    					ORDER BY
    						MinOrderTimes.Locator	ASC ,
    						MinOrderTimes.LineTime	ASC
    				) ,
    			MaxOrderTime	=
    				(
    					SELECT TOP (1)
    						MaxOrderTimes.LineTime
    					FROM
    						dbo.OrderDetails AS MaxOrderTimes
    					WHERE
    						MaxOrderTimes.OrderNumber = OrderDetails.OrderNumber
    					ORDER BY
    						MaxOrderTimes.Locator	DESC ,
    						MaxOrderTimes.LineTime	DESC
    				) ,
    			LineTime	= OrderDetails.LineTime ,
    			Locator		= OrderDetails.Locator ,
    			LineNumber	= OrderDetails.LineNumber ,
    			OrderNumber	= OrderDetails.OrderNumber
    		FROM
    			dbo.OrderDetails AS OrderDetails
    	)
    	AS
    		CalculatedOrderDetails
    ORDER BY
    	OrderNumber	ASC ,
    	LineTime	ASC;
    GO
    

    שימי לב שאני מניח שהערך 0 הוא הערך המינימלי שיכול להופיע בעמודה "איתור", ובאותו אופן, הערך 99999 הוא הערך המקסימלי בעמודה זו. אם ההנחה שלי לא נכונה, אז צריך לשנות את הפתרון שהצגתי כאן.

    מקווה שעזרתי...

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי Ami Levin יום ראשון 29 יולי 2012 08:07
    יום ראשון 29 יולי 2012 04:33
    מנחה דיון

כל התגובות

  • היי נורית,

    למען האמת קצת היה קשה להבין אילו נתונים את רוצה לשלוף, אם אכן הצלחתי להבין אותך כהלכה, מצורף סקריפט שאמור לעזור לך. במידה והבנתי אותך בצורה לא נכונה, אשמח לקבל הסבר מפורט יותר ולנסות לענות שוב.... 

    יוסי חקיקת 

    CREATE TABLE #Tmp (RowTime TIME, Row INT, Invitation INT)
    
    INSERT INTO #Tmp (RowTime, Row,Invitation)
    VALUES
    
    ('12:00',0, 123),
    ('12:05',1, 123),
    ('13:00',2, 123),
    ('13:05',99999, 123)
    
    SELECT 
    	Drived.MaxTime,
    	t.RowTime,
    	t.Row,
    	t.Invitation 
    FROM 
    	(
    		SELECT MAX(RowTime) AS MaxTime,Invitation FROM #Tmp GROUP BY Invitation
    	) AS 
    	Drived 
    INNER JOIN 
    	#Tmp AS t ON  t.Invitation = Drived.Invitation

    יום חמישי 19 יולי 2012 11:54
  • נעזרתי קצת במה שיוסי הציע:

    Select	Max(RowTime) Over(Partition By Invitation),
    		Case When RowTime=Max(RowTime) Over(Partition By Invitation)
    				Then Min(RowTime) Over(Partition By Invitation)
    			Else
    				RowTime
    			End,
    		Row,
    		Invitation
    From	#Tmp;


    Blog: http://about.me/GeriReshef

    יום חמישי 19 יולי 2012 17:58
  • תודה רבה על התשובה אבל אני אסביר שוב.

    יש לי טבלה המראה לכל הזמנה את השורות שלה וכל איתור ש 0 אומר כמה שורות יש להזמנה וכל שורה של איתור 9999 מראה כמה שורות היו בפועל להזמנה

    אני רוצה לקבל תוצאה נוספת לטבלה שתחשב לי את סה"כ הזמנה לכל ההזמנה:

    שעה  איתור שורה הזמנה
    12:00 0 5 1
    12:05 1234 1 1
    12:12 5678 2 1
    12:30 9101 3 1
    12:40 1011 4 1
    12:50 99999 4 1
    13:00 0 1 1
    13:05 99999 1 1

    זו הטבלה שאני רוצה לקבל: החישוב הוא מקסימום של שורה עם איתור 9999 פחות מינימוס של שורה עם איתור 0

    סה"כ זמן שעה  איתור שורה הזמנה
    01:05 12:00 0 5 1
    01:05 12:05 1234 1 1
    01:05 12:12 5678 2 1
    01:05 12:30 9101 3 1
    01:05 12:40 1011 4 1
    01:05 12:50 99999 4 1
    01:05 13:00 0 1 1
    01:05 13:05 99999 1 1

    יום ראשון 22 יולי 2012 06:58
  • DECLARE @tmp table (ordertime time,ordernumber int);
    INSERT INTO @tmp (ordertime,ordernumber)
    VALUES
    ('12:00',1),
    ('12:05',1),
    ('13:00',1),
    ('13:15',1)
    ;
    SELECT * from @tmp;
    SELECT 
    
    convert(char(8), dateadd(minute, datediff(minute,(SELECT min(ordertime) from @tmp t1 WHERE t1.ordernumber =rr.ordernumber),(SELECT max(ordertime) from @tmp t2 WHERE t2.ordernumber =rr.ordernumber )), ''), 114) as aa,* FROM @tmp rr

    נ.ב.

    13:05 <> 1:05

    כך שהתשובות שענו לך עד עכשיו ענו על השאלה הראשונה :)



    • נערך על-ידי tetitu יום ראשון 22 יולי 2012 09:56
    יום ראשון 22 יולי 2012 09:22
  • הי נורית,

    אני לא בטוח אם כבר קיבלת תשובה לשאלה שלך.

    אם לא, אז הנה הפתרון שלי...

    -- Setup
    
    CREATE TABLE
    	dbo.OrderDetails
    (
    	LineTime	TIME(0)	NOT NULL ,
    	Locator		INT		NOT NULL ,
    	LineNumber	INT		NOT NULL ,
    	OrderNumber	INT		NOT NULL
    );
    GO
    
    
    INSERT INTO
    	dbo.OrderDetails
    (
    	LineTime ,
    	Locator ,
    	LineNumber ,
    	OrderNumber
    )
    VALUES
    	('12:00' , 0 , 5 , 1) ,
    	('12:05' , 1234 , 1 , 1) ,
    	('12:12' , 5678 , 2 , 1) ,
    	('12:30' , 9101 , 3 , 1) ,
    	('12:40' , 1011 , 4 , 1) ,
    	('12:50' , 99999 , 4 , 1) ,
    	('13:00' , 0 , 1 , 1) ,
    	('13:05' , 99999 , 1 , 1);
    GO
    
    
    -- Solution - SQL Server 2012
    
    SELECT
    	TotalTime	= CAST (DATEADD (MINUTE , DATEDIFF (MINUTE , FIRST_VALUE (LineTime) OVER (PARTITION BY OrderNumber ORDER BY Locator ASC , LineTime ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) , LAST_VALUE (LineTime) OVER (PARTITION BY OrderNumber ORDER BY Locator ASC , LineTime ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)) , '00:00:00') AS TIME(0)) ,
    	LineTime	= LineTime ,
    	Locator		= Locator ,
    	LineNumber	= LineNumber ,
    	OrderNumber	= OrderNumber
    FROM
    	dbo.OrderDetails
    ORDER BY
    	OrderNumber	ASC ,
    	LineTime	ASC;
    GO
    
    
    -- Solution - SQL Server 2008/2008R2
    
    SELECT
    	TotalTime	= CAST (DATEADD (MINUTE , DATEDIFF (MINUTE , CalculatedOrderDetails.MinOrderTime , CalculatedOrderDetails.MaxOrderTime) , '00:00:00') AS TIME(0)) ,
    	LineTime	= CalculatedOrderDetails.LineTime ,
    	Locator		= CalculatedOrderDetails.Locator ,
    	LineNumber	= CalculatedOrderDetails.LineNumber ,
    	OrderNumber	= CalculatedOrderDetails.OrderNumber
    FROM
    	(
    		SELECT
    			MinOrderTime	=
    				(
    					SELECT TOP (1)
    						MinOrderTimes.LineTime
    					FROM
    						dbo.OrderDetails AS MinOrderTimes
    					WHERE
    						MinOrderTimes.OrderNumber = OrderDetails.OrderNumber
    					ORDER BY
    						MinOrderTimes.Locator	ASC ,
    						MinOrderTimes.LineTime	ASC
    				) ,
    			MaxOrderTime	=
    				(
    					SELECT TOP (1)
    						MaxOrderTimes.LineTime
    					FROM
    						dbo.OrderDetails AS MaxOrderTimes
    					WHERE
    						MaxOrderTimes.OrderNumber = OrderDetails.OrderNumber
    					ORDER BY
    						MaxOrderTimes.Locator	DESC ,
    						MaxOrderTimes.LineTime	DESC
    				) ,
    			LineTime	= OrderDetails.LineTime ,
    			Locator		= OrderDetails.Locator ,
    			LineNumber	= OrderDetails.LineNumber ,
    			OrderNumber	= OrderDetails.OrderNumber
    		FROM
    			dbo.OrderDetails AS OrderDetails
    	)
    	AS
    		CalculatedOrderDetails
    ORDER BY
    	OrderNumber	ASC ,
    	LineTime	ASC;
    GO
    

    שימי לב שאני מניח שהערך 0 הוא הערך המינימלי שיכול להופיע בעמודה "איתור", ובאותו אופן, הערך 99999 הוא הערך המקסימלי בעמודה זו. אם ההנחה שלי לא נכונה, אז צריך לשנות את הפתרון שהצגתי כאן.

    מקווה שעזרתי...

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי Ami Levin יום ראשון 29 יולי 2012 08:07
    יום ראשון 29 יולי 2012 04:33
    מנחה דיון