Consult the SQL query

Table PRN in a number of fields, including
STID (station) ymdhm (time) bChannel (channel)
22 2013-10-10 08:00 8
22 2013-10-10 08:00 2
22 2013-10-10 09:00 8
22 2013-10-10 10:00 2

23 2013-10-10 11:00 8
23 2013-10-10 11:00 2
23 2013-10-10 12:00 2
23 2013-10-10 13:00 8

Note: every station in the integral point time, channel 2 and channel 8 are likely to enter data, I want to list the station 22, the day when the whole point of channel 8 and channel 2 is not only in the data, as indicated in the table are listed at the end of the results should be:
STID (station) ymdhm (time) bChannel (channel)
22 2013-10-10 09:00 8
At the same time, I also want to list the day 24 integer points, which the whole point of channel 2 no data.

So, my ultimate goal is, the two results are compared, get a day 24 hour, when the channel 2 broken time, channel 8 to many data.
In addition, the query can be realized in the ACCESS database.

Started by Monica at November 12, 2016 - 2:50 PM

Your request is very easy to implement SQL server, but no Access testing platform, a bit of trouble

Posted by Don at November 20, 2016 - 3:00 PM

How to realize SQL in SERVER.

Posted by Monica at November 26, 2016 - 3:07 PM

Well, no one ah...

Posted by Monica at December 01, 2016 - 4:07 PM

I will only sqlserver, the first question
----------------------------------------------------------------
Author: DBA_Huangzj (poop all over the wall)
-- Date    :2013-10-17 11:16:38
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> Test data: [prm]
if object_id('[prm]') is not null drop table [prm]
go 
create table [prm]([stid] int,[ymdhm] datetime,[bchannel] int)
insert [prm]
select 22,'2013-10-10 08:00',8 union all
select 22,'2013-10-10 08:00',2 union all
select 22,'2013-10-10 09:00',8 union all
select 22,'2013-10-10 10:00',2 union all
select 23,'2013-10-10 11:00',8 union all
select 23,'2013-10-10 11:00',2 union all
select 23,'2013-10-10 12:00',2 union all
select 23,'2013-10-10 13:00',8
- start query --------------------------


SELECT *
FROM prm a WHERE
NOT EXISTS (SELECT 1 FROM (
SELECT * FROM prm WHERE bchannel=2) b WHERE a.stid=b.stid AND a.ymdhm=b.ymdhm )
AND stid=22 AND ymdhm BETWEEN '2013-10-10 00:00:00.000' AND '2013-10-10 23:59:59.997'

The results ---------------------------- situation
/* stid        ymdhm                   bchannel
----------- ----------------------- -----------
22          2013-10-10 09:00:00.000 8
*/

Posted by Michelle at December 13, 2016 - 5:03 PM

The second problem:
----------------------------------------------------------------
Author: DBA_Huangzj (poop all over the wall)
-- Date    :2013-10-17 11:16:38
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> Test data: [prm]
if object_id('[prm]') is not null drop table [prm]
go 
create table [prm]([stid] int,[ymdhm] datetime,[bchannel] int)
insert [prm]
select 22,'2013-10-10 08:00',8 union all
select 22,'2013-10-10 08:00',2 union all
select 22,'2013-10-10 09:00',8 union all
select 22,'2013-10-10 10:00',2 union all
select 23,'2013-10-10 11:00',8 union all
select 23,'2013-10-10 11:00',2 union all
select 23,'2013-10-10 12:00',2 union all
select 23,'2013-10-10 13:00',8
- start query --------------------------
/*
Create an auxiliary table for 24 hours, 1 days
IF OBJECT_ID('Tempdb..#t','u') IS NOT NULL
DROP TABLE #t
CREATE TABLE #t([time] int)
INSERT INTO #t
SELECT 1
UNION ALL 
SELECT 2
UNION ALL 
SELECT 3
UNION ALL 
SELECT 4
UNION ALL 
SELECT 5
UNION ALL 
SELECT 6
UNION ALL 
SELECT 7
UNION ALL 
SELECT 8
UNION ALL 
SELECT 9
UNION ALL 
SELECT 10
UNION ALL 
SELECT 11
UNION ALL 
SELECT 12
UNION ALL 
SELECT 13
UNION ALL 
SELECT 14
UNION ALL 
SELECT 15
UNION ALL 
SELECT 16
UNION ALL 
SELECT 17
UNION ALL 
SELECT 18
UNION ALL 
SELECT 19
UNION ALL 
SELECT 20
UNION ALL 
SELECT 21
UNION ALL 
SELECT 22
UNION ALL 
SELECT 23
UNION ALL 
SELECT 24
*/




