Another NUUBE question! (-_-)

Transact-SQL
1

Based on the amazing help I have received, I'm coming back with three more questions! :smiley:
BTW I do google these things first and try to intuit the answers, but there really is not that much assistive materials on the internet for T-SQL.

  1. How do I - or CAN I, use COUNT in T-SQL? Here is the code I am having trouble with:

WHERE ((([dbo].[tbl_2017_CRD_Trips].Destination)=1002) AND (([dbo].[tbl_2017_CRD_Trips].FlagForRemoval) Is Null)) OR ((([dbo].[tbl_2017_CRD_Trips].FlagForRemoval) Is Null) AND (([dbo].[tbl_2017_CRD_Trips].TripPurpose)=10 Or ([dbo].[tbl_2017_CRD_Trips].TripPurpose)=999))
GROUP BY [dbo].[tbl_2017_CRD_Trips].HouseholdID, [dbo].[tbl_2017_CRD_Trips].PersonID, Now()
HAVING (((Count([dbo].[tbl_2017_CRD_Trips].TripID))>20 Or (Count([dbo].[tbl_2017_CRD_Trips].TripID))>20))
ORDER BY [dbo].[tbl_2017_CRD_Trips].HouseholdID, [dbo].[tbl_2017_CRD_Trips].PersonID;

  1. Relating to this same query, how do I use a HAVING function properly in T-SQL, it seems to throw up its hands at this - also, I am not sure if the GROUP BY is functional in T-SQL since the MS SQL Server Management Studio provides very ambiguous error messages 'Msg 195, Level 15, State 10, Line 8' may not be Line 8... If anyone can answer the HAVING question that would be great! If anyone can also answer the GROUP BY that would be amazing!

  2. Relating to VBA, how is this used in T-SQL via the MS SQL Server Management Studio (or any other)- I do not see how I can place the needed function into the database as it is not a table I can add nor a query I can create....Here, the VBA function is the GetMiles

WHERE (((GetMiles([tbl_2017_CRD_Trips]![OSrchLat],[tbl_2017_CRD_Trips]![OSrchLon],[tbl_2017_CRD_Trips]![DestLat],[tbl_2017_CRD_Trips]![DestLon]))>200) AND ((GetMiles([tbl_2017_CRD_Trips]![OSrchLat],[tbl_2017_CRD_Trips]![OSrchLon],[tbl_2017_CRD_Trips]![DestLat],[tbl_2017_CRD_Trips]![DestLon])*1000)>"200000") AND ((tbl_2017_CRD_Trips.OSrchLat) Is Not Null) AND ((tbl_2017_CRD_Trips.DestLat) Is Not Null) AND ((tbl_2017_CRD_Trips.FlagForRemoval) Is Null) AND ((tbl_2017_CRD_Person.FlagforRemoval) Is Null) AND ((tbl_2017_CRD_Household.FlagForRemoval) Is Null));

Thank you so much, and yes I am studying T-SQL via tutorialspoint but even this great site has sparse info on this subject.
Even if you can only tackle on of my questions it would be appreciated (there is a pack of 20 coders waiting for a database so they can have work-hours, so you are helping more than just a confused nuube analyst!

-EDIT-
I just realized a 4th major question! How can I use one table twice in a query in T-SQL.
e.g. I have a table "Trips" which is all the trips a person made, one column in this is "NextTripID" and often I have to link the NextTripID (TripID is the key in this table) to TripID in a second Trips table to do a validation such as "two trips to Home in a row". In SQL its just tbl_1, but if that table does not exist I do not expect T-SQL would know what I am requesting. An example join in SQL would be as below:

SELECT tbl_2016_TTS_Trips.TripID, tbl_2016_TTS_Trips_1.TripID
FROM tbl_2016_TTS_Trips INNER JOIN tbl_2016_TTS_Trips AS tbl_2016_TTS_Trips_1 ON tbl_2016_TTS_Trips.NextTripID = tbl_2016_TTS_Trips_1.TripID;

I know I am asking alot, even a hint in the right direction would be greatly appreciated!

2

Please use a SQL formatter and post your code as code (the less than, greater than icon above:

image

And please start using table aliases and stop parenthesizing expressions:

(tbl_2017_CRD_Trips.DestLat) NO!

t.DestLat YES!

It just makes things easier to read.

Q1. You can use count the way you show in your code. If it doesn't work, please paste the whole query (formatted, using table aliases and without extra parens)

Q2. Having is like WHere but after grouping. Again post your whole query and all the error messages

Q3. (I don't relate to VBA!) VBA is not used in SSMS or any other SQL Server IDE. Depending on what GetMiles does, you may be able to recode it in SQL and create a SQL function.

Q4. That's what table aliases are for

3

Looks like it just takes the Lat/Long for two points and calculates the distance between then. That's easy enough in SQL, or you could do it at the front-end (e.g. in the report writer).

EDIT: Ignore that, its in the WHERE clause, you definitely need to do it in SQL and not in the report writer.

Slightly depends whether GetMiles just uses Pythagoras (if so, really easy, but somewhat inaccurate (nearer the Poles IIRC) or if it uses a Levenshtein algorithm (which takes the curvature of the earth into account).

There are formulae for both somewhere on SQLTeam, if you can't find them I should be able to.

There are also Geography Coordinate data types in SQL. They make mincemeat of this type of thing, so if that is something you need (rather than just an occasional need) you might want to look at that in more detail.

Beware that the maths is somewhat hefty ... so doing

WHERE GetMiles(MySearchLat, MySearchLong, DestinationLat, DestinationLong) )> 200

the performance will be horrific - the Function (and the maths behind it) has to be applied to every row in the database :frowning: to decide if it is more than 200 miles apart ... and there are TWO GetMiles() functions in your code :frowning:

If you don't go down the Geography Coords route a better way is:

Calculate the Top Left and Bottom Right Lat/Long of a square/rectangle which contains your 200 mile radius circle

Find all locations that have Lat/Long WITHIN those boundaries

If you have an Index on the Lat/Long for the table containing the Locations it will take "no time at all" to find them, and THEN filter that further with your more detailed, and more maths-heavy, Pythagoras or Levenshtein formula

Beware of using a Scalar User Defined Function in SQL - their performance is dreadful, particularly in a WHERE clause (prevents the query being SARGable)

4

If we can see the actual code for the GetMiles function - that would help determine the next course of action. If it can be coded as an inline-table valued function it could then be included using a CROSS APPLY which could improve the performance over a standard scalar function.

If GetMiles is actually VBA - and not SQL - and there isn't any easy way to convert it to SQL then it is possible to write it as a VB.NET or C# CLR function. The function itself would perform well - but it will not be SARGable...but that may not be so much an issue because you probably won't have any indexes available across these values anyways.

With that said - you might want to consider creating a computed column on your table that utilizes the function (once written) to generate the results as the data is entered. You can then persist that column and index it - then your queries would not have to calculate the values every time and they would be much faster (and SARGable).

You also have a duplicate check which is redundant...if GetMiles is greater than 200 then GetMiles * 1000 will be greater than 200000 - always. Also - due to NULL propagation you really don't need to check for NULL for LAT...the function should take care of checking for NULL and return a NULL if any of the parameters passed in are NULL. If this is a computed column - the value would be NULL and WHERE {MilesColumn} > 200 would not be true.

5

An office-mate provided me with SQL to perform the same function:

SELECT 
lat1Radians,
lon1Radians,
lat2Radians,
lon2Radians,
Round(2 * D.DerivedAsin * (6371), 2) AS Distance
FROM(
	SELECT
		lat1Radians,
		lon1Radians,
		lat2Radians,
		lon2Radians,
		AsinBase / (Power((AsinBase * AsinBase + 1),0.5)) AS  DerivedAsin
	FROM(
		SELECT 
		lat1Radians,
		lon1Radians,
		lat2Radians,
		lon2Radians,
		Sin(power((Sin((lat1Radians - lat2Radians) / 2)  + Cos(lat1Radians) * Cos(lat2Radians) * Sin((lon1Radians - lon2Radians) / 2)),0.5)) AS AsinBase
		FROM (
			SELECT 
			([OSrchLat] / 180) * 3.14159265359 AS lat1Radians,
			([OSrchLon] / 180) * 3.14159265359 AS lon1Radians,
			([DSrchLat] / 180) * 3.14159265359 AS lat2Radians,
			([DSrchLon] / 180) * 3.14159265359 AS lon2Radians
			FROM [dbo].[tbl_2017_CRD_Trips]
			) T
		) C
	) D

So now I'm wondering how to nest it into a select statement- something like pseudocode:
SELECT * from tbl_name WHERE *GetMiles as applied to a field* >100

6

I rewrote the above to use common table expressions - but it could be written the same as you have above...the key here is to remove the reference to the table and just reference the variables...

 Create Function dbo.fnGetMiles (
        @OSrchLat decimal(9,6)
      , @OSrchLon decimal(9,6)
      , @DSrchLat decimal(9,6)
      , @DSrchLon decimal(9,6)
        )
Returns Table
   With Schemabinding
     As
 Return

/*
 Usage:

 Select *
   From dbo.BaseTableWithLatLon         bt
  Cross Apply dbo.fnGetMiles(bt.lat1, bt.lon1, bt.lat2, bt.lon2)        m
  Where m.Distance > 100;

*/

   With inputValues (lat1Radians, lon1Radians, lat2Radians, lon2Radians)
     As (
 Select radians(@OSrchLat)
      , radians(@OSrchLon)
      , radians(@DSrchLat)
      , radians(@DSrchLon) 
      --, (@OSrchLat / 180) * 3.14159265359  As lat1Radians
      --, (@OSrchLon / 180) * 3.14159265359  As lon1Radians
      --, (@DSrchLat / 180) * 3.14159265359  As lat2Radians
      --, (@DSrchLon / 180) * 3.14159265359  As lon2Radians
        )
      , asinBaseCalc (lat1Radians, lon1Radians, lat2Radians, lon2Radians, AsinBase)
     As (
 Select iv.lat1Radians
      , iv.lon1Radians
      , iv.lat2Radians
      , iv.lon2Radians
      , sin(power((sin((iv.lat1Radians - iv.lat2Radians) / 2) 
        + cos(iv.lat1Radians) * cos(iv.lat2Radians) * sin((iv.lon1Radians - iv.lon2Radians) / 2)), 0.5))
   From inputValues             iv
        )
      , derivedAsinCalc (lat1Radians, lon1Radians, lat2Radians, lon2Radians, DerivedAsin)
     As (
 Select bc.lat1Radians
      , bc.lon1Radians
      , bc.lat2Radians
      , bc.lon2Radians
      , bc.AsinBase / (power((bc.AsinBase * bc.AsinBase + 1) , 0.5))
   From asinBaseCalc            bc
        )
 Select d.lat1Radians
      , d.lon1Radians
      , d.lat2Radians
      , d.lon2Radians
      , round(2 * d.DerivedAsin * (6371), 2) As Distance
   From derivedAsinCalc          d;
     Go

I also am utilizing the built-in radians function instead of using the calculation you have...you should validate the results are as expected. You can easily modify this back to using your calculation...

You can easily convert your original code to a function also - just remove the table reference and change the columns to the parameters.