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.