Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Thursday, July 23, 2015 11:35 PM
I am trying to query a code where i need to loop a month in a specified date range. Inside the loop I need to return a result of data each month and need to update the table of the returned data. How do I do the update a field inside the loop? Here's my query:
declare @table1 table (
YEAR_EFF int,
MONTH_EFF int,
IDNumber (8),
SUBS_CNT smallint,
MEM_CNT smallint)
declare @StartDate datetime,
@EndDate datetime
set @StartDate= '1/1/2015'
set @EndDate = '6/30/2015'
while @EndDate >= @StartDate
begin
insert into @table1
select year(@StartDate), month(@StartDate), IDNumber
update @table1
set SUBS_CNT = (select count(SUBS) from other_table
inner join table2 on table2.IDNumber = other_table.IDNumber
where temp_table.SUBS_ID = other_table.SUBS_ID
and temp_table.PLAN_ID = other_table.PLAN_IDand temp_table.RELATION_CODE in ('A', 'B', 'C')
and @StartDate1 between table2.EFF_DT and table2.TERM_DT)
set @StartDate = dateadd(month, 1, @StartDate)
end
select * from @table1
Others says I need to use exec sp_executesql N'' but how do I use it using my code above?
Thanks in advance.
Friday, July 24, 2015 1:44 PM ✅Answered
You don't need a loop. That is the whole idea. And I'll point out that your update statement does not appear to be logically correct since you did not correlate the single row you intended to update with the rows that are selected. But I don't know your data nor your desired logic.
So how do you fix it? You can search the forums or the internet to find the logic to build a calendar table dynamically. Similarly, a search will find logic to build a set of numbers. That is what you need. With the appropriate set of numbers (for discussion purposes I'll stick with your example - the numbers 0 through 5), you simply join this to your start date and that can be used to create the 6 rows you desire. Below is an example that probably doesn't do exactly what you want - but it demonstrates how to generate your ultimate resultset with just a few statements. There is an implied loop - a recursive cte - which is built-in tsql functionality. The script is longer than you need to since it is intended as a learning tool.
set nocount on;
print 'set of numbers';
with nums as (select 0 as x union all select x + 1 from nums where x < 5)
select nums.* from nums order by nums.x;
declare @start date;
set @start = '20150101';
print 'monthly dates generated from set of numbers';
with nums as (select 0 as x union all select x + 1 from nums where x < 5),
dates as (select nums.x, dateadd(month, nums.x, @start) as period_start, dateadd(month, nums.x + 1, @start) as period_end
from nums)
select dates.*
from dates order by dates.x;
declare @table table (id int identity(1, 1) not null, eff_dt date not null, term_dt date not null);
insert @table (eff_dt, term_dt) values ('20150122', '20150201'), ('20150124', '20150201'), ('20150422', '20150423'), ('20141215', '20151214');
print 'sample transaction data';
select * from @table order by eff_dt;
--set @start = '20141201';
set @start = '20150101';
with nums as (select 0 as x union all select x + 1 from nums where x < 5),
dates as (select nums.x, dateadd(month, nums.x, @start) as period_start, dateadd(month, nums.x + 1, @start) as period_end
from nums)
select dates.period_start, sum(case when trn.eff_dt is null then 0 else 1 end) as subs_cnt
from dates left join @table as trn on trn.eff_dt >= dates.period_start and trn.eff_dt < dates.period_end
group by dates.period_start
order by dates.period_start;
Friday, July 24, 2015 2:05 AM
>> I am trying to query a code where I need to loop a month in a specified date range. <<
SQL is a SET-oriented declarative language. We do not use loops! This is like saying you want to eat babies to a vegetarian – so wrong, in so many ways!
You did not know ISO-11179 naming rules and what a field is in SQL. A column is not a field; a field is part of a temporal value {YEAR, MONTH, DAY, HOUR, MINUTE, SECOND}.
Your non-table has no key, and notway to ever have a key! You split a scalar temporal value over two columns! That error is called attribute splitting.
You do not know the ISO-8601 date display format. It is the only one allowed in ANSI/ISO Standard SQL.
A useful idiom is a report period calendar. It gives a name to a range of dates. The worst way would be to use temporal math; it tells the world you do not think in sets or understand declarative programming yet. Here is a skeleton:
CREATE TABLE Month_Periods
(month_name VARCHAR(30) NOT NULL PRIMARY KEY,
month_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
month_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (month_start_date <= month_end_date),
ordinal_period INTETGER NOT NULL UNIQUE
CHECK (ordinal_period > 0)
etc);
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.
Let's do this right. NUMBER is not part of T-SQL, so you should not be posting here.
CREATE TABLE Foobar
(foobar_id CHAR(8) NOT NULL,
foo_month CHAR(10) NOT NULL
CHECK (foo_month LIKE '[12][0-9][0-9][0-9]-[01]-00'),
subs_cnt SMALLINT NOT NULL CHECK (subs_cnt >= 0),
mem_cnt SMALLINT NOT NULL CHECK (mem_cnt >= 0)
);
But this is still wrong! We do not store computed columns. This is not punch cards and magnetic tapes. We do not have to materialize everything, and it is often a bad idea. Your update statement is punch card program on a 1960's tabulator machine, written in SQL. Im will bet that the “temp_table” is a fake scratch tape.
I would love to write a VIEW, but we have no DDL for “Other_Table”, “Table2” and “temp_table”, we cannot do anything. I have the feeling, they are really scratch tapes in a bad disguise.
Post this minimal Netiquette and this entire mess can become one statement, running an order of magnitude faster and much smaller.
From the look of your code, you have been trying to write SQL for less than 3 months. Be patient. It takes years to learn a new language that does not resemble your old ones. ASnd you seem to have the handicap of idiots who advise you to use exec sp_executesql. :(
--CELKO-- <SNIP>
Friday, July 24, 2015 2:38 AM
It is very hard (at least for me) to understand exactly what you want to do from the information you have given us. The best way to get a quick, accurate answer to this type of question is to give us some sample tables and data (in the form of create table or declare table and insert statements). Then a short description of what you want to do, then show us the result you would want from that sample data. That helps us quickly develop and test a proposed solution to give to you. Also tell us the release of SQL Server you are running.
Tom
Friday, July 24, 2015 5:01 AM
It is very hard (at least for me) to understand exactly what you want to do from the information you have given us. The best way to get a quick, accurate answer to this type of question is to give us some sample tables and data (in the form of create table or declare table and insert statements). Then a short description of what you want to do, then show us the result you would want from that sample data. That helps us quickly develop and test a proposed solution to give to you. Also tell us the release of SQL Server you are running.
Tom
Hi,
I basically want to know how to use the update statement inside a loop. :) given the update statement above. Don't mind on the others cause it was set accordingly within the specification.
Friday, July 24, 2015 5:04 AM
Hi MarNipz,
Try to use Merge, this is introduced in SQL 2008+. This will update existing records and insert new records based on matching condition.
Please Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Milan Das
Friday, July 24, 2015 5:13 AM
Hi,
Is this applicable on SQL 2005? Sorry about that....
Friday, July 24, 2015 5:34 AM
Hi,
MERGE is not supported in SQL Server 2005. But we could use other statements to instead or mimicking MERGE.
Please see the following two articles:
SQL Server 2005 T-SQL: Mimic MERGE with OUTPUT
Mimicking Merge Statement in SQL Server 2005
Please Dont forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Milan Das