[SQL Server] IN and EXISTS |
- IN and EXISTS
- Select only rows with Max Value on a column with multiple tables
- Convert VBA Function to T-SQL or some other object - what would be best?
- Very specific query
- splitting up a start time and end time into different dates
- questions about CREATE LOGIN
- Need some help with retrieving correct Device IP Address
- Backup Automation
- Truncate statement
| Posted: 20 Jun 2013 09:06 AM PDT I'm trying to really [b]understand[/b] the difference between IN and EXISTS. Would the following be fair statements?IN• first evaluates: inner query• evaluates outer query until: every row in outer query's table examined• accepts as argument: a list of literal values or a condition (most often used when argument is a list of literal values)• is faster when inner query's table contains: few records / values• is usually: slowerEXISTS• first evaluates: outer query• evaluates outer query until: inner query finds a record that satifies condition (if inner query doesn't find any records that satify condition: until every row in outer query's table examined)• accepts as argument: a condition• is faster when inner query's table contains: many records• is usually: faster |
| Select only rows with Max Value on a column with multiple tables Posted: 20 Jun 2013 08:04 AM PDT The query returns all the rows needed but you can see the bottom 4 rows are duplicates of each other except for the limit_ver_num field. We want to return the first 8 rows and the two rows where limit_ver_num = 2. We want to return the row with the largest limit_ver_num if there is a duplicate record.So the correct results would include rows 1-9 and row 11 Rows 10 and 12 should not be included in the resultsMy current example is in SQL 2008 but will eventually be used in Oracle if that matters[code="sql"]SELECT sam_typ_tst_typ.SAMPLE_TYPE_NUM, sample_type_name, method_name, method_descr_text, test_type_name, limit_ver_numFROM sam_typ_tst_typ, SAMPLE_TYPE, method, limits, test_type, areawhere sam_typ_tst_typ.sample_type_num = sample_type.sample_type_num andsam_typ_tst_typ.TEST_TYPE_NUM = test_type.test_type_num andsam_typ_tst_typ.METHOD_NUM = METHOD.METHOD_NUM andsam_typ_tst_typ.SAMPLE_TYPE_NUM = LIMITS.sample_type_num ANDsam_typ_tst_typ.TEST_TYPE_NUM = LIMITS.TEST_TYPE_NUM ANDSAMPLE_TYPE.AREA_NUM = AREA.area_num andsam_typ_tst_typ.SAMPLE_TYPE_NUM in (select SAMPLE_TYPE_num from SAMPLE_TYPEWHERE SAMPLE_TYPE_NAME = 'MEHCN_INPROCESS');[/code]The row column here is strictly to help count the rows, it is not an ID columnOUTPUT:[code="sql"][b] STN method_descr_text test_type_name limit_ver_num[/b]1 1967 % WATER IN HCN PRODUCT Water % 12 1967 SULFUR DIOXIDE Sulfur Dioxide 13 1967 PROPIONITIRLE Propionitrile ppm 14 1967 ACRYONITRILE Acrylonitrile ppm 15 1967 ACETONITRILE Acetonitrile ppm 16 1967 % Acidity in HCN HCN PR Acidity 17 1967 NH3 IN EP78 - HC Ammonia ppm 18 1967 % ACIDITY IN HC ST BTM Acidity 19 1967 PACKED COOLER PH pH 210 1967 PACKED COOLER PH pH 111 1967 %ACIDITY IN PACKED PK CLR Acidity 212 1967 %ACIDITY IN PACKED PK CLR Acidity 1[/code]I've tried to get this using:SELECT sam_typ_tst_typ.SAMPLE_TYPE_NUM, sample_type_name, method_name, method_descr_text, test_type_name, MAX(limit_ver_num)FROM sam_typ_tst_typ, SAMPLE_TYPE, method, limits, test_type, areawhere sam_typ_tst_typ.sample_type_num = sample_type.sample_type_num andsam_typ_tst_typ.TEST_TYPE_NUM = test_type.test_type_num andsam_typ_tst_typ.METHOD_NUM = METHOD.METHOD_NUM andsam_typ_tst_typ.SAMPLE_TYPE_NUM = LIMITS.sample_type_num ANDsam_typ_tst_typ.TEST_TYPE_NUM = LIMITS.TEST_TYPE_NUM ANDSAMPLE_TYPE.AREA_NUM = AREA.area_num andsam_typ_tst_typ.SAMPLE_TYPE_NUM in (select SAMPLE_TYPE_num from SAMPLE_TYPEWHERE SAMPLE_TYPE_NAME = 'MEHCN_INPROCESS')GROUP BY sam_typ_tst_typ.SAMPLE_TYPE_NUM, sample_type_name, method_name, method_descr_textWhen I use this query I get this message:Msg 8120, Level 16, State 1, Line 1Column 'test_type.test_type_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.Help please, I do some sql work but this is outside anything I've tried to find before. The above may end up working but I don't understand the GROUP BY function well enough to modify it correctly with so many tables involved. |
| Convert VBA Function to T-SQL or some other object - what would be best? Posted: 20 Jun 2013 08:34 AM PDT An Access Programmer - with Linked SQL Tables - Any ideas on the best way to approach this design would be welcome?Here is a simple function that returns a True/False written in MS Access.It is part (1 of 180) of a Rules Engine. In a MS Access Query for example, the query can call Column1 [ID_Building] Comun2 StakingStatus: Building_Status_Staking_Does_Building_FieldWorkDate_Land([ID_Building]) The output would be something like 343434 TrueFor small pulls of a dozen records, it cost a few mili-seconds. The SQL Server Native Client actually does convert it to some sloppy T-SQL.The problem is - I will soon have about 120 columns with more complex rules. Times around 100 to 500 concurrent users.Should I use a Stored Procedure, a TSQL pass-through query, or other method?This is a Rules Engine under development. The rules are run when one of many key fields across many forms are changed.Each user passes in a single [ID_Building] for a session that has around 30 forms (not all open of course) If they change one value, the rules engine evaluates all of the rules and updates a dashboard that needs to give the appearance of "real-time". (e.g. within 1 to 10 seconds).Think about a tax form: change a dependent from 1 to 2, you are a homeowner, 2 incomes, with rental income, .... - the change from 1 to 2 activates a Dashboard that "recommends you file separately" [code="vb"]Public Function Building_Status_Staking_Does_Building_FieldWorkDate_Land(ID_Building) As Boolean ' Rule Building Status - Staking Staking Status Can NOT have an a Field Work Date of type LAND Dim rstMisc As DAO.Recordset Dim SQLMisc As String ' NOTE Added IP Date afterwards10 Building_Status_Staking_Does_Building_FieldWorkDate_Land = False ' set function to false until proven true SQLMisc = "SELECT APD_FieldWorkDate_2.ID_Buildings, APD_FieldWorkDate_2.ID_Bio_Svy_Type FROM APD_FieldWorkDate_2 " & _ "WHERE (((APD_FieldWorkDate_2.ID_Buildings)=" & ID_Building & ") AND ((APD_FieldWorkDate_2.ID_Bio_Svy_Type) In (15,18)));"30 Set rstMisc = CurrentDb.OpenRecordset(SQLMisc, dbOpenDynaset, dbSeeChanges)40 On Error Resume Next50 rstMisc.MoveLast 60 If rstMisc.RecordCount > 0 Then70 Building_Status_Staking_Does_Building_FieldWorkDate_Land = True80 Else90 Building_Status_Staking_Does_Building_FieldWorkDate_Land = False100 End If110 If Err.Number <> 0 Then120 Err.Clear130 Exit Function140 End If ' A Case statement would be OK too.End Function[/code] |
| Posted: 20 Jun 2013 03:07 AM PDT Hello All,I have no idea at all how to do next:Table:[u]ID_____Type_____ Term[/u]1_____Subject_____ sky2 _____Next _____ earth 2 _____Subject_____video 3_____Test _______black 4_____Subject _____white4_____ Subject _____paper4 _____Forms _____ red4_____Subject _____stone5_____Test ________head6 ____Subject _____ leg6 ____Subject _____ waterQuery should check Subject in Type field and in a case of COUNT(ID) = 1 (and Type = Subject), field Result will be Term.In a case of COUNT(ID) > 1 (and Type = Subject), field Result will be Term + ',' + Term + ',' ... ',' + TermFrom table above:[u]ID_____[/u][u]Result[/u]1_____ Sky2_____ video4_____ white, paper, stone6_____ leg, waterAny idea?Thanks,Brano |
| splitting up a start time and end time into different dates Posted: 20 Jun 2013 12:22 AM PDT what i need to do is find the hours both in the AM and PM for each day. so lets say i have the start date 6/20/2013 8:00:00 end date 6/22/2013 17:00:00 i need the out put for each day in AM and PM hours. so the out put would look like:Date-AM-PM6/20/13 - 4 - 56/21/13 - 4 - 56/22/13 - 4 - 5the problem i'm having is that each date that's read in will not always be the same. some times it will only be one day between, others times it will be three days each with different starting time. currently if it one day i have been using: DateDiff(hh,cast(Convert(varchar(2),start,108)as int),cast(Convert(varchar(2),12,108)as int)) to find the AM hours. Any suggestions would be helpful, thank you |
| Posted: 20 Jun 2013 02:49 AM PDT I know I can create a login as follows:CREATE LOGIN test_loginWITH PASSWORD = 'some_password', DEFAULT_DATABASE = AdventureWorksDW, -- or whatever databaseDEFAULT_LANGUAGE = us_english, CHECK_POLICY = OFF;I also know that, if I'm in SQL Server Management Studio and I enable Query | SQLCMD Mode, instead of CREATE LOGIN test_loginI can use:setvar LoginName "test_login"CREATE LOGIN $(LoginName)in the above SQL code.• What is the advantage of using the latter method?• Is there a way I can use the latter method from outside of SQL Server Management Studio (like when I'm using SQL commands in some app)?• In SQL Server Management Studio, I don't see any indication when SQLCMD Mode is enabled or it's not, so if I forget whether I've enabled it, I just have to play with it to find out for myself. Am I missing something? How can I tell?• I know that in the above example, AdventureWorksDW can be enclosed in square brackets as follows:DEFAULT_DATABASE = [AdventureWorksDW],and the command will still work. But what effect, if any, does this have on the CREATE LOGIN command? |
| Need some help with retrieving correct Device IP Address Posted: 19 Jun 2013 11:43 PM PDT All, we got Inventory system that runs in our environment collecting hardware/software information. I like to setup system, that checks the workstation IP Address/IP Subnet and matches that against our Location table and retrieve the location based on IP Information. With people working from home; Virtual Machines, etc.. it's not that straight forward, as devices now have multiple IP Addresses assigned.Find below DDL, and some scenario's that I need help withDECLARE @IPInfo TABLE( MachineId int NOT NULL, IPAddress varchar ( 255 ) NOT NULL);-- Add some sample dataINSERT INTO @IPInfo( MachineId, IPAddress)VALUES( 1, '143.1.96.29' ),( 1, '192.168.0.12' ),( 2, '143.34.96.0' ),( 2, '172.20.10.0' ),( 3, '155.119.212.212' ),( 4, '192.168.1.55' ),( 5, '172.20.25.55' ),( 6, '10.1.55.212' ),( 6, '155.119.232.22'),( 7, '192.168.2.55'),( 7, '192.44.55.212')select * from @IPInfoMachineId IPAddress1 143.1.96.291 192.168.0.122 143.34.96.02 172.20.10.03 155.119.212.2124 192.168.1.555 172.20.25.556 10.1.55.2126 155.119.232.227 192.168.2.557 192.44.55.212I've tried this using GROUP BY with MIN(IPAddress) Option.this works to some extend, but not in all scenario's.I try to filter out what is known as RFC1918 addresses but only if workstation has multiple IP Addresses, we do have in our environment Desktops and they in most cases only have 1 IP Address.The RFC1918 range is 10.0.0.0/8 172.16.0.0/12 192.168.0.0/24What I try to do is followingWHen Machine has multiple IPAddresses, and need to filter out the RFC1918 addresses somehow, the option with MIN(IPAddress) works for 80% but still need also to have that 20% resolved.SELECT MachineId, Min(IPAddress) As IPAddress FROM @IPInfoGROUP BY MachineIdORDER BY MachineIdMachineId IPAddress1 143.1.96.292 143.34.96.03 155.119.212.2124 192.168.1.555 172.20.25.556 10.1.55.212 NOK (This should be the 155.119.232.22)7 192.168.2.55 NOK (This should be 192.44.55.212)Thx for all the help |
| Posted: 20 Jun 2013 12:17 AM PDT Hello Masters!We have more than 100+ instances across different SQL servers, and currently manually checking whether the backup job failed\success. Now we are planning to automate this monitoring. So need help to know how to know the specific backup job failed ? I little bit know that the backup information stored in system database "msdb", but dont know which table contains that information.Can anyone let me know what table contains that information ? Or what is the query to find out backup job's status ? |
| Posted: 19 Jun 2013 09:32 PM PDT Is it possible to rollback a Truncate statement? |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
