Transact SQL :: Compare Two Table With Multiple Date Comparison
Oct 20, 2015
I need to take all records from table @A where ID = 1. Also i need to process the records with datewise from table @A. Here is the table structure
DECLARE @A TABLE (ID INT, ACCOUNT VARCHAR(10), EFFDT DATE)
INSERT INTO @A VALUES (1,'AAA','2015-10-01')
INSERT INTO @A VALUES (1,'BBB','2015-10-01')
INSERT INTO @A VALUES (1,'CCC','2015-10-01')
INSERT INTO @A VALUES (1,'AAA','2015-10-05')
INSERT INTO @A VALUES (1,'DDD','2015-10-01')
INSERT INTO @A VALUES (2,'AAA','2015-10-02')
INSERT INTO @A VALUES (2,'BBB','2015-10-02')
INSERT INTO @A VALUES (2,'CCC','2015-10-02')
INSERT INTO @A VALUES (2,'DDD','2015-10-02')
[code]...
how to achieve this in SQL query, i cannot use CTE or temp table as i need to use this code in another tool, it has to be single query, can use subquery or join would be better.
I would like to return the nearest date of Table B in my table like for
ID W001 in table B should return ID A002 CreatedDatetime: 2014-06-03 20:05:48.000 ID W002 in table B should return ID A004 CreatedDatetime: 2014-06-04 01:05:48.000
I upload data from a Txt File(Txt_Temp) where I have VinNumber with 6 digits. Another table name Resrve_Temp1 where I have Vinumber with 17 digit. Now I need to update the vinnumber 6 digit to 17 digit or to new column in Txt_temp.
Txt_Temp - Table
I tried this code with no succes and only one row is updating
update Txt_Temp Set Txt_Temp.Vinnumber=dbo.R_ResrvStock.Vin from dbo.R_ResrvStock inner join Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=Txt_Temp.VinNumber
OR Add this code in viewÂ
Select dbo.R_ResrvStock.Vin,R_Txt_Temp.Vinnumber,R_Txt_Te mp.Model_Code from dbo.R_ResrvStock inner join R_Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=R_Txt_Temp.VinNumber
A vehicle loading confirm after that what time its gated out so i want to take the time duration between finish loading and gate out, find sample table records , i want to take more than 5 hrs difference between finish loading and gate out.
I have a scenario where I need to compare a single DateTime field in a stored procedure against multiple values passed into the proc.So I was thinking I could pass in the DateTime values into the stored procedure with a User Defined Table type ... and then in the stored procedure I would need to run through that table of values and compare against the CreatedWhenUTC value.I could have the following queries for example:
WHERE CreatedWhenUTC <= dateValue1 OR CreatedWhenUTC <= dateValue2 OR CreatedWhenUTC <= dateValue 3
The <= is determined by another operator param passed in. So the query could also be:
WHERE CreatedWhenUTC > dateValue1 OR CreatedWhenUTC > dateValue2 OR CreateWhenUTC > dateValue3 OR CreateWhenUTC > dateValue4
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail @subject = 'Job Summary', @profile_name = 'SQL SMTP',    [code]....
So my data column [EODPosting].[MatchDate] is defined as a DATE column. I am trying to SELECT from my table where [EODPosting].[MatchDate] is today's date.
Is this not working because GETDATE() is like a timestamp format? How can I get this to work to return those rows where [EODPosting].[MatchDate] is equal to today's date?
Question: How to determine if a date value was between one of the date periods that appear in multiple rows?
Background: We have a table of "license valid" periods, wherein each license can have one or more rows. Â (As you know, a driver's license can be started, expired, renewed, suspended, reinstated, revoked, etc.) Â Instead of of having a license activity table--from which valid license periods could be extrapolated--we store just the periods for which a license was valid.
My task is to take a list of licenses and specific dates and determine if each license was valid as of that date, returning either true or false. Â What is the best way to accomplish this?
DECLARE @ValidityInQuestion TABLE ( LicenseID int , DateValidityInQuestion date);
DECLARE @LicenseValidPeriods TABLE ( LicenseID int , BeginDate date , EndDate date);
[Code] ...
 How then do I query both tables in order to get the same result that results from the following query?
SELECT 12345 AS LicenseID , '2015-01-15' AS DateValidityInQuestion , 1 AS LicenseActive UNION SELECT 67890 , '2015-02-04' , 0;
I assume I need to join on the LicenseID columns, but my understanding stops there.
There are 3 tables Property , PropertyExternalReference , PropertyAssesmentValuation which are common for 60 business rule
SELECT   PE.PropertyExternalReferenceValue  [BAReferenceNumber] , PA.DescriptionCode   [PSDCode] , PV.ValuationEffectiveDate   [EffectiveDate] , PV.PropertyListAlterationDate   [ListAlterationDate]
[code]....
Can we push the data for the above query in a physical table and create index to make the query fast rather than using the same set  tables multiple timesÂ
I have a table called ADSCHL which contains the school_code as Primary key and other two table as
RGDEGR(common field as SCHOOl_code) and RGENRl( Original_school_code) which are refrencing the ADSCHL. if a school_code will be updated both the table RGDEGR (school_code) and RGERNL ( original_schoolcode) has to be updated as well. I have been provided a new data that i have imported to SQL server using SSIS with table name as TESTCEP which has a column name school_code. I have been assigned a task to update the old school_code vale ( ADSCHL) with new school_code ( TESTCEP) and make sure the changes happen across all 3 tables.
I tried using Merge Update function not sure if this is going to work.
Update dbo.ADSCHL SET dbo.ADSCHL.SCHOOL_CODE = FD.SCHOOL_Code FROM dbo.ADSCHL AD INNER JOIN TESTCEP FD ON AD.SCHOOL_NAME = FD.School_Name
Our dev team wants to introduce a complex key, which is made up of  EventDate        datetime2(5)     = ‘2015-10-22 10:19:59.12345’ ConsumerID     bigint               = 1234 SiteID              tinyint              = 15
EventDate will be converted to a bigint and then to a hex value
The hex values will then be concatenated together and stored as a string (varchar). The application will handle the creation of the complex key
  = ‘1BF714C8A0D4F699-4D2-F’
I am trying to argue against this approach and get them to store the values in their native form, in separate columns as a natural key.
To make matters worse, we need to use partitioning, where the partition boundary will be on a hexed datetime2(5) at weekly intervals. I was really hoping a proof of concept would show that hex string comparison could potentially put a row under a wrong partition, but so far, on an 8 million rowset, it is working fine.
For example, in the proof of concept, right partition boundary:
= '1BF398C373960580-2FAF3003-2'  (2014-12-14 23：59：00.00000-800010243-2）
The prior partition boundary is 1
= 'BF3983242B9C000' (2014-12-08 00:00:00.00000) Â How is this working, given that the string lengths (varchar) are different and the row value contains dashes and the partition boundaries are smaller in length without dashes? How varchar string comparison is working here?
SO when i try to load from  Master table to parent and child table i am using using expresssion likeÂ
select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ONÂ A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER ANDÂ A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) Â and convert(date,B.Ftpdate) = convert(date,getdate()) ;
If i use this Expression i am getting the current system date data's only  from Master table to parent and child tables.
My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and  child tables.
If i run this expression to remote server  i cannot change the system date in server.
while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.
What is the Expression on which ever  date i am trying load in  the master table  same date need to loaded in Parent and child table without changing the system Date.
when I'm in MediaImportLog , I want use column ImportSource to compare with column ChainCode in table BillerChain ( so I get BillerInfoCode) and then use the BillerInfoCode I got to compare with column BillerCode in Table Bill ( I get CompanyCode) finally I use CompanyCode to compare with column CompanyCode in table DataBackup so I can get the company's keepmonth How can I get the keepmonth? can I use parameters ?
I would like to update the flag of the promotion ID should the promotion ID date range overlap with Promotion ID(All) Date Range. The general logic is as below.
Update TableName SET PromotionID Flag = 1 AND Reason = 'Overlap with row ID(Overlap row ID number)' Where EACH ROW(Except with Promotion ID ALL) Date Range Overlap with ROW(with promotion ID ALL) Date range
ID       Flag    TestDate        Value   Comment                                  111      2    12/15/2014     7.5        null 222       2        Null         10        received
Matrix_Current table could have 1 or multiple rows as below.
ID       Flag             TestDate          Value     Comment 111        2                01/26/2015      7.9                                    111        2                02/23/2015      7.9                            111        2                04/07/2015      6.8 222       1               null            8           test comment 1 222        3               null            9           test comment 2
When I run below update
 UPDATE  AM  SET  M.Flag = MC.Flag, M.TestDate = MC.TestDate, M.Value = MC.Value, M.comment = MC.Comment  FROM dbo.Matrix M inner join dbo.Matrix_Current MC on M.ID = MC.ID
Matrix table has value below:
ID       Flag    TestDate        Value   Comment                                  111      2    01/26/2015     7.9        222       1        Null        8        test comment 1
I want to update Matrix table from all row from Matrix_Current, final table would like below:
ID       Flag    TestDate        Value   Comment                                  111         2     04/07/2015     6.8        222       3        Null         9       test comment 2
I have two tables that I am needing to link by the tables date field. In one table the date field is defined as varchar(23). The time for this field is always zeros. Example: '2005-12-27 00:00:00.000'
The other table is defined as datetime, and it does have the date and time in this field. Example: 2005-12-27 08:00:35.000
The problem i am having is 2005-12-27 00:00:00.000 does not = 2005-12-27 08:00:35.000.
Because I will never have more than one record on the same date I would like to be able to only compare the date. Example 2005-12-27 = 2005-12-27
Since the fields are 2 different field types, this is giving me a problem. Could someone please help. I have tried everything I know to do.
What I really need is the a way to format the datetime fields date into a string such as '2005-12-27'.
I remember at some point I had some problem with this issue and I couldn't find something that suites a newbie. The point that I was missing was that I couldn't pass the string representation of the date value to a query string. The key is to convert the string date to its equivalent datetime or smalldate . For example imagine you'd like to select all Employees who where born on a specific date stored in a variable called myDate: the BirthDay column in our Employee table has type smalldatetime ---------------------------------------------- string myDate = @"06/06/1978"; .... SqlCommand comm = new SqlCommand("SELECT * FROM EMPLOYEE WHERE [BirthDay] = Convert(smalldatetime,'" + myDate + "', 103))"; .... ---------------------------------------------- Make suer that you don't miss single quotes around value of myDate in your query string. For more info regrading the Convert function look at: http://msdn2.microsoft.com/en-us/library/ms187928.aspx
I'm having a problem with what should be a simple TSQL statement. I have a table which has a datetime field updated. After the update if I type
select * from patient_medication where rec_status_date = '11/10/2000' it returns the rows I want.(All the dates have a time of 00:00:00.000. But if I type select * from patient_medication where rec_status_date <> '11/10/2000' or select * from patient_medication where rec_status_date != '11/10/2000'
The rows that have a value are returned, but none of the null values are returned. Will nulls not work with this comparison? Thanks
In my current job I am learning MS SQL - while having to write valid SQL statements. I need some help designing a query that checks hire dates for establishing participation for (something).
So the statement establishing participation has to have a WHERE clause that will check HIRE_DATE and establish two different goals:
1. WHERE Hire_Date < OCTOBER 1 of this current year 2. WHERE Hire_Date < OCTOBER 1 of previous year
Different question now that I am thinking about it: some of these previous SQL statements have a @ symbol, what does that mean in SQL? Can't find an online resource that references that symbol Example: IF @Type IN ('Workflow','All')
HiI have a table that contains information that has start dates and enddates. They are stored in short date format.I have built a web page that initially returned all the information. Ithen want to return information spcific to todays date, where I useddim todDatetodDate = nowthen I didshTodDate = FormatDateTime(todDate, 2)which returned the date to a web page that was in the same format aswhat was in the access table.SELECT * from Events WHERE stDate = # ' & shTodDate & ' #;"This was fine beacuse it returned all events that started on that day.This doesn't help though because some events last a few days, so Itried the followingSELECT * FROM Events WHERE stDate >=#'&shTodDate&'# ANDendDate<=#'&shTodDate&'#;This still only returned events that started on the current day.Does anyone know how I can use <= or >= when comparing dates?ThankspuREp3s+
Hi all,I am trying to create a stored procedure that will check a date field.I want to check for records that are equal to or greater then 90 daysfrom the current date. I am trying to check this against a fieldcalled LastUpdate. Is there an easy way in SQL to do this?TIA
there is a many to many relation between the tables. Â Each Policy can have 1 or many Liability Constraints. Â Each Liability Constraint can be attached to 1 or many policies.
What I need to do is get the distinct combination of LcCode, LcDesc, LcAmt, LcFreq and LcSetId from the Liability table as well as the earliest PolInceptionDt from any of the policies associated with the Liability Constraint using only the distinct combination of LcCode, LcDesc, LcAmt, and LcFreq.
Give the data above, I would need to end up with the following result:
I tried to use the Row_Number functionand join using PolNum, but I keep getting the wrong results back.
I need to create a query to get 1 row per location and get the minimum PolicyBookingDt and RowUpdateDt from the policy table. All the attributes from the Location table should also be from the Policy that has the minimum PolicyBookingDt.
 So from the above example, i need to get the following:
Getting a little confused on how to create the syntax for this.
I know people use ROW_NUMBER() function to do the pagination but my below two query is bit complex. Sohow to use pagination there ? I used ROW_NUMBER() OVER(ORDER BY IsNull(A.OEReference, B.OEReference) ASC) as Line in one but not sure am i right or wrong.
IF IsNull(@GroupID,'') = '' SELECT IsNull(PartGroupName, 'UnMapped') AS PartGroupName, CASE IsNull(PartGroupName, '') WHEN '' THEN '' ELSE IsNull(IsNull(K.GroupID, IsNull(C.PartGroupID,'')),'') END AS PartGroupID,
Hi, I really need this help urgently. I need to send an email when the dueDate(field name in database) is equal to today's date... I have come out with this code with the help of impathan(jimmy i did not use ur code cos i not very sure sry)... below is the code with no error... but it jus wun send email... Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load, Me.Load con1.Open() Dim cmd As New SqlCommand cmd.CommandText = "select * from custTransaction where convert(datetime,dueDate,101) = convert(datetime,GetDate(),101)" 'Set the connect the command object should use cmd.Connection = con1Dim da As New SqlDataAdapter(cmd)Dim ds As New DataSet da.Fill(ds) con1.Close() If Not ds.Tables(0) Is Nothing ThenIf ds.Tables(0).Rows.Count > 0 Then Dim objEmail As MailMessage = New MailMessage objEmail.From = New MailAddress("my@email.com.sg")objEmail.To.Add(New MailAddress("my@email.com.sg")) objEmail.Subject = "Due Date Reaching"objEmail.Body = Session("dueName") objEmail.Priority = MailPriority.Normal Dim SmtpMail As New SmtpClient("servername") SmtpMail.Send(objEmail) End If End If End Sub Note: I am veri sure that database has the data field dueDate with the value 11/16/2007 smalltimedate(mm/dd/yyyy) Realli veri urgent Thanks so much for ur'll help
DECLARE @Teams AS TABLE(Team VARCHAR(3)) INSERT INTO @Teams SELECT 'IND' UNION SELECT 'SA' UNION SELECT 'AUS' select Team from @Teams where Team > 'AUS'
[code]....
co-relation between comparison operators in WHERE Clause and the respective output.
