November 26, 2012 at 4:38 pm
Hi,
not quite sure if this is info info...but i wanna convert this sp to an append sp.
with rows as (
select *, row_number() over (order by emppin, trxdate) as rownum
from rawtrx)
select *, rowsMinusOne.trxtime as trxtimeIN,
ISNULL(rows.trxtime,1) as trxtimeOUT
from rows as rowsMinusOne
left outer join rows
on rows.rownum = rowsMinusOne.rownum + 1 and rows.emppin = rowsMinusOne.emppin
Think you can help?
November 26, 2012 at 5:09 pm
I'm not sure what you mean. That's not an sp and I'm unaware of the term append sp.
Could you clarify what you need?
November 27, 2012 at 4:10 am
ALTER PROCEDURE dbo.zz_sp_testing_inout
AS
WITH rows AS (SELECT *, row_number() OVER (ORDER BY emppin, trxdate) AS rownum
FROM rawtrx)
SELECT *, rowsMinusOne.trxtime AS trxtimeIN, ISNULL(rows.trxtime, 1) AS trxtimeOUT
FROM rows AS rowsMinusOne LEFT OUTER JOIN
rows ON rows.rownum = rowsMinusOne.rownum + 1 AND rows.emppin = rowsMinusOne.emppin
Okay, does that look more like a stored procedure?
well this works exactly the way I want it to work, it returns the records...I want to add the records returned by this code above to a table.
is that possible?
November 27, 2012 at 4:35 am
Yes, if the table already exists then add an INSERT INTO command below the CTE declaration above the SELECT.
If the table doesnt exist, add the INTO clause above the FROM.
WITH rows AS (SELECT *, row_number() OVER (ORDER BY emppin, trxdate) AS rownum
FROM rawtrx)
INSERT INTO atable (col1, col2,.................)
SELECT *, rowsMinusOne.trxtime AS trxtimeIN, ISNULL(rows.trxtime, 1) AS trxtimeOUT
FROM rows AS rowsMinusOne LEFT OUTER JOIN
rows ON rows.rownum = rowsMinusOne.rownum + 1 AND rows.emppin
WITH rows AS (SELECT *, row_number() OVER (ORDER BY emppin, trxdate) AS rownum
FROM rawtrx)
SELECT *, rowsMinusOne.trxtime AS trxtimeIN, ISNULL(rows.trxtime, 1) AS trxtimeOUT
INTO atable
FROM rows AS rowsMinusOne LEFT OUTER JOIN
rows ON rows.rownum = rowsMinusOne.rownum + 1 AND rows.emppin
November 27, 2012 at 4:37 am
Yes, if the table already exists then add an INSERT INTO command below the CTE declaration above the SELECT.
If the table doesnt exist, add the INTO clause above the FROM.
WITH rows AS (SELECT *, row_number() OVER (ORDER BY emppin, trxdate) AS rownum
FROM rawtrx)
INSERT INTO atable (col1, col2,.................)
SELECT *, rowsMinusOne.trxtime AS trxtimeIN, ISNULL(rows.trxtime, 1) AS trxtimeOUT
FROM rows AS rowsMinusOne LEFT OUTER JOIN
rows ON rows.rownum = rowsMinusOne.rownum + 1 AND rows.emppin
WITH rows AS (SELECT *, row_number() OVER (ORDER BY emppin, trxdate) AS rownum
FROM rawtrx)
SELECT *, rowsMinusOne.trxtime AS trxtimeIN, ISNULL(rows.trxtime, 1) AS trxtimeOUT
INTO atable
FROM rows AS rowsMinusOne LEFT OUTER JOIN
rows ON rows.rownum = rowsMinusOne.rownum + 1 AND rows.emppin
November 27, 2012 at 6:13 pm
ah! yes, i did try this ...i got an error but i know what the reason for the error is so it will work. Thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply