help with select statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help with select statement

Author

Topic

tempus
Starting Member

47 Posts

Posted - 2011-12-14 : 04:48:46

having the following table:

[name] [nvarchar] (100)
[datetime] [datetime]
[status] [char] (10)

table contains information:

name1 2011-12-05 08:05:20 In
name1 2011-12-05 17:05:20 Out
name1 2011-12-06 09:05:20 In
name1 2011-12-06 07:12:13 In
name1 2011-12-06 18:05:20 Out
name1 2011-12-07 07:22:10 In
.....

n names with different information

How can have a select to see like this:

_______| 5th December In |5th December Out | --continuing with 6th and so on, until 31 of december
name1 | 2011-12-05 08:05:20 |2011-12-05 17:05:20
name2 |
name3 |
.
.
namen |

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 05:01:45


SELECT Name,
MAX(CASE WHEN Rn=1 THEN InTime END) AS In1,
MAX(CASE WHEN Rn=1 THEN OutTime END) AS Out1,
MAX(CASE WHEN Rn=2 THEN InTime END) AS In2,
MAX(CASE WHEN Rn=2 THEN OutTime END) AS Out2,
...
MAX(CASE WHEN Rn=n THEN InTime END) AS Inn,
MAX(CASE WHEN Rn=n THEN OutTime END) AS Outn
FROM
(
SELECT DENSE_RANK() OVER (PARTITION BY name ORDER BY dateadd(dd,datediff(dd,0,[datetime]),0)) AS Rn,
name,dateadd(dd,datediff(dd,0,[datetime]),0) AS Date,
MAX(CASE WHEN status='In' THEN [datetime] ELSE NULL END) AS [InTime],
MAX(CASE WHEN status='Out' THEN [datetime] ELSE NULL END) AS [OutTime]
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY) AS Rn,* FROM table)
GROUP BY name,dateadd(dd,datediff(dd,0,[datetime]),0)
)t
GROUP BY Name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tempus
Starting Member

47 Posts

Posted - 2011-12-14 : 07:02:00

Thanks for the info.

I get an error at this line:

(SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY) AS Rn, * FROM table)

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 07:34:39

i missed an alias


SELECT Name,
MAX(CASE WHEN Rn=1 THEN InTime END) AS In1,
MAX(CASE WHEN Rn=1 THEN OutTime END) AS Out1,
MAX(CASE WHEN Rn=2 THEN InTime END) AS In2,
MAX(CASE WHEN Rn=2 THEN OutTime END) AS Out2,
...
MAX(CASE WHEN Rn=n THEN InTime END) AS Inn,
MAX(CASE WHEN Rn=n THEN OutTime END) AS Outn
FROM
(
SELECT DENSE_RANK() OVER (PARTITION BY name ORDER BY dateadd(dd,datediff(dd,0,[datetime]),0)) AS Rn,
name,dateadd(dd,datediff(dd,0,[datetime]),0) AS Date,
MAX(CASE WHEN status='In' THEN [datetime] ELSE NULL END) AS [InTime],
MAX(CASE WHEN status='Out' THEN [datetime] ELSE NULL END) AS [OutTime]
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY) AS Rn,* FROM table)r
GROUP BY name,dateadd(dd,datediff(dd,0,[datetime]),0)
)t
GROUP BY Name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tempus
Starting Member

47 Posts

Posted - 2011-12-14 : 08:04:11

i get the error at this line :

SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY) AS Rn,* FROM table

even executed it separately, seems to be some unclosed quotation, but can't figure where.

thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 09:14:21

[code]
SELECT Name,
MAX(CASE WHEN Rn=1 THEN InTime END) AS In1,
MAX(CASE WHEN Rn=1 THEN OutTime END) AS Out1,
MAX(CASE WHEN Rn=2 THEN InTime END) AS In2,
MAX(CASE WHEN Rn=2 THEN OutTime END) AS Out2,
...
MAX(CASE WHEN Rn=n THEN InTime END) AS Inn,
MAX(CASE WHEN Rn=n THEN OutTime END) AS Outn
FROM
(
SELECT DENSE_RANK() OVER (PARTITION BY name ORDER BY dateadd(dd,datediff(dd,0,[datetime]),0)) AS Rn,
name,dateadd(dd,datediff(dd,0,[datetime]),0) AS Date,
MAX(CASE WHEN status='In' THEN [datetime] ELSE NULL END) AS [InTime],
MAX(CASE WHEN status='Out' THEN [datetime] ELSE NULL END) AS [OutTime]
FROM table
GROUP BY name,dateadd(dd,datediff(dd,0,[datetime]),0)
)t
GROUP BY Name
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tempus
Starting Member

47 Posts

Posted - 2011-12-16 : 02:12:36

Dear Visakh,

i get the results now, but is there the option to make it so that Rn starts with 1st of the month (even if the information i have in table starts from 5th december) and continuing until the last day (30/31) of the month?

name | In1(1st December| Out1 (1st December| In2 (2nd December| Out2 (2nd December) | ..... |In31 (31 December) |Out31 (31 December)

where there are no values , Null is just fine.

thanks in advance for all your help and time!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 03:15:53

quote:
Originally posted by tempus

Dear Visakh,

i get the results now, but is there the option to make it so that Rn starts with 1st of the month (even if the information i have in table starts from 5th december) and continuing until the last day (30/31) of the month?

name | In1(1st December| Out1 (1st December| In2 (2nd December| Out2 (2nd December) | ..... |In31 (31 December) |Out31 (31 December)

where there are no values , Null is just fine.

thanks in advance for all your help and time!

you can do that but you need to build a calendar table for that and use it as base table
use link below to generate such a table and then left join your table to it to get required results

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources