Re: Problem closing patient file! - Database Discussion Boards

Bernhard Hiller3-Mar-13 20:39

3-Mar-13 20:39

The amount of 0 items will make a problem with Mycroft Holmes' answer - that line won't show up.
You need the numbers from 0-23 (somehow generated, or make a table "Hours" with those values, and then JOIN the query suggested by Mycroft Holmes to it, and replace NULL values with 0.

Re: peak hours query

S Douglas4-Mar-13 9:49

S Douglas

4-Mar-13 9:49

Not sure if this will directly work in MySQL, but here is what I would do in SQL Server

SQL

SELECT
    DATEPART(hh, Created_Date)  [Hours]
    , COUNT(*)                  [Patients]
FROM Visit
GROUP BY
    DATEPART(hh, Created_Date)

EDIT

There is a format function in MySQL that will return the hour

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format[^]

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.

Problem closing patient file!

Jassim Rahma2-Mar-13 21:34

Jassim Rahma

2-Mar-13 21:34

Hi,

I have visits table for my patients database. I have created_date field which is timestamp with CURRENT_TIMESTAMP and closed_date with datetime datatypes. I have no problem with the created_date but when I close the file using this command:

SQL

UPDATE visits SET total_amount = param_total_amount, balance_amount = param_balance_amount, visit_is_open = FALSE, closed_date = NOW(), closed_user = param_created_user WHERE visit_id = param_visit_id;

The closed_date will have the same value of the created_date. All my closed_date now equal created_date

Technology News @ www.JassimRahma.com

Re: Problem closing patient file!

Joezer BH2-Mar-13 21:50

Joezer BH

2-Mar-13 21:50

try GETDATE()

Cheees,
Edo

Re: Problem closing patient file!

Jassim Rahma2-Mar-13 22:24

Jassim Rahma

2-Mar-13 22:24

sorry Edo, forgot to mention it's MySQL spo there is no GETDATE()

Technology News @ www.JassimRahma.com

Re: Problem closing patient file!

Joezer BH2-Mar-13 22:29

Joezer BH

2-Mar-13 22:29

Then I agree it's a strange phenomenon Smile | :)

All other fields get updates correctly or the whole row does not get updated?

Cheees,
Edo

Re: Problem closing patient file!

Sandeep Mewara2-Mar-13 21:59

Sandeep Mewara

2-Mar-13 21:59

Not sure how created_date is equal to closed_date.

Make sure, that when you create a patient, at that time populate the created_date field (not closed_date - keep it blank)
At the time of closing the patient, just update the closed_date for that patient, something like:

SQL

UPDATE visits SET closed_date = GETDATE() WHERE visit_id = param_visit_id;

Both are separate queries and both target separate fields.
Refer: http://msdn.microsoft.com/en-us/library/ms188383.aspx[^]

Sandeep Mewara
Microsoft ASP.NET MVP 2012 & 2013

[My Blog]: Sandeep Mewara's Tech Journal!
[My Latest Article]: HTML5 Quick Start Web Application

Re: Problem closing patient file!

Jassim Rahma2-Mar-13 22:26

Jassim Rahma

2-Mar-13 22:26

H Snadeep,

closed_date is Blank... here is the table structure:

SQL

CREATE TABLE `visits` (
  `visit_id` int(11) NOT NULL AUTO_INCREMENT,
  `file_no` int(11) DEFAULT NULL,
  `visit_is_open` bit(1) DEFAULT b'1',
  `clinic_id` int(11) DEFAULT NULL,
  `doctor_id` int(11) DEFAULT NULL,
  `account_category` int(11) DEFAULT NULL,
  `account_number` bigint(11) DEFAULT NULL,
  `price_list_id` int(11) DEFAULT NULL,
  `vital_signs_taken` bit(1) DEFAULT b'0',
  `total_amount` double(11,3) DEFAULT '0.000',
  `balance_amount` double(11,3) DEFAULT '0.000',
  `created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_user` int(11) DEFAULT NULL,
  `closed_date` datetime DEFAULT NULL,
  `closed_user` int(11) DEFAULT NULL,
  PRIMARY KEY (`visit_id`),
  KEY `idx_visits_visit_id` (`visit_id`),
  KEY `idx_visits_file_no` (`file_no`),
  KEY `idx_visits_visit_is_open` (`visit_is_open`),
  KEY `idx_visits_clinic_id` (`clinic_id`),
  KEY `idx_visits_doctor_id` (`doctor_id`),
  KEY `idx_visits_account_category` (`account_category`),
  KEY `idx_visits_account_number` (`account_number`),
  KEY `idx_visits_price_list_id` (`price_list_id`),
  KEY `idx_visits_vital_signs_taken` (`vital_signs_taken`),
  KEY `idx_visits_total_amount` (`total_amount`),
  KEY `idx_visits_balance_amount` (`balance_amount`),
  KEY `idx_visits_created_date` (`created_date`),
  KEY `idx_visits_created_user` (`created_user`),
  KEY `idx_visits_closed_date` (`closed_date`),
  KEY `idx_visits_closed_user` (`closed_user`)
) ENGINE=MyISAM AUTO_INCREMENT=631 DEFAULT CHARSET=utf8;

Technology News @ www.JassimRahma.com

Re: Problem closing patient file!

Sandeep Mewara2-Mar-13 22:44

Sandeep Mewara

2-Mar-13 22:44

Jassim Rahma wrote:
`created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

This is the issue. You have set the field 'create_date' to be updated to current timestamp on any update - Wrong and not needed in your case.

Just keep it:

SQL

-- If you are not updating this field on record creation
 `created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON INSERT CURRENT_TIMESTAMP,

SQL

-- If you are updating the field on record creation
`created_date` timestamp DEFAULT NULL,

Pick that you need and apply.

As per other comment of MySQL, you can use NOW()

Sandeep Mewara
Microsoft ASP.NET MVP 2012 & 2013

[My Blog]: Sandeep Mewara's Tech Journal!
[My Latest Article]: HTML5 Quick Start Web Application

Re: Problem closing patient file!

gvprabu3-Mar-13 22:18

gvprabu

3-Mar-13 22:18

Hi,

Use DEFAULT Constraint....

ALTER TABLE visits MODIFY closed_date DATETIME NOT NULL DEFAULT NOW();

Check the following link

MySQL ALTER Command[^]

modified 4-Mar-13 4:25am.

SQLExpress scripted install

loctrice2-Mar-13 0:26

loctrice

2-Mar-13 0:26

I've got a scripted install for the database on a local tablet. The OS is windows 7, and it's sql 2008. I end up with this error:

Error: 18456, Severity: 14, State: 11.
Login failed for user 'FOO\BAR'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine="">]

I've only got one user account on the machine, and it is an administrator. The best solution I can find so far is "run ssms as administrator", which is not really a valid option. The DB is used by an application only, and we don't even have ssms. I just need the app to be able to use the database. I don't understand why it can't. This same installer has worked on the other machines before this one.

-edit-
I have followed up more on this being a UAC issue. I actually was going to shut off the notifications with the EnableLUA setting in the registry... but that has already been done. I think that rules out the account not having permissions issue.
-end edit-

If it moves, compile it

modified 2-Mar-13 7:02am.

Re: SQLExpress scripted install

loctrice2-Mar-13 2:35

loctrice

2-Mar-13 2:35

this turned out to be an issue with the computer name and the user name being the same. Aparently as of Vista, this is an issue. I had ruled this out when I renamed the computer, uninstalled sql through add/remove programs, and ran the installer again.

Grabbing at straws I just happened to notice that the folders and files don't get removed when you add/remove sql through the control panel. I removed it again, and manually deleted the Microsoft SQL SErver folder, rebooted again, and installed. This worked.

If it moves, compile it

database

Kapilkp1-Mar-13 0:16

Kapilkp

1-Mar-13 0:16

1. what is active database and why it is used?
2. What are the advantages and disadvantages of active database?
3. name some application using active database?
4. Compare with other databases?

