Solved

MS SQL Server MGMT Express

Posted on 2016-08-21
10
41 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 39

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
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.

 

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 39

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSMS Opening Mode 9 20
SQL Syntax Grouping Sum question 7 27
SSRS: Why is Visual Studio stripping these properties? 2 24
SQL Find Carriage Return and Delete it. 3 16
I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

808 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