Solved

MS SQL Server MGMT Express

Posted on 2016-08-21
10
42 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 34

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 34

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 34

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

751 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