Avatar of AmitavaCh
AmitavaCh
Flag 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 '%'
Microsoft SQL Server 2005

Avatar of undefined
Last Comment
AmitavaCh

8/22/2022 - Mon
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
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
AmitavaCh

ASKER
Thank you
Your help has saved me hundreds of hours of internet surfing.
fblack61