locked
T-SQL Query help plz RRS feed

  • Question

  • Hi,

    I have a 2 tables with data in the below DDL:

    DECLARE @Sample TABLE (SubID int,String Varchar(255),DetailID int,[Hour] Varchar(5),Failure int,Success int)
    INSERT INTO @Sample VALUES(899,'Apple', 1232,'00 AM',1,0)
    INSERT INTO @Sample VALUES(899,'Apple', 1233,'00 AM',0,1)
    INSERT INTO @Sample VALUES(899,'Apple', 1234,'00 AM',1,0)
    INSERT INTO @Sample VALUES(899,'Apple', 1235,'01 AM',1,0)
    INSERT INTO @Sample VALUES(899,'Apple', 1236,'01 AM',0,1)
    INSERT INTO @Sample VALUES(899,'Apple', 1237,'01 AM',1,0)
    INSERT INTO @Sample VALUES(899,'Orange', 1238,'00 AM',1,0)
    INSERT INTO @Sample VALUES(899,'Orange', 1239,'00 AM',1,0)
    INSERT INTO @Sample VALUES(899,'Orange', 1240,'00 AM',0,1)
    INSERT INTO @Sample VALUES(899,'Orange', 1241,'01 AM',1,0)
    INSERT INTO @Sample VALUES(899,'Orange', 1242,'01 AM',1,0)
    
    --SELECT * FROM @Sample
    
    DECLARE @test TABLE (SubID int,String Varchar(255),[Min_Hour] varchar(5),Max_hour varchar(5),Property float,Register float,Cal_Property float,Cal_Register float)
    INSERT INTO @Test VALUES(899,'Apple','00 AM','23 PM',1.19,49.07,31.99,4409.32)
    INSERT INTO @Test VALUES(899,'Orange','00 AM','23 PM',3.09,47.07,33.99,4459.32)
    INSERT INTO @Test VALUES(899,'Black','00 AM','23 PM',2.19,48.07,34.99,4439.32)
    INSERT INTO @Test VALUES(899,'Yellow','00 AM','23 PM',3.21,46.07,32.99,4409.32)
    INSERT INTO @Test VALUES(899,'White','00 AM','23 PM',2.09,44.07,31.99,4429.32)
    INSERT INTO @Test VALUES(899,'Pink','00 AM','23 PM',1.02,43.07,35.99,4449.32)
    INSERT INTO @Test VALUES(899,'Red','00 AM','23 PM',1.33,45.07,36.99,4469.32)
    INSERT INTO @Test VALUES(899,'Purple','00 AM','23 PM',2.23,42.07,39.99,4449.32)
    INSERT INTO @Test VALUES(899,'Peach','00 AM','23 PM',1.09,54.07,32.99,4429.32)
    INSERT INTO @Test VALUES(899,'Grape','00 AM','23 PM',1.01,23.07,38.99,4459.32)
    INSERT INTO @Test VALUES(899,'Lemon','00 AM','23 PM',2.21,55.07,37.99,4429.32)
    
    --SELECT * FROM @test
    
    Query:
    SELECT s.SubID,s.String as String_sample,t.String As String_test,s.DetailID,s.Hour,s.Failure,s.Success,t.Property,t.Register,t.Cal_Property,t.Cal_Register
    FROM @Sample s
    JOIN @test t ON s.SubID = t.SubID AND s.String = t.String AND s.Hour >= t.Min_Hour AND s.Hour <= t.Max_hour

    When you run the Query: the results we get are: 

    SubID String_sample String_test DetailID Hour Failure Success Property Register Cal_Property Cal_Register
    899 Apple Apple 1232 00 AM 1 0 1.19 49.07 31.99 4409.32
    899 Apple Apple 1233 00 AM 0 1 1.19 49.07 31.99 4409.32
    899 Apple Apple 1234 00 AM 1 0 1.19 49.07 31.99 4409.32
    899 Apple Apple 1235 01 AM 1 0 1.19 49.07 31.99 4409.32
    899 Apple Apple 1236 01 AM 0 1 1.19 49.07 31.99 4409.32
    899 Apple Apple 1237 01 AM 1 0 1.19 49.07 31.99 4409.32
    899 Orange Orange 1238 00 AM 1 0 3.09 47.07 33.99 4459.32
    899 Orange Orange 1239 00 AM 1 0 3.09 47.07 33.99 4459.32
    899 Orange Orange 1240 00 AM 0 1 3.09 47.07 33.99 4459.32
    899 Orange Orange 1241 01 AM 1 0 3.09 47.07 33.99 4459.32
    899 Orange Orange 1242 01 AM 1 0 3.09 47.07 33.99 4459.32

    Now, I want 4 new columns to be add. With respective to the above result

    1. Sum_Property_Failure: If we see in the above result, we have 2 failures for the String(column) 'Apple' in the Hour '00 AM'  and the Sum of Property(column) for the hour '00 AM' is 3.57, Now I need this 3.57 to be divided by 2 (because I have 2 failures) and where ever we have  failure as 1, the sum 3.57 should be equal distributed with 1.785 and 1.785, so when we do the sum of this it should give us again 3.57

    End result:

    SubID String_sample String_test DetailID Hour Failure Success Property Register Cal_Property Cal_Register Sum_Property-failure
    899 Apple Apple 1232 00 AM 1 0 1.19 49.07 31.99 4409.32 1.785
    899 Apple Apple 1233 00 AM 0 1 1.19 49.07 31.99 4409.32 0
    899 Apple Apple 1234 00 AM 1 0 1.19 49.07 31.99 4409.32 1.785
    899 Apple Apple 1235 01 AM 1 0 1.19 49.07 31.99 4409.32 1.785
    899 Apple Apple 1236 01 AM 0 1 1.19 49.07 31.99 4409.32 0
    899 Apple Apple 1237 01 AM 1 0 1.19 49.07 31.99 4409.32 1.785
    899 Orange Orange 1238 00 AM 1 0 3.09 47.07 33.99 4459.32 4.635
    899 Orange Orange 1239 00 AM 1 0 3.09 47.07 33.99 4459.32 4.635
    899 Orange Orange 1240 00 AM 0 1 3.09 47.07 33.99 4459.32 0
    899 Orange Orange 1241 01 AM 1 0 3.09 47.07 33.99 4459.32 3.09
    899 Orange Orange 1242 01 AM 1 0 3.09 47.07 33.99 4459.32 3.09

    Simlary 3 more column based on Register,Cal_property and Cal_register

    Thanks




    • Edited by SQL_Gun Wednesday, April 24, 2013 6:12 AM edit
    Wednesday, April 24, 2013 6:07 AM

