Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS SQL Server MGMT Express

Posted on 2016-08-21
10
Medium Priority
?
46 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 36

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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 36

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 36

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

783 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