Challenges master, seeking sql statement.

Category: MS-SQL Server -> Foundation Classes Author: bentianamu2 Date: 2011-06-22 14:24:37
 
bentianamu2
2011-06-22 14:24:37
Table a

field
card date ; out flag
id (int) brcard (bigint 8) brdate (varchar 8) brinouttype (varchar 1 )

data(0 is valid)
1 00001 2004-4-5 0
2 000012004-4-5 0
3 00001 ; 2004-4-6 ; 0
4 00002 2004-4-5 0
5 00002 ; 2004-4-6 0
6000022004-4-6 0
7 ; 000032004-4-5 ; 0
8 00003 2004-4-6 0
9 00003 ; 2004-4-6 ; 0
10 00003 2004-4-60
............
..........
...........
found 5-6 this time, within which the card credit card number in the day most of times,
example, the above is
0003 2004-4 - 6 3 times
xiaobao8848
2011-06-22 14:35:37
select top 1 brcard, brdate = max (brdate), frequency = count (*)
from a
where brdate = '2004-4-6 'and brinouttype = 0
group by brcard
order by count (*) desc
zhengniuniu
2011-06-22 14:46:26

select top 1 *
from
(select brcard, brdate, count (*) c
from a
where (brdate between '2004- 4-5 'and '2004-4-6')
and brinouttype = 0
group by brcard, brdate
) b
order ; by c desc
snoopy90324
2011-06-22 14:51:32
- credit card number if you want to consider the maximum number of records, and several of these are displayed, use:

select brcard, brdate = max (brdate), frequency = count (*)
from a
where brdate = '2004-4-6 'and brinouttype = 0
group by brcard
having count (*) = (
select top 1 frequency = count (*)
from a
where brdate = '2004-4-6 'and brinouttype = 0
group by brcard
order by count (*) desc)
best663
2011-06-22 15:03:23
- Test

- Test Data
create table a (id int, brcard char (5), brdate varchar (8), brinouttype varchar (1))
insert a select 1, '00001 ', '2004-4-5', 0
union all select 2, '00001 ', '2004-4-5', 0
union all select 3, '00001 ', '2004-4-6', 0
union all select 4, '00002 ', '2004-4-5 ', 0
union all select 5, '00002', '2004-4-6 ', 0
union all select 6, '00002', '2004-4 -6 ', 0
union all select 7, '00003', '2004-4-5 ', 0
union all select 8, '00003', '2004 -4-6 ', 0
union all select 9, '00003', '2004-4-6 ', 0
union all select 10, '00003', ' 2004-4-6 ', 0
union all select 11, '00002', '2004-4-6 ',0 - increased a test card swipe up with two cases
go

- credit card number if you want to consider the maximum number of records, and several of these are displayed, use:
select brcard, brdate = max (brdate), frequency = count (*)
from a
where brdate = '2004-4-6 'and brinouttype = 0
group by brcard
having count (*) = (
select top 1 frequency = count (*)
from a
where brdate = '2004-4-6 ' and brinouttype = 0
group by brcard
order by count (*) desc)
go

- remove test
drop table a

/ * - Test results

brcard brdate Views ;
-------------------------
00002 2004-4-6 3
00003 2004-4-6 3

(the number of rows affected by line 2)

- * /
rufeng0324
2011-06-22 15:17:04
elder brothers, the above has been resolved, thank you
but it is not the point
in the above table there is a field
table a

field
card Date Time Access flag
id (int) brcardno (bigint 8) brdate (varchar 8) brtime brinouttype ( varchar 1)

data ; ; (0 is valid)
1 ; 000012004-4-5 ; 8:00 0 ;
2 00001 ; 2004-4-5 11:30 ; 0
3 00001 2004-4-6 8:00 0
4 00002 ; 2004-4-5 8:00 ; 0
5 00002 2004-4-6 8:00 0
6 00002 ; 2004-4-6 11:30 ; 0
7 00003 2004-4-5 8:00 0
8 00003 ; 2004-4-6 8:00 ; 0
9 000032004-4-6 11: 30 0
10 00003 ; 2004-4-62 : 00 0
............
..........
how to put the data in the table below a sheet
b

field
Date Card time a credit card swipe card time 2 times 3
id (int) brcardno (bigint 8 ) (varchar 8) (varchar 8) (varchar 8)
to achieve results:
00001 2004-4-5 ; 8:00 11:30
00001 ; 2004-4-6 8:00
00002 2004-4-5 8:00
00002 ; 2004-4-6 8:00 ; 11:30
00003 2004-4-5 ; 8:00
00003 2004 -4-6 8:00 11 : 30 2:00

above solution, and immediately put points
smallbox5090
2011-06-22 15:35:37

- generated record
isnert table b table b (brcardno, swipe Time 1, Time 2 credit card, credit card time 3)
select brcard, brdate
, swipe time 1 = max (case gid% 3 when 0 then brtime else'' end)
, swipe time 2 = max (case gid% 3 when 1 then brtime else '' end)
, swipe time 3 = max (case gid% 3 when 2 then brtime else'' end)
from (
select ; brcard, brdate, brtime
, gid = (select count (*) from a
where a.brcard = aa.brcard
and a.brdate = aa.brdate
and a.brtime <= aa.brtime) -1
from a aa
) a group by brcard, brdate, gid / 3
g940124004
2011-06-22 15:50:33
insert b (card number, date, time of a credit card, credit card time 2, swipe time 3)
select brcard, brdate
, swipe time 1 = max (case gid% 3 when 0 then brtime else'' end)
, swipe time 2 = max (case gid% 3 when 1 then brtime else'' end)
, swipe time 3 = max (case gid% 3 when 2 then brtime else'' end)
from (
select brcard, brdate, brtime
, gid = (select count (*) from a
where a.brcard = aa.brcard
and a.brdate = aa.brdate
and a.brtime <= aa.brtime) -1
from a aa
) a group by brcard, brdate, gid / 3
tao1005
2011-06-22 16:04:49
- Test

- Test Data
create table a (id int, brcard char (5), brdate varchar (8), brtime varchar (8) , brinouttype varchar (1))
insert a select 1, '00001 ', '2004-4-5', '08: 00 ', 0
union all select 2, '00001 ', '2004-4-5', '11: 30 ', 0
union all select 3, '00001', '2004-4-6 ',' 8:00 ', 0
union all select 4, '00002', '2004-4-5 ', '8: 00', 0
union all select 5, '00002 ', '2004-4-6', '8: 00 ', 0
union all select 6, '00002', '2004-4-6 ' , '11: 30 ', 0
union all select 7, '00003', '2004-4-5 ', '8: 00', 0
union all select 8, '00003 ', '2004-4-6', '8: 00 ', 0
union all select 9, '00003', '2004-4-6 ' , '11: 30 ', 0
union all select 10, '00003', '2004-4-6 ', '2: 00', 0
go

- Table b
create table b (card char (5), date varchar (10), swipe time 1 varchar (8), swipe time 2 varchar (8 ), swipe time 3 varchar (8))
go

- generate table b recording
insert b (card number, date, time of a credit card, credit card time 2, Swipe Time 3)
select brcard, brdate
, swipe time 1 = max (case gid% 3 when 0 then brtime else'' end)
, Swipe time 2 = max (case gid% 3 when 1 then brtime else'' end)
, swipe time 3 = max (case gid% 3 when 2 then brtime else'' end)
from (
select brcard, brdate, brtime
, gid = (select count (*) from a
where a.brcard = aa.brcard
and a.brdate = aa.brdate
and a.brtime <= aa.brtime) -1
from a ; aa
) a group by brcard, brdate, gid / 3
go

- Show Insert Results
select * from b
go

- remove test
drop table a, b

/ * - Test Results

Date Time a swipe card swipe card time 2 hours 3
------------------------------------------ -
00001 2004-4-5 08:00 11:30
00001 2004 -4-6 8:00
00002 2004-4-5 ; 8:00
00002 2004-4-6 ; 11:30 8:00
00003 2004-4-5 8:00 ;
00003 2004-4-6 11:30 ; 2:00 8:00

(the number of rows affected by 6 lines)
- - * /
Xbadger
2011-06-22 16:23:51
zjcxc (Zou) ....

time a credit card, credit card time 2, swipe time 3 These fields are based on the above maximum number of times (00032004-4-6 total of three times) of the decision, is a dynamic field.
This means that if there is a card one day brush N times, table b swipe time on the field There are N.
that time a credit card swipe card time 2 hours 3 hours 4 credit card swipe time 5 ...... Time N
card .. how to do that?
a5523320
2011-06-22 16:32:47
B table structure that gives you what? B table structure fixed, the result has been dead set , to dynamic, then we would dynamically generated table B
zjyhclf
2011-06-22 16:39:41
- Test

- Test Data
create table a (id int, brcard char (5), brdate varchar (8), brtime varchar (8), brinouttype varchar (1))
insert a select 1, '00001 ', '2004-4-5 ', '08: 00', 0
union all select 2, '00001 ', '2004-4-5', '11: 30 ', 0
union all select ; 3, '00001 ', '2004-4-6', '8: 00 ', 0
union all select 4, '00002', '2004-4-5 ', '8: 00 ', 0
union all select 5, '00002', '2004-4-6 ', '8: 00', 0
union all ; select 6, '00002 ', '2004-4-6', '11: 30 ', 0
union all select 7, '00003', '2004-4-5 ', '8: 00 ', 0
union all select 8, '00003', '2004-4-6 ', '8: 00', 0
union all ; select 9, '00003 ', '2004-4-6', '11: 30 ', 0
union all select 10, '00003', '2004-4-6 ',' 2:00 ', 0
go