Re: database

Richard MacCutchan1-Mar-13 0:58

Richard MacCutchan

1-Mar-13 0:58

This is not a simple technical question but more akin to research. Try using Google to locate some white papers and make use of the information you find.

Use the best guess

Homework

David Mujica1-Mar-13 3:31

David Mujica

1-Mar-13 3:31

Sounds like a homework assignment.

Re: Homework

Richard MacCutchan1-Mar-13 3:33

Richard MacCutchan

1-Mar-13 3:33

More than likely; or even interview questions.

Use the best guess

Re: database

Mycroft Holmes1-Mar-13 11:57

Mycroft Holmes

1-Mar-13 11:57

1.Store data
2.Makes data available
3.Google
4.All databases are different!

5. Do your own homework.

Never underestimate the power of human stupidity
RAH

Restrict user to delete database object in SQL SERVER 2005

LTMKH28-Feb-13 19:49

LTMKH

28-Feb-13 19:49

Dear All,

I want create new user in SQL server 2005, and allow this user to run any SQL script ( most of this script use to select data using store procedure, create temp table , delete temp table...).

I want to protect this user, not allow him to delete any object such as database, table , view, function...
Can anyone help me please, I'm really stuck with searching...

Thanks.

Re: Restrict user to delete database object in SQL SERVER 2005

Eddy Vluggen1-Mar-13 5:01

Eddy Vluggen

1-Mar-13 5:01

LTM_it_kh wrote:
I want to protect this user, not allow him to delete any object such as database, table , view, function...

Can anyone help me please, I'm really stuck with searching...

Permissions[^]

..it's not "protecting" the user, but "limiting" the user.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: Restrict user to delete database object in SQL SERVER 2005

gvprabu2-Mar-13 1:56

gvprabu

2-Mar-13 1:56

Hi You will give the access rights to that user through TSQL or SQL Server Management Studio...

Check the following links

Script to determine permissions in SQL Server 2005[^]

Configuring user permissions for Microsoft SQL Server[^]

Assigning permissions and roles in SQL Server 2005/2008 databases[^]

Re: Restrict user to delete database object in SQL SERVER 2005

LTMKH28-Mar-13 5:20

LTMKH

28-Mar-13 5:20

Dear gvprabu,

Thanks you so much for your help, finally i'm successful with to do it.

Regard,

Help for RSA ...plzzz

shahbaz_llm28-Feb-13 7:19

shahbaz_llm

28-Feb-13 7:19

i extract data my box's bin file...i get data ADLER32(8bits),crc32(8bits) ""md2(32bit),md4(32bit),md5(32bit) RSA ",sha1(40bits),haval256-4(64bit),whirlpool(128bits) keys

my que is which keys are rsa(working) md2,md4,md5 ?

Regards Blush | :O

sql server table records to sqlite

jojoba201127-Feb-13 18:23

jojoba2011

27-Feb-13 18:23

Hi,
My database is on host (internet) and i wanna to get all records of one special table (it has about 1 million records) and insert into table in SQLite(in local-android mobile) .
the tables are same and only have 10 columns.

how can i do that with SP.
which way is faster?

Re: sql server table records to sqlite

_Damian S_27-Feb-13 18:35

_Damian S_

27-Feb-13 18:35

How about getting your (online) records into a comma separated file, downloading the file, then importing the records into your SQLite database?

Quad skating his way through the world since the early 80's...

Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!

Re: sql server table records to sqlite

jojoba201127-Feb-13 18:40

jojoba2011

27-Feb-13 18:40

thanks for Solution,
but i wanna something like this cause the speed is very hight and wont take time :

SQL

SELECT tt.IdAmlak, tt.ParentId, tt.NameOpp, tt.Price, tt.[Square],
       tt.OstanShahrMantagha, tt.PhoneAddress, tt.CountAmount
INTO TestTable
FROM tblTesting tt

tblTesting :  Table in database Sql Server 2008 in server internet <br />
TestTable  :  Table in Database SQLite in local (android mobile)

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.