Database

Move GROUP_CONCAT's comma values to columns

19-Oct-15 10:28

Perfect, thank you very much Holmes.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."

Jassim Rahma15-Oct-15 6:45

Re: Move GROUP_CONCAT's comma values to columns

15-Oct-15 6:45

Hi,

I am using below code to display times as Pivot table grouped by date, this is basically for fingerprint attendance... I am getting what I want like this:

2012-06-03 10:23:30,10:23:32,10:24:05,10:24:07,10:24:24,10:24:26

How can I make the comma separated values displayed in columns instead of comma so it will be something like this

created_date - time1 - time2 - time3 - time4 --- etc

this is the code:

SQL

SELECT created_date, GROUP_CONCAT(created_time) 
FROM fingerprint
GROUP BY created_date

Technology News @ www.JassimRahma.com

modified 15-Oct-15 12:52pm.

Jassim Rahma15-Oct-15 8:02

Re: Move GROUP_CONCAT's comma values to columns

15-Oct-15 8:02

Ok, Please HELLLLLP Confused | :confused:

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.

SQL

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

Jassim Rahma24-Oct-15 1:28

Filling many to many rel table using Select statement

24-Oct-15 1:28

anybody can help please?

Technology News @ www.JassimRahma.com

indian14314-Oct-15 7:18

Re: Filling many to many rel table using Select statement

14-Oct-15 7:18

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."

Mycroft Holmes14-Oct-15 12:50

Re: Filling many to many rel table using Select statement

14-Oct-15 12:50

Look into UNION. Split your query into 2 by removing the second join.

SQL

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

KDGOLE14-Oct-15 21:27

KDGOLE

14-Oct-15 21:27

SQL

--Insert into #TableC (TableAId, AnotherTableAId, TableAName, AnotherTableAName)
SELECT TableAId, AnotherTableAId, TableAName, AnotherTableAName FROM #TableB b
INNER JOIN #TableA a ON a.Name = b.TableAName  OR a.Name = b.AnotherTableAName

KDGole

Re: Filling many to many rel table using Select statement

indian14317-Oct-15 6:17

Re: Filling many to many rel table using Select statement

17-Oct-15 6:17

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."

indian14319-Oct-15 10:30

Get check time from separate fields in Microsoft SQL Server

19-Oct-15 10:30

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."

Jassim Rahma14-Oct-15 0:11

Re: Get check time from separate fields in Microsoft SQL Server

14-Oct-15 0:11

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

Mycroft Holmes14-Oct-15 1:11

Re: Get check time from separate fields in Microsoft SQL Server

14-Oct-15 1:11

This might give you dome ideas, demonstrating the DATEADD and Convert Date possibilities

SQL

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

Chris Quinn14-Oct-15 2:30

Chris Quinn

14-Oct-15 2:30

You can simplify slightly

SQL

DECLARE @ODate DATE
DECLARE @OHour INT = 8
DECLARE @OMin INT = 23
DECLARE @OpenDT DATETIME
declare @DOB DATE = getdate()  --Or whatever date drom the database
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.
=========================================================

Re: Get check time from separate fields in Microsoft SQL Server

Richard Deeming14-Oct-15 2:47

Richard Deeming

14-Oct-15 2:47

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

Re: Get check time from separate fields in Microsoft SQL Server

Chris Quinn14-Oct-15 2:50

Chris Quinn

14-Oct-15 2:50

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.
=========================================================

Insert within Select Statement

indian14313-Oct-15 7:50

13-Oct-15 7:50

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."

Richard Deeming13-Oct-15 8:43

Richard Deeming

13-Oct-15 8:43

You can't embed an INSERT statement within a SELECT statement.

Try something like this:

SQL

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

indian14313-Oct-15 8:58

13-Oct-15 8:58

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."

indian14313-Oct-15 10:28

13-Oct-15 10:28

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."

Mycroft Holmes13-Oct-15 22:55

Sort GetChildRow datarow array

13-Oct-15 22:55

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

BobW99912-Oct-15 2:58

BobW999

12-Oct-15 2:58

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.

Re: Sort GetChildRow datarow array

Mycroft Holmes12-Oct-15 13:07

Re: Sort GetChildRow datarow array

12-Oct-15 13:07

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

BobW99912-Oct-15 17:34

BobW999

12-Oct-15 17:34

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.

Re: Sort GetChildRow datarow array

Mycroft Holmes12-Oct-15 17:40