Looping In SQL 2000 (Can It Be A Nested Loop)

Jul 20, 2005

I have to automate a process that assigns sales leads to sales people.

For example:
Every day we buy a list of sales leads, it ranges in size from 50 -
100 records.
We have a team of sales people that also can range from 5 - 8 people.

I need to take the new records and divide them evenly among the sales
people.

If i get 50 records, and have 5 sales people, then each sales person
gets 10 leads.
--
So, im guessing that I may need to have a nested loop inside this. I
have tried it several different ways, but cant seem to get it quite
right.

DECLARE @TotalRecordCount int, @TotalSalesPeopleCount int,
@AmountForEach int, @LooperSalesPeoplerecords int,
@LooperNewSalesLeadsRecords int, @SalesPersonID int

SELECT @TotalSalesPeopleCount = COUNT(UserId)
FROM SalesPeople
WHERE Active = 1
--
SELECT @TotalRecordCount = COUNT(*)
FROM NewSalesLeads
--
SELECT @AmountForEach = (@TotalRecordCount/@TotalSalesPeopleCount)
--
SELECT @LooperSalesPeoplerecords = 1
SELECT @LooperNewSalesLeadsRecords = 1
--
WHILE @LooperSalesPeoplerecords <= @TotalSalesPeopleCount
BEGIN
WHILE @LooperNewSalesLeadsRecords <= @TotalRecordCount
BEGIN
SELECT @SalesPersonID = (SELECT UserID
FROM SalesPeople
WHERE UniqueId = @LooperSalesPeoplerecords)

SELECT @LooperSalesPeoplerecords =
(@LooperSalesPeoplerecords + 1)

UPDATE SalesLeads
SET SalesPerson_ID = @SalesPersonID
WHERE UNIQUEID = @LooperSalesPeoplerecords

SELECT @LooperSalesPeoplerecords =
(@LooperSalesPeoplerecords + 1)
END
END

----
Table structures

CREATE TABLE [dbo].[SalesPeople] (
[SalesPerson_ID] [int] NOT NULL ,
[FirstName] [varchar](20)NOT NULL
) ON [PRIMARY]
--
INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (26, 'Bill')
INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (28, 'Bob')
INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (37,
'Chris')
------------------------------------------------
CREATE TABLE [dbo].[SalesLeads] (
[SalesLeadID] [int]NOT NULL ,
[SalesPerson_ID] [int]NOT NULL
) ON [PRIMARY]
--
INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1001,0)
INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1002,0)
INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1003,0)
INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1004,0)
INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1005,0)
INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1006,0)
------------------------------------------------

So in this case, all 3 salespeople should receive 2 salesleads each.

I dummied this down quite a bit. It actually ends up being more like
15 sales people, and about 400,000 sales leads. But it should work on
any level.

Thanks for any help you might shed on this.

View 6 Replies


ADVERTISEMENT

Nested While Loop

Dec 10, 2004

Hi there. I want to avoid a cursur using a quite basing nested while loop. The problem is, that the outer index-variable (i) won't increment at all while the inner loop works perfectly.

This one should be quite easy to solve I guess, I'd be very happy if someone could give me a hint what I should try, though because I don't know what to try. The manual didn't help me much either, using CONTINUE and BREAKs didn't solve this problem for me.

My code:
DECLARE @i INTEGER
DECLARE @j INTEGER
SET @i = 1
SET @j = 0

WHILE(@i<= 10) BEGIN
WHILE(@j <= 100) BEGIN
SELECT @i, @j, COUNT(*) as anz FROM mytable WHERE dim1 = @i AND dim2 = @j
SET @j = @j + 1
END
SET @i = @i + 1
END

Thanks a lot for your help :)
Bernhard

corrected typo...

View 4 Replies View Related

Nested Loop Function

Jul 30, 2007

I want to know how to create a recursive loop/function in SQL, I can’t seem to figure out how to do it. 
The database table I am working with is simply the following: 
SeedID, ThisParentSeedID
1, 0
2, 1
3, 1
4, 2
5, 4
6, 5
7, 6
8, 7
9, 7
10, 7
11, 10
12, 0
13, 0
14, 0 
The example table above shows that SeedID 1 = the parent level of the data. SeedID 2 and 3 are children of SeedID 1, 4 is child of 2, 5 is child of 4... 12 13 and 14 are also parent levels (they are not children of anything).  
I want to know how to create a SQL script that is “object orientedâ€? in that I will not have to create as many levels of nested scripts as there are nested “childrenâ€? in the data.  
What I am wanting to figure out is, with a single script, “which sub-children are assigned to [@SeedID]â€?? So if this script was called, and @SeedID = 1, it would return (2,3,4,5,6,7,8,9,10,11). If @SeedID = 12, it would return null. If @SeedID = 7, it would return (8,9,10,11)  
I have tried to keep my question and data as simple as possible for the sake of getting some feedback or help. If you want me to clarify or explain better, please ask me to!

View 2 Replies View Related

Nested Loop In SQL Query

Nov 1, 2005

Hi,I'm probably missing something obvious (either that or doing this totally wrong).I'm trying to use a nested loop to generate the following results:Unit          Day1         Day2           Day3        Day4          Day5Name1     25             45               89             54              76Name2     48             54               81             74              98What I have so far is this:WHILE @FCount < @TotalFoodUnitsBEGINSELECT (SELECT Unit FROM tbl_acc_FoodVenues WHERE UnitID = (@FCount + 1)) AS Unit  WHILE @FDCount < @Days BEGIN SELECT  (SELECT FdRevenue_a FROM tbl_acc_aud_SportsAudits WHERE AudDate = DATEADD(day, @FDCount, @pdStartDate)) AS Rev  SET @FDCount = @FDCount + 1 END SET @FCount = @FCount + 1ENDAny suggestions please

View 3 Replies View Related

Nested Loop Join

Jan 26, 2006

Hi,

I have came across a situation -
When there are no indices on the tables and if we force SQL server to use the "Nested Loop" joins, the query becomes very slow. Since there are no indices then Nested loop join should not be used.

The background for this problem is -
Analysis services is sending some query to SQL server while doing the cube processing. SQL server is using Nested loop joins even though there are no indices on any of the tables. Is there any way by which we can force the SQL server/Analysis services not to use Nested loop joins since there are no indices in any of the tables.

regards,
datta.

View 1 Replies View Related

Nested Loop Joins

Jan 26, 2006

Hi,

I have came across a situation -
When there are no indices on the tables and if we force SQL server to use the "Nested Loop" joins, the query becomes very slow. Since there are no indices then Nested loop join should not be used.

The background for this problem is -
Analysis services is sending some query to SQL server while doing the cube processing. SQL server is using Nested loop joins even though there are no indices on any of the tables. Is there any way by which we can force the SQL server/Analysis services not to use Nested loop joins since there are no indices in any of the tables.

regards,
datta.

View 1 Replies View Related

Nested Loop Join - Need Help :)

Mar 31, 2006

i'm running the following code on Ms SQL Server 2000, Query Analyzer to analyze the result of Nested Loop Join.

SET STATISTICS PROFILE ON
GO
SELECT pdN.ProductID, pdN.ProductName,
spN.CompanyName, spN.ContactName
FROM dbo.ProductsNew pdN
INNER JOIN dbo.SuppliersNew spN
ON pdN.SupplierId = spN.SupplierId
GO

but the execution plan give me the following result :-

http://i31.photobucket.com/albums/c366/i3lu3fun/executionplan.jpg

instead of using nested, why does it using hash join? is there anything wrong with my code?

View 8 Replies View Related

Imitating Nested For Each Loop In SQL Query

May 25, 2007

Dear All,

I need to create a query to list all the subfolders within a folder.

I have a database table that lists the usual properties of each of the folder.

I have another database table that has two columns

1. Parent folder
2. Child folder

But this table maintains the parent child relationship only to one level.

For example if i have a folder X that has a subfolder Y and Z.
And Y has subfolders A and B.
and B has subfolder C and D
and C has subfolder E and F

The database table will look like

parentfolder child folder
X Y
X Z
Y A
Y B
B C
B D
C E
C F

I want to write a query which will take a folder name as the input and will provide me a list of all the folders and subfolders under it. The query should be based on the table (parent - child) and there should not be any restriction on the subfolder levels to search and report for.

I have been banging my head to do this but i have failed so far. Any help on this will be highly appreciated.

View 3 Replies View Related

Whats Wrong With This Nested While Loop?

Jul 20, 2005

Im getting way too many rows retured..what its trying to do is inserta 0 for revenue for months 7 - 12 (aka July through December) for eachof these cost centers for each payor type..Im getting a lot of repeatsand the concatenation field date always comes back as January 2003instead of the month and date its supposed to--Fiscal Yeardeclare @year smallintset @year = 2004--Month number the Fiscal year starts and endsdeclare @month smallintset @month = 7--Place holder for number of costcentersdeclare @cccounter smallint--loop counter for cost centersdeclare @ccount smallintset @ccount = 1--Place holder for number of payor typesdeclare @ptcounter smallint--loop counter for payor typesdeclare @pcount smallintset @pcount = 1--Temp table to store the blank values for all cost centers/payortypes for the fiscal yeardeclare @Recorded_Revenue_tmp table(Revenue money default 0,[Date] varchar(15),monthn smallint,yearn smallint,[CostCenter] varchar(50),[PayorType] varchar(50))--Temp table to store the values of the cost centersdeclare @costcenter_tmp table(ccid int IDENTITY (1,1),ccname varchar(50))--Inserts cost centers and code into the @costcenter_tmp temp tableinsert into @costcenter_tmp (ccname) select costcenter.fullname + ' '+ costcenter.code from costcenter, agency_cost_centerwhere costcenter.oid = agency_cost_center.cost_center_moniker--Sets the @cccounter variable to the number of cost centersselect @cccounter = count(*) from @costcenter_tmp--Temp table to store the values of the payor typesdeclare @payor_type_tmp table(ptid int identity (1,1),ptname varchar(50))--Inserts payor types into the @payor_type_tmp temp tableInsert into @payor_type_tmp(ptname)select fullname from payor_type,payorwhere payor_type.oid = payor.payor_type_moniker--Sets the @ptcounter variable to the number of payor typesselect @ptcounter = count(*) from @payor_type_tmp--Loop that gets the first part of the fiscal yearWhile (@month <13)begin--Loop that gets the value of the cost center to insertWhile (@ccount <= @cccounter)begin--Loop that inserts values for the first part of the fiscal year intothe @Recorded_Revenue_tmp temp tablewhile (@pcount <= @ptcounter)beginInsert into @Recorded_Revenue_tmp(Revenue, [Date], monthn,yearn,[CostCenter],[PayorType])select 0, datename(month, @month)+ ' ' + cast(@year -1 as varchar(4)),@month, @year -1, ccname, ptnamefrom @costcenter_tmp ct,@payor_type_tmp pt where ct.ccid = @ccount andpt.ptid = @pcountset @pcount = @pcount + 1endset @pcount = 1set @ccount = @ccount + 1endset @ccount = 1set @month = @month + 1endselect * from @Recorded_Revenue_tmpsample return data:(returns 16008 rows!!!)..0000January 200372003Genesis Assertive Community Treatment Team250SAGA..0000January 200372003Genesis Assertive Community Treatment Team250Self Pay..0000January 200372003Genesis Assertive Community Treatment Team250ABH..0000January 200372003Genesis Assertive Community Treatment Team250Managed Medicaid..0000January 200372003Genesis Assertive Community Treatment Team250Managed Medicaid..0000January 200372003Genesis Assertive Community Treatment Team250Managed Medicaid..0000January 200372003Genesis Assertive Community Treatment Team250Managed Medicaid..0000January 200372003Genesis Assertive Community Treatment Team250Commercial..0000January 200372003Genesis Assertive Community Treatment Team250Commercial..0000January 200372003Genesis Assertive Community Treatment Team250Commercial..0000January 200372003Genesis Assertive Community Treatment Team250Commercialthanks -Jim

View 2 Replies View Related

Variable Inside A Nested Loop

Jul 20, 2005

I am trying to write a utility/query to get a report from a table. Belowis the some values in the table:table name: dba_daily_resource_usage_v1conn|loginame|dbname|cum_cpu|cum_io|cum_mem|last_b atch------------------------------------------------------------80 |farmds_w|Farm_R|4311 |88 |5305 |11/15/2004 11:3080 |abcdes_w|efgh_R|5000 |88 |4000 |11/15/2004 12:3045 |dcp_webu|DCP |5967 |75 |669 |11/16/2004 11:3095 |dcp_webu|XYZ |5967 |75 |669 |11/17/2004 11:30I need to write a query which for a given date (say 11/15/2004),generate a resource usage report for a given duration (say 3 days).Here is my query:************************************set quoted_identifier offdeclare @var1 intset @var1=0--BEGIN OUTER LOOPwhile @var1<=3 --INPUT runs the report for 3 daysbegindeclare @vstartdate char (10) --INPUT starting dateset @vstartdate='11/15/2004'--builds a range of datedeclare @var2 datetimeset @var2=(select distinct (dateadd(day,@var1,convert(varchar(10),last_batch,101)))--set @var2=(select distinct (dateadd(day,@var1,last_batch))from dba_daily_resource_usage_v1where convert(varchar (10),last_batch,101)=@vstartdate)set @var1=@var1+1 --increments a daydeclare @var5 varchar (12)--set dateformat mdy--converts the date into 11/15/2004 format from @var2set @var5="'"+(convert(varchar(10),@var2,101))+"'"--print @var5 produces '11/15/2004' as resultdeclare @vloginame varchar (50)declare @vdbname varchar (50)--BEGIN INNER LOOPdeclare cur1 cursor read_only forselect distinct loginame,dbname fromdba_daily_resource_usage_v1where convert(varchar (10),last_batch,101)=@var5--??????PROBLEM AREA ABOVE STATEMENT??????--print @var5 produces '11/15/2004' as result--however cursor is not being built and hence it exits the--inner loop (cursor)open cur1fetch next from cur1 into @vloginame, @vdbnamewhile @@fetch_status=0begin--print @var5 produces '11/15/2004' as resultdeclare @vl varchar (50)set @vl="'"+rtrim(@vloginame)+"'"declare @vd varchar (50)set @vd="'"+@vdbname+"'"--processes the cursorsdeclare @scr varchar (200)set @scr=("select max(cum_cpu) from dba_daily_resource_usage_v1 whereloginame="+@vl+" and dbname="+@vd+" and "+"convert(varchar(10),last_batch,101)="+@var5)--set @var3 =(select max(cum_cpu) from dba_daily_resource_usage_v1where--loginame=@vloginame and dbname=@vdbname--and convert(varchar (10),last_batch,101)=@var5)print @scr--exec @scrfetch next from cur1 into @vloginame, @vdbnameend--END INNER LOOPselect @var2 as "For date"deallocate cur1end--END OUTER LOOP************************************PROBLEM:Even though variable @var5 is being passed as '11/15/2004' inside thecursor fetch (see print @var5 inside the fetch), the value is not beingused to build the cursor. Hence, the cursor has no row set.Basically, the variable @var5 is not being processed/passed correctlyfrom outside the cursor to inside the cursor.Any help please.Thanks*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 3 Replies View Related

Nested Loop Vs. Hash Match?

Dec 20, 2007

I have two queries that seem to be the same, but perform very differently. The first query runs very fast (7000+ records returned in <1 sec.). The execution plan shows that it uses a nested loop with index seeks on both tables.

select *
from t_loadbasic

where ld_nbr in (select ld_nbr from t_tripcombined where comp_date between '11/1/07' and '11/05/07')



The second query is almost the same, save the fact that it uses date variables instead of hard dates. The execution plan shows that it uses a hash match instead of a nested loop with an index scan on the main table (t_loadbasic). This query takes about 12 seconds to run.


declare @startdate datetime


,@enddate datetime


set @startdate = '11/1/07'

set @enddate = '11/5/07'


select *
from t_loadbasic

where ld_nbr in (select ld_nbr from t_tripcombined where comp_date between @startdate and @enddate)

I'm trying to figure out why the database executes these two statements so differently. BTW, I've tried switching the order of the tables. I've tried using joins instead of a subquery. The execution plan seems completely dependant on the use of variables. I can attach the execution plans if necessary.

I apologize if this is too simple a question, but I couldn't find an answer on any forums, web searches or BOL. Thanks in adavance.

View 1 Replies View Related

Problem Of Inserting In A Nested WHILE Loop In SProc

Jan 17, 2008

Hi friends, I've been stumped on this for almost a week now.  Everything works in the stored procedure code below except for the 'INSERT INTO @Uppdates' block of code.  I have a SQL Analyzer test driver and when the code gets to the SELECT statement below the INSERT INTO @Updates line the value of the select line is displayed on the screen and nothing gets written to @Updates.  I hope I'm being clear about this.  Any ideas? IF @Edit=1 AND LEN(@Changes) > 0
BEGIN
--Split and parse changes
DECLARE @curRec int, @nxtRec int, @Record varchar(8000), @TNum int, @TNam varchar(50), @PDesc varchar(512), @PChk varchar(8), @SNum varchar(12), @NScr varchar(10), @OScr varchar(10),
@curField int, @nxtField int, @curSRec int, @nxtSRec int, @subRec varchar(8000), @curSField int, @nxtSField int

DECLARE @NewProj table (
ProjectID int,
SchoolNumber varchar(20),
ArtTeacherNumber int,
TeacherNumber int,
TeacherName varchar(40),
ProjectDescription varchar(512) NULL,
[Checksum] varchar(20) NULL)

DECLARE @Updates table (
ProjectID int,
StudentNumber varchar(12),
NewScore varchar(10) NULL,
OldScore varchar(10) NULL)

SET @curRec = 1

WHILE @curRec IS NOT NULL
BEGIN
SET @nxtRec = NULLIF(CHARINDEX(CHAR(1), @Changes, @curRec), 0)
SET @Record = SUBSTRING(@Changes, @curRec, ISNULL(@nxtRec,8000)-@curRec) --Extract a class record
SET @curField = 1
SET @nxtField = NULLIF(CHARINDEX(CHAR(2), @Record, @curField), 0)
SET @TNum = SUBSTRING(@Record, @curField, ISNULL(@nxtField,1000)-@curField) -- Extract Teacher Number
SET @curField = @nxtField + 1
SET @nxtField = NULLIF(CHARINDEX(CHAR(2), @Record, @curField), 0)
SET @TNam = SUBSTRING(@Record, @curField, ISNULL(@nxtField,1000)-@curField) -- Extract Teacher Name
SET @curField = @nxtField + 1
SET @nxtField = NULLIF(CHARINDEX(CHAR(2), @Record, @curField), 0)
SET @PDesc = SUBSTRING(@Record, @curField, ISNULL(@nxtField,1000)-@curField) -- Extract Project Description
SET @curField = @nxtField + 1
SET @nxtField = NULLIF(CHARINDEX(CHAR(3), @Record, @curField), 0)-- Step over existing checksum
SET @PChk = RIGHT('0000000' + dbo.int2base(Checksum(@PDesc),16),8)-- Calculate new checksum based on project description that may have been changed.
SET @curField = @nxtField + 1

INSERT INTO @NewProj (ProjectID, SchoolNumber, ArtTeacherNumber, TeacherNumber, TeacherName, ProjectDescription, [Checksum])
SELECT DISTINCT Students.ProjectID, @SchoolNumber, @ArtTeacherNumber, @TNum, @TNam, @PDesc, @PChk
FROM @Students Students
WHERE Students.SchoolNumber=@SchoolNumber AND Students.TeacherNumber=@TNum

SET @curSRec = 1
WHILE @curSRec IS NOT NULL
BEGIN
SET @nxtSRec = NULLIF(CHARINDEX(CHAR(3), @Record, @curField), 0)
SET @subRec = SUBSTRING(@Record, @curField, ISNULL(@nxtSRec,8000)-@curField) -- Extract a score sub record. Consists of Student Number, new Score, old Score.
SET @curSField = 1
SET @nxtSField = NULLIF(CHARINDEX(CHAR(4), @subRec, @curSField), 0)
SET @SNum = SUBSTRING(@subRec, @curSField, ISNULL(@nxtSField, 1000)-@curSField) -- Extract Student Number
SET @curSField = @nxtSField + 1
SET @nxtSField = NULLIF(CHARINDEX(CHAR(4), @subRec, @curSField), 0)
SET @NScr = SUBSTRING(@subRec, @curSField, ISNULL(@nxtSField, 1000)-@curSField) -- Extract new Score
SET @curSField = @nxtSField + 1

IF @curSField > LEN(@subRec)
SET @Oscr = NULL-- If no Old Score specified
ELSE
BEGIN
SET @nxtSField = LEN(@subRec) + 1
SET @OScr = SUBSTRING(@subRec, @CurSField, ISNULL(@nxtSField, 1000)-@curSField) -- Extract old Score
END

-- Check for errors
IF ISNUMERIC(@SNum) = 0 OR @NScr IS NULL OR LEN(ISNULL(@PChk,0)) <> 8
BEGIN
SET @UpdateErr = 1
BREAK
END

-- Update the updates table and find ProjectID from existing data table
INSERT INTO @Updates (ProjectID, StudentNumber, NewScore, OldScore)
SELECT DISTINCT Students.ProjectID, @SNum, @NScr, @OScr
FROM @Students Students
WHERE Students.StudentNumber=@SNum

SET @curField = @nxtSRec + 1
SET @curSRec = @nxtSRec + 1
select * from @Updates
END
IF @UpdateErr = 1
BEGIN
BREAK
END
SET @curRec = @nxtRec + 1
END
 Thanks in advance for looking at this, 

View 3 Replies View Related

Generate Result Using Nested Loop And Variables

Dec 3, 2013

How can I generate the following result using nested loop and variables :

col1col2
15
16
17
25
26
27
35
36
37
45
46
47

View 5 Replies View Related

[SQL Server 2000] - Selecting Nested Top N

Mar 30, 2004

Hi There,

We have problems getting the right records from a joined table.

First we have a table with all our Customers (eg. [Algemeen-B])
We join them with a table representing all Contacts per Customer (eg. [Algemeen-C])

An SP joins the tables because we have a few dynamic filters and parameters.

What we need is to link a maximum of @amount contacts per Customer.
Where each contact has a RANK (the TOP @amount ) joining the Customer Table.

We come thisfar:

-- The SP results this SQL select:

select *
from [Algemeen-B] left outer join
(
select * from [Algemeen-C] AS C
where exists
(
select * from [Algemeen-C]
group by bedrnummer
having bedrnummer=C.bedrnummer And min(rank) <= C.Rank and count(*) <= 2
) ) S
on [Algemeen-B].bedrnummer = S.bedrnummer
where KWP>=8

---
In this case I should return a maximum of 2 contacts per each Distinct Customer.
But I think the: 'And min(rank) <= C.Rank and count(*) <= 2' is wrong.

How can I select the TOP 2 Ranked Contacts for each Customer?
Because when there are no Contacts for the Customer it should return only the Columns for [Algemeen-B]
and NULL values for the Colums for [Algemeen-C].

If you need more info Tell me..
Hope someone can help me out..

Colin

View 6 Replies View Related

SQL Server 2000 Query - Nested Subquery Question

Jul 27, 2007

I am using SQL Server 2000. I have a somewhat large query and hope someone could help me with it.
(Man, there needs to be a way to use colors...) The bolded parts of the query need to be replaced by the underlined part. I can't say 'b.HW' because of the scope of the inline query and that they are all on the same level. I've been told I need to change to a nested subquery, but can't for the life of me figure out how to do that. Can someone please show me?
The current query:
SELECT x.SSN,x.RealName,x.BudgetCode,x.TH,b.HW,x.HP,z.HL,x.NHW,x.FSLAOT,y.HHW AS AH,y.NHH,CASE WHEN x.TH < x.HP THEN 'XX' WHEN x.TH > x.HP THEN x.NHW - x.HP - y.NHH WHEN x.TH <= x.HP THEN 0 END AS SOT,CASE WHEN x.TH > x.HP THEN x.NHW - x.HP - y.NHH + x.FSLAOT WHEN x.TH <= x.HP THEN 0 END AS AO
FROM(SELECT a.SSN,a.RealName,a.BudgetCode,SUM(a.Hours) AS TH,CASE WHEN SUM(a.hours) > 40 THEN (SUM(a.hours) - 40) * 1.5 WHEN SUM(a.hours) <= 40 THEN 0 END AS FSLAOT,CASE WHEN SUM(a.hours) >= 40 THEN 40 WHEN SUM(a.hours) < 40 THEN SUM(a.hours) END AS NHW,32 AS HP
FROM dbo.ActivitiesInCurrentFiscalYear aWHERE a.ItemDate BETWEEN startdate and enddate AND a.ScheduleType = 1 AND a.EmployeeType = 2GROUP BY a.SSN, a.RealName, a.BudgetCode) x LEFT OUTER JOIN
(SELECT a.SSN,a.RealName,a.BudgetCode,SUM(a.Hours) AS HHW,CASE WHEN SUM(a.Hours) >= 8 THEN 8 WHEN SUM(a.Hours) < 8 THEN SUM(a.Hours) END AS NHH
FROM dbo.ActivitiesInCurrentFiscalYear a, dbo.Holidays hWHERE a.ItemDate = h.HolidayDate AND a.ItemDate BETWEEN startdate and enddate AND a.EmployeeType = 2 AND a.ScheduleType = 1GROUP BY a.SSN, a.RealName, a.BudgetCode) y ON x.SSN = y.SSN AND x.RealName = y.RealName AND x.BudgetCode = y.BudgetCode LEFT OUTER JOIN
(SELECT a.SSN,a.RealName,a.BudgetCode,SUM(a.Hours) AS HLFROM dbo.ActivitiesInCurrentFiscalYear aWHERE a.ItemDate BETWEEN startdate and enddate AND a.EmployeeType = 2 AND a.ScheduleType = 1 AND (a.program_code = '0080' OR a.program_code = '0081')GROUP BY a.SSN, a.RealName, a.BudgetCode) z ON x.SSN = z.SSN AND x.RealName = z.RealName AND x.BudgetCode = z.BudgetCode LEFT OUTER JOIN
(SELECT a.SSN,a.RealName,a.BudgetCode,SUM(a.Hours) AS HWFROM dbo.ActivitiesInCurrentFiscalYear aWHERE a.ItemDate BETWEEN startdate and enddate AND a.EmployeeType = 2 AND a.ScheduleType = 1 AND NOT (a.program_code = '0080' OR a.program_code = '0081')GROUP BY a.SSN, a.RealName, a.BudgetCode) b ON x.SSN = b.SSN AND x.RealName = b.RealName AND x.BudgetCode = b.BudgetCode)

View 8 Replies View Related

How To Loop Through In Sql Server 2000

Nov 27, 2007

I have a scenario where I have to keep track of users last three passwords,
if the new password entered by user matches with the last three passwords in the PasswordHistory Table then I will display message to user that "you can not repeat any of your  last three passwords.."
Basically I can do : select top 3 passwords from MyTable order by createdDate desc
But how to do comparision ?
I was googling for if anything like array exist in sql server 2000?
Please suggest, any pointer........
Thanks

View 5 Replies View Related

Nesting A Looping Query Withing A Looping Query

Mar 28, 2008

Im having a issue. Im not sure how I am going to carry out but I have two tables in SQL server 2005
TABLES
     Category                    SubCategory           (PK)CategoryName      (PK) SubCategoryNameCategoryID                    SubCategoryIDDate                                Date                      (Just shows the date inserted)                                  (FK)CategoryID
On the front page, I need to have it querys out the CategoryName from Categorys but also querys out all....Well not all but atleast 5 subcategorys that relate to that categoryName. Once its down it moves to the next category and does the same and so on. Does anyone know the trick ?

View 5 Replies View Related

Spin Loop Attaching Database In 2000 =&&> 2005 Upgrade

Apr 21, 2008

I just upgraded a large SQL 2000 database server to SQL 2005, and I have 2 databases that won't attach to the 2005 Server. They are both very small, about 90MB in size, and when I attach them to 2005, the process alternates between running and spinloop status. It can sit forever, and will never complete. I restored backups before the upgrade to a SQL 2000 Server, and reran DBCC CheckDB on both, and got no consistency errors, Updated Stats and indexes, then detached and moved the data files to 2005, and same thing.

