Link to home
Start Free TrialLog in
Avatar of AmitavaCh
AmitavaChFlag for India

asked on

Help in MS SQL

Hi,

The following query gives me the output of a day's attendance only (First IN & Last OUT).
Now, we would like to store the data in a separate table (or may be create a view)and would also like to insert next days' data in the same table, without deleting previous days records. We don't want to work with the main table i.e. History Table, since it has end numbers of rows and query through this table takes some time. We would like to use the new view for publishing attendance on a daily basis. The data can be pushed to the new table at a schedule time during night.

Request you to help me to modify the query.

SELECT * FROM
(SELECT a.link3,
REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') new_date,
MIN(a.TIMESTAMP) AS First_in,
MAX(a.TIMESTAMP) AS Last_Out
from History a
where REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') = '04-apr-2013'
GROUP BY a.link3, REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-')
) A
INNER JOIN emp_sms_email b ON a.link3 = b.CardHolderID
where b.CardNumber like '%'
Avatar of Rich Weissler
Rich Weissler

To get your existing query to output to a table, I'd take the following actions:

1. Expand out the '*' to specify the specific data values you will be putting into the new table.  If anything will break your new query, that * will.  

2. Create the table you'll be putting the data into.  The 'quick and dirty' way to do it would be to change the outermost query to the form
Select <expanded out field list> 
   INTO <newtablename>
  (
     {Inner query unchanged}
  )
  {INNER JOIN unchanged}
  where b.CardNumber like '%' AND 0=1

Open in new window

Which will create the new table, and populate it with zero rows.
(Caveat - if you are returning IDENTITY columns, you'll need decide how you are going to deal with that data, possibly turn off Identity in the new table?)

3. Last, remove the 'AND 0=1' from the WHERE clause, cut the 'INTO <newtablename>' from the Select, and add a 'INSERT INTO <newtablename>' in the line before the first SELECT.
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AmitavaCh

ASKER

Thank you