hello dear GilaMonster.

CREATE TABLE [dbo].[Vacation](

[VacationID] [int] IDENTITY(1,1) NOT NULL,

[Applicant_Id] [int] NULL,

[HourlyVacationStartTime] [Time](0) NULL,

[HourlyVacationEndTime] [Time](0) NULL,

[DailyStartDate] [smalldatetime] NULL,

[DailyEndDate] [smalldatetime] NULL,

[DailyVacationDate] [smalldatetime] NULL,

PRIMARY KEY CLUSTERED

(

[VacationID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE...

for example:

from 2015-10-04 to 2015-10-11 for john I need to have this result:

date HourlyVacationStartTime HourlyVacationEndTime DailyVacation MissionStartTime MissionEndTime

2015-10-04,46800,50400,Null,Null,Null

2015-10-06,null,null,2015-10-06,null,null

2015-10-07,null,null,2015-10-07,null,null

2015-10-10,null,null,null,28800,79200

2015-10-11,null,null,null,46800,50400

thank you

thanks so much for your reply.

dear J Livingston.

I mean dynamic generating columns.in fact if my column is null,column isn't shown.

hello J Livingston.

My output have more than 1 column and column's names are:

PersonelNumber,FirstName,LastName,BaseSalary,BaseSalaryAmount,ExtraSalary,ExtraSalaryAmount,SpecialSalary,SpecialSalaryAmount.

hello J Livingston SQL please change your query for my new out put.thank you.

hello.

thanks for your reply.

I already need this out put:

1188 Ali Ahmadi BaseSalary 1213232323 ExtraSalary 54585 SpecialSalary 245832

1245 ahmad Alipour Null Null ExtraSalary 2526961 Null Null

1478 sara Emami BaseSalary 548745...

in addition to my post:

I don't know at all how to create a suspect database..when I searched it I didn't get how to do this?

hello all.

when I run sql server agent under administrator account, step 1 worked successfully but step 2 was failed.in step 2 I have to copy files from my computer to...

thank you so much dear ser.my problem was solved by your reply.thank you so much.

hello.

In fact my problem is concatenate two Persian nvarchar.when I concatenate two nvarchar which this two nvarchar is Persian,my result is disarranged.please help me

My problem will be solved by using N before nvarchar field but I don't know how to use this N in CTE Query.

hello dear serg.

I have changed your code by COLLATE Persian_100_CI_AS but I still have problem:

declare @myPath nvarchar(4000) = 'E:\ElectronicArchieve';

IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL

DROP TABLE #DirectoryTree;

CREATE TABLE #DirectoryTree...

just one point dear serg: Because my files' name are Persian, dpath is disarranged.how can I solve my problem with Persian name?

thank you so much dear serg.It is my goal completely. thank you so much