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)
 JOINING OF 2 QUERIES

Author  Topic 

smitha
Posting Yak Master

100 Posts
Posted - 2010-03-02 : 03:25:20
Hi, I have got 2 queries and I am getting the resulet for those queries as shown below:

query1
20/02/2010 23:00:00 -0.013666
20/02/2010 00:00:00 -0.013500
21/02/2010 01:00:00 -0.010666
21/02/2010 02:00:00 -0.014000
21/02/2010 03:00:00 -0.012833

query2

20/02/2010 23:00:00 0.01
21/02/2010 00:00:00 0.00
21/02/2010 01:00:00 -0.02
21/02/2010 02:00:00 0.01
21/02/2010 03:00:00 -0.03

but when I joining these queries I am getting the result as follows:

20/02/2010 23:00:00 0.01 -0.013666
21/02/2010 01:00:00 -0.02 -0.010666
21/02/2010 02:00:00 0.01 -0.014000
21/02/2010 03:00:00 -0.03 -0.012833

00:00:00 data is not getting clubbed.
Can anyone help on this problem?




Smitha

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2010-03-02 : 03:36:37
Show your statement please.

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

smitha
Posting Yak Master

100 Posts
Posted - 2010-03-02 : 03:44:44
The query for joining 2 queries is as follows:

SELECT T1A.LOGDATE1, CONVERT(CHAR(10), T1A.LOGTIME1, 108) AS LOGTIME2, T2S.E1_ELE1
FROM dbo.E1_SHIFTC_AVG AS T1A INNER JOIN dbo.E1_SHIFTC_SPOT AS T2S ON T1A.LOGDATE1 = CONVERT(CHAR(10), T2S.LOGDATE, 103) AND ABS(DATEDIFF(minute, T1A.LOGTIME1,T2S.LOGTIME)) < 1


Smitha
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-03-02 : 03:47:58
what is the datatype of T1A.LOGDATE1 ?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

smitha
Posting Yak Master

100 Posts
Posted - 2010-03-02 : 03:54:11
the datatype is datetime
quote:
Originally posted by madhivanan

what is the datatype of T1A.LOGDATE1 ?

Madhivanan

Failing to plan is Planning to fail


Smitha
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-03-02 : 03:58:44

What happens when you try this?

SELECT T1A.LOGDATE1, CONVERT(CHAR(10), T1A.LOGTIME1, 108) AS LOGTIME2, T2S.E1_ELE1
FROM dbo.E1_SHIFTC_AVG AS T1A INNER JOIN dbo.E1_SHIFTC_SPOT AS T2S ON T1A.LOGDATE1 = T2S.LOGDATE AND ABS(DATEDIFF(minute, T1A.LOGTIME1,T2S.LOGTIME)) < 1



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

smitha
Posting Yak Master

100 Posts
Posted - 2010-03-02 : 05:10:02
I am not getting any result
quote:
Originally posted by madhivanan


What happens when you try this?

SELECT T1A.LOGDATE1, CONVERT(CHAR(10), T1A.LOGTIME1, 108) AS LOGTIME2, T2S.E1_ELE1
FROM dbo.E1_SHIFTC_AVG AS T1A INNER JOIN dbo.E1_SHIFTC_SPOT AS T2S ON T1A.LOGDATE1 = T2S.LOGDATE AND ABS(DATEDIFF(minute, T1A.LOGTIME1,T2S.LOGTIME)) < 1



Madhivanan

Failing to plan is Planning to fail


Smitha
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-03-02 : 06:26:37
SELECT T1A.LOGDATE1, CONVERT(CHAR(10), T1A.LOGTIME1, 108) AS LOGTIME2, T2S.E1_ELE1
FROM dbo.E1_SHIFTC_AVG AS T1A INNER JOIN dbo.E1_SHIFTC_SPOT AS T2S ON T1A.LOGDATE1 = dateadd(day,datediff(day,0,T2S.LOGDATE),0) AND ABS(DATEDIFF(minute, T1A.LOGTIME1,T2S.LOGTIME)) < 1


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

smitha
Posting Yak Master

100 Posts
Posted - 2010-03-02 : 06:33:56
still I am not getting any result
quote:
Originally posted by madhivanan

SELECT T1A.LOGDATE1, CONVERT(CHAR(10), T1A.LOGTIME1, 108) AS LOGTIME2, T2S.E1_ELE1
FROM dbo.E1_SHIFTC_AVG AS T1A INNER JOIN dbo.E1_SHIFTC_SPOT AS T2S ON T1A.LOGDATE1 = dateadd(day,datediff(day,0,T2S.LOGDATE),0) AND ABS(DATEDIFF(minute, T1A.LOGTIME1,T2S.LOGTIME)) < 1


