Find Jobs
Hire Freelancers

Need several sql statements converted into stored one procedure

$30-250 USD

Completed
Posted over 6 years ago

$30-250 USD

Paid on delivery
I need this asap. should take an experienced sql guy an hour. I need these sql statements put together so it brings back one dataset from the stored procedure. Its a legacy system so please forgive the backticks in the field names: There are three tables, Apoteek_data, holds all the information about products given out. ZINummer, a master product table Zorgverzekeraar, a table which holds information on companies and their named groups. -- Step 1 retrieve all records from Apoteek_Data table with the given parameters SELECT * FROM ( SELECT LEFT([login to view URL], 3) AS `Group`, t1.`Z-indexnummer`, Afleverhalfuur, Afleverapotheek, Zorgverzekeraar, Basisproduct, Etiketnaam,Receptnummer,`Afleverdatum`, t2.`Gm.: productnaam: GPK=generiek (code)`, t2.`VGZ`, t2.`MENZIS`, [login to view URL], [login to view URL] FROM Apotheek_Data as t1 INNER JOIN ZINummer as t2 ON t1.`Z-indexnummer` = t2.`Artikelnummer: ZI-nummer` WHERE t1.`VPV-Weekaflevering` = '0, Gewone Levering' AND LEFT([login to view URL], 3) != 'ATM' AND (Afleverdatum >= '2017-07-17' AND Afleverdatum <= '2017-07-18') AND Afleverapotheek = 'S' ) AS t; -- the first record it returns is # Group, Z-indexnummer, Afleverhalfuur, Afleverapotheek, Zorgverzekeraar, Basisproduct, Etiketnaam, Receptnummer, Afleverdatum, Gm.: productnaam: GPK=generiek (code), VGZ, MENZIS, CZ, IDEA -- 'SZR', '12146633', '8:30 - 9:00', 'S', 'SZR - Zilveren Kruis Achmea (3311)', 'MY Product', 'MY Product 2', '3732', '2017-07-17 00:00:00', '20044 ', NULL, NULL, NULL, NULL -- So because [login to view URL] = SZR - Zilveren Kruis Achmea (3311) then we want to find out what the insurance group this company belongs to, so we run this... -- Step 2 SELECT `Zorgverzekeraar`, `MainGroup` FROM [login to view URL] where Zorgverzekeraar LIKE '%SZR%'; -- using the Group field returned in the record above. this returns... Zorgverzekeraar, MainGroup, SecGroup -- 'SZR - Zilveren Kruis Achmea (3311)\'', 'IDEA', '' -- so we can see now that the value we need to search in the ZINummer Table are [login to view URL]: ZI-nummer = 12146633 and we need to check the value of IDEA in the ZINummer table -- Step 3 -- we can now run this query SELECT IDEA FROM ZINummer where `ZINummer`.`Artikelnummer: ZI-nummer` = 12146633; -- It returns # IDEA NULL -- from this we can see that the value of IDEA is Null, so we can then check the rest of the like products in the ZINummer table to see if any of them have an IDEA value of 1, we do this by using the Gm.: productnaam: GPK=generiek (code) value brought back in the first query. -- BTW (if the value of IDEA was 1, then we can skip to the next record in Apoteek_data (DO NOT RETURN THE RECORD AND GO TO NEXT RECORD IN STEP 1) -- Step 4 -- so we run this SELECT IDEA FROM ZINummer where `ZINummer`.`Gm.: productnaam: GPK=generiek (code)` = 20044; -- it returns this # IDEA NULL -- only one record returned this case, and the value of IDEA Column is null too so we can now skip to the next record (DO NOT RETURN THE RECORD AND GO NEXT RECORD IN TO STEP 1) in Apoteek_data -- if the value of IDEA column was 1 here, or any of the records returned, RETURN THE RECORD (SHOW IN REPORT) we would bring back the original record at the top in the report and go to the next record in Apoteek_data.
Project ID: 14706964

About the project

12 proposals
Remote project
Active 7 yrs ago

Looking to make some money?

Benefits of bidding on Freelancer

Set your budget and timeframe
Get paid for your work
Outline your proposal
It's free to sign up and bid on jobs
Awarded to:
User Avatar
Hi there I can convert these sql statements to a stored procedure. I can complete it in a couple of hours. Thanks Rinsad
$126 USD in 0 day
4.9 (604 reviews)
8.4
8.4
12 freelancers are bidding on average $148 USD for this job
User Avatar
Hello. Nice to meet you. I just saw your description carefully. Your project is very easy for me. I have rich experience for my sql. I can finish perfectly in a short time. I would like to discuss more about your project with you via chat. Thank you for your viewing my proposal.
$155 USD in 3 days
4.9 (40 reviews)
5.7
5.7
User Avatar
Note: I am full stack developer with exceptional skills and very much proficient in starting this project with you according to all your specifications. Hi Dear, I am glad to offer you my services, I have more than 8 years of experience in design & development of websites. I have great expertise in MySQL, SQL. I am available to discuss the project in detail and ready to get started. Kindly inbox me for further communication. I assure you that you will experience a WOW factor in my work. Many Thanks, Wasi
$250 USD in 3 days
5.0 (18 reviews)
5.3
5.3
User Avatar
hi, i can help you with your project. send me more information about via freelancer.com chat. regards Habilidades y experiencia relevante linux php mysql mariadb Htos propuestos $133 USD - milestone
$133 USD in 0 day
5.0 (7 reviews)
3.8
3.8
User Avatar
Hi I have long experience in this field. I can provide you perfect solution. I can work full time or part time for you. Hope to meet you and discuss please. Thanks Relevant Skills and Experience Mysql Proposed Milestones $120 USD - com
$120 USD in 3 days
4.9 (4 reviews)
2.3
2.3
User Avatar
i ill gather all within 2 hours Relevant Skills and Experience i m a SQL expert with 7 years exp Proposed Milestones $60 USD - all
$60 USD in 1 day
0.0 (0 reviews)
0.0
0.0
User Avatar
A proposal has not yet been provided
$144 USD in 1 day
0.0 (0 reviews)
0.0
0.0

About the client

Flag of UNITED KINGDOM
Ballymena, United Kingdom
5.0
14
Payment method verified
Member since Apr 26, 2005

Client Verification

Thanks! We’ve emailed you a link to claim your free credit.
Something went wrong while sending your email. Please try again.
Registered Users Total Jobs Posted
Freelancer ® is a registered Trademark of Freelancer Technology Pty Limited (ACN 142 189 759)
Copyright © 2024 Freelancer Technology Pty Limited (ACN 142 189 759)
Loading preview
Permission granted for Geolocation.
Your login session has expired and you have been logged out. Please log in again.