Answers

  • Here is a try for SUM_Property_failure column

    ;WITH CTE_SAMPLE
    AS
    (
    SELECT 
    	s.SubID,s.String as String_sample,t.String As String_test,s.DetailID,s.Hour,s.Failure,s.Success,t.Property,t.Register,t.Cal_Property,t.Cal_Register
    	,SUM(s.Failure) OVER(PARTITION BY s.String, s.Hour) AS COUNT_Failure
    FROM 
    	@Sample s
    		INNER JOIN @test t 
    			ON s.SubID = t.SubID AND s.String = t.String AND s.Hour >= t.Min_Hour AND s.Hour <= t.Max_hour
    )
    SELECT *, 
    	CASE
    		WHEN Failure = 0 THEN 0 
    		ELSE Property / COUNT_Failure
    	END AS Sum_Property_failure
    FROM
    	CTE_SAMPLE


    Krishnakumar S

    • Marked as answer by SQL_Gun Wednesday, April 24, 2013 6:45 AM
    Wednesday, April 24, 2013 6:38 AM

All replies

  • Sorry, there is a misunderstanding, actually we should NOT do the sum, its just the value 1.19 needs to be split into 2 (like: 1.19/2) and this results should be distributed among the rows where the failure is 1, so when you the sum of this, it will be again 1.19

    Result:

    SubID String_sample String_test DetailID Hour Failure Success Property Register Cal_Property Cal_Register Sum_Property-failure
    899 Apple Apple 1232 00 AM 1 0 1.19 49.07 31.99 4409.32 0.595
    899 Apple Apple 1233 00 AM 0 1 1.19 49.07 31.99 4409.32 0
    899 Apple Apple 1234 00 AM 1 0 1.19 49.07 31.99 4409.32 0.595
    899 Apple Apple 1235 01 AM 1 0 1.19 49.07 31.99 4409.32 0.595
    899 Apple Apple 1236 01 AM 0 1 1.19 49.07 31.99 4409.32 0
    899 Apple Apple 1237 01 AM 1 0 1.19 49.07 31.99 4409.32 0.595
    899 Orange Orange 1238 00 AM 1 0 3.09 47.07 33.99 4459.32 1.545
    899 Orange Orange 1239 00 AM 1 0 3.09 47.07 33.99 4459.32 1.545
    899 Orange Orange 1240 00 AM 0 1 3.09 47.07 33.99 4459.32 0
    899 Orange Orange 1241 01 AM 1 0 3.09 47.07 33.99 4459.32 1.545
    899 Orange Orange 1242 01 AM 1 0 3.09 47.07 33.99 4459.32 1.545

      
    • Edited by SQL_Gun Wednesday, April 24, 2013 6:35 AM edit
    Wednesday, April 24, 2013 6:34 AM
  • Here is a try for SUM_Property_failure column

    ;WITH CTE_SAMPLE
    AS
    (
    SELECT 
    	s.SubID,s.String as String_sample,t.String As String_test,s.DetailID,s.Hour,s.Failure,s.Success,t.Property,t.Register,t.Cal_Property,t.Cal_Register
    	,SUM(s.Failure) OVER(PARTITION BY s.String, s.Hour) AS COUNT_Failure
    FROM 
    	@Sample s
    		INNER JOIN @test t 
    			ON s.SubID = t.SubID AND s.String = t.String AND s.Hour >= t.Min_Hour AND s.Hour <= t.Max_hour
    )
    SELECT *, 
    	CASE
    		WHEN Failure = 0 THEN 0 
    		ELSE Property / COUNT_Failure
    	END AS Sum_Property_failure
    FROM
    	CTE_SAMPLE


    Krishnakumar S

    • Marked as answer by SQL_Gun Wednesday, April 24, 2013 6:45 AM
    Wednesday, April 24, 2013 6:38 AM
  • Thanks Krishna looks like I'm getting the results what I'm looking for.
    Wednesday, April 24, 2013 6:45 AM