Madhivanan

Failing to plan is Planning to fail


Smitha
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-03-02 : 07:19:54
SELECT T1A.LOGDATE1, CONVERT(CHAR(10), T1A.LOGTIME1, 108) AS LOGTIME2, T2S.E1_ELE1
FROM dbo.E1_SHIFTC_AVG AS T1A INNER JOIN dbo.E1_SHIFTC_SPOT AS T2S ON T1A.LOGDATE1 >= dateadd(day,datediff(day,0,T2S.LOGDATE),0) AND T1A.LOGDATE1 < dateadd(day,datediff(day,0,T2S.LOGDATE)+1,0) AND ABS(DATEDIFF(minute, T1A.LOGTIME1,T2S.LOGTIME)) < 1



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

smitha
Posting Yak Master

100 Posts
Posted - 2010-03-02 : 08:04:22
no, still I am not getting any data,
but if I give the query like this

SELECT T1A.LOGDATE1, CONVERT(CHAR(10), T1A.LOGTIME1, 108) AS LOGTIME2, T2S.E1_ELE1
FROM dbo.E1_SHIFTC_AVG AS T1A INNER JOIN dbo.E1_SHIFTC_SPOT AS T2S ON T1A.LOGDATE1 >= CONVERT(CHAR(10), DATEADD(day, DATEDIFF(day, 0, T2S.LOGDATE), 0), 103) AND T1A.LOGDATE1 < CONVERT(CHAR(10), DATEADD(day, DATEDIFF(day, 0, T2S.LOGDATE) + 1, 0), 103) AND ABS(DATEDIFF(minute, T1A.LOGTIME1,T2S.LOGTIME)) < 1

I am getting the data like this, but agian not with 12 o clock data

11/02/2010 02:00:00 12.00
10/02/2010 23:00:00 12.00
11/02/2010 01:00:00 12.00
11/02/2010 03:00:00 12.00


quote:
Originally posted by madhivanan

SELECT T1A.LOGDATE1, CONVERT(CHAR(10), T1A.LOGTIME1, 108) AS LOGTIME2, T2S.E1_ELE1
FROM dbo.E1_SHIFTC_AVG AS T1A INNER JOIN dbo.E1_SHIFTC_SPOT AS T2S ON T1A.LOGDATE1 >= dateadd(day,datediff(day,0,T2S.LOGDATE),0) AND T1A.LOGDATE1 < dateadd(day,datediff(day,0,T2S.LOGDATE)+1,0) AND ABS(DATEDIFF(minute, T1A.LOGTIME1,T2S.LOGTIME)) < 1



Madhivanan

Failing to plan is Planning to fail


Smitha
Go to Top of Page

smitha
Posting Yak Master

100 Posts
Posted - 2010-03-03 : 05:57:04
is there anyone who can help on this matter
quote:
Originally posted by smitha

no, still I am not getting any data,
but if I give the query like this

SELECT T1A.LOGDATE1, CONVERT(CHAR(10), T1A.LOGTIME1, 108) AS LOGTIME2, T2S.E1_ELE1
FROM dbo.E1_SHIFTC_AVG AS T1A INNER JOIN dbo.E1_SHIFTC_SPOT AS T2S ON T1A.LOGDATE1 >= CONVERT(CHAR(10), DATEADD(day, DATEDIFF(day, 0, T2S.LOGDATE), 0), 103) AND T1A.LOGDATE1 < CONVERT(CHAR(10), DATEADD(day, DATEDIFF(day, 0, T2S.LOGDATE) + 1, 0), 103) AND ABS(DATEDIFF(minute, T1A.LOGTIME1,T2S.LOGTIME)) < 1

I am getting the data like this, but agian not with 12 o clock data

11/02/2010 02:00:00 12.00
10/02/2010 23:00:00 12.00
11/02/2010 01:00:00 12.00
11/02/2010 03:00:00 12.00


quote:
Originally posted by madhivanan

SELECT T1A.LOGDATE1, CONVERT(CHAR(10), T1A.LOGTIME1, 108) AS LOGTIME2, T2S.E1_ELE1
FROM dbo.E1_SHIFTC_AVG AS T1A INNER JOIN dbo.E1_SHIFTC_SPOT AS T2S ON T1A.LOGDATE1 >= dateadd(day,datediff(day,0,T2S.LOGDATE),0) AND T1A.LOGDATE1 < dateadd(day,datediff(day,0,T2S.LOGDATE)+1,0) AND ABS(DATEDIFF(minute, T1A.LOGTIME1,T2S.LOGTIME)) < 1



Madhivanan

Failing to plan is Planning to fail


Smitha


Smitha
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-03-03 : 08:06:52
Post your query1 and query2

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

smitha
Posting Yak Master

100 Posts
Posted - 2010-03-04 : 03:34:58
Average query

SELECT CONVERT(CHAR(10), LOGDATE, 103) AS LOGDATE1, CONVERT(CHAR(10), DATEADD(HOUR, DATEDIFF(HOUR, 0, LOGTIME) + 1, 0), 108) AS LOGTIME1, AVG(E1_ELE1) AS E1_ELE1_AVG
FROM dbo.E1_SHIFTREPA
WHERE (DATEPART(HOUR, LOGTIME) IN (22, 23)) OR
(LOGTIME >= DATEADD(hh, - 1, DATEADD(day, DATEDIFF(day, 0, LOGDATE) + 1, 0))) AND (LOGTIME <= DATEADD(day, DATEDIFF(day, 0, LOGDATE) + 1,
0)) AND (LOGDATE = CASE WHEN LOGTIME = '00:00:00' THEN dateadd(day, + 1, LOGDATE) END) 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)

AVERAGE QUERY RESULT
20/02/2010 23:00:00 -0.013666
20/02/2010 00:00:00 -0.013500
21/02/2010 01:00:00 -0.010666
21/02/2010 02:00:00 -0.014000
21/02/2010 03:00:00 -0.012833


SPOT 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:02:59', 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))

SPOT QUERY RESULT
20/02/2010 23:00:00 0.01
21/02/2010 00:00:00 0.00
21/02/2010 01:00:00 -0.02
21/02/2010 02:00:00 0.01
21/02/2010 03:00:00 -0.03


JOINING QUERY

SELECT T1A.LOGDATE1, CONVERT(CHAR(10), T1A.LOGTIME1, 108) AS LOGTIME2, T2S.E1_ELE1
FROM dbo.E1_SHIFTC_AVG AS T1A INNER JOIN dbo.E1_SHIFTC_SPOT AS T2S ON T1A.LOGDATE1 = CONVERT(CHAR(10), T2S.LOGDATE, 103) AND
ABS(DATEDIFF(minute, T1A.LOGTIME1,T2S.LOGTIME)) < 2

JOINING QUERY RESULT

20/02/2010 23:00:00 0.01 -0.013666
21/02/2010 01:00:00 -0.02 -0.010666
21/02/2010 02:00:00 0.01 -0.014000
21/02/2010 03:00:00 -0.03 -0.012833
quote:
Originally posted by madhivanan

Post your query1 and query2

Madhivanan

Failing to plan is Planning to fail


Smitha
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-03-04 : 04:42:48
Try this


SELECT LOGDATE1,LOGTIME1, AVG(E1_ELE1) AS E1_ELE1_AVG 
FROM
(
SELECT CONVERT(CHAR(10), LOGDATE, 103) AS LOGDATE1, CONVERT(CHAR(10), DATEADD(HOUR, DATEDIFF(HOUR, 0, LOGTIME) + 1, 0), 108) AS LOGTIME1, AVG(E1_ELE1) AS E1_ELE1_AVG
FROM dbo.E1_SHIFTREPA
WHERE (DATEPART(HOUR, LOGTIME) IN (22, 23)) OR
(LOGTIME >= DATEADD(hh, - 1, DATEADD(day, DATEDIFF(day, 0, LOGDATE) + 1, 0))) AND (LOGTIME <= DATEADD(day, DATEDIFF(day, 0, LOGDATE) + 1, 
0)) AND (LOGDATE = CASE WHEN LOGTIME = '00:00:00' THEN dateadd(day, + 1, LOGDATE) END) 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)

UNION ALL

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:02:59', 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))
) AS T
GROUP BY LOGDATE1,LOGTIME1


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

smitha
Posting Yak Master

100 Posts
Posted - 2010-03-04 : 07:08:27
I am getting the result like below, but I am not getting any values

