| Author |
 Topic  |
|
|
mas1337
Starting Member
India
5 Posts |
 Posted - 04/20/2012 : 11:21:58
|
Hey,
I am making attendance management system in VS2010 with RFID technology, doing all my backend data manipulation in SQL server 2005, I know basic SQL, but I'm going to need some help with this this issue.
I have the following tables:
facultymasterdata, timings and rawdumps - data in rawdumps is saved from a serial port data logger program I wrote in vb.net, fid being the RFID tag ID, timecode being A,B,C or D (for identifying time as intime-A, lunchout-B, lunchin-C, outtime-D) and currtime is the current system time. I get the RFID tag IDs via a microcontroller that adds the time code according to physical button presses.
For example 4C00A2C82A0C-A, which I split using substrings and insert into the table.
What I need to do is sort the all the data from 'rawdumps' and copy it into 'timings' according to the timecode. The fid and currtime fields in 'rawdumps' which will have a timecode as "A" get saved into the field intime_a in timings similarly time code of B will be saved under the field lunchout_b. AND also the fname from 'facultymasterdata' corresponding to the fid.
Basically I have these:
And I need to get this:
Sample data and DDL scripts:
create table rawdumps
(
fid varchar(max) PRIMARY KEY CLUSTERED,
timecode char(10),
currtime datetime
)
insert into rawdumps values('4C00A2C82A0C','A','6:07:51 PM')
insert into rawdumps values('4C00A2C82A0C','B','7:07:51 PM')
insert into rawdumps values('4C00A2C82A0C','C','8:07:51 PM')
insert into rawdumps values('4C00A2C82A0C','D','9:07:51 PM')
create table timings
(
fname varchar(max),
fid varchar(max) PRIMARY KEY CLUSTERED,
intime_a
outtime_d
lunchout_b
lunchin_c
)
insert into timings values('Abc','4C00A2C82A0C','6:07:51 PM','9:07:51 PM','7:07:51 PM','8:07:51 PM')
create table facultymasterdata
(
fname varchar(max),
fid varchar(max) PRIMARY KEY CLUSTERED,
dept varchar(max)
title varchar(max)
phone char(10)
dob datetime
)
insert into facultymasterdata values('Abc','4C00A2C82A0C','ECE','Undergrad Student','8801589940','26/05/1988')
I tried playing around with something like this:
and I get this error "The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns."
insert into timings (fname,fid,intime_a,outtime_d,lunchout_b,lunchin_c)
select a.fname, b.fid, b.currtime
from facultymasterdata a, rawdumps b
where a.fid = b.fid
order by b.timecode
Any help of info will be appreciated.
Thanks a lot. |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 04/20/2012 : 11:47:18
|
Without commenting on the problem description that you posted, just looking at the query, the error message that you see is because the number of columns in the insert list does not match the number of columns in the select list. So your code should be one of the following. In either of the examples below, I am trying to make sure that the number of columns in the select list exactly equals and are in the same relative positions as the insert list.INSERT INTO timings
(
fname,
fid,
intime_a,
outtime_d,
lunchout_b,
lunchin_c
)
SELECT a.fname,
b.fid,
b.currtime,
NULL,
NULL,
NULL
FROM facultymasterdata a,
rawdumps b
WHERE a.fid = b.fid
ORDER BY
b.timecodeOr may be this:INSERT INTO timings
(
fname,
fid,
intime_a
)
SELECT a.fname,
b.fid,
b.currtime
FROM facultymasterdata a,
rawdumps b
WHERE a.fid = b.fid
ORDER BY
b.timecodeYou probably don't need the ORDER BY clause in your select list unless there is an identity column or something similar in the table into which you are inserting. (But that is only a comment, that is not what is causing the error message). |
 |
|
|
mas1337
Starting Member
India
5 Posts |
Posted - 04/20/2012 : 12:20:43
|
quote:
Originally posted by sunitabeck
Without commenting on the problem description that you posted, just looking at the query, the error message that you see is because the number of columns in the insert list does not match the number of columns in the select list. So your code should be one of the following. In either of the examples below, I am trying to make sure that the number of columns in the select list exactly equals and are in the same relative positions as the insert list.INSERT INTO timings
(
fname,
fid,
intime_a,
outtime_d,
lunchout_b,
lunchin_c
)
SELECT a.fname,
b.fid,
b.currtime,
NULL,
NULL,
NULL
FROM facultymasterdata a,
rawdumps b
WHERE a.fid = b.fid
ORDER BY
b.timecodeOr may be this:INSERT INTO timings
(
fname,
fid,
intime_a
)
SELECT a.fname,
b.fid,
b.currtime
FROM facultymasterdata a,
rawdumps b
WHERE a.fid = b.fid
ORDER BY
b.timecodeYou probably don't need the ORDER BY clause in your select list unless there is an identity column or something similar in the table into which you are inserting. (But that is only a comment, that is not what is causing the error message).
Hi, thanks for your reply, both those queries work. But what they do is add all the values of currtime under intime_a, I need it to split the currtime into intime_a,outtime_d,lunchout_b and lunchin_c according to the timecode field next to the currtime.
Essentially convert the currtime cloumn into rows according to the timecode field. |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 04/20/2012 : 12:56:56
|
I think this - but run the select query without the insert part to see if that is what you really want.INSERT INTO timings
(
fname,
fid,
intime_a,
outtime_d,
lunchout_b,
lunchin_c
)
SELECT a.fname,
b.fid,
MAX(CASE WHEN timeCode = 'A' THEN b.currtime END) AS inttime_a,
MAX(CASE WHEN timeCode = 'D' THEN b.currtime END) AS outtime_d,
MAX(CASE WHEN timeCode = 'B' THEN b.currtime END) AS lunchout_b,
MAX(CASE WHEN timeCode = 'C' THEN b.currtime END) AS lunchin_c
FROM facultymasterdata a,
rawdumps b
WHERE a.fid = b.fid
GROUP BY a.fname, b.fid |
|
|
|
mas1337
Starting Member
India
5 Posts |
Posted - 04/20/2012 : 13:44:47
|
quote:
Originally posted by sunitabeck
I think this - but run the select query without the insert part to see if that is what you really want.INSERT INTO timings
(
fname,
fid,
intime_a,
outtime_d,
lunchout_b,
lunchin_c
)
SELECT a.fname,
b.fid,
MAX(CASE WHEN timeCode = 'A' THEN b.currtime END) AS inttime_a,
MAX(CASE WHEN timeCode = 'D' THEN b.currtime END) AS outtime_d,
MAX(CASE WHEN timeCode = 'B' THEN b.currtime END) AS lunchout_b,
MAX(CASE WHEN timeCode = 'C' THEN b.currtime END) AS lunchin_c
FROM facultymasterdata a,
rawdumps b
WHERE a.fid = b.fid
GROUP BY a.fname, b.fid
That's exactly what I needed, I was looking into cross tabs and pivots. This works perfectly!
Thank you Sunita. |
|
|
| |
Topic |
|
|
|
insert data into a table from another table, while
