Simplified Query

Hi,

I have a table in a database to calculate a factor. I made a calculation through code vb and datatables, but I think it could simplify a lot if through SQL.
I would like someone to give me some help to simplify with SQL.
In this table for a date, color, density, type and location i need find factor. While date, type and location the values are pre-defined (Location: 01,05,08 and type: type 1, type2), color and density is mesuared (the value changes).


Example
Date: 02/05/2017
Color: 2,13
Density: 4,25
Type: Type1
Location: 05
The factor is 0,06

Example
Date: 02/05/2017
Color: 2,13
Density: 4,81
Type: Type1
Location: 05
The factor is 0,0675

best regards
Attached example table


table.xlsx
rflorencioAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
Not sure of the requirement.

What do we want to do with Factor ? Calculate it, or use it for something ? Or, emulate your VB code but in SQL ?

The table.xlsx seems to have a factor in there for everything, but missing some colors and density.

If you could please explain a bit more, might be able to help you.
0
rflorencioAuthor Commented:
Hi,

I need find fator (The value). Color for ex., if is zero means could be all the colors between ( 0 to 2,1), density if zero could be all values between (0 and 4,1)


Example
Date: 02/05/2017
Color: 0,13
Density: 4,25
Type: Type1
Location: 05
The factor is 0,0595

best regards
0
Éric MoreauSenior .Net ConsultantCommented:
what is your current calculation in VB that gives you the required factor?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Mark WillsTopic AdvisorCommented:
Sorry, I still dont understand your requirement.

We have Factor, Color and Density which you need some kind of calculation for.

If you could explain each component and tell us how it is derived / calculated / the basis of its value / how it relates to the other values....

At the moment it doesnt make sense repeating Color 2.1 Density 4.5 factor 0.06

May as well be Sky is Blue, Rocks are Hard, so, factor is .12345

They seem to be totally unrelated - so - what is their relationship to any calculation and what is the SQL supposed to resolve ?
0
rflorencioAuthor Commented:
Hi,

What I intend to find is just the FACTOR to then make other calculations with it.
Imagine an apple, I'll do quality control.
The calculation order will be date, density, type and location.
Date: This is not always the case but we can imagine, for ex. if the apples are harvested between 01/05 and 05/05 as there is less quantity they value more than those harvested from 05/15.
Color: Knowing the date of the harvest we will check the corresponding color step.
Density: For the date and color found previously, we will find the density according to the hardness of the apple.
Type: Having the date, color and density, we will find the corresponding type (variety).
Location: Lastly knowing all the previous parameters we verified in which place they are. If they are further away they have a smaller factor.
Attached exemple how should calculate factor.

best regards
Explanation.png
0
Éric MoreauSenior .Net ConsultantCommented:
I made a calculation through code vb and datatables

Can you please provide this calculation? For example, it is not clear for us what weight should be given to Location 01 compared to 05.
0
rflorencioAuthor Commented:
Hi,

When I get a container of apples, I already know what location it comes from and what type, in this case location = 05 and type = type1.
Date, color, and density are typically different from container to container. But the sequence of calculations should be, find the date, color, density, type and location.
I supply all these variables to the program, I only intend to use these variables to get the factor.
In a very basic way it would look like this (Considering excel my table to find this):
1- Did a query to return records of the table in this case would be> = 01/05/177 and <15/05/2017 (returns 60 records)
2 - it would query the result of the previous query and filter those records in which the color would be> = 2,10 and <2,21 (15 records)
3 - Did another query for the density in which it would focus on the records of the 2nd query should be> = 4.80 and <5.10 (returns 2 records)
4 - did another query with the result of the 3rd and found the type. (returns 2 records)
5 - Finally did another query based on the 4th query in which it obtained the factor. (Return 1 record)
best regards
0
Éric MoreauSenior .Net ConsultantCommented:
This is not giving us any clues! Once again, and for the last time, where is your VB code?
0
Shaun KlineLead Software EngineerCommented:
It sounds like this is not a calculation, but rather querying a table given known values to find the factor value.

In SQL, you could do this as one query:
SELECT Factor
FROM <your table>
WHERE <date field> >= <start date range> AND <date field> < <end date range>
   AND <location field> = <location value>
   AND <type field> = <type value>
   AND <color field> >= <start color range> AND <color field <= <end color range>
   AND <density field> >= <start density range> AND <density field> < <end density range>

Open in new window

0
rflorencioAuthor Commented:
Hi,

In this code I do not have the color, so I need to see if there is any cleaner way to do it. There is also a field by which it is filtered which is the contract code. There may be different factors for different contracts.

 Public Shared Sub Getfactor(ByRef RowG As DataRow, ByVal TbFactor As DataTable)
        Dim LTypes As New List(Of String)
        Dim mtype As String = If(IsDBNull(RowG("idType")), "", RowG("idType"))
        Dim VBonusQ As Decimal = 0
        Dim CBonusQ As Decimal = 0

        If TbFactor.Rows.Count > 0 Then

            LTypes.Clear()
            For v = 0 To TbFactor.Rows.Count - 1
                Dim xV As String = If(IsDBNull(TbFactor.Rows.Item(v)("TypeName")), "", TbFactor.Rows.Item(v)("TypeName"))
                If xV.Trim.Length > 0 Then
                    If Not LTypes.Contains(xV) Then
                        LTypes.Add(xV)
                    End If
                End If
            Next
            Dim xQueryType As String = " and Type = '' "
            If mtype.Length > 0 Then
                If LTypes.Contains(mtype) Then
                    xQueryType = " and Type = '" & mtype & "' "
                End If
            End If

            Dim xLoc As String = If(IsDBNull(RowG("Location")), "", RowG("Location"))
            If xLoc.Length > 0 Then
                Dim RowA() As DataRow = TbFactor.Select("CodContrat = '" & RowG("CodContrat") & "' and Location = '" &
                                           RowG("Location") & "' " & xQueryType, "date,density")
                If Not RowA Is Nothing Then
                    If RowA.Length > 0 Then

                        If Not IsDBNull(RowG("OrderDate")) Then
                            Dim OrderDate As Date = RowG("OrderDate")
                            Dim Dens As Decimal = FormatNumber(If(IsDBNull(RowG("Density")), 0, RowG("Density")), 3)
                            For i = RowA.Length - 1 To 0 Step -1
                                If RowA(i)("Date") <= OrderDate Then
                                    Dim RowAlineas() As DataRow = TbFactor.Select("CodContrat = '" & RowG("CodContrat") & "' and Location = '" &
                                           RowG("Location") & "' and date = '" & Format(RowA(i)("date"), "yyyy-MM-dd") &
                                           "' " & xQueryType, "Date,density")
                                    For a = RowAlineas.Length - 1 To 0 Step -1
                                        If RowAlineas(a)("density") <= Dens Then
                                            RowG("Factor") = RowAlineas(a)("Factor")
                                            Exit For
                                        End If
                                    Next
                                    Exit For
                                End If
                            Next
                        End If
                    End If
                End If
            End If

        End If

    End Sub

Open in new window


best regards
0
rflorencioAuthor Commented:
Hi Shaun,
Yes in fact. I probably exposed the problem badly.

best regards
0
rflorencioAuthor Commented:
Hi Shaun,

With your query the result is 48 records not one as expected. Because i need know the range before, if consider all records not work.

Example
Date: 02/05/2017
Color: 0,13
Density: 4,25
Type: Type1
Location: 05
The factor is 0,0595

SELECT Factor
FROM factor
WHERE '02/05/2018' >= '01/05/2018' AND '02/05/2018'  < '15/05/2018'
   AND Location = 05
   AND type = 'Type 1'
   AND 0.13 >= 0 AND 0.13 <= 2.31
   AND 4.25 >= 0 AND 4.25 < 5.5

Open in new window


best regards
0
Mark WillsTopic AdvisorCommented:
in SQL you would typically select data from a table. Using the spreadsheet names

select Date,Color,Density,Type,Location,Factor
from yourtablename
where date >= '01/05/2018' AND date  < '15/05/2018'
   AND Location = 05
   AND type = 'Type 1'
   AND color between 0 AND 2.31
   AND density between 0 AND 5.5
ORDER BY Factor desc

Open in new window

And if you want to sort or rank, you can add in constructs like row_number() , top, or add to the bottom, an ORDER BY, or a range of other possibilities
0
Shaun KlineLead Software EngineerCommented:
From the VBA code you posted, it would appear that your SQL code should look something like this:

SELECT Factor
FROM factor 
WHERE Date >= '01/05/2018' AND Date < '15/05/2018'
AND Location = '05'
AND type = 'Type 1'
AND Color >= 0 AND Color <= 2.31
AND Density >= 0 and Density < 5.5
ORDER BY Date, Density

Open in new window


The VBA code uses equals for both Density and Date, so using a range like above may not be correct, and therefore those lines should look like:
Date = '02/05/2018'
Density = 0.13

And if you only want the first result, the first line changes to:
SELECT TOP 1 Factor

Finally, you mentioned a contract. Just add that to the WHERE clause. Something like:
AND CodContrat = '<your contract value here>'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.