Fiat Coupe Forum
- Founded by Kayjey & James Northam
- Funded by the Club for the benefit of all owners
Fiat Coupe Club UK
join the club
Fiat Coupe Forum
 
» Announced
    Posting images


» Related sites
    Main club site
    fiatcoupe.net


» External data
    owners listed
 
Who's Online Now
1 registered members (Edinburgh), 223 guests, and 3 spiders.
Key: Admin, Global Mod, Mod
Forum Statistics
Forums69
Topics113,543
Posts1,340,440
Members1,785
Most Online731
Jan 14th, 2020
Top Posters(All Time)
barnacle 33,521
stan 32,122
Theresa 23,296
PeteP 21,482
bockers 21,070
JimO 17,917
Nigel 17,366
Edinburgh 16,662
RSS Feeds
Club Events
Club Information
Track Events
Rolling Road/RWYB
Social Events
Non-UK Events
Coupé Related Chat
Coupé Spotting
Coupé News/Press
Buying/Selling Advice
Insuring a Coupé
Basic FAQ's
How to Guides
Forum Issues
Technical Problems
General Maintenance
Styling
Tuning
Handling
ICE and Alarm
Coupés for Sale
Coupés Wanted
Parts for Sale
Parts Wanted
Group Buys
Business Forum
Other Vehicles for Sale/Wanted
Other Items for Sale/Wanted
Haggling/Offers
Ebay links
Other Cars
Other Websites
General Chat
Previous Thread
Next Thread
Print Thread
SQL ish help required #1643182
05/08/2020 14:55
05/08/2020 14:55

E
elder81
Unregistered
elder81
Unregistered
E



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

Re: SQL ish help required [Re: ] #1643229
06/08/2020 14:25
06/08/2020 14:25
Joined: Dec 2005
Posts: 12,293
Sandhurst
Begbie Offline
Ex El Presidente
Begbie  Offline
Ex El Presidente
I AM a Coop

Joined: Dec 2005
Posts: 12,293
Sandhurst
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?


Originally Posted by Jonny - After being taken out at Spa
Your car is Usain Bolt with wellies
Re: SQL ish help required [Re: ] #1643235
06/08/2020 17:50
06/08/2020 17:50
Joined: Dec 2006
Posts: 3,927
The Faringdon Folly
O
oxfordSteve Offline
Forum is my job
oxfordSteve  Offline
Forum is my job
O

Joined: Dec 2006
Posts: 3,927
The Faringdon Folly
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?





Powered by UBB.threads™ PHP Forum Software 7.7.1
(Release build 20190129)
PHP: 7.3.33 Page Time: 0.011s Queries: 15 (0.005s) Memory: 0.7445 MB (Peak: 0.7898 MB) Data Comp: Off Server Time: 2024-03-29 07:36:16 UTC