VIEWING OF JOINED TABLES - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

VIEWING OF JOINED TABLES

Author

Topic

smitha
Posting Yak Master

100 Posts

Posted - 2010-02-23 : 03:57:25

Hi,
I am using 3 queries.
In 1st query I am taking the average of the data based on time.
In 2nd query I am taking the data for that particular time.
Now I am clubbing these 2 queries in 3rd query by using the query as below.

select T2S.E1_ELE1, T1A.E1_ELE1_AVG from dbo.E1_SHIFTA_AVG AS T1A INNER JOIN dbo.E1_SHIFTA_SPOT AS T2S ON T1A.LOGDATE1 = CONVERT(CHAR(10), T2S.LOGDATE, 103) AND T1A.LOGTIME1 = T2S.LOGTIME

The problem is while I am clubbing the queries, all the datas are not clubbed because I have given the query T1A.LOGTIME1 = T2S.LOGTIME and if the time is different by at least 1 second (ex: 1st query time is 7:00:00 and the 2nd query time is 7:00:01) the related data will not be updated in the 3rd query.
I want to club the data if it is 7:00:00 or 7:00:01.
please help me in this matter.
Thanks in advance

Smitha

Kristen
Test

22859 Posts

Posted - 2010-02-23 : 04:09:14

"... T1A.LOGDATE1 = CONVERT(CHAR(10), T2S.LOGDATE, 103) ..."

Do you really have a text date in one table, and a datetime one in another?

And what is LOGTIME1 - another text field containing a time? or a datetime field?

This would be so straightforward with a single datetime datatype field.

smitha
Posting Yak Master

100 Posts

Posted - 2010-02-23 : 04:18:08

T1A.LOGTIME1 is the time from 2nd query and T2S is the time from 1st query.
So I am giving the condition saying that both the time should be same.
I know the solution that only the condition should be for hours and minutes(that means both the queries time - only hour and minute should match). But I don't know how to put it in code.

Smitha

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-23 : 04:30:31

What is the datatype of LOGDATE1?

You can trucate seconds part by

dateadd(minute,datediff(minute,0,T1A.LOGTIME1 ),0)

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-23 : 04:30:32

What is the datatype of LOGDATE1?

You can trucate seconds part by

dateadd(minute,datediff(minute,0,T1A.LOGTIME1 ),0)

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-02-23 : 04:54:25

If both fields were combined Date and Time in a datetime datatype object you could just do

WHERE ABS(DATEDIFF(Second, DateTimeCol1, DateTimeCol2)) < 2 -- within 2 seconds

but you have one text date, one datetime date, and times in a separate field (and presumably text format?). You could still do it with CONVERT and string concatenation ... but ... why? Why not sort out the data / column DDL to use the proper datetime datatype in the first place?

smitha
Posting Yak Master

100 Posts

Posted - 2010-02-23 : 06:24:11

I Truncated and got the result and thank you once again I have got one more query
as I said I am using 3 queries. In first query I am taking the average values and in 2nd query I am taking the data for a particular time.

Now what is happening is at night 12 0 clock in 1st query it is giving the date as previous date only whereas in 2nd query it is giving the date as next date.
So when I tried to club these queries the 12 o clock data is not coming.
Can you help in this matter.

quote:
Originally posted by madhivanan

What is the datatype of LOGDATE1?

You can trucate seconds part by

dateadd(minute,datediff(minute,0,T1A.LOGTIME1 ),0)

Madhivanan

Failing to plan is Planning to fail

Smitha

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-23 : 07:45:27

Can you post the full code you used?

Madhivanan

Failing to plan is Planning to fail

smitha
Posting Yak Master

100 Posts

Posted - 2010-02-23 : 08:00:08

