En cours

ASP.NET / VB / MS SQL: Fix Complex SQL Queries

I have a website that is powered by ASP.NET / Visual Basic and Microsoft SQL.

There are two problems that need solving. Both problems involve complicated SQL queries.

This is URGENT and must be completed today.

### TASK 1 ###

Results are not grouped correctly.

For example, a building may have 20 "One Bedroom" units and 10 "Two Bedroom" units.

There are 30 total units but only 2 types. Only the two types should be displayed on my webpage like this:

---------------------------------------------

Unit Type: One Bedroom

Rent: $100 - $200

Size: 500 Square Feet up to 900 Square Feet

Availability: Yes

---------------------------------------------

Unit Type: Two Bedroom

Rent: $200 - $300

Size: 700 Square Feet up to 950 Square Feet

Availability: No

---------------------------------------------

However because the SQL query is not grouped correctly, I get 30 results instead of 2.

### TASK 2 ###

There are two queries and neither of them are correct.

The first one is for the "COMMERCIAL PROPERTY DEVELOPMENTS" section:

Dim sql As String = "select distinct [url removed, login to view], Name, Description,

Address from ipm_project p join ipm_project_field_value fp on

[url removed, login to view] = [url removed, login to view] and fp.Item_value = '1' join

ipm_project_discipline d on [url removed, login to view] = [url removed, login to view] and [url removed, login to view] =

(Select KeyID from ipm_discipline where KeyName = 'Commercial

Projects' ) Where [url removed, login to view] = 'y'"

The second one is for the "RESIDENTIAL PROPERTY DEVELOPMENTS" section.

Dim sql2 As String = "select [url removed, login to view], Name, Description from

ipm_project p join ipm_project_field_value pl on [url removed, login to view] =

[url removed, login to view] and pl.Item_ID = (select Item_ID from

ipm_project_field_desc where Item_Tag = 'tag_PROPLOCATION') and

pl.Item_Value like '%Property Development Page%' Where Available = 'Y'

Order By newid() "

---------------

The problem with the first query is that it shows all available

'Commercial Projects' however it should ONLY show available

'Commercial Projects' if the "Item_Tag" named "tag_PROPLOCATION" is

like '%Property Development Page%'

The problem with the second query is that it shows all available

projects where the "Item_Tag" named "tag_PROPLOCATION" is like

'%Property Development Page%' however it should only show them if they

are 'Residential For Rent'

---------------------------------------

I will provide screenshots and links upon requests. I will answer any quesitons about the requirments that you may have.

Compétences : .NET, SQL, Visual Basic

Voir plus : webpage problem, two string problem, string problems, string problem, rent commercial, rent asp, pl sql and, pl developments, named query, microsoft fix it website, get asp net, fix my feet website, fix it it, answer my query, p sql, microsoft asp.net, SQL Query, sql queries, sql projects, SQL PL SQL , sql microsoft, Sql 2012, queries, pl\sql, pl/sql

Concernant l'employeur :
( 59 commentaires ) Pictou, Canada

N° du projet : #2373906

Décerné à :

Ciofi

I can do this for you in max 2hrs, and I can start now.

70 $ USD en 0 jours
(20 Commentaires)
5.3

4 freelance ont fait une offre moyenne de 78 $ pour ce travail

samitXI

I can do this. Thanks

150 $ USD en 1 jour
(20 Commentaires)
6.6
crypted

please check your pm.

40 $ USD en 0 jours
(28 Commentaires)
5.2
arunwizard

please check pmb

50 $ USD en 1 jour
(0 Commentaires)
0.0