SELECT a.[time]
from #t a 
WHERE [time] NOT IN (SELECT [hour] FROM  (SELECT *,datepart(hh,ymdhm)[HOUR]
FROM prm 
WHERE bchannel<>2)b )



The results ---------------------------- situation
/* 
time
-----------
1
2
3
4
5
6
7
10
12
14
15
16
17
18
19
20
21
22
23
24
*/

Posted by Michelle at December 14, 2016 - 5:25 PM

create table prn
(stid int, ymdhm datetime, bchannel int)

insert into prn
 select 22,'2013-10-10 08:00',8 union all
 select 22,'2013-10-10 08:00',2 union all
 select 22,'2013-10-10 09:00',8 union all
 select 22,'2013-10-10 10:00',2 union all
 select 23,'2013-10-10 11:00',8 union all
 select 23,'2013-10-10 11:00',2 union all
 select 23,'2013-10-10 12:00',2 union all
 select 23,'2013-10-10 13:00',8


- list No. 22 station the day when the whole point of channel 8 and channel 2 is not only in the data, 
select a.stid,a.ymdhm,max(a.bchannel) 'bchannel'
 from prn a
 where a.stid=22
 group by a.stid,a.ymdhm
 having count(1)=1 and max(a.bchannel)=8

/*
stid        ymdhm                   bchannel
----------- ----------------------- -----------
22          2013-10-10 09:00:00.000 8

(1 row(s) affected)
*/ 


A list of the day -- 24 integer points, which the whole point of channel 2 no data. 
select a.number 'Channel 2 did not come to the data.'
from
(select number
 from master.dbo.spt_values
 where type='P' and number between 1 and 24) a
left join 
(select stid,ymdhm,bchannel,datepart(hh,ymdhm) 'hh' 
 from prn) b on a.number=b.hh
where b.hh is null

/*
Channel 2 did not come to the data.
-----------
1
2
3
4
5
6
7
14
15
16
17
18
19
20
21
22
23
24

(18 row(s) affected)
*/

Posted by Jesse at December 24, 2016 - 5:54 PM

WITH cte AS
(
SELECT stid,ymdhm,channel2=SUM(CASE bchannel WHEN 2 THEN 1 ELSE 0 END),channel8=SUM(CASE bchannel WHEN 8 THEN 1 ELSE 0 END)
FROM prm
GROUP BY stid,ymdhm
)

--22 base station, only 8 channel signal
SELECT stid,ymdhm,bchannel=8
FROM cte
WHERE stid=22 AND channel2=0 AND channel8>0

Posted by Haley at January 07, 2017 - 6:02 PM

if object_id('[prm]') is not null drop table [prm]
go 
create table [prm]([stid] int,[ymdhm] datetime,[bchannel] int)
insert [prm]
select 22,'2013-10-10 08:00',8 union all
select 22,'2013-10-10 08:00',2 union all
select 22,'2013-10-10 09:00',8 union all
select 22,'2013-10-10 10:00',2 union all
select 23,'2013-10-10 11:00',8 union all
select 23,'2013-10-10 11:00',2 union all
select 23,'2013-10-10 12:00',2 union all
select 23,'2013-10-10 13:00',8
- start query --------------------------

-- the first question
SELECT *
FROM prm a 
WHERE NOT EXISTS 
(
SELECT 1 FROM prm b 
WHERE b.bchannel=2 and a.stid=b.stid AND a.ymdhm=b.ymdhm 
)
AND stid=22  and a.bchannel = 8
AND ymdhm >= '2013-10-10' AND ymdhm < '2013-10-11'
/*
stid	ymdhm	                bchannel
22	    2013-10-10 09:00:00.000	8
*/


- second issues
;with t
as
(
select 1 as a
union all
select a + 1
from t
where t.a <24
)

select t.a as hour
from t
left join prm p
       on datepart(hour,p.[ymdhm]) = t.a
          and p.bchannel = 2
where p.stid is null
/*
hour
1
2
3
4
5
6
7
9
13
14
15
16
17
18
19
20
21
22
23
24
*/

Posted by Claude at January 12, 2017 - 6:32 PM