Hi all,

I have written the following simple SQL and it works perfectly:

Select orgunit.shortname as 'Store', datepart(wk, schedulekpiday.dayId) as 'Week Number', schedulekpiday.DayID as 'Date', KPI.shortname, schedulekpiday.Value * 100 as 'Efficiency Score'
from schedulekpiday
inner join KPI
on schedulekpiday.KpiId = kpi.KPIID
inner join orgunit
on orgunit.orgunitid = schedulekpiday.orgid
where --schedulekpiday.orgId = 1533 and schedulekpiday.DAYID between '2020-08-10' and '2020-08-16'and
KPI.KPIId = 244

In the software I work with we have a report writer that generates columns based on the SQL.

The report writer is getting confused when using the datepart fucntion, instead of creating one column, it create 2.

Any ideas how to stop this happening, perhaps another method of getting week number from a date that doesn't require an additional ,?

Thanks

Mark

I'm guessing the S/W is seeing the , as the separator, so would it some how be able to work this calculation out into a variable, then call the variable in the SQL query?

Apologies is you have tried this, but is the DayID the correct datatype in all referenced tables? If you substitute GetDate(), for s.dayID in the datepart function, does it work correctly??
Could you write a wrapper function in SQL that takes a date as the only argument returns the week number ?

Is it a known issue in the Report tool?