You need to extract a value from a table. You have some input to find the correct row and then your SQL-proc-code needs to return a value of this row.
I have a code today, but the way to get the correct value has changed. You can probably still use a lot from the old code.
I have attached the design of the table (picture), an excel-sheet with the content of the table and the proc. which is not working properly. You must NOT fix the code because it is working in the wrong way. You need to start from scratch, but you can copy a lot from the code and the code might make you understand better.
The difference between the code and how I want it is this:
I used to weight a part of the time before and after a time-period. The simpler way which I want now is to take Starttime + 1/2 of duration time and find the value of this specific time (which should be between time1 and time2). So if starttime is 19:10:00 and duration is 00:30:00 then find the price of 19:25:00. Meaning which row has 19:25:00 between time1 and time2?
Some facts:
- The table has channel, month (comma separated), time1 and time2 and price.
- Time1 and time2 are "boarder" times meaning you need to check >=Time1 and <=Time2
- I need price out
- Input to proc is: Date, Channel, Start-time and duration (coming from another table in the system and triggered when the record in that table is saved)
- You need to use date to get the correct month (by splitting month)
- You need to use channel to find the correct channel (of course)
- You need to use Start-time and duration to find the correct price in this way:
-Starttime + 1/2 x the duration should be between time1 and time2.
Your code should replace the old code. So you need to look at the code to see what information you can expect in and which information you should output. Date, Starttime and duration are all defined in the table as datetime-data type.