I have a report that needs to "jump" to 2 URL's at the same time. I also need
them to open in a second and third window leaving the first open to the
orginal report. Can this be done? I have the report right now that can "jump"
to one of the links in the cell. But it uses the original window.TTT
"Robert B." wrote:
> I have a report that needs to "jump" to 2 URL's at the same time. I also need
> them to open in a second and third window leaving the first open to the
> orginal report. Can this be done? I have the report right now that can "jump"
> to one of the links in the cell. But it uses the original window.sql
Showing posts with label time. Show all posts
Showing posts with label time. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
Julian date time Conversion
Can anyone tell me how to convert julian date time to DateTime and Vice Versa?
the function which I have only convers the date to Julian and julian to date but the time is not appended.
How can i get the time into Julian format and from julian format?
Any help would be appreciated.
thanks.If you poke around the source forthis page, you'll find the JavaScript that they use to do it.
|||
the function which I have only convers the date to Julian and julian to date but the time is not appended.
How can i get the time into Julian format and from julian format?
Any help would be appreciated.
thanks.If you poke around the source forthis page, you'll find the JavaScript that they use to do it.
|||
Try the links below for UDF Julian to DateTime conversion code. Hope this helps.
http://www.novicksoftware.com/udfofweek/Vol2/T-SQL-UDF-Vol-2-Num-3-udf_DT_FromJulian.htm
http://www.novicksoftware.com/udfofweek/Vol2/T-SQL-UDF-Vol-2-Num-2-udf_DT_ToJulian.htm
Joins Question
I have 3 tables. Table1(time,readingA) Table2(time,readingB)
table3(time,readingC)
Now the time can be same and it can be different. Now i want to know
how do i join so that i get the data: time,readingA,readingB,readingC
If the time is same then it is fine, but if the time is not same in
two tables for eg : if table1 has a record for time 12:30 and Table2
and table3 does not have that time then it should show data from
table1 and the readingB and readingC will be blank.
I hope my question is clear.
Thanks for helpHi
If you are not interested in the time then you may not want to truncate
everything to midnight when they are inserted (which if you don't have a tim
e
portion on your date/time will happen anyhow). The isssue then is what will
happen if there are multiple records for each day? If the time is require fo
r
some other reason you can also use the convert function to compare the date
part of the datetime
SELECT CONVERT(char(8),T1.time,112) AS Time, T1.readingA, T2.readingB,
T3.readingC
FROM Table1 T1
JOIN Table2 T2 ON CONVERT(char(8),T1.time,112) = CONVERT(char(8),T2.time,112
)
JOIN Table3 T3 ON CONVERT(char(8),T1.time,112) = CONVERT(char(8),T3.time,112
)
John
"Pradeep" wrote:
> I have 3 tables. Table1(time,readingA) Table2(time,readingB)
> table3(time,readingC)
> Now the time can be same and it can be different. Now i want to know
> how do i join so that i get the data: time,readingA,readingB,readingC
> If the time is same then it is fine, but if the time is not same in
> two tables for eg : if table1 has a record for time 12:30 and Table2
> and table3 does not have that time then it should show data from
> table1 and the readingB and readingC will be blank.
>
> I hope my question is clear.
> Thanks for help
>|||Hi,
if I understand correctly your question, you need to use left join.
Something like
SELECT T1.time AS Time, T1.readingA, T2.readingB,
T3.readingC
FROM Table1 T1
LEFT JOIN Table2 T2
ON T2.time=T2.time
LEFT JOIN Table3 T3
ON T1.time= T3.time
"Pradeep" <agarwalp@.eeism.com> wrote in message
news:364c5b9b.0502012334.5b8f2955@.posting.google.com...
>I have 3 tables. Table1(time,readingA) Table2(time,readingB)
> table3(time,readingC)
> Now the time can be same and it can be different. Now i want to know
> how do i join so that i get the data: time,readingA,readingB,readingC
> If the time is same then it is fine, but if the time is not same in
> two tables for eg : if table1 has a record for time 12:30 and Table2
> and table3 does not have that time then it should show data from
> table1 and the readingB and readingC will be blank.
>
> I hope my question is clear.
> Thanks for help|||It looks like I may have got this mixed up! As Ana says use left JOIN
although you may not want your times to 3/100 of a second, in which
case you will still need to truncate them
SELECT T1.Time, T1.readingA, T2=AD.readingB,
T3.readingC
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.time =3D T2.time
LEFT JOIN Table3 T2 ON T1.time =3D T3.time
John
table3(time,readingC)
Now the time can be same and it can be different. Now i want to know
how do i join so that i get the data: time,readingA,readingB,readingC
If the time is same then it is fine, but if the time is not same in
two tables for eg : if table1 has a record for time 12:30 and Table2
and table3 does not have that time then it should show data from
table1 and the readingB and readingC will be blank.
I hope my question is clear.
Thanks for helpHi
If you are not interested in the time then you may not want to truncate
everything to midnight when they are inserted (which if you don't have a tim
e
portion on your date/time will happen anyhow). The isssue then is what will
happen if there are multiple records for each day? If the time is require fo
r
some other reason you can also use the convert function to compare the date
part of the datetime
SELECT CONVERT(char(8),T1.time,112) AS Time, T1.readingA, T2.readingB,
T3.readingC
FROM Table1 T1
JOIN Table2 T2 ON CONVERT(char(8),T1.time,112) = CONVERT(char(8),T2.time,112
)
JOIN Table3 T3 ON CONVERT(char(8),T1.time,112) = CONVERT(char(8),T3.time,112
)
John
"Pradeep" wrote:
> I have 3 tables. Table1(time,readingA) Table2(time,readingB)
> table3(time,readingC)
> Now the time can be same and it can be different. Now i want to know
> how do i join so that i get the data: time,readingA,readingB,readingC
> If the time is same then it is fine, but if the time is not same in
> two tables for eg : if table1 has a record for time 12:30 and Table2
> and table3 does not have that time then it should show data from
> table1 and the readingB and readingC will be blank.
>
> I hope my question is clear.
> Thanks for help
>|||Hi,
if I understand correctly your question, you need to use left join.
Something like
SELECT T1.time AS Time, T1.readingA, T2.readingB,
T3.readingC
FROM Table1 T1
LEFT JOIN Table2 T2
ON T2.time=T2.time
LEFT JOIN Table3 T3
ON T1.time= T3.time
"Pradeep" <agarwalp@.eeism.com> wrote in message
news:364c5b9b.0502012334.5b8f2955@.posting.google.com...
>I have 3 tables. Table1(time,readingA) Table2(time,readingB)
> table3(time,readingC)
> Now the time can be same and it can be different. Now i want to know
> how do i join so that i get the data: time,readingA,readingB,readingC
> If the time is same then it is fine, but if the time is not same in
> two tables for eg : if table1 has a record for time 12:30 and Table2
> and table3 does not have that time then it should show data from
> table1 and the readingB and readingC will be blank.
>
> I hope my question is clear.
> Thanks for help|||It looks like I may have got this mixed up! As Ana says use left JOIN
although you may not want your times to 3/100 of a second, in which
case you will still need to truncate them
SELECT T1.Time, T1.readingA, T2=AD.readingB,
T3.readingC
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.time =3D T2.time
LEFT JOIN Table3 T2 ON T1.time =3D T3.time
John
Friday, March 23, 2012
JOINs and ONs
I have worked with various forms of SQL over many years and have recently started using SQL Server for the first time. In the time I've used, I've created various reports, programs etc. mainly performing selections of data for reporting purposes. Joins have, of course, been necessary in many of these.
On using SQL Server, I have seen one thing though that I haven't seen before when using Joins and that is where the ONs appear in a list and seemingly not directly attached to the JOIN references listed before them. I must admit I have always quoted the JOIN followed immediately by the ON setting.
Does SQL Server re-sort the commands itself while processing so that it fits the JOINs and ONs or is there a more definite reason for this way of creating the SELECT.
Regards
Jim Jackson
What you describe is often a form of 'nesting' JOINs.
It would be so much easier to read if folks would use parentheses.
For Example:
SELECT {ColList}
FROM TableA ta
JOIN Table B tb
JOIN Table C tc
ON tb.PKCol = tc.FKCol
ON ta.PKCOl = tb.FKCol
WHERE {Criteria}
This would be easier to read and understand by simply using parentheses.
SELECT {ColList}
FROM TableA ta
JOIN ( Table B tb
JOIN Table C tc
ON tb.PKCol = tc.FKCol
)
ON ta.PKCOl = tb.FKCol
WHERE {Criteria}
Without the parentheses, the Query Processor is left to its own imagination in working out what it should do. Many times that will be what was desired -BUT sometimes, there can be unexpected results.
With simple consecutive JOINS, it is best to follow each JOIN with its matching ON condition.
|||Thanks, Arnie
I must admit I'd figured the nesting answer and have tried a couple of test ones myself to get the feel for them.
But you are right about using parentheses especially when you are using a couple of right outer joins in the middle of a set of approx. 6 nested joins separated by another inner join or two. I'm trying to figure one out that was provided for a report in our CRM application which is just being implemented. And I'm still not sure if I've got the brackets in the places they should be appearing!
Regards
Jim
Wednesday, March 21, 2012
Joining tbls from different db's with WHERE statement
Hi,
This is the first time I've joined tables residing on seperate databases. It
works without the WHERE statement, but when I add the WHERE clause in, it
returns no rows. I am certain the argument is correct & have used it in a
simple table and it returns rows. Is there something specal about doing this
against two tables from different DB's
Thanks for any clues.
Ant
SELECT i.Log_id,
e.name,
e.tel,
e.email,
i.TimeSubmit,
i.Priority,
i.Status,
i.ProDes,
i.emp_id
FROM IDB.dbo.IRL i
INNER JOIN NDB.dbo.Employees e ON i.emp_id = e.emp_id
WHERE i.Status = 'Open'Could it be that only a small number of records in table IRL have a status
of open, by INNER JOINing to Employee you eleminate the small number. Try
this to find out...
SELECT i.Log_ID, i.Status , i.emp_id, e.emp_id
FROM IDB.dbo.IRL i
LEFT JOIN NDB.dbo.Employees e ON i.emp_id = e.emp_id
WHERE i.Status = 'Open'
HTH. Ryan
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:A82B8A64-7986-417D-B34F-3B6EE50928C2@.microsoft.com...
> Hi,
> This is the first time I've joined tables residing on seperate databases.
> It
> works without the WHERE statement, but when I add the WHERE clause in, it
> returns no rows. I am certain the argument is correct & have used it in a
> simple table and it returns rows. Is there something specal about doing
> this
> against two tables from different DB's
> Thanks for any clues.
> Ant
> SELECT i.Log_id,
> e.name,
> e.tel,
> e.email,
> i.TimeSubmit,
> i.Priority,
> i.Status,
> i.ProDes,
> i.emp_id
> FROM IDB.dbo.IRL i
> INNER JOIN NDB.dbo.Employees e ON i.emp_id = e.emp_id
> WHERE i.Status = 'Open'
This is the first time I've joined tables residing on seperate databases. It
works without the WHERE statement, but when I add the WHERE clause in, it
returns no rows. I am certain the argument is correct & have used it in a
simple table and it returns rows. Is there something specal about doing this
against two tables from different DB's
Thanks for any clues.
Ant
SELECT i.Log_id,
e.name,
e.tel,
e.email,
i.TimeSubmit,
i.Priority,
i.Status,
i.ProDes,
i.emp_id
FROM IDB.dbo.IRL i
INNER JOIN NDB.dbo.Employees e ON i.emp_id = e.emp_id
WHERE i.Status = 'Open'Could it be that only a small number of records in table IRL have a status
of open, by INNER JOINing to Employee you eleminate the small number. Try
this to find out...
SELECT i.Log_ID, i.Status , i.emp_id, e.emp_id
FROM IDB.dbo.IRL i
LEFT JOIN NDB.dbo.Employees e ON i.emp_id = e.emp_id
WHERE i.Status = 'Open'
HTH. Ryan
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:A82B8A64-7986-417D-B34F-3B6EE50928C2@.microsoft.com...
> Hi,
> This is the first time I've joined tables residing on seperate databases.
> It
> works without the WHERE statement, but when I add the WHERE clause in, it
> returns no rows. I am certain the argument is correct & have used it in a
> simple table and it returns rows. Is there something specal about doing
> this
> against two tables from different DB's
> Thanks for any clues.
> Ant
> SELECT i.Log_id,
> e.name,
> e.tel,
> e.email,
> i.TimeSubmit,
> i.Priority,
> i.Status,
> i.ProDes,
> i.emp_id
> FROM IDB.dbo.IRL i
> INNER JOIN NDB.dbo.Employees e ON i.emp_id = e.emp_id
> WHERE i.Status = 'Open'
Monday, March 19, 2012
Joining Table Operation
I have two tables Customer and Customer Details.
Both are joinde with cust_id.
How can I insert data both the tables at the same time using ado.net.
Should I insert individually or can I use JOIN operation for insertion.
Please correct me with an example would be really appreciable.
Thanks in advanceYOu can′t do a multi-insert in a table, you have to wrap these two statement in a transaction and execute / commit these two statements together.
Both are joinde with cust_id.
How can I insert data both the tables at the same time using ado.net.
Should I insert individually or can I use JOIN operation for insertion.
Please correct me with an example would be really appreciable.
Thanks in advanceYOu can′t do a multi-insert in a table, you have to wrap these two statement in a transaction and execute / commit these two statements together.
HTH, Jens Suessmeyer.|||Note that the best way to achieve this is by performing both inserts in a transaction. And this is the simplest method to solve this problem. SQL Server 2000/2005 also has INSTEAD OF TRIGGERS that can be used to say update a complex read-only view. With this approach you will simply insert/update/delete from the view & the trigger logic can be perform the necessary DML operations on the base tables. This approach however is overkill for this particular problem.|||Just in addition, transactions are very costly on the server side, so use them wisely.
HTH, Jens Suessmeyer.
Monday, March 12, 2012
Joining date and time
Hi all,
I need some help with joining two fields of type datetime, one with date
relevancy and the other with time.
If i join the integer part of date field with the fraction part of time
field, the joined datetime is not the same.
What's the trick here?
TIA, JozzaOne way... taking date from @.a, time from @.b
declare @.a datetime, @.b datetime
set @.a = getdate()-1
set @.b = dateadd(hh,5,getdate())
select @.a, @.b,dateadd(ms,datediff(ms,convert(varcha
r(10),@.b,101),@.b),
convert(varchar(10),@.a,101))
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Can you show us an example? What do you mean by "fraction part of time?"
Keith Kratochvil
"Jozza" <hmm@.hmm.com> wrote in message
news:lrcjg.3576$oj5.1220262@.news.siol.net...
> Hi all,
> I need some help with joining two fields of type datetime, one with date
> relevancy and the other with time.
> If i join the integer part of date field with the fraction part of time
> field, the joined datetime is not the same.
> What's the trick here?
> TIA, Jozza
>|||I thought that datetime is stored the way that integer part of a float
represents the date and the fraction part represents the time.
So adding them together would join them. But it doesn't seem to be the case
on SLQ server.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OvXKJ0hjGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Can you show us an example? What do you mean by "fraction part of time?"
> --
> Keith Kratochvil
>
> "Jozza" <hmm@.hmm.com> wrote in message
> news:lrcjg.3576$oj5.1220262@.news.siol.net...
>|||Converting fields to varchar, concatenate strings and convert it back to
datetime does the trick. (which was not exactly what your exemple was, but i
got the idea)
Is there any other way where i could add fields together in mathematical
terms, because i suspect there could be and error in string conversions when
different locale formats are used. Or am i wrong?
Thanks, Jozza
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:22B16AB3-DF86-4878-B181-727F79448589@.microsoft.com...
> One way... taking date from @.a, time from @.b
> declare @.a datetime, @.b datetime
> set @.a = getdate()-1
> set @.b = dateadd(hh,5,getdate())
> select @.a, @.b,dateadd(ms,datediff(ms,convert(varcha
r(10),@.b,101),@.b),
> convert(varchar(10),@.a,101))
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Well concatenating the strings might lead to wrong date format if the string
format changes. Thats why I didn't go for the concatenation.
And the example I gave was in mathematical terms :)
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||After looking at the example a little bit longer i realize that you are
absolutely correct.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:33203119-C451-4C10-9733-F8D9EA1B0229@.microsoft.com...
> Well concatenating the strings might lead to wrong date format if the
> string
> format changes. Thats why I didn't go for the concatenation.
> And the example I gave was in mathematical terms :)
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
>
I need some help with joining two fields of type datetime, one with date
relevancy and the other with time.
If i join the integer part of date field with the fraction part of time
field, the joined datetime is not the same.
What's the trick here?
TIA, JozzaOne way... taking date from @.a, time from @.b
declare @.a datetime, @.b datetime
set @.a = getdate()-1
set @.b = dateadd(hh,5,getdate())
select @.a, @.b,dateadd(ms,datediff(ms,convert(varcha
r(10),@.b,101),@.b),
convert(varchar(10),@.a,101))
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Can you show us an example? What do you mean by "fraction part of time?"
Keith Kratochvil
"Jozza" <hmm@.hmm.com> wrote in message
news:lrcjg.3576$oj5.1220262@.news.siol.net...
> Hi all,
> I need some help with joining two fields of type datetime, one with date
> relevancy and the other with time.
> If i join the integer part of date field with the fraction part of time
> field, the joined datetime is not the same.
> What's the trick here?
> TIA, Jozza
>|||I thought that datetime is stored the way that integer part of a float
represents the date and the fraction part represents the time.
So adding them together would join them. But it doesn't seem to be the case
on SLQ server.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OvXKJ0hjGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Can you show us an example? What do you mean by "fraction part of time?"
> --
> Keith Kratochvil
>
> "Jozza" <hmm@.hmm.com> wrote in message
> news:lrcjg.3576$oj5.1220262@.news.siol.net...
>|||Converting fields to varchar, concatenate strings and convert it back to
datetime does the trick. (which was not exactly what your exemple was, but i
got the idea)
Is there any other way where i could add fields together in mathematical
terms, because i suspect there could be and error in string conversions when
different locale formats are used. Or am i wrong?
Thanks, Jozza
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:22B16AB3-DF86-4878-B181-727F79448589@.microsoft.com...
> One way... taking date from @.a, time from @.b
> declare @.a datetime, @.b datetime
> set @.a = getdate()-1
> set @.b = dateadd(hh,5,getdate())
> select @.a, @.b,dateadd(ms,datediff(ms,convert(varcha
r(10),@.b,101),@.b),
> convert(varchar(10),@.a,101))
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Well concatenating the strings might lead to wrong date format if the string
format changes. Thats why I didn't go for the concatenation.
And the example I gave was in mathematical terms :)
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||After looking at the example a little bit longer i realize that you are
absolutely correct.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:33203119-C451-4C10-9733-F8D9EA1B0229@.microsoft.com...
> Well concatenating the strings might lead to wrong date format if the
> string
> format changes. Thats why I didn't go for the concatenation.
> And the example I gave was in mathematical terms :)
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
>
Joining 2 fields: Redundancy results..need help
I am working on this access DB that has been created by some consulting firm couple of years ago, which I wasnt here at that time.
The DB is being by college faculty members to store about students who study abroad..
now,
there are students who has more than one major.. and when I run query to find out how many students are studying abroad..
it shows me more than the actual number of students who are studying abroad, because it shows same student twice because of double major..
In other words, it inserts two records for a student who has 2 majors into the DB.
Now my question is, is it possible to combine 2 records into one record on query results?
I know you can do the following: SELECT StudentName = 'major + major'
but the problem is, the name for field major is the same.. so I cannot say in my query 'Major + Major' to combine 2 records. it doesnt work..
let me know if anyone has solution to this, that will be greatful...
thanks,
moradCan you post your table structure? That would help me give you more specific answers.
The short answer is to pick one of the majors as the "most important", and select only that row using a condition in the WHERE clause. If you want access to both rows, use a LEFT JOIN to get access to the second row. If you post a DDL declaration, I can give you more specific help.
-PatP|||I just attached the structure..
well, there is most important major on this database..
and all records are stored in that table...
for example.. here is my query
First Name Last Name Host Country ProgramName Sponsor
Rebecca AINSWO Griffith University Australia N/A Direct
Ronda ALEXAN Universitat at Bonn Germany N/A Western Michigan University
Matt ANDER Rikkyo University Japan N/A Western Michigan University
Matt ANDER Rikkyo University Japan N/A Western Michigan University
Nicholas Applin University of Wollongong Australia N/A Western Michigan University
I get two records of Ander! because Matt Ander has double major, thus he has double records...two records are the same except the major.
let me know what you think...
thanks|||Now I've got the stuff that I needed to get specific! Try using:SELECT *
FROM tblMajor AS a
LEFT JOIN tblMajor AS b
ON (b.SID = a.SID)
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID)
AND (a.MajId < b.MajId OR b.MajId IS NULL);I'm pretty sure that this will give you what you want.
-PatP|||it worked :) but here is the thing though..
it only selected the duplicates, what about the other records that are not duplicate..
the query doesnt show them...
what do I have to add to show the rest... let me know :)
because I tried to use the union and I couldnt t use it union because I need to have same amount of columns for two tables.
thanks.. I appreciate your help|||Crud! The syntax I posted works with real SQL, but not with Jet (the default engine supplied with MS-Access). You could use something like:SELECT a.*
, (SELECT Max(b.MajId)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS second_major
FROM tblMajor AS a
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID);This works around an ugly limitation of the Jet database engine.
-PatP|||I tried that today at work, and it worked, but the only thing is that I wanted to change is, instead of showing what record number of the 2nd major for the student, I wanted to show the actual 2nd Major Name.
in other words I want second_major to show the actual name of the major and not the field number of 2nd major..
let me know if you have an idea..
thanks|||Picky, picky, picky... ;)SELECT a.*
, (SELECT Max(b.Major)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS second_major
FROM tblMajor AS a
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID);...should fix you right up!
-PatP|||hey pat,
thanks for great help...
Now, what I asked you about was for tblMajor.
what I am trying to do now, is run a query to list students and their majors as well as their minors..
When I tried to run your code for Majors.. it worked, and it added a new field called second major.
but now, when I try to include Minor in my query, it would show the same problem, because student can have more than one minor.
So basically, the objective is to get the following results
SID, Major, 2nd Major, Minor, 2nd Minor
And the minor table is same as major table design as shown above in my previous post.
Now what I want to know, is how to combine these two queries into one
this:
SELECT a.*, (SELECT Max(b.Major)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS [Second Major]
FROM tblMajor AS a
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID);
AND
SELECT e.*, (SELECT Max(f.Minor)
FROM tblMinor AS f
WHERE f.SID = e.SID
AND e.MinId < b.MinId) AS [Second Minor]
FROM tblMinor AS e
WHERE e.MinId = (SELECT Min(g.MinId)
FROM tblMinor AS g
WHERE g.SID = e.SID);
Now I was thinking of having using SID as relationship between them, but then I couldnt figure it out.. let me know if you have an idea of how to..
thanks|||The second minor throws an interesting wrinkle into the query, because it now makes a three set intersection instead of just two. You'll need to test this carefully with your data, but I think that you can use:SELECT a.*
, (SELECT Max(b.Major)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS second_major
, (SELECT Max(d.Minor)
FROM tblMajor AS d
WHERE d.SID = a.SID
AND d.Minor <> a.Minor) AS second_minor
FROM tblMajor AS a
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID);The gist of this query is that the first row you'd find if the table was sorted by SID then by MajId is assumed to contain the student's "primary" major and minor. The b and d subqueries find the largest Major and Minor that aren't the "primary" values. While this makes perfect sense to me as an outsider, it may or may not make sense in terms of your data, YMMV (your milage may vary). Test this carefully, but logically it should work.
-PatP|||I've tried the code you posted with few tweeks and I was able to get it through
SELECT a.*
, (SELECT Max(b.Major)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS second_major
, (SELECT Max(d.Minor)
FROM tblMinor AS d, tblMinor AS e
WHERE d.SID = a.SID
AND d.Minor <> e.Minor) AS second_minor
FROM tblMajor AS a, tblProcessInfo, tblMinor
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID) AND tblProcessInfo.SID = a.SID AND tblProcessInfo.Term = '041';
Now, this query only shows the 2nd minor and not the first minor..
I tried to select tblMinor.Minor in the select statement, and that didnt help much..
let me know what you think..
thanks..|||This is a pure crap-shoot, based on the assumption that the tblMinor structure is exactly like the tblMajor structure. You'll need to test this very carefully before you "bless" this into production!!!SELECT a.*
, (SELECT Max(b.Major)
FROM tblMajor AS e
WHERE e.SID = a.SID
AND a.MajId < e.MajId) AS second_major
, (SELECT Max(d.Minor)
FROM tblMinor AS e
WHERE e.SID = b.SID
AND b.MinId < e.MinId) AS second_minor
FROM tblProcessInfo AS p
JOIN tblMajor AS a
ON (a.SID = p.SID
AND a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID))
LEFT JOIN tblMinor AS b
on (b.SID = p.SID
AND b.MinId = (SELECT Min(d.MinId)
FROM tblMinor AS d
WHERE d.SID = a.SID))
WHERE tblProcessInfo.Term = '041';If this doesn't work, I'd suggest that you create a "play" copy of your MDB file. Butcher the names and the universities to avoid giving out any usable personal information and post the MDB so I can work with your structures instead of having to guess about everything.
Better yet, see if you can find some enterprising grad student scrabbling for some way to get a few co-op dollars or even just some resum worthy experience! I'm sure that some of them would eat this kind of problem alive, and grovel for the opportunity!
-PatP|||hey pat, thanks for your great help..
I was gone for finals and projects that were due..but everything is back to normal now :)
Now, I need a logical explanation for this problem..
When I run a query of how many students were in a certain country from year 2000 to 2004 I get 490 Students (No duplication records)
And when I run a query of how many students with majors (tblMajor Does have duplication records because of having more than one major) that went to that country from 2000 to 2004.. I get 435
I am missing 50 records when I link tblMajor.SID with tblPermInfo.SID and run a query.
I just dont get it why?!
I thought for myself, that tblPermInfo maybe is giving 490 because there is duplication of records for having more than one major, but then it is not linked to tblMajor..so there is no duplication in what so ever.
but when i run a query where SID of tblMajor and tblPermInfo is matched...it only gives me 435...
so there are SIDs that are left over because there is no match btw tables..right?
What other logical reasons could there be..
let me know what you think..
thanks
kicker
The DB is being by college faculty members to store about students who study abroad..
now,
there are students who has more than one major.. and when I run query to find out how many students are studying abroad..
it shows me more than the actual number of students who are studying abroad, because it shows same student twice because of double major..
In other words, it inserts two records for a student who has 2 majors into the DB.
Now my question is, is it possible to combine 2 records into one record on query results?
I know you can do the following: SELECT StudentName = 'major + major'
but the problem is, the name for field major is the same.. so I cannot say in my query 'Major + Major' to combine 2 records. it doesnt work..
let me know if anyone has solution to this, that will be greatful...
thanks,
moradCan you post your table structure? That would help me give you more specific answers.
The short answer is to pick one of the majors as the "most important", and select only that row using a condition in the WHERE clause. If you want access to both rows, use a LEFT JOIN to get access to the second row. If you post a DDL declaration, I can give you more specific help.
-PatP|||I just attached the structure..
well, there is most important major on this database..
and all records are stored in that table...
for example.. here is my query
First Name Last Name Host Country ProgramName Sponsor
Rebecca AINSWO Griffith University Australia N/A Direct
Ronda ALEXAN Universitat at Bonn Germany N/A Western Michigan University
Matt ANDER Rikkyo University Japan N/A Western Michigan University
Matt ANDER Rikkyo University Japan N/A Western Michigan University
Nicholas Applin University of Wollongong Australia N/A Western Michigan University
I get two records of Ander! because Matt Ander has double major, thus he has double records...two records are the same except the major.
let me know what you think...
thanks|||Now I've got the stuff that I needed to get specific! Try using:SELECT *
FROM tblMajor AS a
LEFT JOIN tblMajor AS b
ON (b.SID = a.SID)
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID)
AND (a.MajId < b.MajId OR b.MajId IS NULL);I'm pretty sure that this will give you what you want.
-PatP|||it worked :) but here is the thing though..
it only selected the duplicates, what about the other records that are not duplicate..
the query doesnt show them...
what do I have to add to show the rest... let me know :)
because I tried to use the union and I couldnt t use it union because I need to have same amount of columns for two tables.
thanks.. I appreciate your help|||Crud! The syntax I posted works with real SQL, but not with Jet (the default engine supplied with MS-Access). You could use something like:SELECT a.*
, (SELECT Max(b.MajId)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS second_major
FROM tblMajor AS a
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID);This works around an ugly limitation of the Jet database engine.
-PatP|||I tried that today at work, and it worked, but the only thing is that I wanted to change is, instead of showing what record number of the 2nd major for the student, I wanted to show the actual 2nd Major Name.
in other words I want second_major to show the actual name of the major and not the field number of 2nd major..
let me know if you have an idea..
thanks|||Picky, picky, picky... ;)SELECT a.*
, (SELECT Max(b.Major)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS second_major
FROM tblMajor AS a
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID);...should fix you right up!
-PatP|||hey pat,
thanks for great help...
Now, what I asked you about was for tblMajor.
what I am trying to do now, is run a query to list students and their majors as well as their minors..
When I tried to run your code for Majors.. it worked, and it added a new field called second major.
but now, when I try to include Minor in my query, it would show the same problem, because student can have more than one minor.
So basically, the objective is to get the following results
SID, Major, 2nd Major, Minor, 2nd Minor
And the minor table is same as major table design as shown above in my previous post.
Now what I want to know, is how to combine these two queries into one
this:
SELECT a.*, (SELECT Max(b.Major)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS [Second Major]
FROM tblMajor AS a
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID);
AND
SELECT e.*, (SELECT Max(f.Minor)
FROM tblMinor AS f
WHERE f.SID = e.SID
AND e.MinId < b.MinId) AS [Second Minor]
FROM tblMinor AS e
WHERE e.MinId = (SELECT Min(g.MinId)
FROM tblMinor AS g
WHERE g.SID = e.SID);
Now I was thinking of having using SID as relationship between them, but then I couldnt figure it out.. let me know if you have an idea of how to..
thanks|||The second minor throws an interesting wrinkle into the query, because it now makes a three set intersection instead of just two. You'll need to test this carefully with your data, but I think that you can use:SELECT a.*
, (SELECT Max(b.Major)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS second_major
, (SELECT Max(d.Minor)
FROM tblMajor AS d
WHERE d.SID = a.SID
AND d.Minor <> a.Minor) AS second_minor
FROM tblMajor AS a
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID);The gist of this query is that the first row you'd find if the table was sorted by SID then by MajId is assumed to contain the student's "primary" major and minor. The b and d subqueries find the largest Major and Minor that aren't the "primary" values. While this makes perfect sense to me as an outsider, it may or may not make sense in terms of your data, YMMV (your milage may vary). Test this carefully, but logically it should work.
-PatP|||I've tried the code you posted with few tweeks and I was able to get it through
SELECT a.*
, (SELECT Max(b.Major)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS second_major
, (SELECT Max(d.Minor)
FROM tblMinor AS d, tblMinor AS e
WHERE d.SID = a.SID
AND d.Minor <> e.Minor) AS second_minor
FROM tblMajor AS a, tblProcessInfo, tblMinor
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID) AND tblProcessInfo.SID = a.SID AND tblProcessInfo.Term = '041';
Now, this query only shows the 2nd minor and not the first minor..
I tried to select tblMinor.Minor in the select statement, and that didnt help much..
let me know what you think..
thanks..|||This is a pure crap-shoot, based on the assumption that the tblMinor structure is exactly like the tblMajor structure. You'll need to test this very carefully before you "bless" this into production!!!SELECT a.*
, (SELECT Max(b.Major)
FROM tblMajor AS e
WHERE e.SID = a.SID
AND a.MajId < e.MajId) AS second_major
, (SELECT Max(d.Minor)
FROM tblMinor AS e
WHERE e.SID = b.SID
AND b.MinId < e.MinId) AS second_minor
FROM tblProcessInfo AS p
JOIN tblMajor AS a
ON (a.SID = p.SID
AND a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID))
LEFT JOIN tblMinor AS b
on (b.SID = p.SID
AND b.MinId = (SELECT Min(d.MinId)
FROM tblMinor AS d
WHERE d.SID = a.SID))
WHERE tblProcessInfo.Term = '041';If this doesn't work, I'd suggest that you create a "play" copy of your MDB file. Butcher the names and the universities to avoid giving out any usable personal information and post the MDB so I can work with your structures instead of having to guess about everything.
Better yet, see if you can find some enterprising grad student scrabbling for some way to get a few co-op dollars or even just some resum worthy experience! I'm sure that some of them would eat this kind of problem alive, and grovel for the opportunity!
-PatP|||hey pat, thanks for your great help..
I was gone for finals and projects that were due..but everything is back to normal now :)
Now, I need a logical explanation for this problem..
When I run a query of how many students were in a certain country from year 2000 to 2004 I get 490 Students (No duplication records)
And when I run a query of how many students with majors (tblMajor Does have duplication records because of having more than one major) that went to that country from 2000 to 2004.. I get 435
I am missing 50 records when I link tblMajor.SID with tblPermInfo.SID and run a query.
I just dont get it why?!
I thought for myself, that tblPermInfo maybe is giving 490 because there is duplication of records for having more than one major, but then it is not linked to tblMajor..so there is no duplication in what so ever.
but when i run a query where SID of tblMajor and tblPermInfo is matched...it only gives me 435...
so there are SIDs that are left over because there is no match btw tables..right?
What other logical reasons could there be..
let me know what you think..
thanks
kicker
Monday, February 20, 2012
JOIN problem with time involved
I'm starting with:
dtTimeIn bStatus
1899-12-30 12:00:00 0
1899-12-30 12:00:01 0
1899-12-30 12:00:02 0
1899-12-30 12:00:03 1
1899-12-30 12:00:04 1
1899-12-30 12:00:05 0
1899-12-30 12:00:06 0
1899-12-30 12:00:07 1
I'm trying to get to:
dtTimeStart dtTimeFinish bStatus
1899-12-30 12:00:00 1899-12-30 12:00:02 0
1899-12-30 12:00:03 1899-12-30 12:00:04 1
1899-12-30 12:00:05 1899-12-30 12:00:06 0
1899-12-30 12:00:07 2005-09-09 22:00:00 1
what is the logic with which you want to create the second table?|||
I'm trying to group the 0's and 1's but still maintain the sequence of 0's and 1's.
Ex: For the first three seconds it's off then, for two seconds it's on, then for....
|||The query below should get what you want. There are some assumptions in the query that you need to modify based on your data/requirements.
with tt_seq
as
(
select t1.dtTimeIn, t1.bStatus,
coalesce((select dateadd(second, -1, min(t2.dtTimeIn))
from tt as t2
where t2.dtTimeIn > t1.dtTimeIn
and t2.bStatus = case t1.bStatus
when 0 then 1
else 0
end), t1.dtTimeIn) as next_dtTimeIn
from tt as t1
)
select min(t.dtTimeIn) as dtTimeStart, max(t.dtTimeIn) as dtTimeEnd, t.bStatus
from tt_seq as t
group by t.next_dtTimeIn, t.bStatus
order by t.next_dtTimeIn;
Thanks for the help! Took me awhile to figure what you did.
Nicely done.
|||Well done!I hope i would act as you one day.|||I've tried looking up the syntax for "with <tablename> as" in BOL 2000. Can't find anything.
Does anyone know of a keyword I can use to find out more about this syntax?
Thx.|||WITH is CTE syntax, new in SQL Server 2005. You can use a derived table in SQL Server 2000 to do the same.
select min(t.dtTimeIn) as dtTimeStart, max(t.dtTimeIn) as dtTimeEnd, t.bStatus
from
(
select t1.dtTimeIn, t1.bStatus,
coalesce((select dateadd(second, -1, min(t2.dtTimeIn))
from tt as t2
where t2.dtTimeIn > t1.dtTimeIn
and t2.bStatus = case t1.bStatus
when 0 then 1
else 0
end), t1.dtTimeIn) as next_dtTimeIn
from tt as t1
) as t
group by t.next_dtTimeIn, t.bStatus
order by t.next_dtTimeIn;|||This is a SQL Server 2005 feature. See the following entry in SQL Server 2005 BOL for more info: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/27cfb819-3e8d-4274-8bbe-cbbe4d9c2e23.htm -- Adam MachanicSQL Server MVPhttp://www.datamanipulation.net-- <Bullfrog@.discussions.microsoft.com> wrote in message news:2a2f39de-068c-4a7b-b71f-38961a9b5b69@.discussions.microsoft.com...I've tried looking up the syntax for "with <tablename> as" in BOL 2000. Can't find anything. Does anyone know of a keyword I can use to find out more about this syntax?Thx.
Subscribe to:
Posts (Atom)