question

pendragon-2137 avatar image
0 Votes"
pendragon-2137 asked pendragon-2137 edited

Replace query string with actual month date 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, 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;












sql-server-generalsql-server-transact-sqlazure-sql-database
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @pendragon-2137,

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·

sorry i was gonna update the post but was diagnosed with covid and couldn't update the post what turn to be a cold led to a respiratory issues

0 Votes 0 ·
cooldadtx avatar image
0 Votes"
cooldadtx answered

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')
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ErlandSommarskog commented

Hi @pendragon-2137,

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

And this is absolutely not the way to do it.

The correct way is to set up a view across these tables, and then query the view as if it was a singe table. I did not show this, since Pendragon is a beginner and this was an example database.

But even less suitable is dynamic SQL. Pendragon, forget that you never saw Melissa's post. There may be the day when you are going to learn dynamic SQL, but that is not now. The important less right now is that this is a very inappropriate database design,

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.