How to make a month and year dynamic for this SP?

Question

Hi,

I recieve 2 excel files from a vendor everymonth,sometimes i recieve the files from them before the start of month and sometimes within the first week of month( lets say for february 2013 i recieve the files around jan last week or feb first week) now i have to load these files into a table for which i have a Stored procedure as below

INSERT
INTO   EDW.Planning.TEDW_F_TRANSACTION_CHANNEL_OPS_GOAL
SELECT -1                                                                         AS JOB_LOGGING_KEY,
              TEDW_J_PROCESS_DATE.PROCESS_DATE_KEY            AS PROCESS_DATE_KEY,
              TEDW_F_STORE_OPS_GOAL.DATE_KEY,
              TEDW_F_STORE_OPS_GOAL.TRANSACTION_CHANNEL_KEY,
              SUM(TEDW_F_STORE_OPS_GOAL.OPS_GOAL)                    AS OPS_GOAL
FROM   EDW.Planning.TEDW_F_STORE_OPS_GOAL
INNER JOIN
              EDW.Calendar.TEDW_D_DATE_TYPE_1
ON            TEDW_D_DATE_TYPE_1.DATE_KEY                     = TEDW_F_STORE_OPS_GOAL.DATE_KEY
cross join
              EDW.Utility.TEDW_J_PROCESS_DATE
WHERE  TEDW_D_DATE_TYPE_1.FISCAL_YEAR_ID        = 2013
AND           TEDW_D_DATE_TYPE_1.FISCAL_MONTH_OF_YEAR_DESC           = 'February'
GROUP BY
              TEDW_J_PROCESS_DATE.PROCESS_DATE_KEY,
              TEDW_F_STORE_OPS_GOAL.DATE_KEY,
              TEDW_F_STORE_OPS_GOAL.TRANSACTION_CHANNEL_KEY
ORDER BY
              TEDW_J_PROCESS_DATE.PROCESS_DATE_KEY,
              TEDW_F_STORE_OPS_GOAL.DATE_KEY,
              TEDW_F_STORE_OPS_GOAL.TRANSACTION_CHANNEL_KEY

But if you observe it the month and year are hardcoded,Now my task is to identify the month and year and make it dynamic.

Can somebody please help me with any tips or suggestions?

Thanks,
Champ

Answer 1

I am guess MOnth and Year are your parameters? then something like this

Declare @M varchar(30)  =''
Declare @y varchar(30)  =''
If @M=''
set @M=(select datename(month,(GETDATE())))
If @Y=''
set @Y=(select (year(GETDATE())))
print @y  Print @M

Declare @M varchar(30)  ='03-01-2013'
Declare @y varchar(30)  ='03-01-2013'
If @M=''
set @M=(select datename(month,(GETDATE())))
else set @M=(select datename(month,('03-01-2013')))
If @Y=''
set @Y=(select (year(GETDATE())))
else set @y=(select (year('03-01-2013')))
print @y  Print @M

---

Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Dia Agha .

Answer 2

hi Dig Agha,

Can you please help me in modifying my stored procedure how does your example will fit it?I am unable to figure out how does this one help me.

Thanks,
Champ

Answer 3

Create a proc for that:

create proc usp_importExcelData (@year int = null, @month varchar(20) = null)

as

if @year is null set @year = year(getdate())

if @month is null set @month = datename(month,getdate())

INSERT
INTO   EDW.Planning.TEDW_F_TRANSACTION_CHANNEL_OPS_GOAL
SELECT -1                                                                         AS JOB_LOGGING_KEY,
              TEDW_J_PROCESS_DATE.PROCESS_DATE_KEY            AS PROCESS_DATE_KEY,
              TEDW_F_STORE_OPS_GOAL.DATE_KEY,
              TEDW_F_STORE_OPS_GOAL.TRANSACTION_CHANNEL_KEY,
              SUM(TEDW_F_STORE_OPS_GOAL.OPS_GOAL)                    AS OPS_GOAL
FROM   EDW.Planning.TEDW_F_STORE_OPS_GOAL
INNER JOIN
              EDW.Calendar.TEDW_D_DATE_TYPE_1
ON            TEDW_D_DATE_TYPE_1.DATE_KEY                     = TEDW_F_STORE_OPS_GOAL.DATE_KEY
cross join
              EDW.Utility.TEDW_J_PROCESS_DATE
WHERE  TEDW_D_DATE_TYPE_1.FISCAL_YEAR_ID        = @year

AND           TEDW_D_DATE_TYPE_1.FISCAL_MONTH_OF_YEAR_DESC           = @month

GROUP BY
              TEDW_J_PROCESS_DATE.PROCESS_DATE_KEY,
              TEDW_F_STORE_OPS_GOAL.DATE_KEY,
              TEDW_F_STORE_OPS_GOAL.TRANSACTION_CHANNEL_KEY
ORDER BY
              TEDW_J_PROCESS_DATE.PROCESS_DATE_KEY,
              TEDW_F_STORE_OPS_GOAL.DATE_KEY,
              TEDW_F_STORE_OPS_GOAL.TRANSACTION_CHANNEL_KEY

Answer 4

Hi Christa,

As i mentioned earlier the table has already values for past month and i am not sure if i will get the files for next month or this month end.So,i am not sure how a getdate function would work?

Thanks

Answer 5

is it possible for you to write your procedure here, will help you code . as per your this question s

 recieve 2 excel files from a vendor everymonth,sometimes i recieve the files from them before the start of month and sometimes within the first week of month( lets say for february 2013 i recieve the files around jan last week or feb first week) now i have to load these files into a table for which i have a Stored procedure as below

But if you observe it the month and year are hardcoded,Now my task is to identify the month and year and make it dynamic.

Can somebody please help me with any tips or suggestions?

Now my task is to identify the month and year and make it dynamic. --> the file that coming in has a date column? that file is of so and so date?

---

Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Dia Agha .

Answer 6

Hi, I believe you will have the data in the excel file just for that month right ? If yes,

For instance say you recieve a file with Feb-2013 data, you could check if the file exists in the directory using extended stored procedure or some other way that works best for you and store this data in a Temporary table use parameters to read data  and do whatever you have to in the stored procedure and load it into the actual table. This is just a thought as I know nothing about your logistics & requirements.

Answer 7

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions AND formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL AS much AS possible AND not local dialect.

This is minimal polite behavior on SQL forums.

>> I receive 2 excel files from a vendor every month, sometimes I receive the files from them before the start of month AND sometimes within the first week of month (lets say for February 2013 I receive the files around January last week or February first week) now I have to load these files into a table for which I have a Stored procedure AS below <<

Hey, we are “The Database Guys” AND we do not care where the data comes from. This is the basis of any tiered architecture. Since we use SQL, your silly “INSERT INTO.. SELECT .. ORDER BY..” is good for a laugh. Tables have no ordering! BY DEFINITION!

Another laugh is “date” because a date is a unit of temporal measurement on a Calendar scale. It is not a key, BY DEFINITION. Is there a “meter_id”, “liter_id”, VIN_id? etc? NO!! Units of measure do not have ids! This is basic data modeling!

Likewise, “fiscal_year_id” is absurd; it is just a “fiscal year”, not an identifier! Think about “fiscal_month_of_year_desc” is not a description but a name, 'February' in this case. Even worse, it should be “fiscal_month” because we do not split any value into parts like that; a month without a year is impossible, BY DEFINITION.

Someone went from the spreadsheet that was built by a non-RDBMS user, and kept the non-model. You need to go from the RDBMS back to the spreadsheet and to the corporate data dicitonary. I would not do the sum in the proceure that loads the working table.

I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[0-3][0-9]-00'

>> but if you observe it the month and year are hardcoded,now my task is to identify the month and year and make it dynamic. Can somebody please help me with any tips or suggestions? <<

Put this into a procedure with a parameter:

WHERE T1.fiscal_month = @in_fiscal_month

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL