Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS SQL Server MGMT Express

Posted on 2016-08-21
10
Medium Priority
?
45 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 35

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 35

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 35

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

722 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