Solved

MS SQL Server MGMT Express

Posted on 2016-08-21
10
40 Views
Last Modified: 2016-08-28
Dear
I need your help . I have attendance application run on  MS SQL express and i need to get all transactions that recorded in PM (TRANS_TIME) to Change it  be ( Out) transactions and all AM in(TRANS_TIME)  to be changed to (IN ) , as i have many transactions recorded wrong on the system .
Note:
IN = 0  ( in TRANS_TYPE_ID) column in DB
OUT=1 ( in TRANS_TYPE_ID) column in DB
1.jpg
0
Comment
Question by:Alkannetworks
  • 5
  • 3
  • 2
10 Comments
 
LVL 38

Expert Comment

by:Aaron Tomosky
ID: 41764537
i can't write pastable sql code on my mobile, but here is a high level psudosql
Update tablename set transtypeid =0 where convert(trans_time to 24 hour) < 12

Then the reverse for >= 12 is PM
0
 
LVL 33

Expert Comment

by:ste5an
ID: 41764554
E.g.
WHERE HOUR(trans_time) < 12

Open in new window

0
 

Author Comment

by:Alkannetworks
ID: 41764823
Dears
Can u help me in writing the complete right code
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:Alkannetworks
ID: 41764831
let me correct my question , how i can select just all transactions in PM or in AM so i can select trasntype_id and change it manual to in or out transactions
thnks
0
 
LVL 33

Expert Comment

by:ste5an
ID: 41764892
What is the problem? Take your code and complete it:

Update tablename set transtypeid =0
WHERE HOUR(trans_time) < 12

Open in new window

0
 

Accepted Solution

by:
Alkannetworks earned 0 total points
ID: 41765171
it works when i wrote it like below
-------------------------------------------------------
UPDATE    RAW_TRANSACTIONS
SET              TRANS_TYPE_ID = 0
WHERE     ({ fn HOUR(TRANS_TIME) } < 12)
0
 
LVL 33

Expert Comment

by:ste5an
ID: 41765302
hmm, sry, meant DATEPART(HOUR, trans_time) < 12..
0
 

Author Closing Comment

by:Alkannetworks
ID: 41772830
no one gives me complete correct answer
0
 
LVL 38

Expert Comment

by:Aaron Tomosky
ID: 41773023
That's right, we give assistance and guidance not a "complete correct answer" that you can just paste right in and use. If you are going to expect us to do all the work for you, at least setup a sqlfiddle but know that many of the experts including myself will not just do the work, we offer guidance.
0
 

Author Comment

by:Alkannetworks
ID: 41773717
Ok , I know that , I am Just not familiar with SQL , and for sure I didn't mean that to find someone to do my work .
Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question