Replace query string with actual month date SQL

Arthur 1 Reputation point
2021-08-11T16:06:31.673+00:00

Select us.CYCLE_CODE, us.CYCLE_RUN_MONTH, us.CYCLE_RUN_YEAR, us.BILL_SEQ_NO, us.BAN, us.Price_plan_code, soc.SOC_DESCRIPTION, us.Product_Type, us.at_charge_amt, at_call_dur_round_min, us.TOLL_DUR_ROUND_MIN, us.TOLL_CHARGE_AMT, att.ACC_TYPE, att.ACC_SUB_TYPE, att.DESCRIPTION
From Account_Type att, SOC, Billing_Account ba, US1907 us

Hi im new to sql but im trying to learn this is example database that need to run according to a specific month In code US1907
the code needs to be replace with the actual month 08 every time is runned .
also the first specific day are pre month example:

the actual numbers that don't update are in front set table DB_TABLE is already created inside database and so data is pooled from

US:

01 09
04 09
07 10
10 10
13 10
16 10
22 10
25 10
27 10
28 10
30 10

US0000 is changed for desired cycle and month. The first 2 digits are cycles they don't change unless DBA decides to and the other 2 for the month and pre month , the generated report start at the end of the previous month and ends almost at the end

Update: sorry i was diagnosed with covid and had to be hospitalized :

here is an update on the code i forgot to mention that's inserting data to a table :

INSERT INTO DB_TABLE
Select
'TABLE' AS "REPORT_CODE",
us.CYCLE_CODE,
DECODE(us.CYCLE_CODE, 1, DECODE(us.CYCLE_RUN_MONTH+1, 13, 1, us.CYCLE_RUN_MONTH+1), 4, DECODE(us.CYCLE_RUN_MONTH+1, 13, 1, us.CYCLE_RUN_MONTH+1), us.CYCLE_RUN_MONTH) AS "CYCLE_RUN_MONTH",
DECODE(us.CYCLE_CODE, 1, DECODE(us.CYCLE_RUN_MONTH, 12, us.CYCLE_RUN_YEAR+1, us.CYCLE_RUN_YEAR), 4, DECODE(us.CYCLE_RUN_MONTH, 12, us.CYCLE_RUN_YEAR+1, us.CYCLE_RUN_YEAR), us.CYCLE_RUN_YEAR) AS "CYCLE_RUN_YEAR",
us.BILL_SEQ_NO,
us.BAN,
us.Price_plan_code,
us.SOC_DESCRIPTION,
us.Product_Type,
--Ciclo, BAN, Price_plan_code, SOC_DESCRIPTION, Product_Type,
(CASE ACC_TYPE
WHEN 'I' THEN 'Consummer'
WHEN 'B' THEN 'Buisness'
WHEN 'G' THEN 'Work'
ELSE 'None'
END) AS ACC_TYPE,
ACC_SUB_TYPE,
DESCRIPTION,
Sum(Decode(us.at_charge_amt,Null,0,us.at_call_dur_round_min)) "Billed",
(Sum(us.at_call_dur_round_min) - Sum(Decode(us.at_charge_amt,Null,0,us.at_call_dur_round_min))) "free minutes",
Sum(us.at_call_dur_round_min) "TOTAL",
Sum(us.at_charge_amt) "CHARGES"
From
(
Select us.CYCLE_CODE, us.CYCLE_RUN_MONTH, us.CYCLE_RUN_YEAR, us.BILL_SEQ_NO, us.BAN, us.Price_plan_code, soc.SOC_DESCRIPTION, us.Product_Type, us.at_charge_amt, at_call_dur_round_min, us.TOLL_DUR_ROUND_MIN, us.TOLL_CHARGE_AMT, att.ACC_TYPE, att.ACC_SUB_TYPE, att.DESCRIPTION
From Account_Type att, SOC, Billing_Account ba, US0000 us
Where
ba.BAN = us.BAN
And ba.ACCOUNT_TYPE = att.ACC_TYPE
And ba.ACCOUNT_SUB_TYPE = att.ACC_SUB_TYPE
And us.Price_plan_code = soc.SOC
And soc.EXPIRATION_DATE Is Null
And us.Product_Type = 'F'
And us.Call_Action_Code = 2
And us.Cancel_Record_Ind = 'N'
And Trim(us.AT_FEATURE_CODE) = 'STD14'
) us
Where
(trim(us.acc_type)||trim(us.acc_sub_type)) Not In ('BZ','Bt','BT')
Group By
us.CYCLE_CODE, us.CYCLE_RUN_MONTH, us.CYCLE_RUN_YEAR, us.BILL_SEQ_NO, us.BAN, us.Price_plan_code, us.SOC_DESCRIPTION, us.Product_Type, ACC_TYPE, ACC_SUB_TYPE, DESCRIPTION;
COMMIT;

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,676 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Michael Taylor 47,806 Reputation points
    2021-08-11T18:09:17.287+00:00

    Your FROM clause doesn't look right to me as it seems like there should be some joins to combine the tables the way you need. However as for the actual question of filtering by month that is easy to do via the WHERE clause. 

    SELECT <columns>