View 3 Replies View Related

Replication Loop Back Detection Problems In SQL Server 2000

May 10, 2007

Hi,



I am monitoring my replication process and I have a problem with the loop detection, I can see origined transacctions in the server, executed in the same for the replication.

The subscriptions configuration is sp_helpsubscription


exec sp_addsubscription @publication = N'PUBLICATION',
@article = N'all',
@subscriber = N'192.168.0.23',
@destination_db = N'PRUEBABD',
@sync_type = N'none',
@update_mode = N'read only',
@offloadagent = 0,
@dts_package_location = N'distributor',
@loopback_detection = 'true'
GO







I need for help.

View 4 Replies View Related

SQL Server 2008 :: Difference Between FOR LOOP And FOREACH LOOP?

May 28, 2010

difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.

View 4 Replies View Related

Loop Though Table Using RowID, Not Cursor (was Loop)

Feb 22, 2006

I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks

View 6 Replies View Related

Foreach Loop Doesn't Loop

Mar 3, 2006

I have a foreach loop that is supposed to loop through a recordset, however it doesn't loop. It just repeats the same row, row after row.

I would like to look into the recordset variable but I can't because it is a COM object and the ADODB namespace is not available in the script task.

Any solution to this? anyone experienced anything similar

View 1 Replies View Related

Fishing For A Clue. To Loop Or Not To Loop

Jul 8, 2006

I have a table called Tbltimes in an access database that consists of the following fields:

empnum, empname, Tin, Tout, Thrs

what I would like to do is populate a grid view the a select statement that does the following.

display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I€™m trying to accomplish.

Tbltimes

|empnum | empname | Tin | Tout | Thrs |

| 1 | john | 2:00PM | 3:00PM |1hr |

| 1 | john | 2:00PM | 3:00PM | 1hr |

| 2 | joe | 1:00PM | 6:00PM | 5hr |

GridView1

| 1 | John | 2hrs |

| 2 | Joe | 5hrs |

im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.

View 18 Replies View Related

Looping In T-SQL

Mar 19, 2002

I want to loop through a recordset and do inserts into another table based on each record.

The way I have been doing it is copy my key data into a temp table,
Loop through temp finding the max ID
Doing what I need to do, deleting the max, then finding the new max and looping until no records exist.

I know there has to be a better way. The table I am working with is millions of records.
Thanks in advance,
Chris Reeder

View 2 Replies View Related

DTS And Looping

Mar 28, 2002

I need to loop through a set of tables and move the data through a data pump from one server to another. This set of tables is dynamic so I have greated a global recordset and the looping is working fine.

During the looping process I need to change the transformations for each table so the source, destination, and transformation of the datapump are correct for the next table in the loop. I am using a VBS to handle this right now but cannot get the transformation to change. I essentially want to auto-remap using a vbs script. Is this possible?

Thanks for any help.

View 1 Replies View Related

Help With Looping

May 23, 2008

Hello clever people
I have a table that holds duplicates that I want to change into a table that has no duplicates. The current table is this
name
compound_id integer
name varchar(150)
name_type integer

This table stores chemical names. There is no primary key in the table so there are multiple compound_id's. I think the original idea was to have four name-types
1 = chemical name
2 = a description of the chemical
3 = a synonym of the chemical
4 = a formula of the chemical

I have created a new table called compound_name with this structure

id int primary key (auto identity)
compound_id int used as a foreign key
compound_name varchar(150)
compound_desc varchar(250)
compound_synonym varchar(150)
compound_formula varchar(50)
compound_trade_nme varchar(50)

I have also started to populate the new table by running this code
insert into compound_name(compound_id,compound_name)
SELECT DISTINCT compound_id, name
FROM dbo.name
WHERE (name_type = 1)

Now I need to somehow loop through the name table getting distinct compound_id's, and perform a case when name_type = 2 (which is synonym name_type) Then inside the loop update compound_name.compound_synonym for each compound_id which matches name_type 2
Then case 3 do the same for name_type 3 which is the name_type for descripton
Then case 4 do the same for name_type 4 which is the formula

Any help would be appreciated

View 6 Replies View Related

Help With Looping!

Sep 29, 2004

Hi there, I am new to SQL and am having trouble looping a script. I have the following script that needs to be refreshed a large number of times, or needs to be looped indefinitely until stopped:

select
df.tablespace_name "Tablespace",
block_size "Block Size",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
dba_tablespaces ts,
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from dba_free_space
group by tablespace_name) fs
where
ts.tablespace_name = fs.tablespace_name
and
df.tablespace_name = fs.tablespace_name(+)
;

I know this question may have a very easy solution, but I have no idea how to solve it.

Thanks in advance

View 4 Replies View Related

Looping

Mar 21, 2007

hi guys,

I have a table like this,
S D C


Bi Ar Ar
Bi Ar Ch
Bi Ar Ma
Bi Au Ar
Bi Au Ch
Bi Au Ma
As Ar Ar
As Ar Ch
As Ar Ma
As Au Ar
As Au Ch
As Au Ma
As Au Ma


I have 3 columns S, D, C. i have text values in it. I need to write a query such that it will check each row for distinct value.For ex, all the rows are distinct except the last one. so i need to see all the duplicate entries. can anyone help me?

View 4 Replies View Related

Looping

Jun 15, 2007

Hi guys,

Can anybody tell me how to do looping in SQL?

Regards,
David

View 6 Replies View Related

A Question About Looping

Sep 6, 2007

Hi All,
 I would like to know the best way to approach the following requirement:
I have an ASP.net 2 web site which gets its data from SQL 2005.
I am trying to run a series of 'rules' which are SQL where statements stored in a table, against rows stored in another table. I open the 'Rules' table looping through all records. I copy each rule to a string and put it on the end of the SQL statement so that the rule will only be appended if it passes the rule... this may be a little confusing.
The rules process will fire when the details have been submitted to the database.
Table containg rules would contain something like:
ID, RuleSQL
1, (ClientAge >18)
2, (ClientIncome>10000)
3 Etc...
This a very simplified version of the table but gives the general idea.
I currently use ASP.NET 2 and sqlconnections/datareaders to do this. I would like to know if there is a way of doing the same thing server side using Transact SQL because that would (I believe) speed up the time taken to perform all the tests as i wouldn't need to rely on ASP to open all recordsets and append the data.
If the ASP route would be the standard way of doing it and is not likely to have a detremental effect on performance then i am fine to stick with it because i know it works.
any comments or suggestions would be welcomed.
Thanks,
Ian

View 2 Replies View Related

Looping Parameters?

Jan 17, 2008

I have an array (12,2) of values plus a profile variable that I want to pass as parameters while writing to a database.  I've been told that I've set up the parameters wrong, and they cannot be changed every time I loop using the method I'm using. But I have no idea how to use any other method. Please... I'm down to the wire in terms of deadline here. I have until midnight to get it uploaded and running online.  [CODE]Sub WriteClasses(ByVal CreditsArray)        Dim i As Integer        Dim EnrollDb As SqlConnection        Dim cmdEnroll As SqlCommand        EnrollDb = New SqlConnection("Server=LONNASQLEXPRESS;Integrated Security=True;database=LGordonTouroReg")        cmdEnroll = New SqlCommand("INSERT INTO Enrollment (SectionID, Semester, Year, ClassID, StudentID) VALUES (@SectionID, 'Fall', '2007', @ClassID, @StudentID)", EnrollDb)                EnrollDb.Open()        For i = 0 To 12        cmdEnroll.Parameters.AddWithValue("@SectionID", CreditsArray(i, 2))
        cmdEnroll.Parameters.AddWithValue("@ClassID", CreditsArray(i, 0))
        cmdEnroll.Parameters.AddWithValue("@studentID", Profile.StudentID)            If Not CreditsArray(i, 0) = "" Then                cmdEnroll.ExecuteNonQuery()                Response.Write(CreditsArray(i, 0) & " has been added to your schedule.<br/>")            End If        Next i        EnrollDb.Close()    End Sub[/CODE]

View 6 Replies View Related

Looping Through Sqlparameters

Apr 11, 2008

lets say i have a stored procedure (for insert command) which i am calling in my code to execute.  The user provided data is being stored in a array. My class takes the stored procedure name and also takes parameters name and types.  Is there any way to loop through the parameters,  (various columns in the table which is of diffrent data type ie varchar, int, etc).  How to implement it?

View 1 Replies View Related

Looping Through One Row At A Time

Sep 14, 2004

Hoping for a little help... I'm attemting to call a stored proc, pass parameters, and display the data 1 record at a time. I need to be able to show the data in a series of lables or text boxes. So the user will see one record, pushed into the lables, click a button and go to the next record...so on and so forth.

I think I have the code to get the data correct, it's the displaying data in lables and looping through the recordset the has me clueless.


Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
If Not Page.IsPostBack Then ' IF This is the first page load
Dim UserID As String = Request.QueryString("UserID")
' parameter for stored procedure
Dim RoleID As String = Request.QueryString("RoleID")


Dim DS As DataSet
Dim MyConnection As SqlConnection
Dim MyCommand As SqlDataAdapter

MyConnection = New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionString"))
MyCommand = New SqlDataAdapter("getdirective", MyConnection)
MyCommand.SelectCommand.CommandType = CommandType.StoredProcedure
MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@roleID", SqlDbType.NVarChar)).Value = RoleID

Try
DS = New DataSet
MyCommand.Fill(DS)

'Display data in a series of lables or highly formated datagrid

Catch ex As Exception
Response.Write("<font color=red>Error: " & ex.Message & "</font>")

End Try


Else 'IF the page is being reloaded


End If


End Sub


Private Sub cmdAck_Click(...) Handles cmdAck.Click
'This need to loop through the records


End Sub



Thanks for any help!!!

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved