|
Ok, Please HELLLLLP 
I have this table with 16 rows only:
http://www.jassimrahma.com/temp/attendence_table.png[^]
and I am using below code now to split the time of attendance into columns and getting this result:
http://www.jassimrahma.com/temp/attendence_result.png[^]
but I am not happoy with it! for example, 7th July, there is only one fingerprint so it should be only F1 but it's repeating it in F1, F3 and F4.
SELECT DATE(attendance_date_time) AS attendance_date, SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 1), ',', -1) AS F1,
IF(LENGTH(GROUP_CONCAT(TIME(attendance_date_time))) - LENGTH(REPLACE(GROUP_CONCAT(TIME(attendance_date_time)), ',', '')) > 1,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 2), ',', -1) ,NULL) AS F2,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 3), ',', -1) AS F3,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 4), ',', -1) AS F4
FROM employee_attendance
GROUP BY DATE(attendance_date_time);
How can I fix this please?
Thanks,
Jassim[^]
Technology News @ www.JassimRahma.com
|
|
|
|
|
anybody can help please?
Technology News @ www.JassimRahma.com
|
|
|
|
|
Hi All,
I have Table A, TabB and TableC, I need to fill TableC by using Select statement and join on TableB and TableA two times. Because TableC is a many to many relationship table on TableA and TableB.
TableA(Id, Name, Desc)
TableB(Id, TableAId, AnotherTableAId, TableAName, AnotherTableAName)
TableC(Id, TableAId, AnotherTableAId, TableAName, AnotherTableAName)
Now the problem is TableA fills the new Data and TableC should be synced to TableB but should have new Ids from TableA and same TableAName, AnotherTableAName values from Old TableB rows. If I run as below, the rows aren't coming correctly, some times more rows than in TableB or some time less rows than in TableB.
Here is how I tried but failed, any suggestion or link or even code snippet would help a lot.
Insert into TableC (TableAId, AnotherTableAId, TableAName, AnotherTableAName)
SELECT TableAId, AnotherTableAId, TableAName, AnotherTableAName FROM TableB b
INNER JOIN TableA a ON a.Name = b. TableAName
INNER JOIN TableA a2 ON a2.Name = b. AnotherTableAName
Please help me with this, thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Look into UNION. Split your query into 2 by removing the second join.
Insert into TableC (TableAId, AnotherTableAId, TableAName, AnotherTableAName)
SELECT TableAId, AnotherTableAId, TableAName, AnotherTableAName FROM TableB b
INNER JOIN TableA a2 ON a2.Name = b. AnotherTableAName
UNION
Insert into TableC (TableAId, AnotherTableAId, TableAName, AnotherTableAName)
SELECT TableAId, AnotherTableAId, TableAName, AnotherTableAName FROM TableB b
INNER JOIN TableA a ON a.Name = b. TableAName
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
I did all of that, thanks for your help and what I felt was to use the Table variable and insert values into it, and then loop through table variable to check if that record combination doesn't exist in the table then insert the set.
I like your approach too.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
I got it Kuldeep, thanks for your support, it is possible by using Merge and Table Variables.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Hi,
I am using aloha POS and they have the date for every check in separate fields and now I want to calculate the total time for the checks but unable to get the how of it..
- The date is DOB and it's datetime but I just need to extra the getdate() from it.
- The open time is OPENHOUR and OPENMIN
- The close time is CLOSEHOUR and CLOSEMIN
so basically the open time for the check will be the DATE FROM DOB + OPENHOUR + OPENMIN
and the close time will be DATE FROM DOB + CLOSEHOUR + CLOSEMIN
How can I get the total minutes for the check?
Thanks,
Jassim[^]
Technology News @ www.JassimRahma.com
|
|
|
|
|
This might give you dome ideas, demonstrating the DATEADD and Convert Date possibilities
DECLARE @DOB DATETIME = GETDATE()
DECLARE @ODate DATE
DECLARE @OHour INT = 8
DECLARE @OMin INT = 23
DECLARE @OpenDT DATETIME
SELECT @ODate = CONVERT(DATE,@DOB)
SET @OpenDT = @ODate
SELECT @OpenDT = DATEADD(HOUR,@OHour,@OpenDT)
SELECT @OpenDT = DATEADD(MINUTE,@OMin,@OpenDT)
PRINT @OpenDT
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You can simplify slightly
DECLARE @ODate DATE
DECLARE @OHour INT = 8
DECLARE @OMin INT = 23
DECLARE @OpenDT DATETIME
declare @DOB DATE = getdate()
SELECT @OpenDT = @DOB
SELECT @OpenDT = DATEADD(MINUTE,(@OHour * 60) + @OMin, @OpenDT)
PRINT @OpenDT
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
You've declared @ODate as DATE, so you can't add time parts to it.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I'm adding the minutes to @OpenDT so it does work - @ODate is superfluous and can be removed
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Hi All,
I have a situation that I have 3 tables like TabA, TabB and TabC, I have to insert into TabC by using a select statement from TabB but one value I have to take from Scope_Identity from inserting a row into TabA.
So my Insert should look like below.
Insert into TabC (Col1, Col2, TabAId)
Select Col1, Col2, (Insert into TabA(CurrentDate) Values (DateTime) Scope_Identity) FROM TabB.
So the Scope_Identity of the TabA insert should be used in the Select.
Do we have any way to do it? Any sort of advice, code snippet and link is greatly helpful. Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
You can't embed an INSERT statement within a SELECT statement.
Try something like this:
DECLARE @TabAId int;
INSERT INTO TabA (CurrentDate) VALUES (GetUtcDate());
SET @TabAId = Scope_Identity();
INSERT INTO TabC (Col1, Col2, TabAId)
SELECT Col1, Col2, @TabAId FROM TabB;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
This works if we have only one TabAId, but we need multiple TabAIds like different TabAIds for every row that SELECT Col1, Col2, @TabAId FROM TabB; generates, thats my problem. Can you help me pls?
Even if I can use Insert withing Select that's also fine only thing if I can retrieve that Scope_Identity in selected rows.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Or even if I can do it in the following way but I am getting errors in the below query, it is saying its not able to recognize the columns, htge.[Name], htge.[Description]. Can I get those columns into Table variable some how? It will fix all my problems.
DECLARE @MyTableVar table (DocumentListId int, [Name] varchar(max), [Description] varchar(max));
INSERT INTO dbo.DocumentList (CreatedDate,CreatedBy,ModifiedDate,ModifiedBy)
OUTPUT INSERTED.DocumentListId,
htge.[Name],
htge.[Description]
INTO @MyTableVar
SELECT GETDATE() ,CURRENT_USER,GETDATE(),CURRENT_USER, htge.[Name], htge.[Description]
FROM OPENROWSET('SQLNCLI', 'Server=xxx;UID=xx;PWD=xxx',
'SELECT * FROM WEB_WebAdmin.dbo.HealthTopicGroup where HealthTopicGroupId < 45') as htge;
SELECT * frOM @MyTableVar
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
You could use an onInsert trigger spit to insert the new records into TabC.
Caveat an error on the trigger is a bitch to locate in the future.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I just discovered what is a big problem with the order that child rows are retrieved from datatables created from a SQLCe database.
Definition
'Front Inner Camber Link
Dim arrFrInnerCamb() As RCCarV2DataSet.FrInnerCambRow
Dim FrInnerCambRow As DataRow
The child rows are retrieved and stored in an array of DataRows.
'Get the array of alternate Inner Camber Positions
arrFrInnerCamb = FrSuspRow.GetChildRows("FrSuspension_FrInnerCamb")
The problem is the order the values are stored in the Sql database are not always stored in the order of the primary key as I expect. This problem only occurs after deleting rows from the database.
To correct this problem I would like to just sort the arrFrInnerCamb by the primary key and all will be good. Unfortunately can't figure out a simple way to do this. I can use a brute force and ignorance method but figure there must be an easier way. Any suggestions would be appreciated.
|
|
|
|
|
Try storing the child rows in a DataTable, you will then have all the manipulating capabilities inherent in the datatable and sorting will be simple.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks I'm trying that. I was just hoping for an easy way to sort the DataRow Array so I don't have to do a major program restructure.
|
|
|
|
|
Using the array of datarows is wrong, so you will benefit from the refactor 
You could sort the datarows BEFORE you load them into the array!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
How to optimize this query.
SELECT share_id, page_id, parent_title, title, share_type, shared_on,
IF(FIND_IN_SET('".$_POST["student_id"]."_Y', students), 'Y', 'N') AS is_viewed
FROM dash_sharing
WHERE book_id = :book_id AND
( FIND_IN_SET('".$_POST["student_id"]."_N', students)
OR FIND_IN_SET('".$_POST["student_id"]."_Y', students))
AND (page_id, shared_on) IN (SELECT page_id, MAX(shared_on)
FROM dash_sharing AS mtab
WHERE
book_id = :book_id AND ( FIND_IN_SET('".$_POST["student_id"]."_N', students)
OR FIND_IN_SET('".$_POST["student_id"]."_Y', students))
GROUP BY page_id)
ORDER BY FIELD(is_viewed, 'N','Y') ASC, shared_on DESC
|
|
|
|
|
I am building a system in asp.net C# with an Sql Server DB.
I have split the work into separate tasks.
I have put a timeline of around 41 days. The requirements are as below.
Can anyone say how long it would take and if my timeline is too long?
1. Requirements Gathering and Associated Documentation - Requirements Specification/Functional Specification and Agreement with Stakeholder(s)/Users - 4 days
2 Create DB and Tables and 4 tables - 4 days
3 Create Login Form - 2 days
4 Create Menu - 2 days
5 Create Initial Input Form - containing all the fields that need to be recorded inititally and the 2 calculated fields - 5 days
6 Create “Update Single File” form - Allow user to update this file and write it back to the table in the DB - 3 days
7 Create “Update Multiple Files” form - Display multiple files maybe based criteria selected from dropdown and allow user to possibly do a bulk update of the records - 4 days
8 Create “Add Staff” form and have it writing to the Staff table - 2 days
9 Create “LogOut” form - 1 day
10 Create “Reports”. This would involve creating reports that would have the Grouping of the data in the various different scenarios e.g. Total for Invoice, Total for Trans Number. A report for Audit trail may be needed also. These reports may need to be created in SSRS reporting. - 5 days
11 Audit Trail - Create a record in the audit table each time a record has been updated. Maybe display this audit trail in the “Update Single File” form - 3 days
12 Create Stored Procedures in DB to carry out the above functions - 3 days
13 Carry out functional reviews with Stakeholder(s)/Users - 1 day
14 Carry out Unit testing - 1 day
15 UAT with Stakeholder(s)/Users and Sign Off of System - 1 day
|
|
|
|
|
That really is like asking "how long is a piece of string?"  Only you will know if you can actually achieve this timeline.
Some observations from my own experiences though
Step 1 - if you get "awkward" stakeholders this step could well take longer than 4 days (actual time spent or elapsed!)
Step 2 - seems a little long as you appear to already have an idea of the tables required - if you're including all of the admin hoops to jump through with DBA's or other control mechanisms I would make it clear in the estimate
Step 14 - seems a bit light, but some of the builds do seem a bit long.
Step 15 - see my earlier comments about stakeholders! I've never run UAT in less than 3 days (and even that was a struggle)
If this timeline is going out to the stakeholders double everything and make it clear whether or not this is a "working days elapsed" timeline or a "working hours spent"
Under promise and over deliver 
|
|
|
|
|
Hi!
On SQL server, when I try to save value 0.4569 to a decimal(9,3) column, the value is truncated. It becomes 0.456. It there a way how to change this behavior of sql server? I would like such numbers to be rounded (to 0.457 in my example) instead of truncating them. Is there some global setting for this?
Thanks!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