10/2/2010 12:00:00 AM 1/1/1900 12:00:00 AM
10/2/2010 12:00:00 AM 1/1/1900 11:00:00 PM
11/2/2010 12:00:00 AM 1/1/1900 12:00:00 AM
11/2/2010 12:00:00 AM 1/1/1900 1:00:00 AM
11/2/2010 12:00:00 AM 1/1/1900 2:00:00 AM
11/2/2010 12:00:00 AM 1/1/1900 3:00:00 AM
11/2/2010 12:00:00 AM 1/1/1900 4:00:00 AM
11/2/2010 12:00:00 AM 1/1/1900 5:00:00 AM
11/2/2010 12:00:00 AM 1/1/1900 6:00:00 AM
11/2/2010 12:00:00 AM 1/1/1900 11:00:00 PM
12/2/2010 12:00:00 AM 1/1/1900 1:00:00 AM
12/2/2010 12:00:00 AM 1/1/1900 2:00:00 AM
12/2/2010 12:00:00 AM 1/1/1900 3:00:00 AM
12/2/2010 12:00:00 AM 1/1/1900 4:00:00 AM
12/2/2010 12:00:00 AM 1/1/1900 5:00:00 AM
12/2/2010 12:00:00 AM 1/1/1900 6:00:00 AM
quote:
Originally posted by madhivanan

Try this


SELECT LOGDATE1,LOGTIME1, AVG(E1_ELE1) AS E1_ELE1_AVG 
FROM
(
SELECT CONVERT(CHAR(10), LOGDATE, 103) AS LOGDATE1, CONVERT(CHAR(10), DATEADD(HOUR, DATEDIFF(HOUR, 0, LOGTIME) + 1, 0), 108) AS LOGTIME1, AVG(E1_ELE1) AS E1_ELE1_AVG
FROM dbo.E1_SHIFTREPA
WHERE (DATEPART(HOUR, LOGTIME) IN (22, 23)) OR
(LOGTIME >= DATEADD(hh, - 1, DATEADD(day, DATEDIFF(day, 0, LOGDATE) + 1, 0))) AND (LOGTIME <= DATEADD(day, DATEDIFF(day, 0, LOGDATE) + 1, 
0)) AND (LOGDATE = CASE WHEN LOGTIME = '00:00:00' THEN dateadd(day, + 1, LOGDATE) END) 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)

UNION ALL

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:02:59', 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))
) AS T
GROUP BY LOGDATE1,LOGTIME1


Madhivanan

Failing to plan is Planning to fail


Smitha
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-03-04 : 07:30:28
Are you sure you are getting the above results only?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

smitha
Posting Yak Master

100 Posts
Posted - 2010-03-04 : 23:54:41
YES, Whatever I am getting the result the same thing I have copied here
quote:
Originally posted by madhivanan

Are you sure you are getting the above results only?

Madhivanan

Failing to plan is Planning to fail


Smitha
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-03-05 : 02:08:39
Note that the SELECT statement starts with

SELECT LOGDATE1,LOGTIME1, AVG(E1_ELE1) AS E1_ELE1_AVG

You can see three columns there
How is it possible when you run the query you get only two columns?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

smitha
Posting Yak Master

100 Posts
Posted - 2010-03-05 : 05:55:52
true there are 3 columns, but I am not getting the result for e1_ele1

and if I add another 2 columns also, I am getting the result only for date and time
Smitha
Go to Top of Page

smitha
Posting Yak Master

100 Posts
Posted - 2010-03-05 : 06:10:52
one thing I observed, If I put the code in a new query the , AVG(E1_ELE1) AS E1_ELE1_AVG is getting deleted and only date and time results are getting populated.
SELECT LOGDATE1, LOGTIME1
FROM(SELECT CONVERT(CHAR(10), LOGDATE, 103) AS LOGDATE1, CONVERT(CHAR(10), DATEADD(HOUR, DATEDIFF(HOUR, 0, LOGTIME) + 1, 0), 108)AS LOGTIME1, AVG(E1_ELE1) AS E1_ELE1_AVG FROM dbo.E1_SHIFTREPA
WHERE (DATEPART(HOUR, LOGTIME) IN (22, 23)) OR (LOGTIME >= DATEADD(hh, - 1, DATEADD(day, DATEDIFF(day, 0, LOGDATE) + 1, 0))) AND (LOGTIME <= DATEADD(day, DATEDIFF(day, 0,LOGDATE) + 1, 0)) AND (LOGDATE = CASE WHEN LOGTIME = '00:00:00' THEN dateadd(day, + 1, LOGDATE) END) 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)
UNION ALL
SELECT LOGDATE, LOGTIME, E1_ELE1 FROM dbo.E1_SHIFTREPA AS E1_SHIFTREPA_1
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:02:59', 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))) AS T
GROUP BY LOGDATE1, LOGTIME1



whereas If I put the code in already created query I am getting the error as follows:
invalid column name 'E1_ELE1'

Smitha
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site