Using max row of transactional data before a given end date
TABLE1
store_ID
Relation_ID
Store_Open
Store_Close
Trans_Date
TABLE2
Relation_ID
Relation_Name
TABLE3
Store_ID
Store_Name
TABLE1 is transactional, table 2 is static as is table 3
I need to find all the stores and their names from TABLE3 where the stores opened in 2016. Also all the names where they closed in 2017. But only for a Relation_Name = "MyStore"
I have a query that, without using the transaction dates, gives results, but I'm sure its wrong. I need to only use the Max record previous to the end of 2016.
(I know the date syntax can change from platform to platform - this is for DB2, or oracle)
The query that almost works is:
Select distinct(A.Store_ID)
,A.Store_Name
from TABLE3 A
join TABLE2 B on A.Relation_ID= B.Relation_ID
join TABLE3 C on A.Store_ID = B.Store_ID
where B.Relation_Name = "MyStore"
and C.Store_Open < '01/01/2017'
and C.Store_Close > '01/01/2017'
How do I only use the Max records of TABLE1 in 2016?
store_ID
Relation_ID
Store_Open
Store_Close
Trans_Date
TABLE2
Relation_ID
Relation_Name
TABLE3
Store_ID
Store_Name
TABLE1 is transactional, table 2 is static as is table 3
I need to find all the stores and their names from TABLE3 where the stores opened in 2016. Also all the names where they closed in 2017. But only for a Relation_Name = "MyStore"
I have a query that, without using the transaction dates, gives results, but I'm sure its wrong. I need to only use the Max record previous to the end of 2016.
(I know the date syntax can change from platform to platform - this is for DB2, or oracle)
The query that almost works is:
Select distinct(A.Store_ID)
,A.Store_Name
from TABLE3 A
join TABLE2 B on A.Relation_ID= B.Relation_ID
join TABLE3 C on A.Store_ID = B.Store_ID
where B.Relation_Name = "MyStore"
and C.Store_Open < '01/01/2017'
and C.Store_Close > '01/01/2017'
How do I only use the Max records of TABLE1 in 2016?
please provide some sample data and expected results
The first two words (select distinct) of your query that almost works, do not look good to me. If you instead select the Store_ID and Store_Name from table3, where they are in the results of a sub-query from tables1 and 2, the values you want returned are likely already distinct.
ASKER
The enclosed spreadsheet would reflect a small example of the three tables and the expected results.
The detail behind the results are as follows (not needed in the results, just provided to help exlain).
Only the "MyStore" stores are utilized. From Table2, we see this is Relation_ID = 25
for 1/1/2016 there were two stores that were active - #1 & #7
the adds were 1 store #6
the deletes wer 1 store #7 (store #1 has an old record that said it closed in 2016 but was later corrected in early 2016)
the total stores at the end of 2016 was 2 (stores #1 & #6)
I realize that the results would probably involve more than one query, but the concept would be the same - to take the most current transaction date within or before the year (2016)
Add_Delete_Query.xlsx
The detail behind the results are as follows (not needed in the results, just provided to help exlain).
Only the "MyStore" stores are utilized. From Table2, we see this is Relation_ID = 25
for 1/1/2016 there were two stores that were active - #1 & #7
the adds were 1 store #6
the deletes wer 1 store #7 (store #1 has an old record that said it closed in 2016 but was later corrected in early 2016)
the total stores at the end of 2016 was 2 (stores #1 & #6)
I realize that the results would probably involve more than one query, but the concept would be the same - to take the most current transaction date within or before the year (2016)
Add_Delete_Query.xlsx
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.