Loop problem?? Writing only one record? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Loop problem?? Writing only one record?

Author

Topic

dlorenc
Posting Yak Master

172 Posts

Posted - 2009-03-02 : 10:59:43

I am using the following code in a .dtx script. Why does it only write the last record into the destination database?...

I can run the same code in mgmt studio, and it displays 60 one record 'tables' in the results area....what I would like, is the code to write 60 records....

declare @weekstart datetime,@weekend datetime, @OnThisDay datetime, @loop int
set @OnThisDay = '1/7/2008'

set @loop = 60

While @loop >0
begin
set @weekstart = dateadd(day,datediff(day,0,@OnThisDay)-(datepart(dw,@OnThisDay)+@@datefirst-1)%7,-7)
set @weekend = dateadd(day,datediff(day,0,@OnThisDay)-(datepart(dw,@OnThisDay)+@@datefirst-1)%7,0)

declare @x float,@y float;
select @x = count(distinct change_ID_)
FROM dbo.CHG_Change
where Actual_End_Date is not NULL and Actual_End_Date - Create_Date <=2592000 and (dateadd(s,Actual_End_Date,'19700101') between @weekstart and @weekend)

select @y = count(distinct change_ID_)
FROM dbo.CHG_Change
where Actual_End_Date is not NULL and dateadd(s,Actual_End_Date,'19700101') between @weekstart and @weekend

SELECT distinct 62 as MetricID -- Desktop and Laptop Deployments within 30 days of request
,DATEADD(d, - DATEPART(dw, @OnThisDay), @OnThisDay) as ReportWeek
,.75 as Target
,@x as value1
,@y as value2
,@x/@y as metricvalue
,GETDATE() AS Entered
FROM dbo.CHG_Change
where Actual_End_Date is not NULL and Actual_End_Date - Create_Date <=2592000 and (dateadd(s,Actual_End_Date,'19700101') between @weekstart and @weekend)
--2592000 seconds in 30 days

set @OnThisDay = @onThisDay +7
set @loop=@loop -1
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-02 : 12:45:35

where are you doing the insert? i cant see any insert in posted code

dlorenc
Posting Yak Master

172 Posts

Posted - 2009-03-02 : 13:21:47

I cant do an insert..as the data destination is in a different table...

using sql manager, I'd be happy if I can get the records to apprear in a single table in the results area...I could then just cut and paste into excel..then import into the destination table..I do know how to do that....

using business intelligence development studio (BIDS) I did create an ssis script that can do the imporrt..but I just pasted the above script into the sql of the OLE DB Source widget...but when I execute I only get the first record of the 60 loops the code runs thru.... if I knew how to add an image here, I could clip out the results area of the sql mgmt studio to show....??..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-02 : 13:24:58

can i ask what your original reqmnt is? can you explain what you want giving some data in below format?

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

dlorenc
Posting Yak Master

172 Posts

Posted - 2009-03-02 : 14:36:26

requirement is to: 1.count the number of records, between a weekly period (sun-sat), that close (which is defined by Actual_end_date is not NULL); 2. count the number of records, between a weekly period, that have been closed in 30 days or less (defined by Actual_end_date - Create_Date <=30); provide the metricvalue as #2/#1 (or the percentage of record that were closed in 30 days or less; provide these numbers for each weekly period beginning in the first week of january 2007 thru the current week( I just started with a 60 week loop to see what I got).

an example from the code above provides:

MetricID ReportWeek Target Value1 Value2 MetricValue Entered
62 1/5/2008 .75 273 326 .8374.. 3/2/2009
62 1/12/2008 .75 522 587 .8743.. 3/2/2009

my problem is really with sql mgmt studio, in that the above code creates 60 one record tables in the results window...as I only have read-only access to the database I am reading from, I cannot create a temporary table for an insert or update...as I cannot read/write across multiple database (our sys admin does not allow cross table work), I cannot write the data into the target destination database/table). I can use ssis but the code does the same thing as in sql mgmt studio..only writes the first record from the first loop into the destination database...

does this make more sense?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 09:03:40

not much. can you provide the reqd info in format as specified in link?

dlorenc
Posting Yak Master

172 Posts

Posted - 2009-03-03 : 11:01:27

1. How do I: use sql mgmt studio to capture the 60 tables produced by this code, into one table so that I can cut and paste into excel (from excel, I can do an ssis script to import to the target destination database...

requirement is to: 1.count the number of records, between a weekly period (sun-sat), that close (which is defined by Actual_end_date is not NULL); 2. count the number of records, between a weekly period, that have been closed in 30 days or less (defined by Actual_end_date - Create_Date <=30); provide the metricvalue as #2/#1 (or the percentage of record that were closed in 30 days or less; provide these numbers for each weekly period beginning in the first week of january 2007 thru the current week( I just started with a 60 week loop to see what I got).

2. DDL
USE [BCCS_ScoreCard]
GO
/****** Object: Table [dbo].[ScoreCardMetrics] Script Date: 03/03/2009 09:51:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ScoreCardMetrics](
[KeyID] [int] IDENTITY(1,1) NOT NULL,
[MetricID] [int] NOT NULL,
[ReportWeek] [datetime] NULL,
[Target] [float] NULL,
[MetricValue] [float] NULL,
[Value1] [int] NULL,
[Value2] [int] NULL,
[Value3] [int] NULL,
[Value4] [int] NULL,
[Value5] [int] NULL,
[Updated] [datetime] NULL,
[Entered] [datetime] NOT NULL,
[Comment] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_ScoreCardMetrics] PRIMARY KEY CLUSTERED
(
[KeyID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

3. DML sample data:

Change_ID_ Create_date Actual_End_Date
CHG000000200000 1184070435 1184070497
CHG000000200001 1184072249 1184702726
CHG000000200002 1184072537 1184251943
CHG000000200003 1184075052 1184080834
CHG000000200004 1184075116 1184256118
CHG000000200005 1184075404 1184605193
CHG000000200006 1184076088 1187011762
CHG000000200007 1184076503 1184275700
CHG000000200008 1184076854 1184163941
CHG000000200009 1184077546 1186175240

4. Attempted DML is in the lead note in this thread.

5. an example from the code above provides:

MetricID ReportWeek Target Value1 Value2 MetricValue Entered
62 1/5/2008 .75 273 326 .8374 3/2/2009
62 1/12/2008 .75 522 587 .8743 3/2/2009

Comments - my problem is really with sql mgmt studio, in that the above code creates 60 one record tables in the results window...as I only have read-only access to the database I am reading from, I cannot create a temporary table for an insert or update...as I cannot read/write across multiple database (our sys admin does not allow cross table work), I cannot write the data into the target destination database/table). I can use ssis but the code does the same thing as in sql mgmt studio..only writes the first record from the first loop into the destination database...

dlorenc
Posting Yak Master

172 Posts

Posted - 2009-07-30 : 19:05:33

I determined my problem is that ssis does not do multiple result sets...so I just rewrote the query to produce all months in a single result set.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources