JOIN Onto Itself And Contains The Lower Date
Jun 2, 2008
Hello,
I have been having a hard time with this issue. I am attempting to join a table onto itself to get the closest date onto a single row.
What i mean is:
I have the following data
id date
1 10/07/08
2 10/06/07
3 10/06/03
4 10/06/03
the new table should have the current id and the one closes to it as so.
1 10/07/08 2 10/06/07
2 10/06/07 3 10/06/03
3 10/06/03 null null
4 10/06/03 null null
but i am getting duplicates do to the 10/06/03.
1 10/07/08 2 10/06/07
2 10/06/07 3 10/06/03
2 10/06/07 4 10/06/03
3 10/06/03 null null
4 10/06/03 null null
i want so that if there is a duplicate i can take the id thats higher. I cant figure it out.
This is my current sql:
SELECT PB.ID,PB.StartDate, PB2.ID, PB2.Startdate
from table PB
left outer join table PB2 on PB.keyID = PB2.keyID
and PB2.StartDate < PB.StartDate
and PB.StartDate = (select top(1) StartDate from table PB3 where PB.keyID = PB3.keyID
and PB2.StartDate < PB3.StartDate order by PB3.StartDate asc)
Thanks for the help.
View 3 Replies
ADVERTISEMENT
Jan 4, 2007
I have following problem:table includes times for startup and end of operation as datetime fieldrelated to daily shift operations:dateid date starttime endtime458 2006-12-29 22:00 23:15458 2006-12-29 00:15 01:30459 2006-12-30 20:00 21:10459 2006-12-30 22:15 23:35459 2006-12-30 23:30 00:40459 2006-12-30 01:50 02:30records are inserted for a date related to begining of the shift, althoughsome operations are performed also past the midnight (actualy next day, ex:2006-12-31), but belongs to same shift (group)Now I need to build a function that corrects (updates) the date of everyoperation recorded after midnight to a date+1 value, so all records relatedto same groups (458, 459, etc) that starts after midnight has correct date.The procedure has to update already exiting table.Any solution?Grey
View 8 Replies
View Related
Sep 28, 2007
Hi I know i can use a LOWER or UPPER function to change the
case of letters, but say i want to change all but the 1st letter
ie i want MARK SMITH to be Mark Smith ... MARK and SMITH
are 2 seperate columns so im assuming something like
select UPPER(first_name,1) ...
View 6 Replies
View Related
Apr 17, 2007
hi
I have a column in my database i would like to convert to lowercase
is their a t-sql statement or something i can use so i dont have to do it manually ??
cheers!!!
View 2 Replies
View Related
May 19, 2008
Hi all,
I have a big problem with the design and the queries of a couple of tables. I have to calculate the weighted average of the number of days between an invoice and the sum of its payments (rateal payments), weighting this number with the invoice's amount, and only when the sum of the invoice's payments equal invoice amount.
I have designed two FactTables to accomplish this:
Example:
data in the fist table (FactInvoice) looks like this:
CustomerId, InvoiceDate, InvoiceNumber, InvoiceAmount
1234567 2008-03-10 123 1000.00
1234567 2008-04-10 150 2000.00
and data in the second one (FactPayments) looks as below:
CustomerId, PaymentDate, InvoiceNumber, PaymentsAmount
1234567 2008-03-10 123 500
1234567 2008-03-15 123 500
1234567 2008-04-20 150 800
1234567 2008-04-23 150 1200
Let's suppose that I'm querying the cube for the customer 1234567, at the date 2008-05-01. I need to sum the payments for the first invoice multiplicating it for the count of days elapsed, then divide the number by the InvoiceAmount (or PaymentsAmount, is the same) : (0 days * 500‚¬ + 5 days * 500‚¬ + 10 days * 800‚¬ + 13 days * 1200)/3000‚¬ = 8,7 days of weighted average to cash an invoice.
How can I do this with Analisys Services ? Is it too complicated ? Here, in the southern Italy, the problem of customer's debit is heavily felt, and total like this are really important!
Any help or suggestion will be really appreciated.
Marco
View 13 Replies
View Related
Apr 25, 2007
I have a query that is the initial part of a stored procedure; the results are put in a temporary table that will be used by several other operations. I'm trying to consolidate some of these, possibly getting the whole thing down to one query. One of steps I'm trying to consolidate results in a join that looks like this (where c is a table alias created previously):
Code:
LEFT JOIN proccode_t pc ON pc.proccode=c.proccode
AND (pc.effectivedate<=c.dateofservice OR pc.effectivedate IS NULL)
I don't have any influence over the schema in this database, I can only write select queries. Things are set up such that proccode_t will have several records for any given proccode, each with a different effectivedate. The oldest/original effectivedate is often NULL. This means my join up there often matches up with several records in proccode_t, when I only ever want to match the one most recent and occasionally not match anything at all.
I know a few ways to accomplish this (as covered in another recent thread here), but none of them seem to match well to this situation. A sub query that uses TOP 1 ORDER BY effectivedate DESC would almost cover it, but if the query returns NULL that might or might not mean it matched the NULL effectivedate for that proccode. It's also kinda slow.
The proccoce_t table does contain another field to tell you which of the codes is currently effective aside from just using the date, and that would be the normal way to do this here. However, this procedure is for auditing old data and therefore the currently effective proccode may not be the one I'm concerned with.
Any thoughts?
View 1 Replies
View Related
Oct 24, 2013
I'm using SQL Dev 2.1 and i want to find the latest date and also joining tables. My query is below:-
I want to get the latest DEBT_COLLECTION_STEPS_V.TRANSACTION_DATE
As there are more that one date relating to the same invoice number but different comments posted against the DEBT_COLLECTION_STEPS_V.DESCRIPTION.
SELECT AR_INVOICE_INQ_V.CLIENT CLIENT,
AR_INVOICE_INQ_V.CLI_NAME CLI_NAME,
AR_INVOICE_INQ_V.PARTNER PARTNER,
AR_INVOICE_INQ_V.PAR_NAME PAR_NAME,
AR_INVOICE_INQ_V.MANAGER MANAGER,
[Code] ....
View 1 Replies
View Related
Feb 6, 2014
Got a data set like this:
rowID PersonID Start Date End Date
===== ======== ========== ==========
001 6575556 19/06/2013 09/07/2013
001 6575556 20/06/2013 12/07/2013
001 6575556 21/06/2013 12/07/2013
002 9478522 15/05/2013 18/05/2013
003 7753423 22/08/2013 01/09/2013
Person can have more than one start/end date therefore I get multiple of the same row ID and Person ID when looking at their dates.
I want to display the most recent end date and associated data if there is more than one start/end date for the same person. I decided to do a self join with max Date aggregate using this against a main select from the Table1:
SELECT PersonID,
MAX([End Date]) AS MaxEndDate
FROM Table1
GROUP BY
PersonID
And join it this way:
select RowID,
PersonID,
[End Date]
FROM Table1 INNER JOIN (
SELECT PersonID,
MAX([End Date]) AS MaxEndDate
[Code] ....
When I run the sub-query on its own it gives me the single PersonID and Max Date but on self-joining with Table1 I still get the duplicates values.
View 2 Replies
View Related
Jul 20, 2005
{CREATE TABLEs and INSERTs follow...}Gents,I have a main table that is in ONE-MANY with many other tables. For example, ifthe main table is named A, there are these realtionships:A-->BA-->CA-->DA-->EWith one field in Common (Person). The tables B, C, D and E are History tables,with Start and End dates. Each person has a Program history (table B, ie), anExperience history (table C, ie), and so on...many differernt types ofhistories, and it may grow from here....table F, G, etc.The included CREATE TABLEs and INSERTs contain tables A, B and C.The problem: Each tblCase (table A) record has a date. When joining all of thehistory tables to tblCase on Person, obviously you get a cross-product of eachhistory unless you specify a WHERE clause that extracts one single record fromeach of the histories (duh...that's the point...to extract a single record fromeach history, because there can only be one value in effect at the time of theCase.)QUESTION: From a performance standpoint, would it behoove me to maintain thesurrogate ***HistoryID from each history table in tblCase, or, assuming theindexes are set up properly, would a WHERE condition for each history besufficient? For example, the following select works as expected:SELECT CasePerson, CaseDate, ProCode, ExpYearFROM tblExperienceHistory INNER JOIN (tblCase INNER JOIN tblProgramHistory ONtblCase.CasePerson = tblProgramHistory.ProPerson) ON tblCase.CasePerson =tblExperienceHistory.ExpPersonWHERE CaseDate BETWEEN ProStartDate and ProEndDateAND CaseDate BETWEEN ExpStartDate and ExpEndDateIt extracts the single record from each history for each person for each case.But I'm afraid of performace with such a scenario.Instead, I could store each ***HistoryID in the table tblCase, and then justjoin on that...no WHERE needed. But the trade-off is that I'd have to buildprocesses to maintain that. ("Hey, when you insert a record into tblCase, makesure to go get each HistoryID from the History tables!" or "If the user changesthe date ranges in one of histories, make sure to update tblCase to match thenew historyID!")Maybe a clustered index on each ***History table on Person/StartDate combinedwith the WHERE clause should perform as well as a real JOIN on surrogateintegers.It seems cheesey to have to resort to surrogate IDs...but the performanceincrease might be worth it. Also, if I go that route, whenever I add a newhistory table, I'd have to change the design of tblCase AND any SPs thatreference it. With the WHERE solution, I'd only have to change the SPs.Comments are welcome! (tblCase grows at 250,000 records per year; the historytables will increase about 1000 records per year)DCMFANCREATE TABLE [dbo].[tblCase] ([CaseID] [char] (5) CONSTRAINT [PK_tblCase] PRIMARY KEY CLUSTERED NOT NULL ,[CaseDate] [smalldatetime] NOT NULL ,[CasePerson] [char] (5) NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[tblExperienceHistory] ([ExperienceHistID] [int] IDENTITY (1, 1) NOT NULL ,[ExpPerson] [char] (5) NOT NULL ,[ExpStartDate] [smalldatetime] NOT NULL ,[ExpEndDate] [smalldatetime] NOT NULL ,[ExpYear] [int] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[tblProgramHistory] ([ProgramHistID] [int] IDENTITY (1, 1) NOT NULL ,[ProPerson] [char] (5) NOT NULL ,[ProStartDate] [smalldatetime] NOT NULL ,[ProEndDate] [smalldatetime] NOT NULL ,[ProCode] [int] NOT NULL) ON [PRIMARY]GOINSERT INTO [tblCase]([CaseID], [CaseDate], [CasePerson])VALUES('12345', '3/1/03', '00000')INSERT INTO [tblCase]([CaseID], [CaseDate], [CasePerson])VALUES('A1G34', '4/23/03', '00001')INSERT INTO [tblExperienceHistory]([ExpPerson], [ExpStartDate], [ExpEndDate],[ExpYear])VALUES('00000', '1/1/03', '5/19/03', 1)INSERT INTO [tblExperienceHistory]([ExpPerson], [ExpStartDate], [ExpEndDate],[ExpYear])VALUES('00000', '5/20/03', '12/31/03', 2)INSERT INTO [tblExperienceHistory]([ExpPerson], [ExpStartDate], [ExpEndDate],[ExpYear])VALUES('00001', '4/20/03', '11/1/03', 0)INSERT INTO [tblProgramHistory]([ProPerson], [ProStartDate], [ProEndDate],[ProCode])VALUES( '00000', '2/1/03', '9/30/03', '55555')INSERT INTO [tblProgramHistory]([ProPerson], [ProStartDate], [ProEndDate],[ProCode])VALUES( '00000', '10/1/03', '5/1/04', '55555')INSERT INTO [tblProgramHistory]([ProPerson], [ProStartDate], [ProEndDate],[ProCode])VALUES( '00001', '1/1/03', '12/31/03', '55555')
View 4 Replies
View Related
Aug 14, 2006
Hi there ;
I've a photo viewer which shows the image based on a datetime QueryString. I've a couple of buttons like next, prev, last, .... .
in the "prev" for instance, i want to select the image with a datetime that is lower than the current image datetime, and is the biggest one of course.but i'm stuck in it!
By the way i use .net 2.0 & Sql 05 Express
thanks in advance
View 4 Replies
View Related
Apr 6, 2007
For example,
select fieldA form tableA where fieldA = 'aaa'
I got following output
fieldA
---------
aaa
aAa
AAA
AAa
...
if I want select only the lower case 'aaa', how can I do that?
View 2 Replies
View Related
May 2, 2008
My SQL Server database is not case sensetive.
How can I compare like cluase with search for capital and small letter?
For example
SELECT add1 from xcty_all where add1 like '%AL'%'
I need only
...................
10 ltncewwod way AL
456 Ruio St. AL
NOT
Duci Ral Rd Mexico
Albi Road Hawai CA
I want to ingore this bold letter on search
Sanjeev Shrestha
12/17/1971
View 1 Replies
View Related
Jul 20, 2005
I need to split a string in two if there are lowercase characters atthe end of it.For example:'AAPLpr' becomes 'AAPL' and 'pr''Ta' becomes 'T' and 'a''MSFT' becomes 'MSFT' and '''TAPA' becomes 'TAPA' and ''I am using SQL 2000. I read about "collate Latin1_General_CS_AS" butnot sure if I can use that outside of a select statement.Thank you in advance for any help.
View 2 Replies
View Related
Oct 25, 2006
I am trying to get this code to work: (left(religion,1))+ lower(right(religion,len(religion)-1))As Religion
I am getting this error:
Invalid length parameter passed to the RIGHT function.
View 6 Replies
View Related
Sep 10, 2015
I have the following 2 tables:
Location:
Policy:
1 Location can be attached to many policies.
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.
View 4 Replies
View Related
Sep 20, 2006
hi i want to select * from table1 where name =petter?now if there is many type of petter in table linke PETTER , Petter And petter which record will come in display?if i want all this three (PETTER,Petter,petter) will come in display which command is for this ??? regard
View 4 Replies
View Related
Jun 17, 2003
Hi,
My database is case insensitive. However, the application is case sensitive for the data in the table.
I need to find out the data from the table where the data is stored in lower case.
Here is the sample data:
Table: inv_manager
column: sku varchar(5)
Data: 1134X
1135x
1123a
b145Y
I just need query to return row 2, 3, 4.
Here is my query:
select sku from inv_manager where sku like lower('%[a-z]%')
The problem is it returns all the rows from the table including first one.
How can I return just the data with lower case ?
Thanks
View 2 Replies
View Related
Dec 21, 2006
Currently i have 2 type of data
A and a
But when i try to:
select * from tableA where col = 'a'
then all record with A and a will be selected, any way to avoid it and select only record with col ='a'?
View 1 Replies
View Related
Jul 7, 2015
In the database, most of our cities are stored in all upper case. For reporting purposes, I need to have them returned as upper/lower.  I used the below function, which works great for one word cities.  However I can’t figure out how to get it to capitalize the 1st letter of each word, for addresses containing multiple names such as Rancho Santa Margarita.Â
Upper(left(CR_MEMBER_ALLMEMBERDETAILS.ADDRESSCITY,1))+lower(substring(CR_MEMBER_ALLMEMBERDETAILS.ADDRESSCITY, 2, LEN(CR_MEMBER_ALLMEMBERDETAILS.ADDRESSCITY)))As ADDRESSCITY
This returns back: ‘Rancho santa margarita’; I need it to return ‘Rancho Santa Margarita’. Is this possible to do at the query level?
View 10 Replies
View Related
Sep 18, 2006
Hi
I can't create unique index like that:
1 - create table abc ( colZ varchar(10) )
2 - insert into abc values ( "test")
3 - insert into abc values ("Test")
4 - create unique index idx_abc on abc ( colZ ) -- This doesnt work
.... duplicate key was found for object name abc......
Is not there difference between "Test" and "test"?
Can I work around this?
cheers,
View 1 Replies
View Related
May 23, 2001
I have two tables that I am trying to join and both have similar columns with which I am trying to use. One is Date and the other is LOGdate. Date's format is "01/01/2000" BUT LOGdate's is "01/01/2000 12:41:00 PM/AM". Can I join these two tables?
Any help would be greatly appreciated.
Thank you
View 1 Replies
View Related
May 23, 2001
I have two tables that I am trying to join and both have similar columns with which I am trying to use. One is Date and the other is LOGdate. Date's format is "01/01/2000" BUT LOGdate's is "01/01/2000 12:41:00 PM/AM". Can I join these two tables? If yes how do I get rid of the excess time on LOGdate.
Any help would be greatly appreciated.
Thank you
View 2 Replies
View Related
Jul 16, 2014
I have 2 tables:
Table Transaction
-----------------
EmpID TransDate
00001 1/1/2014
00001 1/2/2014
00001 1/3/2104
00001 1/4/2014
00001 1/5/2014
00001 1/6/2014
00001 1/15/2014
00001 2/1/2014
00001 2/2/2014
00001 2/20/2004 ....
Table Master
---------------------------
EmpID EffectiveDateFr Group
00001 1/1/2014 A
00001 1/5/2014 B
00001 1/9/2014 C
00001 2/1/2014 B
00001 2/20/2014 A ....
I want to create query the output should be:
EmpID TransDate Group
00001 1/1/2014 A
00001 1/2/2014 A
00001 1/3/2104 A
00001 1/4/2014 A
00001 1/5/2014 B
00001 1/6/2014 B
00001 1/15/2014 C
00001 2/1/2014 B
00001 2/2/2014 B
00001 2/20/2004 A
View 4 Replies
View Related
Sep 14, 2015
I need to Filter Employees by Available Date and grab their Basic contact information
I used Join to Join both tables but I can't seem to find a way to only get data from employees where DateAv > SelectedDate.
I'm using this in a SqlCommand with C#.Â
SELECT " EmployeeNameTable.ID, EmployeeNameTable.FullName, EmployeeNameTable.DateAV,ContactTable.HomePhone, ContactTable.MobilePhone, ContactTable.Email FROM EmployeeNameTable INNER JOIN ContactTable On EmployeeNameTable.ID = ContactTable.ID OrderBy EmployeeNameTable.FullName"
This code loads all employees Successfully but it doesn't filter them by the DateAV Column which is in Date Format.
View 2 Replies
View Related
Aug 29, 2006
I'm still haven't resolved the issue with displaying information from a SQL database. The text I'm displaying is in ALL CAPS in the SQL database, and I'm trying to convert it so that when I display it in gridview, The First Letter Of Each Word Is Capitalized, as apposed to ALL CAPS. I've tried the text-transform feature of CSS, but I noticed in a SQL book there are LOWER() & UPPER() string functions. The ideal thing to do then, would be to do some select statement that converts all the incoming text to lowercase, then use the CSS text-transform: capitalize , to convert the first letter of each word to caps. Basically, I need a select statement or something that converts my sql material to lowercase. Thanks.
View 2 Replies
View Related
May 8, 2007
Hi,
I want to search for alphanumeric values between an upper and lower bound in a sql database.For example: search for a serial number like pvf-456-3b. Upper bound is q, lower bound is g.I should then get every serial number starting with g - q.If possible the bounds should be more specific like "search for serial number between gt2 and qy"Can anybody help me out?
View 7 Replies
View Related
Jul 20, 2005
I am running the following OSQL command and capturing the return codefor the error .Whenver i have an error like server not exists or uableto login I get a return code of 1 for the %ERRORLEVEL%.Howeverwhenever I have an errorof a wrong dbcompatibility error the retuncode id 1 even though sql returns an iformation message from OSQL thatthe right copatibilty levels are 60,70 and 80.How can i get OSQL toreturn the right return code whenver a error of this type occurrs frombatch mode sql.The OSQL i am running from the batch isosql -S%SrvName% -U%Username% -P%Userpswd% -n -w 132 -d%DBname%-Q%sqlcmd% -o%Dirrpt%\%DBname%_%SPname%.txtECHO %errorlevel% >> %logbatch%IF %ERRORLEVEL% NEQ 0 Goto SQLErrorsqlcms is exec sp_dbcompatibiltylevel srvrname, dbname 80Thanks in anticipation.Ajay
View 3 Replies
View Related
Nov 12, 2015
What I'm trying to select is the closest value from a list given by a parameter or select the matched value.
declare @compare as int
set @compare = 8
declare @table table
(
Number int
)
insert into @table
values(1),
(2),
(3),
(4),
(5),
(10)
If the parameter value match one of the values from the table list, select that matched one.If the value does not exist in the table list, select the closest lower value from the table list, in this case, it would be value 5.
View 3 Replies
View Related
Sep 16, 2013
I have built a sample table, query, and results for this question. I am using SQL server.
declare @TableX Table
(
Date Date not null,
ID int not null,
Tick varchar(6) not null
)
INSERT @tableX
[Code] ....
Expected results:
DATE(No column name)count
2013-09-02LON1
2013-09-03LON1
2013-09-04LON1
2013-09-05LON1
2013-09-06LON1
2013-09-02USA2
2013-09-03USA2
2013-09-04USA2
2013-09-05USA2
2013-09-06USA2
I want to select unique country - date pairs. It is not even necessary to have the count of each one, just the list of unique country/dates.
My query here uses 'group by' to accomplish this task, but there may be a way to do this with a self join. I believe using a self join would make the query faster.
1) Is this possible to do with a self join?
View 2 Replies
View Related
May 22, 2014
I have a virtual server (VMware ESX) with 64GB RAM running a single instance of SQL 2012 SP1. The max memory config is set to 59392 (58GB).
The Page Life Expectancy for this server has been averaging well under 10 mins for the last few days, according to our monitoring.
I have been checking the amount of data in the buffer cache periodically during the day with the below query, which seems to show that there is never more than about 10GB of data at any one time, frequently dropping below 5GB:
SELECT COUNT(*) AS BufferPages,
CONVERT(decimal(10, 2), COUNT(*) / 128.0) AS BufferMB
FROM sys.dm_os_buffer_descriptorsWhy would the amount of cached data be so low (and cause so much churn)?
I am aware that other things will require some of that memory (plan cache etc.) but with Max Mem of 58GB, I would expect there to be a much higher amount of actual cached data at any one time. I did the same checks on another VM with the same amount of RAM/Max Mem setting, and there was 50GB of data in the cache, with PLE measured in hours.
View 9 Replies
View Related
Apr 30, 2008
Hello
Can any one tell me the difference between Cross Join, inner join and outer join in laymans language
by just taking examples of two tables such as Customers and Customer Addresses
Thank You
View 1 Replies
View Related
Sep 1, 2014
Is there any way to restore a database of higher version to lower version.
E.g. I have created a database in sql server 2012, created some tables & procedures in that.I took Full backup of that database. Can I restore it to sql 2008r2 or any lower version.
I know direct restore is not possible, I have to use either import or export option or generating script,but i want to know is there any easy step to do so.
Vimal Lohani
SQL DBA | MCP (70-461,70-462)
View 4 Replies
View Related
Jan 25, 2007
All,
I am using Reporting Services 2005. One of my reports is getting the following error when I try to export to Excel. It will export to .CSV though.
"Destination array was not long enough. Check destIndex and length, and the array's lower bounds."
Any suggestions would be greatly appreciated. Please copy me at machelle.a.chandler@intel.com.
Machelle
View 10 Replies
View Related