Data from one Table to Two Columns

Question

Data from one Table to Two Columns

patelmohamad

SSCommitted

Points: 1873
More actions
September 5, 2014 at 1:05 am

#151373

Hi All,
I need a help to populate the Data in two Column from one table
DRIVER_IDDRIVER_NAMELOGGED_IN_DATETIMELOGGED_OUT_DATETIME
4SAM2014-08-30 12:31:532014-07-05 09:47:41
4SAM2014-08-28 11:37:232014-07-05 09:47:41
I have used union all but the result is
SELECT
DRIVER_ID
,'SAM ' AS DRIVER_NAME
,LOGGED_DATETIME AS LOGGED_IN_DATETIME
,NULL AS LOGGED_OUT_DATETIME
FROM
D_DRIVER_LOGGED_HISTORY
WHERE
DRIVER_ID = 4
AND IS_LOGGED_IN = 1
UNION ALL
SELECT
DRIVER_ID
,'SAM ' AS DRIVER_NAME
,NULL AS LOGGED_IN_DATETIME
,LOGGED_DATETIME AS LOGGED_OUT_DATETIME
FROM
D_DRIVER_LOGGED_HISTORY
WHERE
DRIVER_ID = 4
AND IS_LOGGED_IN = 0
ORDER BY
LOGGED_OUT_DATETIME
,LOGGED_IN_DATETIME DESC
DRIVER_IDDRIVER_NAMELOGGED_IN_DATETIMELOGGED_OUT_DATETIME
4SAM2014-08-30 12:31:53NULL
4SAM2014-08-28 11:37:23NULL
4SAMNULL2014-07-05 09:47:41
4SAMNULL2014-07-05 09:47:41
Thanks in advance
Patel Mohamad

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

What exactly is your desired output?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

patelmohamad SSCommitted Points: 1873 More actions · Answer 2

I want the output as below

DRIVER_IDDRIVER_NAMELOGGED_IN_DATETIMELOGGED_OUT_DATETIME

4SAM2014-08-30 12:31:532014-07-05 09:47:41

4SAM2014-08-28 11:37:232014-07-05 09:47:41

Patel Mohamad

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

Ah I see. Use an INNER JOIN on DriverID instead of the union all.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

patelmohamad SSCommitted Points: 1873 More actions · Answer 4

Hi Koen Verbeeck,

I have tried it but no luck, I have 81 Records for that particular driver but it is multiplying the records and the output value is 6561 records,

it is not fulfilling the requirement any other idea?

Thanks

Patel Mohamad

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 5

patelmohamad (9/5/2014)
Hi Koen Verbeeck,
I have tried it but no luck, I have 81 Records for that particular driver but it is multiplying the records and the output value is 6561 records,
it is not fulfilling the requirement any other idea?
Thanks

What is the query that you used?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

patelmohamad SSCommitted Points: 1873 More actions · Answer 6

here is the query

SELECT

DDLH_IN.DRIVER_ID

,DDLH_IN.IS_LOGGED_IN

,DDLH_OUT.IS_LOGGED_IN

,DRIVER_NAME

,DDLH_IN.LOGGED_DATETIME AS LOGGED_IN_DATETIME

,DDLH_OUT.LOGGED_DATETIME AS LOGGED_OUT_DATETIME

FROM

D_DRIVER_LOGGED_HISTORY AS DDLH_IN

INNER JOIN D_DRIVER_LOGGED_HISTORY AS DDLH_OUT

ON DDLH_IN.DRIVER_ID = DDLH_OUT.DRIVER_ID

WHERE

DDLH_IN.DRIVER_ID = 4

Patel Mohamad

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 7

Does this work?

SELECT

DDLH_IN.DRIVER_ID

,DDLH_IN.IS_LOGGED_IN

,DDLH_OUT.IS_LOGGED_IN

,DRIVER_NAME

,LOGGED_IN_DATETIME= DDLH_IN.LOGGED_DATETIME

,LOGGED_OUT_DATETIME= DDLH_OUT.LOGGED_DATETIME

FROM dbo.D_DRIVER_LOGGED_HISTORY DDLH_IN

JOIN dbo.D_DRIVER_LOGGED_HISTORY DDLH_OUT ON DDLH_IN.DRIVER_ID = DDLH_OUT.DRIVER_ID

WHERE DDLH_IN.DRIVER_ID = 4 AND DDLH_IN.IS_LOGGED_IN = 1 AND DDLH_OUT.IS_LOGGED_IN = 0;

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 8

Quick thought, use aggregation and group by to eliminate the nulls (cross tab style)

😎

patelmohamad SSCommitted Points: 1873 More actions · Answer 9

Hi Koen Verbeeck

No Luck from the query, the data is multiplied by the table one rows to table two rows i.e 53*28 = 1484.

Thanks

Patel Mohamad

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 10

If a single driver has multiple logged in dates and multiple logged out dates, you need to join on something else as well.

How do you know which logged in date belongs to which logged out date?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

patelmohamad SSCommitted Points: 1873 More actions · Answer 11

yeah that's the Major issue in it, other wise I have to take loop and then populate the data which I don't want to do.

instead of taking loop I want to do in easy and convenient way.

Patel Mohamad

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 12

patelmohamad (9/5/2014)
yeah that's the Major issue in it, other wise I have to take loop and then populate the data which I don't want to do.
instead of taking loop I want to do in easy and convenient way.

I still don't know how logged in dates are related to logged out dates for a driver.

Please do not say it is "the next row".

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

patelmohamad SSCommitted Points: 1873 More actions · Answer 13

:-D:-D off course it is in next row.

on the base of is_logged_in true/false, the value is inserted into the table

Patel Mohamad

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 14

Ugh. 🙂

This is bad design, because a table doesn't have an order. You should be able to random mix the rows in the table and still achieve the same result.

If a driver logs in, does he has to log out before he can log in again?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP