Record Movement Report - 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 2012 Forums

Transact-SQL (2012)

Record Movement Report

Author

Topic

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2015-04-14 : 03:39:16

Hello All,

Good afternoon.

I`m creating a script in the record movement table

The purpose of these is to check the Line Assignment of the Employee on to correct the End Date of the assignment.

The End Date of the 1st record should minus 1 day of the start date of 2nd record
then the end date of the 2nd record should be minus 1 day of the start date of 3rd record
then the end date of the 3rd record will be null since it is the last record.

Please see may query below.



select
y.[Employee ID],
y.[Start Date],
y.[End Date],
case when 
(y.[End Date] = (select Max(g.MAD_AllocationDate) from T_ManpowerAllocationDetail G where g.MAD_EmployeeID = y.[Employee ID]))
then 'NULL'
else (select convert(varchar(10),Max(dateadd(day,-1,g.[Start Date])),101) from TMP_Movement G where g.[Employee ID] = y.[Employee ID]) end
[New End Date],
y.Line
from
(

select
g.MAD_EmployeeID [Employee ID],
Min(g.MAD_AllocationDate) [Start Date],
max(g.MAD_AllocationDate) [End Date],
g.MAD_LineCode [Line]
from T_ManpowerAllocationDetail g
where g.MAD_EmployeeID in ('00029392','00030156')
group by MAD_EmployeeID, MAD_LineCode
) Y
order by y.[Employee ID], y.[Start Date] asc

The code above shows these result

| Col No | EMPLOYEE ID | Start Date | New End Date | Line No |
| 1 | 00029392 | 2015-03-30 | 04/06/2015 | NRN10-L14 |
| 2 | 00029392 | 2015-04-07 | NULL | DMORES-L24 |
| 3 | 00030156 | 2015-03-25 | 04/05/2015 | DRN10-L08 |
| 4 | 00030156 | 2015-03-30 | 04/05/2015 | DRN10-L10 |
| 5 | 00030156 | 2015-04-06 | NULL | DRN10-L12 |

As you can see Col No 1 and 2 were already correct since the End Date of Col 1 is the date before the start date of Col 2.

The problem is from Col 3 to 5.
The End Date of Col 3 should "2015-03-29" which is the date before the Col 4 Start date

I`m expecting to have the result below.

| Col No | EMPLOYEE ID | Start Date | New End Date | Line No |
| 1 | 00029392 | 2015-03-30 | 04/06/2015 | NRN10-L14 |
| 2 | 00029392 | 2015-04-07 | NULL | DMORES-L24 |
| 3 | 00030156 | 2015-03-25 | 03/29/2015 | DRN10-L08 |
| 4 | 00030156 | 2015-03-30 | 04/05/2015 | DRN10-L10 |
| 5 | 00030156 | 2015-04-06 | NULL | DRN10-L12 |

Kindly help me on how to solve my requirement.

Thank you in Advance.

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-04-14 : 05:01:13

In SQL 2012 it is available LEAD(), so will use it.


;WITH cteSample
AS
(		
	SELECT 1 AS [Col No] , '00029392' AS [EMPLOYEE ID], '2015-03-30' AS [Start Date],'04/06/2015' AS [New End Date],'NRN10-L14' AS [Line No]
	UNION ALL SELECT 2,'00029392','2015-04-07', NULL , 'DMORES-L24'
	UNION ALL SELECT 3,'00030156','2015-03-25', '04/05/2015' , 'DRN10-L08'
	UNION ALL SELECT 4,'00030156','2015-03-30', '04/05/2015' , 'DRN10-L10'
	UNION ALL SELECT 5,'00030156','2015-04-06', NULL , 'DRN10-L12')

SELECT 
	[Col No],[EMPLOYEE ID],[Start Date]
	--,[New End Date]
	,DATEADD(DAY,-1 ,
			LEAD([Start Date],1,NULL) OVER(PARTITION BY [EMPLOYEE ID] ORDER BY [Col No]))
	AS NewEndDate
	,[Line No]
FROM
	cteSample

and the output:


Col No	EMPLOYEE ID	Start Date	NewEndDate	Line No
1	00029392	2015-03-30	2015-04-06 00:00:00.000	NRN10-L14
2	00029392	2015-04-07	NULL	DMORES-L24
3	00030156	2015-03-25	2015-03-29 00:00:00.000	DRN10-L08
4	00030156	2015-03-30	2015-04-05 00:00:00.000	DRN10-L10
5	00030156	2015-04-06	NULL	DRN10-L12

sabinWeb MCP

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2015-04-14 : 05:37:37

Hi,

Thank you for your help.

I just want to confirm regarding to this part because you have a select Union command

WITH cteSample
AS
(
SELECT 1 AS [Col No] , '00029392' AS [EMPLOYEE ID], '2015-03-30' AS [Start Date],'04/06/2015' AS [New End Date],'NRN10-L14' AS [Line No]
UNION ALL SELECT 2,'00029392','2015-04-07', NULL , 'DMORES-L24'
UNION ALL SELECT 3,'00030156','2015-03-25', '04/05/2015' , 'DRN10-L08'
UNION ALL SELECT 4,'00030156','2015-03-30', '04/05/2015' , 'DRN10-L10'
UNION ALL SELECT 5,'00030156','2015-04-06', NULL , 'DRN10-L12')

I have seen a sample query like this before can I replace it with Select * from table?

Kristen
Test

22859 Posts

Posted - 2015-04-14 : 06:13:05

quote:
Originally posted by Gerald30

I have seen a sample query like this before can I replace it with Select * from table?

You shouldn't use SELECT * (at all!) For example, what happens if someone adds a huge column to the table to store a binary image, or a massive column of Comments - ALL your SELECT * queries will then pull ALL columns, even if they are not used, and a) your code will run like a dog and b) fixing every SELECT * in your code will take ages. Bad habit to get into. List only the columns you need in the SELECT clause.

Exception is


EXISTS (SELECT * FROM MyTable WHERE ...)

in this instance "*" tells SQL to optimise to use whichever column / any column it likes (e.g. for best performance). I suspect that this doesn't actuall cause SQL to do anything special, so it may, nowadays, just be "historical"

Kristen
Test

22859 Posts

Posted - 2015-04-14 : 06:13:53

An answer to your question is that all SELECT statements in the UNION / UNION ALL must return the same number of columns (and same type of data in each column, or data that can be implicitly converted/cast)

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2015-04-14 : 06:36:06

HI,

Sorry I did not get it.

Actually this part

SELECT 1 AS [Col No] , '00029392' AS [EMPLOYEE ID], '2015-03-30' AS [Start Date],'04/06/2015' AS [New End Date],'NRN10-L14' AS [Line No]
UNION ALL SELECT 2,'00029392','2015-04-07', NULL , 'DMORES-L24'
UNION ALL SELECT 3,'00030156','2015-03-25', '04/05/2015' , 'DRN10-L08'
UNION ALL SELECT 4,'00030156','2015-03-30', '04/05/2015' , 'DRN10-L10'
UNION ALL SELECT 5,'00030156','2015-04-06', NULL , 'DRN10-L12')

Is already stored in a table using these query



select
y.[Employee ID],
y.[Start Date],
y.[End Date],
case when 
(y.[End Date] = (select Max(g.MAD_AllocationDate) from T_ManpowerAllocationDetail G where g.MAD_EmployeeID = y.[Employee ID]))
then 'NULL'
else (select convert(varchar(10),Max(dateadd(day,-1,g.[Start Date])),101) from TMP_Movement G where g.[Employee ID] = y.[Employee ID]) end
[New End Date],
y.Line
from
(

select
g.MAD_EmployeeID [Employee ID],
Min(g.MAD_AllocationDate) [Start Date],
max(g.MAD_AllocationDate) [End Date],
g.MAD_LineCode [Line]
from T_ManpowerAllocationDetail g
where g.MAD_EmployeeID in ('00029392','00030156')
group by MAD_EmployeeID, MAD_LineCode
) Y

How can I use the suggested solution without doing the union all part?

Thanks

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-04-14 : 08:31:37

Hi,
The key point of this is.


LEAD([Start Date],1,NULL) OVER(PARTITION BY [EMPLOYEE ID] ORDER BY [Col No])

This gave us the information needed.Please read about it on BOL.

I used CTE (Union) because I don't have your data source. You can add the LEAD line in your script
I could adjust your script, but I don't know the logic behind it, and the DDL of your table

(Why use Min(g.MAD_AllocationDate) [Start Date],
max(g.MAD_AllocationDate) [End Date]
)


select
g.MAD_EmployeeID [Employee ID],
g.MAD_AllocationDate [Start Date],
LEAD(g.MAD_AllocationDate,1,NULL) OVER(PARTITION BY g.MAD_EmployeeID ORDER BY g.MAD_AllocationDate)  as [End Date],
g.MAD_LineCode [Line]
from T_ManpowerAllocationDetail g
where g.MAD_EmployeeID in ('00029392','00030156')

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources