I have two tables with the same structure / different data. I run a query between the tables to find the different records for the out put. When the values of the records or NULL, I do not get the output.
Code:
SELECT dbo.Closest3StoresB_RBT.CustomerPost, dbo.Closest3StoresB_RBT.ClosestStore1,
dbo.Closest3StoresB_RBT.ClosestStore2,
dbo.Closest3StoresB_RBT.ClosestStore3
FROM dbo.Closest3StoresB_RBT INNER JOIN
dbo.Closest3StoresB_OLD_RBT ON dbo.Closest3StoresB_OLD_RBT.CustomerPost =
dbo.Closest3StoresB_RBT.CustomerPost
WHERE
(dbo.Closest3StoresB_OLD_RBT.ClosestStore1 <> dbo.Closest3StoresB_RBT.ClosestStore1) OR
(dbo.Closest3StoresB_OLD_RBT.ClosestStore2 <> dbo.Closest3StoresB_RBT.ClosestStore2) OR
Hi , I need to compare two date fields in two different tables.One of the field is varchar(8) and other is dateime.When there is a date in one field and NULL in other field , how do I compare these two vales?
I'm having some issues with what seems to be a simple update statement but is giving me grief when one or both of the columns I'm comparing are null. My statement (simplified) is as follows:-
UPDATE TAB_A SET TAB_A.TRADCODE = TAB_B.TRADCODE FROM TADS_STAGE.DBO.UNCLBRAMDEPT TAB_B JOIN TADS.DBO.UNCLBRAMDEPT TAB_A ON TAB_B.BRANCODE = TAB_A.BRANCODE AND TAB_B.MERDCODE = TAB_A.MERDCODE AND ( TAB_B.TRADCODE <> TAB_A.TRADCODE )
If either of the TRADCODE fields (or both) are null then the comparison fails to return the row to update. I've tried setting the ANSI_NULLS setting to off, this has no effect, presumably because it will only work when comparing a column to a variable or evaluating if the column is null itself.
I've considered using ISNULL, but if one of the columns happens to contain the value that I specify as the replacement value then the comparison will result true and not include the row.
I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?
I am trying to think my way through a solution which I believe others have probably come across... I am trying to implement a matching routine wherein I need to match an address against a high value and a low value (or, for that matter an input date vs. a start and end date) to return the desired row ... i.e. if I were to use a straight vb program I would just use the following lookup:
" WHERE zip_code = @zip_code AND addr_prim_lo <= @street_number AND addr_prim_hi >= @street_number " & _
" AND addr_prim_oe = @addr_prim_oe AND street_pre = @street_pre AND street_name = @street_name " & _
" AND street_suff = @street_suff AND street_post = @street_post " & _
" AND (expiry_date = '' OR expiry_date = '00000000' OR expiry_date > @expiry_date)" & _
" GROUP BY fire_ID, police_ID, fire_opt_in_out, police_opt_in_out"
My question, then, is how would you perform this type of query using a lookup / merge join or script? I have not found a way to implement a way to set the input columns? I can set the straight matches without a problem, i.e. lookup zip code = input zip code, but can't think of the correct way to set comparisons, i.e. lookup value 1 <= input value AND lookup value 2 >= input value
SELECT * FROM PABX INNER JOIN LOGIN ON (PABX.COD_CLIENTE = LOGIN.COD_CLIENTE) AND LEFT(LOGIN.TELEFONE1,3) = LEFT(PABX.NRTELEFONE,3) LEFT JOIN AUXILIAR ON (AUXILIAR.ORIGEM=LOGIN.LOCALIDADE) WHERE pabx.COD_cliente = 224 and SUBSTRING(PABX.NRTELEFONE,4,1) NOT IN ('9', '8', '7') AND LOGIN.UF = RIGHT(PABX.LOCALIDADE,2) AND LOGIN.LOCALIDADE <> PABX.LOCALIDADE AND PABX.CLASSIFICA IS NULL AND PABX.LOCALIDADE <> AUXILIAR.DESTINO AND (BLOQUEADO = 0 OR BLOQUEADO IS NULL)
But It has a problem because when AUXILIAR.DESTINO returns null (it means there is no registry) the condition AND PABX.LOCALIDADE <> AUXILIAR.DESTINO doesn't work, like 'SAO PAULO' is different from 'NULL' but for my query no it's not even equal, and this condition ommit the results....how can I solve it ?
PS: Both auxiliar.destino and pabx.localidade is varchar(255)
Hello! I have a field "End" in my database that is mapped as DateTime and allows nulls. Now I want to do a SQL-Select (in a SqlDataSource) like SELECT * FROM My_Table Where (([End] = @EndDate) OR ([End] = null)) @EndDate is a valid DateTime, but the second OR condition doesn't work. What is the best way to check if the [End]-field is empty or null? Thank you very much!
I need to check Table1 by Table2 only on NOT NULL cells and if all of them in the row match do not return that row as the result. In this case it will be:
SELECT a.Server, a.Databases, a.Users, a.Names FROM Table1 EXCEPT SELECT ISNULL(b.Server,c.Server), ISNULL(b.Databases,c.Databases), ISNULL(b.Users,c.Users), ISNULL(b.Names,c.Names) FROM Table2 AS a, Table1 AS c
But for many rows (like 100 000) it takes ages to get results, any better way to work on this?
I would like to compare some values in two columns which are in the same table. I want to check that there are no differences between the values if the ID is Test1 and Test2
Example table
IDValue1Value 2 TEST1HouseTango TEST2HouseTango with test as ( select * from ExampleTable where ID= 'TEST' ),
There are two tables in my Database, tb1 and tb2 which both have the same attribute ID. I would like to ensure that there is nothing in ID in tb1 which is not listed in ID in tb2, can anyone help?
I have a table called WorkItem. It models a chunk of work done duringa working day.It has two columns that I'm interested in:Start (smalldatetime) - the TIME the work block is begunDuration (int) - the duration in minutes of the work block.In another table called OvertimeRates I have information about ratemultipliers and a column that tells me the TIME that the ratemultiplier kicks in.e.g.OTRateBegins (smalldatetime)In terms of calculating whether a particular work block starts afterthe OTRateBegins, I could (I presume) do something like:If CONVERT(smalldatetime, Start, 108) > CONVERT(smalldatetime,OTRateBegins, 108)However, would I be better off using DATEPART functions to get the hourand minute parts of both the Start and OTRateBegins, and using theminstead? For some reason, (probably paranoia!), I am suspicious of theCONVERT function.Apologies for not posting DDL, but I felt that the situation didn'treally warrant it.ThanksEdward
I've been working with T-SQL in a MSSQL Server Management Studio (2005) for about a week now. I've been trying to convert some horribly written VB code from a MS Access DB over to SQL so it can be automated on a SQL backend.
Most of the learning process and coding has gone surprisingly well. The problem is with comparing some data to determine which one needs to be flagged.
Three tables to note in bold, with notable fields in italics below them:
EmployeeData HRID (identity)
ResourceAllocation ID (identity) [Last Name] (linked to HRID) Project [Resource Start Date] [Resource End Date] [Percent Utilization]
tblHCvalues RAID (linked to ResourceAllocation.ID) a monthyear and quarteryear for every month and quarter from 2012-2014. IE january12, february12, 1q12, 2q13, etc...
And yes, there are probably a thousand ways to optimize that tblHCvalues, but I'll ask about that later. Just work with the structure I have
Here's how it works: Each employee's data and unique HRID is in the EmployeeData tableAn employee can be on one or multiple projects at any timeThose projects are stored per project in the ResourceAllocation table with a link to the Employee's HRID, and all the other information listed aboveEven though an employee might be on two projects, they can only count for headcount on one project.
We use rules that compare the percent of work being done on a project, and the start and end dates of the employee (resource) on that project to determine which project should be counted for Headcount. The code uses a cursor to go through each HRID, and then pull up all the ResourceAllocation records associated with it.Run the rules to determine which ResourceAllocation record counts toward headcountA stored procedure then runs that fills out the tblHCvalues in the way we want for the project we want
All of it works, except for the rules that compare the things, so that's what I want to focus on in this thread. How do I write these rules:
Here are the rules, and they should work for any number of multiple resource allocations for one employee:
Choose the ResourceAllocation with the greatest [Percent Utilization]If the top ResourceAllocations have equal [Percent Utilization], choose the ResourceAllocation with the earliest [Resource Start Date]If the [Percent Utilization] and the [Resource Start Date] are equal, choose the latest [Resource End Date]If all three fields are equal, choose the first ResourceAllocation (aka, screw it and pick one at random)
I'm sure I could use a bunch of IF statements to compare it all, but even that is complicated to think about. There has to be an easier way, right?
ID | AMOUNT | VAT ( COST TABLE ) 1 |20.125 |4.821 .... different to ID 1 in INV Table 2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
ID | AMOUNT | VAT ( COST TABLE ) 1 |20.125 |4.821 .... different to ID 1 in INV Table 2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
ID | AMOUNT | VAT ( COST TABLE ) 1 |20.125 |4.821 .... different to ID 1 in INV Table 2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
SELECTIndustry, 100.0 * SUM(CASE when ceoischairman = 'yes' then 1 else 0 end) / COUNT(DISTINCT CompID) AS [YesPercent], 100.0 * SUM(CASE when ceoischairman = 'no' then 1 else 0 end) / COUNT(DISTINCT CompID) AS [NoPercent] FROMTCompanies GROUP BYIndustry ORDER BYIndustry
This code above is working as I need it but I need to insert some additional functionality. Thanks
I need to add something like this:
IF YesPercent > NoPercent UPDATE tableX SET CEOIsChairman='Yes' WHERE Industry='<the industry value being evaluated>' Else If NoPercent > YesPercent UPDATE tableX SET CEOIsChairman='No' WHERE Industry='<the industry value being evaluated>' Else UPDATE tableX SET CEOIsChairman='Equal' WHERE Industry='<the industry value being evaluated>' End
Im Working with stored procedure. How can i compare Columns with specific values. I want to get the greater values of those column and inserted it to other columns. i want something like these CASE WHEN a> b,c,d THEN a WHEN b> a,c,d THEN b WHEN c> a,b,d THEN c WHEN d> a,d,c THEN d
is there any ways to implement this? i got an error.. thanks please help..
I would be very grateful if someone could help me. I have very little SQL knowledge and would like a push in the right direction:
I have a application that receives GPS lattitude and longitude values. I was originally using file base system where I would load all the points from the file into memory and do the calculations in the software but I now would like to use SQL Server Express.
I have created the tables:
[Table 1] GPS Points: id int Primary key latitude real longitude real desc nvarchar(128)
[Table 2] GPS Locations (comprises of one or more GPS Points from [1] above) to form a boundary: GPS Locations database structure is: locationId int primary key gpspoint1 int gpspoint2 int gpspoint3 int gpspoint4 int desc nvarchar(128)
Table 2 contains up to 4 gps points ids from table 1 and GpsPoints1-4 can be null.
I have created the tables and I have inserted GPS values into both tables.
My problem: I am trying to compare the current Lat/Lon (which are passed into the SQL fuction as two real datatype value) and I want to return all Table 2 locationId's where the current Lat/Lon are withing GPSPoints1-4 area. gpspoint1, gpspoint2, gpspoint3, gpspoint4 reference an id from Table 1. I am not sure how I can do this?
I have implemetned a SQL function that returns all points from Table 1 that are within the predefined radius of 1.5 miles from the current Lat/Lon values passed in.
where 3963.191 is earths radius in miles, radius is 1.5, @LatDec is the current latitude and @LonDec is the current longitude
Uses a lot of Trig and (for me) is quite complicated. This works very well and is very accurate. It also only uses a single table. I want to be able to now look for the current position within a boundary of 3 or more positions from Table 1 and this is where I am struggling?
Also, any suggestions on how I could do this better would be very much appreciated?
Hello I have two matrices. One contains sales data for the current year, the other prior year. Both matrices use different data sets
I'd like to compare the two - possibly by creating a third matrix that subtracts prior year from current year.
Any ideas? When I create a third matrix and substitute a formula like =sum(values, "Data source for matrix 1") - sum(values, "Data source for matrix 2"), the resultant matrix subtracts the grand total from the first matrix - not the individual "cell".
I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?
I have a table Tbl1 which has 7 columns.This table will be my base table.By using our current application version ,i'll be creating record for Client1. Col1 will have value that application will generate(id).Then i'll be creating Tbl2 with same columns.Then i'll be creating same record for Client1 again ,using our new application version .Col1 will have different (id)value.I would like to compare the rest of the columns if there is any discrepancy caused by new version(columns Col2 -Col7).If there are same ,don't show me anything.
I have a table of raw data with supplier names, and i need to join it to our supplier database and pull the supplier numbers.
The issue is that the raw data does not match our database entries for these suppliers; sometimes there are extra periods, commas, or abbreviations (i.e. FedEx, FederalExpress, FedEx, inc.) etc. I'm trying to create a query that will search for entries that are similar.
I tried setting a variable to be equal to the raw data field, and then using a LIKE '%@Variable%' to try and return anything that would contain it, but it didnt return any rows.
I need to compare two consecutive rows (if BEGDA of second row is 1 day greater than ENDDA of first row then I need to pick First row BEGDA and 2nd row ENDDA)
I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?
can somebody explain me how I can assign a NULL value to a datetime type field in the script transformation editor in a data flow task. In the script hereunder, Row.Datum1_IsNull is true, but still Row.OutputDatum1 will be assigned a value '0001-01-01' which generates an error (not a valid datetime). All alternatives known to me (CDate("") or Convert.ToDateTime("") or Convert.ToDateTime(System.DBNull.Value)) were not successful. Leaving out the ELSE clause generates following error: Error: Year, Month, and Day parameters describe an un-representable DateTime.
SELECT DSNew, DTTM, RQDT FROM dbo.Feb INNER JOIN DMSEFL ON ACTR = DSNew where cast(DSNew as varchar(20)) = cast(ACTR As varchar(20)) If I run the above query I get zero recs back.
If I substitute a Value then I get the desired results (ie. where DSNew = '93235500') or if I enter (ACTR = '93235500') or if I put (where DSNew = '93235500' AND ACTR = '93235500')
Can anyone suggest a reason why this is happening. I know the records exist on both tables I ran the query in Acess and got the desired resutls.
HiThe scenario:The price of products are determined by size.I have a Prices table that contains 3 columnsWidth Length and Price.User inputs their own width and length values as inWidth and inLength.It is unlikely that these values will exactly match existing lengths and widths in the price table.I need to take these User Input values and round them up to the nearest values found in the Prices table to pull the correct price.What is the most efficient way of achieving this?Thanks for your time.C# novice!
I have the following insert statement in place:Insert WPHPayments(constituentID, constituentName, campaignYear, fundID, fundDescription, dateAndTimeEntered, amount)Select gt.constituentID, gt.constituentName, gt.campaignYear, gt.fundID, gt.fundDescription, gt.dateAndTimeEntered, gt.amountFrom GTPROCENTERFUNDPAYMENTEXTRACT gt, WPHExtractWhere gt.constituentID = WPHExtract.wph_constIDI want to insert all of the values that are in the GTPROCENTERFUNDPAYMENTEXTRACT table that have the same constituentID that as the records in the WPHExtract table. Am I just missing something becasue the syntax is showing that everytihing is correct however there is nothing comming back in the result set. Thanks in advance everyone. Regards,RB
how to get sumofshares (#tab1) and TotalOutStanding(#tab2) summ up with these values,
MasterKey (#tab1) and IssueKey (#tab2) are like primary key and foreign key
so the request is
need to calculate, sumofshares (#tab1) and TotalOutStanding(#tab2) as below
1)ShareBenefist = U and year( request_dat) in (2012 , 2103) and (Name for 2012 should match with 2013 name and 2012 Acctno should match with 2013 accounno) in (#tab1) then '2012 and 2013 accts UN Veriverted' 2)ShareBenefist = V and year( request_dat) in (2012 , 2103) and (Name for 2012 should match with 2013 name and 2012 Acctno should match with 2013 accounno) in (#tab1) then '2012 and 2013 accts Veriverted' 3)ShareBenefist = N and year( request_dat) in (2012 , 2103) and (Name for 2012 should match with 2013 name and 2012 Acctno should match with 2013 accounno) in (#tab1) then '2012 and 2013 accts NONVERT' 4)year( request_dat) =2102 and Name and Acctno not match with 2013 account name and acctno (#tab1) then '2012 last year accounts' 5)year( request_dat) = 2013 and Name and Acctno not match with 2013 account name and acctno (#tab1) then '2012 This year accounts'
for ex 1) the below accounts in #tab1 has both 2012 and 2013 and acctno same in both years and name is same in both years so it is condired as
insert into #tab1 values (1012, 100,'Tom',800, '08/22/2013')
for ex 2)
insert into #tab1 values (1013, 101,'Bat',550, '09/15/2013')
for ex 4) 2012 records there is not match acctno and name in 2013 recods
insert into #tab1 values (1002, 102,'Kit', 1600, '06/12/2012')
for ex 5) 2013 records there is no match of name and acct no with 2012 records
insert into #tab1 values (1010, 104,'Sim',200, '04/21/2013') insert into #tab1 values (1014, 100,'Pet',200, '02/21/2013') insert into #tab1 values (1016, 110,'Sun',800, '03/22/2013') insert into #tab1 values (1017, 111,'Bet',550, '12/15/2013')
Expected Results (just for format)
AcctTypeDescription,SumofShares, OtotalutStand '2012 and 2013 accts UN Veriverted',2700,234 '2012 and 2013 accts Veriverted' ,2890,234 '2012 and 2013 accts NONVERT' ,4533,325 '2012 last year accounts' ,2334,567 '2012 This year accounts' ,2222,877
My query "select blah, blah, rank from tablewithscores" will return results that can legitimately hold nulls in the rank column. I want to order on the rank column, but those nulls should appear at the bottom of the list
