Create a calendar table containing one [date] per day indexed on [Date]

Join this table to the one specified (where [date] between Start and End dates)

GROUP BY [date] HAVING COUNT(*) =...

The problem is the WHERE statement, it changes the LEFT JOIN to an INNER JOIN (there are several posts on this forum that details why)

Change the WHERE to an AND...

The result of the pivot without GROUPING will produce the following

To reduce this to one line per ID we can GROUP on ID and Name but we require...

Or

SELECT coursegroup,COUNT(DISTINCT npp) AS [NoOfIPTS]

FROM dbo.Course

GROUP BY coursegroup

SELECT n.[ID],n.[Name],

MAX(CASE WHEN a.SeqNo = 1 THEN a.AddressField ELSE '' END) AS [Address1],

MAX(CASE WHEN a.SeqNo = 2 THEN a.AddressField ELSE '' END) AS [Address2],

MAX(CASE WHEN a.SeqNo =...

If the job step is type 'Transact-SQL Script (TSQL)', as in your case, and uses xp_cmdshell then the command will be run by SQL Server and SQL Server will need to...

For the query in bcp, I do not know and cannot find any documentation that states a limit.

For EXEC the limit is the variable and therefore varchar(8000) for SQL2K. However...

Only two things I can think of then

First, and most probable, your SQL Server is running under the 'Local System Account' instead of a Domain account. The Local System Account cannot...

ah! 

Just spotted something, you have a space in the output directory, you will need to put quotes around that as well

exec xp_cmdshell N'xcopy...

Changing the database will make no difference if, as you should be, storing the dates as datetime.

The way the date is read dd/mm/yyyy or mm/dd/yyyy is dependant on the language...

If the data is as suggested by the others, ie each database row = 4 rows in spreadsheet and the 1st row = header then you will find it difficult...

Try

declare @cmd varchar(4000), @sql varchar(100)

set @sql = 'select * from sysobjects'

set @cmd = 'master..xp_cmdshell ''bcp "' + @sql + '" queryout c:\test.csv -n -S (LOCAL) -e '''

exec...

Doh!! XCOPY errors are redirected to STDERR and wont appear in the job.txt file

Set the Job Step to log output and check the...

Probably access permissions, 2 thing to try

1. Change job step to log information to a file and check the file

2. change the command to

exec xp_cmdshell N'xcopy /Y "D:\Program Files\Microsoft SQL...

By default XCOPY will prompt to overwrite files and since prompts cannot be resolved in xp_cmdshell without using TYPE and redirector/pipe, I suspect that is why 'nothing happens'

Try adding /Y...