Create tables using date value (looping)

[PrudhviB]

Thank you @Kurt_Bremser this actually worked for my request. Much appreciate your help

[Tom]

It looks like you have both DATE and DATETIME values in your tables. 

Looks like the variable MONTHYEAR has DATE values.  Is it always the first day of the month? Or could the value be any day in the month?

It looks like the variables with names like xxx_DATE are actually DATETIME values instead of DATE values.  Might reduce confusion to rename them. 

What do you mean by PRODUCT COUNT?   It looks like you are just counting the number of observations with non-missing values of BENCH.  Since you are grouping by BENCH that value will be the same as if you just did COUNT( * ) or COUNT( 1 ) except for any groups created where BENCH is missing, which have count(bench) as zero no matter how many observations there were.

So is the question how you can pass in BEGIN and END date values and have it generate a dataset name and also the appropriate date ranges for the WHERE clause?  The first thing to figure out is what parts of the code needs to change.  So generate two versions of your query for two different date ranges.  Then see where the code is different.  Then you can start determining how you can generate the part you need. 

You also need to decide how the user is going to provide the BEGIN and END values.  Do you want them to provide actual dates?  Or do you want them to provide pseudo dates in YYYYMM style like you did in the question? 

Also how to you want to name the generated dataset?  Looks like you are using the style generated by the MONYY format. Note that is not a good style to use since when you sort it lexicographically it does not sort in chronological order.  Also you appear to want to supply both a BEGIN and an END date, so you need to include both dates in the dataset name to avoid confusion.

There are problems with your proposed SQL.  You are grouping by two copies of bench, but are not including both of them in the output.  Why?  What variable in FINAL did you use to find the MAX()?  How can the records be both before and after that datetime value?  Are you just trying to check if some datetime variable in one of the other two tables is between the start and end datetime values in the inventory table?  Which variable?

[PrudhviB]

Hi @Tom 

i have added my responses below in red.

It looks like you have both DATE and DATETIME values in your tables. Yes

Looks like the variable MONTHYEAR has DATE values.  Is it always the first day of the month? Or could the value be any day in the month? It will be a just the last date of the month for all the months (e.g, 30Nov2018)

It looks like the variables with names like xxx_DATE are actually DATETIME values instead of DATE values.  Might reduce confusion to rename them. This is coming from my company data table 

What do you mean by PRODUCT COUNT?   It looks like you are just counting the number of observations with non-missing values of BENCH.  Since you are grouping by BENCH that value will be the same as if you just did COUNT( * ) or COUNT( 1 ) except for any groups created where BENCH is missing, which have count(bench) as zero no matter how many observations there were. yes i can use any of the COUNT methods.

So is the question how you can pass in BEGIN and END date values and have it generate a dataset name and also the appropriate date ranges for the WHERE clause?  The first thing to figure out is what parts of the code needs to change.  So generate two versions of your query for two different date ranges.  Then see where the code is different.  Then you can start determining how you can generate the part you need.  all i want to change is the file name and the dates in the where clause. 

You also need to decide how the user is going to provide the BEGIN and END values.  Do you want them to provide actual dates?  Or do you want them to provide pseudo dates in YYYYMM style like you did in the question? the dates will be in the pseudo format 

Also how to you want to name the generated dataset?  Looks like you are using the style generated by the MONYY format. Note that is not a good style to use since when you sort it lexicographically it does not sort in chronological order.  Also you appear to want to supply both a BEGIN and an END date, so you need to include both dates in the dataset name to avoid confusion. i can name them with number sequence (naming is not my main concern, i can just name them from 1 - 100) and begin date is 01Nov2017 and my end date will be dynamic as or today() )

There are problems with your proposed SQL.  You are grouping by two copies of bench, but are not including both of them in the output.  Why? sorry in the initial code i have only showed the final code i have attached the updated code bench=cycles and i have them in the output as previous and current cycles

What variable in FINAL did you use to find the MAX() ? MonthYear from Final will be my Max  and that would be today() How can the records be both before and after that datetime value?  Are you just trying to check if some datetime variable in one of the other two tables is between the start and end datetime values in the inventory table? yes correct   Which variable? class variable whihc is acct in the new code.  

I have also attached the updated code for better understanding