FROM US1907 us
WHERE us.CYCLE_RUN_MONTH = '08'

    I'm assuming here that CYCLE_RUN_MONTH is a double digit month value as you indicated. If it is a different data type then you need to adjust the rules.

    If you want the value for the current month then that is a slight expression adjustment. 

    SELECT <columns>
FROM US1907 us
WHERE us.CYCLE_RUN_MONTH = FORMAT(GETDATE(), 'MM')
    0 comments
  2. Erland Sommarskog 100.9K Reputation points MVP
    2021-08-11T21:17:27.403+00:00

    So US1907 is a table name, and there is a table name US1908 etc?

    Since you are new to SQL and this is an example database, I will tell you: Find a different example database. This database is very badly designed.

    In a relational database, a table is supposed to model a unique entity with a number of attributes which we recognise as columns. The data is in the table is identified by itself, that is, by keys in the data.

    If you have a number of tables USYYMM, these tables obviously model the same entity and the year-month value is a key value in that data set. That is, that should be one single table with a month column as part of the key.

    If you are new to SQL, this is not the place to tell you to fix those tables, nor it is really the place to tell how to work around the problem by creating a view. When you are new to SQL, you should work with properly designed databases, so that you get the basics right.

    If you want to continue to work with this database, you will continue to use US1907, US1908. There is no shortcut for this - for the reasons I explained above.

    0 comments
  3. MelissaMa-MSFT 24,176 Reputation points
    2021-08-12T01:50:51.723+00:00

    Hi @Arthur ,

    Welcome to Microsoft Q&A!

    What is the relationship among these four tables? It is recommended to add the where conditions so that we could get a clearer and more intuitive result.

    For example as below, you could replace the col1,col2 and so on with actual column names in your tables. 

    Select us.CYCLE_CODE, us.CYCLE_RUN_MONTH, us.CYCLE_RUN_YEAR, us.BILL_SEQ_NO, us.BAN, us.Price_plan_code, soc.SOC_DESCRIPTION,  
 us.Product_Type, us.at_charge_amt, at_call_dur_round_min, us.TOLL_DUR_ROUND_MIN, us.TOLL_CHARGE_AMT, att.ACC_TYPE,  
  att.ACC_SUB_TYPE, att.DESCRIPTION  
From Account_Type att, SOC, Billing_Account ba, US1907 us  
where att.col1=soc.col1 and SOC.col2=ba.col2 and ba.col3=us.col3

    Only based on your actual requirement, you could consider to use dynamic sql statement like below.

    Say there are tables named as US1908,US1909 and so on. 

    declare @sql varchar(max)  
  
set @sql='Select us.CYCLE_CODE, us.CYCLE_RUN_MONTH, us.CYCLE_RUN_YEAR, us.BILL_SEQ_NO, us.BAN, us.Price_plan_code, soc.SOC_DESCRIPTION,  
 us.Product_Type, us.at_charge_amt, at_call_dur_round_min, us.TOLL_DUR_ROUND_MIN, us.TOLL_CHARGE_AMT, att.ACC_TYPE,  
  att.ACC_SUB_TYPE, att.DESCRIPTION  
From Account_Type att, SOC, Billing_Account ba, US19'+ FORMAT(GETDATE(), 'MM')+' us'  
  
print @sql  
exec( @sql)

    Best regards,
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  4. Tom Phillips 17,716 Reputation points
    2021-08-12T17:01:27.407+00:00

    Just to be clear.

    Assuming "US1907" is a table name, and you are wanting to replace it with "US1908", and presumably "US1909", etc. Table names cannot be dynamic. In order to do what you are trying to do would require "dynamic SQL" which basically means generating a string with the select statement you are trying to run, and then executing the string, as Melissa describes.

    Also, as Erland points out, this is a horrible database design and you should consider fixing it. There is almost never a reason to "partition" a table using physical table names. This makes querying the tables extremely difficult.

    0 comments