Need Help Writing a Complex Query

[VIJSQL01]

hi,

I have a table, which contain 8 different columns. I need to figure out the values which are same across selected four columns with in a table. In the same query, i want to see the value in those two columns where values are different.

An example would be as follows,

Table Name: Table1
Columns in the table: A, B, C, D, E, F, G, H

Scenario1:
A = B = C = D and
E <> F

Scenario2:
A = B = C = D and
G <> H

Scenario3:
A = B = C = D and
E = F and
G<>H

Scenario4:
A = B = C = D and
E <> F and
G <> H

Scenario5:
A = B = C = D and
E <> F and
G = H

I was abale to figure out a query, which will bring 4 equall values. But it also brings two equal values. This is not i want. I am interested to see the rows, which has same value for all the four columns and ofcourse it should meet other additional criterias also.
Below is the query which i used,

SELECT  a.*
FROM    RT_State_Master a
        INNER JOIN
        (
            SELECT  Rent_Max , Rent_Min, Rent_Mean, Rent_Median, COUNT(*) totalCount
            FROM    RT_State_Master
            GROUP   BY Rent_Max , Rent_Min, Rent_Mean, Rent_Median
            HAVING  COUNT(*) > 1
        ) b ON  a.Rent_Max = b.Rent_Max AND
                a.Rent_Min = b.Rent_Min AND
                a.Rent_Mean = b.Rent_Mean AND
                a.Rent_Median = b.Rent_Median

PLEASE HELP ME WITH THIS.

Thanks,VIJSQL01

 

[gmmastros]

I don't understand. Can you post some sample data and expected results. It will help in my understanding.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[VIJSQL01]

My apologies, for not being clear.

Here you go now,

The data is as follows.
For the State ZZZZZ, we have Rental property data characteristics such as Rent Mean, Rent Median, Rent Min, Rent Max, pricePerSqft_Mean, pricePerSqft_Median, capRateMean and capRateMedian. The data volume is huge because we have historic and i need check the data for the following scenario.

I need to fetch the data,in the following way.

Scenario1-
where Rent Mean = Rent Median = Rent Min = Rent Max
and pricePerSqft_Mean <> pricePerSqft_Median

Scenario2-
where Rent Mean = Rent Median = Rent Min = Rent Max
and capRateMean <> capRateMedian

Scenario3-
where Rent Mean = Rent Median = Rent Min = Rent Max
and pricePerSqft_Mean = pricePerSqft_Median
and capRateMean <> capRateMedian

Scenario4-
where Rent Mean = Rent Median = Rent Min = Rent Max
and pricePerSqft_Mean <> pricePerSqft_Median
and capRateMean = capRateMedian

ListDate Geo Property_Type BedRooms Rent_Mean Rent_Median Rent_Min Rent_Max pricePerSqft_Mean pricePerSqft_Median capRate_mean capRate_med
201201 xxxxx CONDO 2 766 752 744 838 0.53 0.47 0 0
201201 xxxxx CONDO 3 897 765 749 1393 0.48 0.48
201201 xxxxx SFH 1 602 582 479 804 0.9 0.89 0.06 0.06

please read above sample data as 12 data columns.

Thanks for your help.
-VIJSQL01

 

[gmmastros]

Take a look at this code.

Declare @Temp 
Table	(
		ListDate VarChar(10), 
		Geo VarChar(20),
		Property_Type VarChar(20),
		BedRooms Int,
		Rent_Mean Int,
		Rent_Median Int,
		Rent_Min Int,
		Rent_Max Int,
		pricePerSqft_Mean Decimal(10,2),
		pricePerSqft_Median Decimal(10,2),
		capRate_mean Decimal(10,2),
		capRate_med Decimal(10,2)
		)

--ListDate Geo Property_Type BedRooms Rent_Mean Rent_Median Rent_Min Rent_Max pricePerSqft_Mean pricePerSqft_Median capRate_mean capRate_med
Insert Into @Temp Values('201201','xxxxx','CONDO',2,766,752,744,838, 0.53,0.47,0,0)
Insert Into @Temp Values('201201','xxxxx','CONDO',3,897,765,749,1393,0.48,0.48,NULL,NULL)	
Insert Into @Temp Values('201201','xxxxx','SFH'  ,1,602,582,479,804, 0.9, 0.89,0.06,0.06)
Insert Into @Temp Values('201201','xxxxx','CONDO',1,500,500,500,500, 0.9, 0.89,0.06,0.06)
Insert Into @Temp Values('201201','xxxxx','CONDO',1,500,500,500,500, 0.89, 0.89,0.06,0.06)
Insert Into @Temp Values('201201','xxxxx','CONDO',1,500,500,500,500, 0.9,  0.89,0.05,0.06)

Select	*,
        Case When Rent_Mean = Rent_Median
                  And Rent_Mean = Rent_Min
                  And Rent_Mean = Rent_Max
                  And pricePerSqft_Mean <> pricePerSqft_Median
             Then 1
             Else 0
             End As Scenario1,
        Case When Rent_Mean = Rent_Median
                  And Rent_Mean = Rent_Min
                  And Rent_Mean = Rent_Max
                  And capRate_mean <> capRate_med
             Then 1
             Else 0
             End As Scenario2,
        Case When Rent_Mean = Rent_Median
                  And Rent_Mean = Rent_Min
                  And Rent_Mean = Rent_Max
                  And pricePerSqft_Mean = pricePerSqft_Median
                  And capRate_mean <> capRate_med
             Then 1
             Else 0
             End As Scenario3,
        Case When Rent_Mean = Rent_Median
                  And Rent_Mean = Rent_Min
                  And Rent_Mean = Rent_Max
                  And pricePerSqft_Mean <> pricePerSqft_Median
                  And capRate_mean = capRate_med
             Then 1
             Else 0
             End As Scenario4
From	@Temp

Notice how I put multiple conditions in each case statement to match your requirements. Also notice that I create a table variable and fill it with dummy data. I did this so that it would be easy for me to test the various scenarios.

Please be aware that the previous query will return a row in the output for every row you have in your table. This may not be what you want.

If you want to only return rows where one of the scenarios is satisfied, you could do this instead.

Declare @Temp 
Table	(
		ListDate VarChar(10), 
		Geo VarChar(20),
		Property_Type VarChar(20),
		BedRooms Int,
		Rent_Mean Int,
		Rent_Median Int,
		Rent_Min Int,
		Rent_Max Int,
		pricePerSqft_Mean Decimal(10,2),
		pricePerSqft_Median Decimal(10,2),
		capRate_mean Decimal(10,2),
		capRate_med Decimal(10,2)
		)

--ListDate Geo Property_Type BedRooms Rent_Mean Rent_Median Rent_Min Rent_Max pricePerSqft_Mean pricePerSqft_Median capRate_mean capRate_med
Insert Into @Temp Values('201201','xxxxx','CONDO',2,766,752,744,838, 0.53,0.47,0,0)
Insert Into @Temp Values('201201','xxxxx','CONDO',3,897,765,749,1393,0.48,0.48,NULL,NULL)	
Insert Into @Temp Values('201201','xxxxx','SFH'  ,1,602,582,479,804, 0.9, 0.89,0.06,0.06)
Insert Into @Temp Values('201201','xxxxx','CONDO',1,500,500,500,500, 0.9, 0.89,0.06,0.06)
Insert Into @Temp Values('201201','xxxxx','CONDO',1,500,500,500,500, 0.89, 0.89,0.06,0.06)
Insert Into @Temp Values('201201','xxxxx','CONDO',1,500,500,500,500, 0.9,  0.89,0.05,0.06)

;With Data As
(
Select	*,
        Case When pricePerSqft_Mean <> pricePerSqft_Median
             Then 1
             Else 0
             End As Scenario1,
        Case When capRate_mean <> capRate_med
             Then 1
             Else 0
             End As Scenario2,
        Case When pricePerSqft_Mean = pricePerSqft_Median
                  And capRate_mean <> capRate_med
             Then 1
             Else 0
             End As Scenario3,
        Case When pricePerSqft_Mean <> pricePerSqft_Median
                  And capRate_mean = capRate_med
             Then 1
             Else 0
             End As Scenario4
From	@Temp
Where   Rent_Mean = Rent_Median
        And Rent_Mean = Rent_Min
        And Rent_Mean = Rent_Max
)
Select  * 
From    Data
Where   Scenario1 = 1 or Scenario2 = 1 or Scenario3 = 1 or Scenario4 = 1

Notice that I put the rent conditions in the where clause. I did this because all 4 scenarios have that same condition. Depending on your indexes, this may speed up your query a lot.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[VIJSQL01]

This is great!. This worked. Thanks a ton for providing the solution.