- generate table b recording
declare @ s varchar (8000), @ i int
select ; @ s ='', @ i = max (times)
from (select number = count (*) from a group by brcard, brdate) a
while @ i> 0
select @ s = ', swipe time' + cast (@ i as varchar) + '= max (case gid when' + cast (@ i as varchar) + 'then brtime else'''' end)' + @ s
, @ i = @ i-1
exec ('select card = brcard, date = brdate' + @ s + '
from (
select brcard, brdate, brtime
, gid = (select count (*) from a
where a.brcard = aa.brcard
and a.brdate = aa.brdate
and a.brtime <= aa.brtime) -1
from a aa
) a ; group by brcard, brdate
')
go

- remove test
drop table a

/ * - Test results

date card swipe time a swipe card time 2 hours 3
-------------------------------- -----------
00001 2004-4-5 08:00 11:30 ;
00001 2004-4-6 8:00 ;
00002 2004-4-5 8:00
00002 2004-4-6 11:30 8:00
00003 2004-4-5 ; 8:00
00003 2004-4-6 ; 11:30 2:00 8:00

(the number of rows affected 6 lines)
- * /
bjnjboy
2011-06-22 16:49:51
- Test

- Test Data
create table a (id int, brcard char (5), brdate varchar (8), brtime varchar (8), brinouttype varchar (1))
insert a select 1, '00001 ', '2004-4-5', '08: 00 ', 0
union all select 2, '00001', '2004-4-5 ', '11: 30', 0
union all select 3, '00001 ', '2004-4-6', '8: 00 ', 0
union all select 4, '00002', '2004-4-5 ', '8: 00', 0
union all select 5, '00002 ', '2004-4-6', '8: 00 ', 0
union all select 6, '00002 ', '2004-4-6', '11: 30 ', 0
union all select 7, '00003', '2004-4-5 ', '8: 00', 0
union all select 8, '00003 ', '2004-4-6', '8: 00 ', 0
union all select 9, '00003 ', '2004-4-6', '11: 30 ', 0
union all select 10, '00003', ' 2004-4-6 ', '2: 00', 0
go

- generate table b recording
declare @ s varchar (8000), @ i int
select @ s ='', @ i = max (times)
from (select number = count (*) from a group by brcard, brdate) a
while @ i> 0
select @ s = ', swipe time' + cast (@ i as varchar) + '= max (case gid when' + cast ( @ i as varchar) + 'then brtime else'''' end)' + @ s
, @ i = @ i-1
exec ('select card = brcard, date = brdate '+ @ s +'
from (
select brcard, brdate, brtime
, gid = (select count (*) from a
where a.brcard = aa.brcard
and a.brdate = aa.brdate
and a.brtime <= aa.brtime)
from a aa
) a group by brcard, brdate
order by brcard, brdate
')
go

- remove test
drop table a

/ * - Test results

Date Card ; the time a card swipe card time 2 hours 3
----------------- --------------------------
00001 2004-4-5 08: 00 11:30
00001 2004-4-6 8:00
00002 2004-4-5 8:00
00002 2004-4-6 11:30 8:00
00003 2004-4-5 8:00
00003 2004-4-6 11:30 2:00 8:00

(the number of rows affected by 6 lines)
- * /
kingwolfster
2011-06-22 16:53:38
- processing statement

declare @ s varchar (8000), @ i int
select @ s ='', @ i = max (times)
from (select number of times = count (*) from a group by brcard, brdate) a
while @ i> 0
select @ s = ', swipe time' + cast (@ i as varchar) + '= max (case gid when' + cast (@ i as varchar) + 'then brtime else'''' end)' + @ s
, @ i = @ i-1
exec ('select card = brcard, date = brdate' + @ s + '
from (
select brcard, brdate, brtime
, gid = (select count (*) from a
where a.brcard = aa.brcard
and a.brdate = aa.brdate
and a . brtime <= aa.brtime)
from a aa
) a group by brcard, brdate
order by brcard, brdate
')
fjpppo
2011-06-22 16:57:31
is a dynamic table above has been resolved. there is a problem:
If only pass an array variable, some card
example: brcard: 3301001,3301002,3301003 .... This is the date ...
card swipe at those time (only one array variable)
how to do /