SELECT CONVERT(CHAR(10), LOGDATE, 103) AS LOGDATE1, DATEADD(HOUR, DATEDIFF(HOUR, 0, LOGTIME) + 1, 0) AS LOGTIME1, AVG(E1_ELE1)
FROM dbo.E1_SHIFTREPA
WHERE (DATEPART(HOUR, LOGTIME) IN (22, 23)) OR
(DATEPART(HOUR, LOGTIME) IN (23, 24)) OR
(DATEPART(HOUR, LOGTIME) IN (0, 1)) OR
(DATEPART(HOUR, LOGTIME) IN (1, 2)) OR
(DATEPART(HOUR, LOGTIME) IN (2, 3)) OR
(DATEPART(HOUR, LOGTIME) IN (3, 4)) OR
(DATEPART(HOUR, LOGTIME) IN (4, 5))
GROUP BY LOGDATE, DATEADD(HOUR, DATEDIFF(HOUR, 0, LOGTIME) + 1, 0), CONVERT(CHAR(10), LOGDATE, 103)
this is one query
SELECT LOGDATE, LOGTIME, E1_ELE1 FROM dbo.E1_SHIFTREPA
WHERE (LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 22:58:00', 102) AND CONVERT(DATETIME, '1900-01-01 23:02:00', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 23:58:00', 102) AND CONVERT(DATETIME, '1900-01-01 00:00:00', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 00:58:00', 102) AND CONVERT(DATETIME, '1900-01-01 01:02:00', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 01:58:00', 102) AND CONVERT(DATETIME, '1900-01-01 02:02:00', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 02:58:00', 102) AND CONVERT(DATETIME, '1900-01-01 03:02:00', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 03:58:00', 102) AND CONVERT(DATETIME, '1900-01-01 04:02:00', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 04:58:00', 102) AND CONVERT(DATETIME, '1900-01-01 05:02:00', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 05:58:00', 102) AND CONVERT(DATETIME, '1900-01-01 06:02:00', 102))
this is second query

Sorry I changed the timings

Can anyone help on this
Smitha

smitha
Posting Yak Master

100 Posts

Posted - 2010-02-25 : 00:41:40

Is there anybody who can help on this topic

Smitha

smitha
Posting Yak Master

100 Posts

Posted - 2010-02-25 : 02:33:35

AM I TO UNDERSTAND THAT THERE IS NO ONE WHO CAN SOLVE THIS PROBLEM

Smitha

Kristen
Test

22859 Posts

Posted - 2010-02-25 : 02:49:24

I suggested 2 days ago that you change your strings to datetime datatype columns, and combine the time with the date in a single column, and then the problem becomes easy.

Speaking for myself I find the way you are tackling it with differing datatypes unworkable, and I don't have the time that it will take to help you work a solution that way about. Sorry.

smitha
Posting Yak Master

100 Posts

Posted - 2010-02-25 : 04:20:43

I attempted your 2 days back answer also, but I am still with the same problem till now.
And if you don't have the time to answer, that's ok. anyway thanks

quote:
Originally posted by Kristen

I suggested 2 days ago that you change your strings to datetime datatype columns, and combine the time with the date in a single column, and then the problem becomes easy.

Speaking for myself I find the way you are tackling it with differing datatypes unworkable, and I don't have the time that it will take to help you work a solution that way about. Sorry.

Smitha

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-25 : 04:44:54

quote:
Originally posted by smitha

SELECT CONVERT(CHAR(10), LOGDATE, 103) AS LOGDATE1, DATEADD(HOUR, DATEDIFF(HOUR, 0, LOGTIME) + 1, 0) AS LOGTIME1, AVG(E1_ELE1)
FROM dbo.E1_SHIFTREPA
WHERE (DATEPART(HOUR, LOGTIME) IN (22, 23)) OR
(DATEPART(HOUR, LOGTIME) IN (23, 24)) OR
(DATEPART(HOUR, LOGTIME) IN (0, 1)) OR
(DATEPART(HOUR, LOGTIME) IN (1, 2)) OR
(DATEPART(HOUR, LOGTIME) IN (2, 3)) OR
(DATEPART(HOUR, LOGTIME) IN (3, 4)) OR
(DATEPART(HOUR, LOGTIME) IN (4, 5))
GROUP BY LOGDATE, DATEADD(HOUR, DATEDIFF(HOUR, 0, LOGTIME) + 1, 0), CONVERT(CHAR(10), LOGDATE, 103)
this is one query
SELECT LOGDATE, LOGTIME, E1_ELE1 FROM dbo.E1_SHIFTREPA
WHERE (LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 22:58:00', 102) AND CONVERT(DATETIME, '1900-01-01 23:02:00', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 23:58:00', 102) AND CONVERT(DATETIME, '1900-01-01 00:00:00', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 00:58:00', 102) AND CONVERT(DATETIME, '1900-01-01 01:02:00', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 01:58:00', 102) AND CONVERT(DATETIME, '1900-01-01 02:02:00', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 02:58:00', 102) AND CONVERT(DATETIME, '1900-01-01 03:02:00', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 03:58:00', 102) AND CONVERT(DATETIME, '1900-01-01 04:02:00', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 04:58:00', 102) AND CONVERT(DATETIME, '1900-01-01 05:02:00', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 05:58:00', 102) AND CONVERT(DATETIME, '1900-01-01 06:02:00', 102))
this is second query

Sorry I changed the timings

Can anyone help on this
Smitha

Why do you want to club these two?

Madhivanan

Failing to plan is Planning to fail

smitha
Posting Yak Master

100 Posts

Posted - 2010-02-25 : 05:42:01

because the 1st query contains the average values and the second query contains the instantaneous values and I need both of them in single query for my application.
all the datas are coming in the 3rd query except for the time 00:00:00 because in 1st query it is stamped as previous date with the time as 00:00:00 and in the 2nd query it is stamped as today's date 00:00:00.

quote:
Originally posted by madhivanan

quote:
Originally posted by smitha

SELECT CONVERT(CHAR(10), LOGDATE, 103) AS LOGDATE1, DATEADD(HOUR, DATEDIFF(HOUR, 0, LOGTIME) + 1, 0) AS LOGTIME1, AVG(E1_ELE1)
FROM dbo.E1_SHIFTREPA
WHERE (DATEPART(HOUR, LOGTIME) IN (22, 23)) OR
(DATEPART(HOUR, LOGTIME) IN (23, 24)) OR
(DATEPART(HOUR, LOGTIME) IN (0, 1)) OR
(DATEPART(HOUR, LOGTIME) IN (1, 2)) OR
(DATEPART(HOUR, LOGTIME) IN (2, 3)) OR
(DATEPART(HOUR, LOGTIME) IN (3, 4)) OR
(DATEPART(HOUR, LOGTIME) IN (4, 5))
GROUP BY LOGDATE, DATEADD(HOUR, DATEDIFF(HOUR, 0, LOGTIME) + 1, 0), CONVERT(CHAR(10), LOGDATE, 103)
this is one query
SELECT LOGDATE, LOGTIME, E1_ELE1 FROM dbo.E1_SHIFTREPA
WHERE (LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 22:58:00', 102) AND CONVERT(DATETIME, '1900-01-01 23:02:00', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 23:58:00', 102) AND CONVERT(DATETIME, '1900-01-01 00:00:00', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 00:58:00', 102) AND CONVERT(DATETIME, '1900-01-01 01:02:00', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 01:58:00', 102) AND CONVERT(DATETIME, '1900-01-01 02:02:00', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 02:58:00', 102) AND CONVERT(DATETIME, '1900-01-01 03:02:00', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 03:58:00', 102) AND CONVERT(DATETIME, '1900-01-01 04:02:00', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 04:58:00', 102) AND CONVERT(DATETIME, '1900-01-01 05:02:00', 102)) OR
(LOGTIME BETWEEN CONVERT(DATETIME, '1900-01-01 05:58:00', 102) AND CONVERT(DATETIME, '1900-01-01 06:02:00', 102))
this is second query

Sorry I changed the timings

Can anyone help on this
Smitha

Why do you want to club these two?

Madhivanan

Failing to plan is Planning to fail

Smitha

Kristen
Test

22859 Posts

Posted - 2010-02-25 : 05:46:37

Well I'm sorry, but one of us is misunderstanding something. You didn't answer my suggestion of using datetime datatype.

Here's a much better suggestion:

Post a DDL (Create table) of the table.

Also post some INSERT statements with some sample data (a few rows will do)

Also post the results you expect to get, and an explanation of what you want to happen.

That will be much better for you to learn, and for us to help you do it right, that us trying to understand how you are trying to manage your text-format dates

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources