Need the logic to get this done without cursors

Suppose in the table below the left column is called intNumber and right column is called strItem.I want to select the rows where the intNumber number appears for the first time(the ones marked with arrows), how do I do it?

for intNumber = 1 what is the logic to choose x rather than y ?

for intNumber = 2 what is the logic to choose z rather than m ?

|||

dilbert1947:

I want to select the rows where the intNumber number appears for the first time

As khtan is asking, what is the logic behind your requirement? SQL Server has no concept of "first" without some kind of data sorting.

Typically what you are asking for could be accomodated by this sort of query, where the non-unique columns(s) are aggregated in some manner:

SELECT intNumber, MIN(strItem) FROM myTable GROUP BY intNumber ORDER BY intNumber

|||

Are you trying to accomplish this from code on a page, or in your SQL data manager? If you are doing this from a page, you could use a function with logic such as:

dataset1 = SELECT DISTINCT id FROM tableName //populates with 1 entry for each different ID</P><P>while(dataset != null){ //loop until out of ID's</P><P>dataset2 = SELECT TOP 1 * FROM tableName WHERE id = dataset1.value; // grab top record for each distinct ID</P><P>//do something with data</P><P>dataset1.movenext // move to next record</P><P>}

|||

Sorry for the mess. That should read:

dataset1 = SELECT DISTINCT id FROM tableName //populates with 1 entry for each different ID

while(dataset != null){ //loop until out of ID's

dataset2 = SELECT TOP 1 * FROM tableName WHERE id = dataset1.value; // grab top record for each distinct ID

do something with data

dataset1.movenext // move to next record

}

Need suggestion on TSql Query with Joins

Hi All,

Please suggest me is there any performance/other differences between the below two queries.

-query1

select T1.name,T1.Number, T2.Dept, T2.Desig

From T1 Inner Join T2 on T1.EID = T2.EID

-query2

select T1.name,T1.Number, T2.Dept, T2.Desig

From T1 Inner Join (Select Dept, Desig From T2) As T2 on T1.EID = T2.EID

Thanks

Senthil

There is no performance difference for your quires.

Both are executed in same way..

Suppose, if your query2, subquery has any distinct or group by clause then the query1 may perform better than query2.

|||Thanks Sekaran!

Need stored procedure that shows one value for null and another for not null

I have a stored sprocedure with two parameters that currently searches a table for a matching record see SQL below:

**************************************************************

@.WorkOrderNum numeric(18,0)

,@.StackNum numeric(18,0)

AS

--This procedure is used in iFIX. It looks for matching

--Work Order Number / Stack Number combination.

SELECT

work_order_no

,stack_no

FROM

dbo.prod_data

WHERE

(dbo.prod_data.work_order_no = @.WorkOrderNum)

AND (dbo.prod_data.stack_no = @.StackNum)

******************************************************************************

What I need is a stored procedure that will look for a matching criteria and if it finds some it will return a value of "2", if it does not find any criteria it needs to return a value of "1". This value does not need to be stored, for display only.

Any help would be greatly appreciated

You could do below:

Code Snippet

select case when exists(

SELECT *

FROM

dbo.prod_data as p

WHERE

p.work_order_no = @.WorkOrderNum

AND p.stack_no = @.StackNum
) then 2 else 1 end as matched

|||

Thanks it worked perfectly

Need sql to return the result of a query as comma seperated values.

Hi,
I need a sql that returns thequery result as comma seperated list of values, instead of several rows. Below is the scenario...
Table Name - Customer
Columns - CustomerID, Join Date
Say below is the data of Customer table ...
CustomerID JoinDate
1 04/01/2005
2 01/03/2003
3 06/02/2004
4 01/05/2002
5 09/07/2005
Now i want to retrieve all the customerid's who have joined this year. Below is the query that i use for this case.
Select CustomerID from Customer where JoinDate between '01/01/2005' and GetDate()
This gives the below result as two rows.
CustomerID
1
5
But i need to get the result as '1,5' (comma seperated list of resulting values).
Any help is highly appreciated
Thanks in Advance
Ramesh

You need declare a variable :
declare @.value nvarchar(200)
Select@.value=case when @.value is null then '' else @.value+',' end+cast(CustomerID as varchar) from Customer where JoinDate between '01/01/2005' and GetDate()
|||Hi,
You can manage your goal by using COALESCE
Please check the following URLs for sample COALESCE usage for returning the column values of a tables in a string seperated by a delimeter character.
http://www.kodyaz.com/ShowPost.aspx?PostID=76
http://www.kodyaz.com/article.aspx?ArticleID=29

As a sample you can run the below code on Northwind database also
DECLARE @.s as nvarchar(4000)
DECLARE @.char as char(1)
SELECT @.char = ','
SELECT @.s = @.char
SELECT @.s = COALESCE(FirstName + @.char + @.s , '') FROM Employees
SELECT SUBSTRING(@.s, 0, LEN(@.s)-1) AS Employees

I hope this helps,
Eralper
|||

Hi All,

I Solved the problem with the below query...

DECLARE @.CustomerIDs VARCHAR(8000)

SELECT @.CustomerIDs = ISNULL(@.CustomerIDs + ',', '') + CAST(CustomerID AS VARCHAR(10))
FROM CUSTOMER
WHERE JoinDate BETWEEN '01/01/2005' and GetDate()

SELECT @.CustomerIDs AS CustomerID

|||It's great! It really worked fine. Thanks a lot...

Need SQL Server 2005 tool similar to Oracle's Connection Manager

I would like to know if there is a comparable tool in SQL Server 2005 to the
Oracle Connection Manager (link to Overview on Oracle product below)
http://www.oracle.com/technology/products/oraclenet/htdocs/cman_overview.htmHi
"Jason" wrote:
> I would like to know if there is a comparable tool in SQL Server 2005 to the
> Oracle Connection Manager (link to Overview on Oracle product below)
> http://www.oracle.com/technology/products/oraclenet/htdocs/cman_overview.htm
>
Apart from connection pooling I don't know of any third party product that
does anything like this.
For example see http://msdn2.microsoft.com/en-us/library/ms716319.aspx
John

Need some with query

Hi, I need some help formulating a query. Below is an example records.
What I am trying to do is to come up with a query that will tell me which
record has a position_startdate that is less than position_enddate from the
record above.
ID EMP_ID POSITION POSITION_STARTDATE POSITION_ENDDATE
-- -- -- -- --
--
18 18 SALES 1/21/03
6/5/04
25 18 SALES MGR 6/5/04
12/28/04
31 18 DEPT SUP 10/18/04
8/8/05
45 18 STORE MGR 8/8/05
null
In this example, the query should pickup ID = 31 since 10/18/04 is less than
the previous postion_enddate which is 12/28/04.
Also, I am running this query with tons or employees.
I am currently working on this and thought that I might post it and get a
quick response back.
Thanks,
JB..Rick Shaw wrote:

> Below is an example
> records. What I am trying to do is to come up with a query that will
> tell me which record has a position_startdate that is less than
> position_enddate from the record above.
You should provide more information to get a better answer but I think
this should do it, assuming that when you say 'record above' you mean a
record with a lower ID.
Select T1.* from Table T1 where T1.Position_StartDate < (select top 1
T2.Position_EndDate from Table T2 where T2.ID < T1.ID order by T2.ID
Desc)
HTH,
Stijn Verrept.|||Stijn Verrept wrote:

> Rick Shaw wrote:
>
> You should provide more information to get a better answer but I think
> this should do it, assuming that when you say 'record above' you mean
> a record with a lower ID.
> Select T1.* from Table T1 where T1.Position_StartDate < (select top 1
> T2.Position_EndDate from Table T2 where T2.ID < T1.ID order by T2.ID
> Desc)
Maybe
Select T1.* from Table T1 where T1.Position_StartDate < (select top 1
T2.Position_EndDate from Table T2 where T2.ID < T1.ID and T1.Emp_ID =
T2.Emp_ID order by T2.ID
Desc)
is better, depends if you need to compare it with the same Emp_ID or not
Kind regards,
Stijn Verrept.|||Thanks Stijn. It worked great.
Thanks again for the hand.
Rick..
"Stijn Verrept" <stjin@.entrysoft.com> wrote in message
news:v7KdnRH9lLMwlRjenZ2dnUVZ8qudnZ2d@.sc
arlet.biz...
> Stijn Verrept wrote:
>
> Maybe
> Select T1.* from Table T1 where T1.Position_StartDate < (select top 1
> T2.Position_EndDate from Table T2 where T2.ID < T1.ID and T1.Emp_ID =
> T2.Emp_ID order by T2.ID
> Desc)
> is better, depends if you need to compare it with the same Emp_ID or not
> --
> Kind regards,
> Stijn Verrept.

Need some help with update query

Hi There ,

I run the query below and get the error message below the query.
How can i do this update?

UPDATE GEO_Plaats
SET NetNummer = (SELECT GEO_Netnummers.Netnummer
FROM GEO_Netnummers
WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats)
WHERE EXISTS
(SELECT GEO_Netnummers.Netnummer
FROM GEO_Netnummers
WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats);

ERROR:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Cheers WimmoThis is untested but it might be of assistance.

UPDATE GEO_Plaats
SET NetNummer = n.Netnummer
FROM GEO_Plaats p
JOIN GEO_Netnummers n
ON p.Plaats = n.Plaats

Good Luck!|||As you see problem is in your subquery

(SELECT GEO_Netnummers.Netnummer
FROM GEO_Netnummers
WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats)

which returns more than 1 record from GEO_Netnummers table for specific Plaats in GEO_Plaats table. Simply speaking one "Plaat" from GEO_Plaats have more records in GEO_Netnummers table (cardinality is 1:N)

If all records in GEO_Netnummers have the same Netnummer for specific GEO_Plaats.Plaats you can use distinct to retrive 1 record:

UPDATE GEO_Plaats
SET NetNummer = (SELECT DISTINCT GEO_Netnummers.Netnummer
FROM GEO_Netnummers
WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats)
WHERE EXISTS
(SELECT GEO_Netnummers.Netnummer
FROM GEO_Netnummers
WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats);

If GEO_Netnummers.Netnummer differs for specific GEO_Plaats.Plaats you have 2 options:
1) use max() or min() function to retrieve 1 record. But in this case you have to realise if your subquery returns Netnummers: 1, 2, 5, 70 you select max() it means 70 but what if 5 is correct one?

UPDATE GEO_Plaats
SET NetNummer = (SELECT max(GEO_Netnummers.Netnummer)
FROM GEO_Netnummers
WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats)
WHERE EXISTS
(SELECT GEO_Netnummers.Netnummer
FROM GEO_Netnummers
WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats);

2) use another restrictions in your WHERE clause to retrieve 1 unique number. This restrictions depend on your "business'' rules

UPDATE GEO_Plaats
SET NetNummer = (SELECT GEO_Netnummers.Netnummer
FROM GEO_Netnummers
WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats
AND GEO_Netnummers.SOMETHING = SOMETHING
...)
WHERE EXISTS
(SELECT GEO_Netnummers.Netnummer
FROM GEO_Netnummers
WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats);

It seems to me, Netnummer is primary key in GEO_Netnummers table, so I think DISTINC won't help. In this case I'd use 2. scenario and option 2|||As you see problem is in your subquery
[...]
which returns more than 1 record from GEO_Netnummers table for specific Plaats in GEO_Plaats table. Simply speaking one "Plaat" from GEO_Plaats have more records in GEO_Netnummers table (cardinality is 1:N)

It might be a data problem. The table and column names indicate this is about the Dutch phone system; plaats = city, netnummer = area code.

As far as I know, a 'plaats' is supposed to have exactly 1 'netnummer'; several 'plaatsen' may share a 'netnummer'. If that's true and this error occurs, there may be invalid data in the GEO_netnummers table.

The error might also occur if the joining is done on the city's name; several cities may exist with identical names but different 'netnummers'.

need script to monitor os free space & errors

I am looking for a script to check the os free space and email if getting below a threshold.
Additionally, I am looking for another script to read the sql server log for errors and to email if error (or a string) is found.
MikeYou can take help of ALERTS in SQL server for free space threshold, refer to books online for more information.

Also can take help of Notification services or third party tools like BMC Patrol to alert if any error occurs in SQL error log/|||Procedure for extracting error from Errorlog and send email:

1) Open a notepad, cut and paste the following sample of errors
that you would like to find:

Error: 3414
Error: 3437
Error: 3619
Error: 8651
Error: 9002
BACKUP failed to complete
consistency errors
Exception Access Violation
.
.
.

and save it as "search_list.txt"

2) Open another notepad, cut & paste the below sample to
extract the error and output to a file (C:\List_error.txt) if
any error in the Errorlog is matched with the "Search_list.txt"
provided above:

IF EXIST DEL C:\List_errorlog.txt
Findstr /i /g:C:\Search_List.txt D\MSSQL\LOG\errorlog >
C:\List_errorlog.txt

osql -Sservername -E -Q"xp_sendmail @.recipients = 'DBA_on_call@.yahoo.com', @.message = 'See attached file for error',@.attachments = 'H:\MSSQL\LOG\List_errorlog.txt', @.subject = 'SQL Server Errorlog'"

save it as a command file like "Search_error.cmd"

3) start the Enterprise Manager and schedule a job that run the
"Search_error.cmd" for every 10 min or whenever...

Goodluck

SVT

Need records forthe last month

I need to retrieve records that are posted within the last month and I've
tried variations of below, but do not get all the desired result. I either
get records outside the range or not all the records I should. Posted is
Date1 below.
select convert(char,date1,101)as date1,convert(char,date2,101)as date2 from
table1 WHERE DATEDIFF( month, date1 , GETDATE() ) <=1
select convert(char,date1,101)as date1,convert(char,date2,101)as date2 from
table1 where date1 <= dateadd(month, -1, getdate())
It seems it should be very simple, but I haven't found the satisfactory
result.
I've been spending a lot of time on this and would appreciate some ideas.You don't explain exactly what is wrong with the result sets, but I'll
hazard a guess. GETDATE() returns both a date and a time part. So DateDiff
and date add will retain these, normally this is the desired operation but
in your case, I'd guess that you want all the rows in the last month, that
is everything after midnight (12:00am) on the given day. I'd suggest that
you do this like this...
select
convert(char,date1,101)as date1, convert(char,date2,101) as date2
from table1
where date1 <= dateadd(month, -1, dateadd( day, datediff( day, 0,
getdate() ), 0 ) )
It's like your second attempt, but with one difference, the way that getdate
is read.
The code dateadd(day, datediff( day, 0, getdate()),0) will perform a
GetDate() which is effectivly now, then strip off the time part to return
12:00am. Will return all rows which were returned in the last month, but
also including anything from the entire day of the first day in the range.
Regards
Colin Dawson
www.cjdawson.com
"SK" <SK@.discussions.microsoft.com> wrote in message
news:644FB09F-2EA2-434C-B4DC-D5BE6E46936A@.microsoft.com...
>I need to retrieve records that are posted within the last month and I've
> tried variations of below, but do not get all the desired result. I
> either
> get records outside the range or not all the records I should. Posted is
> Date1 below.
> select convert(char,date1,101)as date1,convert(char,date2,101)as date2
> from
> table1 WHERE DATEDIFF( month, date1 , GETDATE() ) <=1
>
> select convert(char,date1,101)as date1,convert(char,date2,101)as date2
> from
> table1 where date1 <= dateadd(month, -1, getdate())
> It seems it should be very simple, but I haven't found the satisfactory
> result.
> I've been spending a lot of time on this and would appreciate some ideas.
>
>|||Thank you so much for answering on a Saturday.
Yes, I wanted all the dates within the last month. After spending all day
on it and getting very , I finally found something that seems to
work. I created a date table with sample dates and compared very closely.
It was never quite accurate. I tried the code, but it returns records from
2005, which is a problem I was having also.
But, finally this seems to work.
select convert(char,date1,1) as date1
from table1
where date1 <= getdate() and date1 >=
convert(char,dateadd(month,-1,getdate()),1)
Thanks again,
SK
"Colin Dawson" wrote:

> You don't explain exactly what is wrong with the result sets, but I'll
> hazard a guess. GETDATE() returns both a date and a time part. So DateDi
ff
> and date add will retain these, normally this is the desired operation but
> in your case, I'd guess that you want all the rows in the last month, that
> is everything after midnight (12:00am) on the given day. I'd suggest tha
t
> you do this like this...
>
> select
> convert(char,date1,101)as date1, convert(char,date2,101) as date2
> from table1
> where date1 <= dateadd(month, -1, dateadd( day, datediff( day, 0,
> getdate() ), 0 ) )
> It's like your second attempt, but with one difference, the way that getda
te
> is read.
> The code dateadd(day, datediff( day, 0, getdate()),0) will perform a
> GetDate() which is effectivly now, then strip off the time part to return
> 12:00am. Will return all rows which were returned in the last month, but
> also including anything from the entire day of the first day in the range.
> Regards
> Colin Dawson
> www.cjdawson.com
> "SK" <SK@.discussions.microsoft.com> wrote in message
> news:644FB09F-2EA2-434C-B4DC-D5BE6E46936A@.microsoft.com...
>
>

Need Quick Help Please

My boss wants a list of sites from our table with other information. Below i
s
the query I am using to get the information. Trouble is, the query returns
between 3 to 5 rows per site because of the history table that keeps a
history of each site. How can I get it to return only the history of the
first site found? Thank you for any help.
SELECT sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, acct_number.acct_number_description,
status_types.name,
sites_status_history.status_date
FROM sites INNER JOIN
site_acct_number_join ON sites.site_id =
site_acct_number_join.site_id INNER JOIN
acct_number ON site_acct_number_join.acct_number_id =
acct_number.acct_number_id INNER JOIN
sites_status_history ON sites.site_id =
sites_status_history.site_id INNER JOIN
status_types ON sites_status_history.status_type_id =
status_types.type_id
WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328',
'059505', '081101', '081111', '081183', '081196', '081200', '081205',
'081213',
'081242', '081248', '081340', '081992', '088477',
'091001', '091003', '091036', '091054', '091055', '091072', '091075',
'091082', '091310', '091878',
'092448', '095005', '095006', '095007', '095327',
'095470', '095514', '095676', '096049', '096057', '096214', '097011',
'097019', '097297', '097306',
'097320', '097356', '097428', '097470', '097472',
'097480', '097625', '097787', '097801', '097805', '097807', '097809',
'097813', '097822', '510245',
'510266', '510268', '510809', '512001', '512009',
'512012', '512023', '512032', '512035', '512037', '512041', '512042',
'512043', '512044', '512048',
'512058', '512060', '512068', '512070', '512071',
'512073', '512076', '512078', '512079', '512080', '512081', '512086',
'512088', '512089', '512090',
'512097', '512107', '512111', '512113', '512125',
'512131', '512145', '512159', '512166', '512176', '512218', '512235',
'512459', '512491', '512492',
'512639', '512695', '512696', '512697', '512742',
'512791', '512819', '512857', '513003', '513028', '513030', '513093',
'513095', '513103', '513131',
'513154', '513268', '513982', '514004', '514007',
'514012', '514014', '514015', '514017', '514018', '514023', '514025',
'514027', '514030', '514031',
'514032', '514033', '514035', '514036', '514037',
'514038', '514039', '514041', '514042', '514043', '514044', '514049',
'514050', '514053', '514054',
'514058', '514060', '514061', '514065', '514067',
'514068', '514070', '514075', '514078', '514079', '514082', '514084',
'514085', '514086', '514098',
'514166', '514177', '514206', '514245', '514456',
'514477', '515401', '515430', '515451', '515629', '515667', '515689',
'516562', '516565', '517711',
'517744', '517771', '517773', '517774', '517787',
'517788', '517790', '517794', '517796', '517801', '517807', '517825',
'517834', '517835', '517836',
'517840', '517848', '517849', '517871', '620600',
'621900', '632100', '632700', '639200', '641400', '643100', '643300',
'647800', '652101', '860201',
'865344', '865637', '865690', 'IL0082', 'IL0083',
'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
'IL0175', 'IL0178', 'IL0180', 'IL0181',
'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
'IN0041', 'IN0042', 'IN0043', 'IN0045',
'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
'IN0062', 'IN0064', 'IN0066',
'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
'IN0147', 'IN0149', 'IN0332',
'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
'JTX102', 'JTX103', 'JTX104',
'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
'TX0151', 'TX0157', 'TX0158',
'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
'TX0181', 'TX0182', 'TX0183',
'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
'WFS113'))
ORDER BY sites.client_site_id, sites_status_history.status_date DESCI don't understand why do you need to list the SiteIDs. Anyway, try this
query \:
SELECT sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, acct_number.acct_number_description,
status_types.name,
SH1.status_date
FROM sites INNER JOIN
site_acct_number_join ON sites.site_id =
site_acct_number_join.site_id INNER JOIN
acct_number ON site_acct_number_join.acct_number_id =
acct_number.acct_number_id INNER JOIN
sites_status_history SH1 ON sites.site_id =
SH1.site_id INNER JOIN
status_types ON sites_status_history.status_type_id =
status_types.type_id
WHERE SH1.status_date = (select min(SH2.status_date)
from
sites_status_history SH2
where SH1.site_id =
SH2.site_id )
and (your list of IDs)
Perayu
"Mike Collins" wrote:

> My boss wants a list of sites from our table with other information. Below
is
> the query I am using to get the information. Trouble is, the query returns
> between 3 to 5 rows per site because of the history table that keeps a
> history of each site. How can I get it to return only the history of the
> first site found? Thank you for any help.
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> sites_status_history.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id =
> site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id
=
> acct_number.acct_number_id INNER JOIN
> sites_status_history ON sites.site_id =
> sites_status_history.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id
=
> status_types.type_id
> WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328'
,
> '059505', '081101', '081111', '081183', '081196', '081200', '081205',
> '081213',
> '081242', '081248', '081340', '081992', '088477',
> '091001', '091003', '091036', '091054', '091055', '091072', '091075',
> '091082', '091310', '091878',
> '092448', '095005', '095006', '095007', '095327',
> '095470', '095514', '095676', '096049', '096057', '096214', '097011',
> '097019', '097297', '097306',
> '097320', '097356', '097428', '097470', '097472',
> '097480', '097625', '097787', '097801', '097805', '097807', '097809',
> '097813', '097822', '510245',
> '510266', '510268', '510809', '512001', '512009',
> '512012', '512023', '512032', '512035', '512037', '512041', '512042',
> '512043', '512044', '512048',
> '512058', '512060', '512068', '512070', '512071',
> '512073', '512076', '512078', '512079', '512080', '512081', '512086',
> '512088', '512089', '512090',
> '512097', '512107', '512111', '512113', '512125',
> '512131', '512145', '512159', '512166', '512176', '512218', '512235',
> '512459', '512491', '512492',
> '512639', '512695', '512696', '512697', '512742',
> '512791', '512819', '512857', '513003', '513028', '513030', '513093',
> '513095', '513103', '513131',
> '513154', '513268', '513982', '514004', '514007',
> '514012', '514014', '514015', '514017', '514018', '514023', '514025',
> '514027', '514030', '514031',
> '514032', '514033', '514035', '514036', '514037',
> '514038', '514039', '514041', '514042', '514043', '514044', '514049',
> '514050', '514053', '514054',
> '514058', '514060', '514061', '514065', '514067',
> '514068', '514070', '514075', '514078', '514079', '514082', '514084',
> '514085', '514086', '514098',
> '514166', '514177', '514206', '514245', '514456',
> '514477', '515401', '515430', '515451', '515629', '515667', '515689',
> '516562', '516565', '517711',
> '517744', '517771', '517773', '517774', '517787',
> '517788', '517790', '517794', '517796', '517801', '517807', '517825',
> '517834', '517835', '517836',
> '517840', '517848', '517849', '517871', '620600',
> '621900', '632100', '632700', '639200', '641400', '643100', '643300',
> '647800', '652101', '860201',
> '865344', '865637', '865690', 'IL0082', 'IL0083',
> 'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
> 'IL0175', 'IL0178', 'IL0180', 'IL0181',
> 'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
> 'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
> 'IN0041', 'IN0042', 'IN0043', 'IN0045',
> 'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
> 'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
> 'IN0062', 'IN0064', 'IN0066',
> 'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
> 'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
> 'IN0147', 'IN0149', 'IN0332',
> 'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
> 'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
> 'JTX102', 'JTX103', 'JTX104',
> 'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
> 'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
> 'TX0151', 'TX0157', 'TX0158',
> 'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
> 'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
> 'TX0181', 'TX0182', 'TX0183',
> 'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
> 'WFS113'))
> ORDER BY sites.client_site_id, sites_status_history.status_date DESC|||That is excellent. I still have some duplicates because of another table, bu
t
I should be able to follow your logic and get rid of those. Thank you so muc
h.
p.s. Boss wants to see the siteIDs.
"Perayu" wrote:
[vbcol=seagreen]
> I don't understand why do you need to list the SiteIDs. Anyway, try this
> query \:
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> SH1.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id =
> site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id
=
> acct_number.acct_number_id INNER JOIN
> sites_status_history SH1 ON sites.site_id =
> SH1.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id
=
> status_types.type_id
> WHERE SH1.status_date = (select min(SH2.status_date)
> from
> sites_status_history SH2
> where SH1.site_id =
> SH2.site_id )
> and (your list of IDs)
>
> Perayu
>
> "Mike Collins" wrote:
>|||I thought I could figure out how to get rid of the other duplicate record
that is showing up for most of the records, but have not been able to figure
this out yet.
Can you please help again? The acct_number_description field causes two
records to be returned for most records. Here is the select statement
(modified a little for what I need today). How can this select be modified t
o
keep the acct_number_description from returning two records per site?
SELECT sites.site_id, sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, SH1.status_date, status_types.name,
acct_number.acct_number_description
FROM sites
INNER JOIN site_acct_number_join ON sites.site_id =
site_acct_number_join.site_id
INNER JOIN acct_number ON site_acct_number_join.acct_number_id =
acct_number.acct_number_id
INNER JOIN sites_status_history SH1 ON sites.site_id = SH1.site_id
INNER JOIN status_types ON SH1.status_type_id = status_types.type_id
WHERE SH1.status_date = (
select Top 1 SH2.status_date
from sites_status_history SH2
where SH1.site_id = SH2.site_id And SH2.status_type_id = 8)
Order By sites.client_site_id
Thank you so much for your assistance. I also see that you have looked at my
other problem of the day. One other question I have is how did you learn how
to do SQL statements like that? Is there a SQL Server book that covers these
techniques?
"Perayu" wrote:
[vbcol=seagreen]
> I don't understand why do you need to list the SiteIDs. Anyway, try this
> query \:
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> SH1.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id =
> site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id
=
> acct_number.acct_number_id INNER JOIN
> sites_status_history SH1 ON sites.site_id =
> SH1.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id
=
> status_types.type_id
> WHERE SH1.status_date = (select min(SH2.status_date)
> from
> sites_status_history SH2
> where SH1.site_id =
> SH2.site_id )
> and (your list of IDs)
>
> Perayu
>
> "Mike Collins" wrote:
>

Need Quick Help Please

My boss wants a list of sites from our table with other information. Below is
the query I am using to get the information. Trouble is, the query returns
between 3 to 5 rows per site because of the history table that keeps a
history of each site. How can I get it to return only the history of the
first site found? Thank you for any help.
SELECT sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, acct_number.acct_number_description,
status_types.name,
sites_status_history.status_date
FROM sites INNER JOIN
site_acct_number_join ON sites.site_id =
site_acct_number_join.site_id INNER JOIN
acct_number ON site_acct_number_join.acct_number_id =
acct_number.acct_number_id INNER JOIN
sites_status_history ON sites.site_id =
sites_status_history.site_id INNER JOIN
status_types ON sites_status_history.status_type_id =
status_types.type_id
WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328',
'059505', '081101', '081111', '081183', '081196', '081200', '081205',
'081213',
'081242', '081248', '081340', '081992', '088477',
'091001', '091003', '091036', '091054', '091055', '091072', '091075',
'091082', '091310', '091878',
'092448', '095005', '095006', '095007', '095327',
'095470', '095514', '095676', '096049', '096057', '096214', '097011',
'097019', '097297', '097306',
'097320', '097356', '097428', '097470', '097472',
'097480', '097625', '097787', '097801', '097805', '097807', '097809',
'097813', '097822', '510245',
'510266', '510268', '510809', '512001', '512009',
'512012', '512023', '512032', '512035', '512037', '512041', '512042',
'512043', '512044', '512048',
'512058', '512060', '512068', '512070', '512071',
'512073', '512076', '512078', '512079', '512080', '512081', '512086',
'512088', '512089', '512090',
'512097', '512107', '512111', '512113', '512125',
'512131', '512145', '512159', '512166', '512176', '512218', '512235',
'512459', '512491', '512492',
'512639', '512695', '512696', '512697', '512742',
'512791', '512819', '512857', '513003', '513028', '513030', '513093',
'513095', '513103', '513131',
'513154', '513268', '513982', '514004', '514007',
'514012', '514014', '514015', '514017', '514018', '514023', '514025',
'514027', '514030', '514031',
'514032', '514033', '514035', '514036', '514037',
'514038', '514039', '514041', '514042', '514043', '514044', '514049',
'514050', '514053', '514054',
'514058', '514060', '514061', '514065', '514067',
'514068', '514070', '514075', '514078', '514079', '514082', '514084',
'514085', '514086', '514098',
'514166', '514177', '514206', '514245', '514456',
'514477', '515401', '515430', '515451', '515629', '515667', '515689',
'516562', '516565', '517711',
'517744', '517771', '517773', '517774', '517787',
'517788', '517790', '517794', '517796', '517801', '517807', '517825',
'517834', '517835', '517836',
'517840', '517848', '517849', '517871', '620600',
'621900', '632100', '632700', '639200', '641400', '643100', '643300',
'647800', '652101', '860201',
'865344', '865637', '865690', 'IL0082', 'IL0083',
'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
'IL0175', 'IL0178', 'IL0180', 'IL0181',
'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
'IN0041', 'IN0042', 'IN0043', 'IN0045',
'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
'IN0062', 'IN0064', 'IN0066',
'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
'IN0147', 'IN0149', 'IN0332',
'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
'JTX102', 'JTX103', 'JTX104',
'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
'TX0151', 'TX0157', 'TX0158',
'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
'TX0181', 'TX0182', 'TX0183',
'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
'WFS113'))
ORDER BY sites.client_site_id, sites_status_history.status_date DESC
I don't understand why do you need to list the SiteIDs. Anyway, try this
query \:
SELECT sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, acct_number.acct_number_description,
status_types.name,
SH1.status_date
FROM sites INNER JOIN
site_acct_number_join ON sites.site_id =
site_acct_number_join.site_id INNER JOIN
acct_number ON site_acct_number_join.acct_number_id =
acct_number.acct_number_id INNER JOIN
sites_status_history SH1 ON sites.site_id =
SH1.site_id INNER JOIN
status_types ON sites_status_history.status_type_id =
status_types.type_id
WHERE SH1.status_date = (select min(SH2.status_date)
from
sites_status_history SH2
where SH1.site_id =
SH2.site_id )
and (your list of IDs)
Perayu
"Mike Collins" wrote:

> My boss wants a list of sites from our table with other information. Below is
> the query I am using to get the information. Trouble is, the query returns
> between 3 to 5 rows per site because of the history table that keeps a
> history of each site. How can I get it to return only the history of the
> first site found? Thank you for any help.
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> sites_status_history.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id =
> site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id =
> acct_number.acct_number_id INNER JOIN
> sites_status_history ON sites.site_id =
> sites_status_history.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id =
> status_types.type_id
> WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328',
> '059505', '081101', '081111', '081183', '081196', '081200', '081205',
> '081213',
> '081242', '081248', '081340', '081992', '088477',
> '091001', '091003', '091036', '091054', '091055', '091072', '091075',
> '091082', '091310', '091878',
> '092448', '095005', '095006', '095007', '095327',
> '095470', '095514', '095676', '096049', '096057', '096214', '097011',
> '097019', '097297', '097306',
> '097320', '097356', '097428', '097470', '097472',
> '097480', '097625', '097787', '097801', '097805', '097807', '097809',
> '097813', '097822', '510245',
> '510266', '510268', '510809', '512001', '512009',
> '512012', '512023', '512032', '512035', '512037', '512041', '512042',
> '512043', '512044', '512048',
> '512058', '512060', '512068', '512070', '512071',
> '512073', '512076', '512078', '512079', '512080', '512081', '512086',
> '512088', '512089', '512090',
> '512097', '512107', '512111', '512113', '512125',
> '512131', '512145', '512159', '512166', '512176', '512218', '512235',
> '512459', '512491', '512492',
> '512639', '512695', '512696', '512697', '512742',
> '512791', '512819', '512857', '513003', '513028', '513030', '513093',
> '513095', '513103', '513131',
> '513154', '513268', '513982', '514004', '514007',
> '514012', '514014', '514015', '514017', '514018', '514023', '514025',
> '514027', '514030', '514031',
> '514032', '514033', '514035', '514036', '514037',
> '514038', '514039', '514041', '514042', '514043', '514044', '514049',
> '514050', '514053', '514054',
> '514058', '514060', '514061', '514065', '514067',
> '514068', '514070', '514075', '514078', '514079', '514082', '514084',
> '514085', '514086', '514098',
> '514166', '514177', '514206', '514245', '514456',
> '514477', '515401', '515430', '515451', '515629', '515667', '515689',
> '516562', '516565', '517711',
> '517744', '517771', '517773', '517774', '517787',
> '517788', '517790', '517794', '517796', '517801', '517807', '517825',
> '517834', '517835', '517836',
> '517840', '517848', '517849', '517871', '620600',
> '621900', '632100', '632700', '639200', '641400', '643100', '643300',
> '647800', '652101', '860201',
> '865344', '865637', '865690', 'IL0082', 'IL0083',
> 'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
> 'IL0175', 'IL0178', 'IL0180', 'IL0181',
> 'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
> 'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
> 'IN0041', 'IN0042', 'IN0043', 'IN0045',
> 'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
> 'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
> 'IN0062', 'IN0064', 'IN0066',
> 'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
> 'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
> 'IN0147', 'IN0149', 'IN0332',
> 'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
> 'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
> 'JTX102', 'JTX103', 'JTX104',
> 'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
> 'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
> 'TX0151', 'TX0157', 'TX0158',
> 'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
> 'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
> 'TX0181', 'TX0182', 'TX0183',
> 'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
> 'WFS113'))
> ORDER BY sites.client_site_id, sites_status_history.status_date DESC
|||That is excellent. I still have some duplicates because of another table, but
I should be able to follow your logic and get rid of those. Thank you so much.
p.s. Boss wants to see the siteIDs.
"Perayu" wrote:
[vbcol=seagreen]
> I don't understand why do you need to list the SiteIDs. Anyway, try this
> query \:
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> SH1.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id =
> site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id =
> acct_number.acct_number_id INNER JOIN
> sites_status_history SH1 ON sites.site_id =
> SH1.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id =
> status_types.type_id
> WHERE SH1.status_date = (select min(SH2.status_date)
> from
> sites_status_history SH2
> where SH1.site_id =
> SH2.site_id )
> and (your list of IDs)
>
> Perayu
>
> "Mike Collins" wrote:
|||I thought I could figure out how to get rid of the other duplicate record
that is showing up for most of the records, but have not been able to figure
this out yet.
Can you please help again? The acct_number_description field causes two
records to be returned for most records. Here is the select statement
(modified a little for what I need today). How can this select be modified to
keep the acct_number_description from returning two records per site?
SELECT sites.site_id, sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, SH1.status_date, status_types.name,
acct_number.acct_number_description
FROM sites
INNER JOIN site_acct_number_join ON sites.site_id =
site_acct_number_join.site_id
INNER JOIN acct_number ON site_acct_number_join.acct_number_id =
acct_number.acct_number_id
INNER JOIN sites_status_history SH1 ON sites.site_id = SH1.site_id
INNER JOIN status_types ON SH1.status_type_id = status_types.type_id
WHERE SH1.status_date = (
select Top 1 SH2.status_date
from sites_status_history SH2
where SH1.site_id = SH2.site_id And SH2.status_type_id = 8)
Order By sites.client_site_id
Thank you so much for your assistance. I also see that you have looked at my
other problem of the day. One other question I have is how did you learn how
to do SQL statements like that? Is there a SQL Server book that covers these
techniques?
"Perayu" wrote:
[vbcol=seagreen]
> I don't understand why do you need to list the SiteIDs. Anyway, try this
> query \:
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> SH1.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id =
> site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id =
> acct_number.acct_number_id INNER JOIN
> sites_status_history SH1 ON sites.site_id =
> SH1.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id =
> status_types.type_id
> WHERE SH1.status_date = (select min(SH2.status_date)
> from
> sites_status_history SH2
> where SH1.site_id =
> SH2.site_id )
> and (your list of IDs)
>
> Perayu
>
> "Mike Collins" wrote:

Need Quick Help Please

My boss wants a list of sites from our table with other information. Below is
the query I am using to get the information. Trouble is, the query returns
between 3 to 5 rows per site because of the history table that keeps a
history of each site. How can I get it to return only the history of the
first site found? Thank you for any help.
SELECT sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, acct_number.acct_number_description,
status_types.name,
sites_status_history.status_date
FROM sites INNER JOIN
site_acct_number_join ON sites.site_id = site_acct_number_join.site_id INNER JOIN
acct_number ON site_acct_number_join.acct_number_id = acct_number.acct_number_id INNER JOIN
sites_status_history ON sites.site_id = sites_status_history.site_id INNER JOIN
status_types ON sites_status_history.status_type_id = status_types.type_id
WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328',
'059505', '081101', '081111', '081183', '081196', '081200', '081205',
'081213',
'081242', '081248', '081340', '081992', '088477',
'091001', '091003', '091036', '091054', '091055', '091072', '091075',
'091082', '091310', '091878',
'092448', '095005', '095006', '095007', '095327',
'095470', '095514', '095676', '096049', '096057', '096214', '097011',
'097019', '097297', '097306',
'097320', '097356', '097428', '097470', '097472',
'097480', '097625', '097787', '097801', '097805', '097807', '097809',
'097813', '097822', '510245',
'510266', '510268', '510809', '512001', '512009',
'512012', '512023', '512032', '512035', '512037', '512041', '512042',
'512043', '512044', '512048',
'512058', '512060', '512068', '512070', '512071',
'512073', '512076', '512078', '512079', '512080', '512081', '512086',
'512088', '512089', '512090',
'512097', '512107', '512111', '512113', '512125',
'512131', '512145', '512159', '512166', '512176', '512218', '512235',
'512459', '512491', '512492',
'512639', '512695', '512696', '512697', '512742',
'512791', '512819', '512857', '513003', '513028', '513030', '513093',
'513095', '513103', '513131',
'513154', '513268', '513982', '514004', '514007',
'514012', '514014', '514015', '514017', '514018', '514023', '514025',
'514027', '514030', '514031',
'514032', '514033', '514035', '514036', '514037',
'514038', '514039', '514041', '514042', '514043', '514044', '514049',
'514050', '514053', '514054',
'514058', '514060', '514061', '514065', '514067',
'514068', '514070', '514075', '514078', '514079', '514082', '514084',
'514085', '514086', '514098',
'514166', '514177', '514206', '514245', '514456',
'514477', '515401', '515430', '515451', '515629', '515667', '515689',
'516562', '516565', '517711',
'517744', '517771', '517773', '517774', '517787',
'517788', '517790', '517794', '517796', '517801', '517807', '517825',
'517834', '517835', '517836',
'517840', '517848', '517849', '517871', '620600',
'621900', '632100', '632700', '639200', '641400', '643100', '643300',
'647800', '652101', '860201',
'865344', '865637', '865690', 'IL0082', 'IL0083',
'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
'IL0175', 'IL0178', 'IL0180', 'IL0181',
'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
'IN0041', 'IN0042', 'IN0043', 'IN0045',
'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
'IN0062', 'IN0064', 'IN0066',
'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
'IN0147', 'IN0149', 'IN0332',
'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
'JTX102', 'JTX103', 'JTX104',
'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
'TX0151', 'TX0157', 'TX0158',
'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
'TX0181', 'TX0182', 'TX0183',
'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
'WFS113'))
ORDER BY sites.client_site_id, sites_status_history.status_date DESCI don't understand why do you need to list the SiteIDs. Anyway, try this
query \:
SELECT sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, acct_number.acct_number_description,
status_types.name,
SH1.status_date
FROM sites INNER JOIN
site_acct_number_join ON sites.site_id = site_acct_number_join.site_id INNER JOIN
acct_number ON site_acct_number_join.acct_number_id = acct_number.acct_number_id INNER JOIN
sites_status_history SH1 ON sites.site_id = SH1.site_id INNER JOIN
status_types ON sites_status_history.status_type_id = status_types.type_id
WHERE SH1.status_date = (select min(SH2.status_date)
from
sites_status_history SH2
where SH1.site_id =SH2.site_id )
and (your list of IDs)
Perayu
"Mike Collins" wrote:
> My boss wants a list of sites from our table with other information. Below is
> the query I am using to get the information. Trouble is, the query returns
> between 3 to 5 rows per site because of the history table that keeps a
> history of each site. How can I get it to return only the history of the
> first site found? Thank you for any help.
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> sites_status_history.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id => site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id => acct_number.acct_number_id INNER JOIN
> sites_status_history ON sites.site_id => sites_status_history.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id => status_types.type_id
> WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328',
> '059505', '081101', '081111', '081183', '081196', '081200', '081205',
> '081213',
> '081242', '081248', '081340', '081992', '088477',
> '091001', '091003', '091036', '091054', '091055', '091072', '091075',
> '091082', '091310', '091878',
> '092448', '095005', '095006', '095007', '095327',
> '095470', '095514', '095676', '096049', '096057', '096214', '097011',
> '097019', '097297', '097306',
> '097320', '097356', '097428', '097470', '097472',
> '097480', '097625', '097787', '097801', '097805', '097807', '097809',
> '097813', '097822', '510245',
> '510266', '510268', '510809', '512001', '512009',
> '512012', '512023', '512032', '512035', '512037', '512041', '512042',
> '512043', '512044', '512048',
> '512058', '512060', '512068', '512070', '512071',
> '512073', '512076', '512078', '512079', '512080', '512081', '512086',
> '512088', '512089', '512090',
> '512097', '512107', '512111', '512113', '512125',
> '512131', '512145', '512159', '512166', '512176', '512218', '512235',
> '512459', '512491', '512492',
> '512639', '512695', '512696', '512697', '512742',
> '512791', '512819', '512857', '513003', '513028', '513030', '513093',
> '513095', '513103', '513131',
> '513154', '513268', '513982', '514004', '514007',
> '514012', '514014', '514015', '514017', '514018', '514023', '514025',
> '514027', '514030', '514031',
> '514032', '514033', '514035', '514036', '514037',
> '514038', '514039', '514041', '514042', '514043', '514044', '514049',
> '514050', '514053', '514054',
> '514058', '514060', '514061', '514065', '514067',
> '514068', '514070', '514075', '514078', '514079', '514082', '514084',
> '514085', '514086', '514098',
> '514166', '514177', '514206', '514245', '514456',
> '514477', '515401', '515430', '515451', '515629', '515667', '515689',
> '516562', '516565', '517711',
> '517744', '517771', '517773', '517774', '517787',
> '517788', '517790', '517794', '517796', '517801', '517807', '517825',
> '517834', '517835', '517836',
> '517840', '517848', '517849', '517871', '620600',
> '621900', '632100', '632700', '639200', '641400', '643100', '643300',
> '647800', '652101', '860201',
> '865344', '865637', '865690', 'IL0082', 'IL0083',
> 'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
> 'IL0175', 'IL0178', 'IL0180', 'IL0181',
> 'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
> 'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
> 'IN0041', 'IN0042', 'IN0043', 'IN0045',
> 'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
> 'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
> 'IN0062', 'IN0064', 'IN0066',
> 'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
> 'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
> 'IN0147', 'IN0149', 'IN0332',
> 'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
> 'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
> 'JTX102', 'JTX103', 'JTX104',
> 'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
> 'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
> 'TX0151', 'TX0157', 'TX0158',
> 'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
> 'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
> 'TX0181', 'TX0182', 'TX0183',
> 'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
> 'WFS113'))
> ORDER BY sites.client_site_id, sites_status_history.status_date DESC|||That is excellent. I still have some duplicates because of another table, but
I should be able to follow your logic and get rid of those. Thank you so much.
p.s. Boss wants to see the siteIDs.
"Perayu" wrote:
> I don't understand why do you need to list the SiteIDs. Anyway, try this
> query \:
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> SH1.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id => site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id => acct_number.acct_number_id INNER JOIN
> sites_status_history SH1 ON sites.site_id => SH1.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id => status_types.type_id
> WHERE SH1.status_date = (select min(SH2.status_date)
> from
> sites_status_history SH2
> where SH1.site_id => SH2.site_id )
> and (your list of IDs)
>
> Perayu
>
> "Mike Collins" wrote:
> > My boss wants a list of sites from our table with other information. Below is
> > the query I am using to get the information. Trouble is, the query returns
> > between 3 to 5 rows per site because of the history table that keeps a
> > history of each site. How can I get it to return only the history of the
> > first site found? Thank you for any help.
> >
> > SELECT sites.client_site_id, sites.site_name, sites.address,
> > sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> > status_types.name,
> > sites_status_history.status_date
> > FROM sites INNER JOIN
> > site_acct_number_join ON sites.site_id => > site_acct_number_join.site_id INNER JOIN
> > acct_number ON site_acct_number_join.acct_number_id => > acct_number.acct_number_id INNER JOIN
> > sites_status_history ON sites.site_id => > sites_status_history.site_id INNER JOIN
> > status_types ON sites_status_history.status_type_id => > status_types.type_id
> > WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328',
> > '059505', '081101', '081111', '081183', '081196', '081200', '081205',
> > '081213',
> > '081242', '081248', '081340', '081992', '088477',
> > '091001', '091003', '091036', '091054', '091055', '091072', '091075',
> > '091082', '091310', '091878',
> > '092448', '095005', '095006', '095007', '095327',
> > '095470', '095514', '095676', '096049', '096057', '096214', '097011',
> > '097019', '097297', '097306',
> > '097320', '097356', '097428', '097470', '097472',
> > '097480', '097625', '097787', '097801', '097805', '097807', '097809',
> > '097813', '097822', '510245',
> > '510266', '510268', '510809', '512001', '512009',
> > '512012', '512023', '512032', '512035', '512037', '512041', '512042',
> > '512043', '512044', '512048',
> > '512058', '512060', '512068', '512070', '512071',
> > '512073', '512076', '512078', '512079', '512080', '512081', '512086',
> > '512088', '512089', '512090',
> > '512097', '512107', '512111', '512113', '512125',
> > '512131', '512145', '512159', '512166', '512176', '512218', '512235',
> > '512459', '512491', '512492',
> > '512639', '512695', '512696', '512697', '512742',
> > '512791', '512819', '512857', '513003', '513028', '513030', '513093',
> > '513095', '513103', '513131',
> > '513154', '513268', '513982', '514004', '514007',
> > '514012', '514014', '514015', '514017', '514018', '514023', '514025',
> > '514027', '514030', '514031',
> > '514032', '514033', '514035', '514036', '514037',
> > '514038', '514039', '514041', '514042', '514043', '514044', '514049',
> > '514050', '514053', '514054',
> > '514058', '514060', '514061', '514065', '514067',
> > '514068', '514070', '514075', '514078', '514079', '514082', '514084',
> > '514085', '514086', '514098',
> > '514166', '514177', '514206', '514245', '514456',
> > '514477', '515401', '515430', '515451', '515629', '515667', '515689',
> > '516562', '516565', '517711',
> > '517744', '517771', '517773', '517774', '517787',
> > '517788', '517790', '517794', '517796', '517801', '517807', '517825',
> > '517834', '517835', '517836',
> > '517840', '517848', '517849', '517871', '620600',
> > '621900', '632100', '632700', '639200', '641400', '643100', '643300',
> > '647800', '652101', '860201',
> > '865344', '865637', '865690', 'IL0082', 'IL0083',
> > 'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
> > 'IL0175', 'IL0178', 'IL0180', 'IL0181',
> > 'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
> > 'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
> > 'IN0041', 'IN0042', 'IN0043', 'IN0045',
> > 'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
> > 'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
> > 'IN0062', 'IN0064', 'IN0066',
> > 'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
> > 'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
> > 'IN0147', 'IN0149', 'IN0332',
> > 'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
> > 'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
> > 'JTX102', 'JTX103', 'JTX104',
> > 'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
> > 'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
> > 'TX0151', 'TX0157', 'TX0158',
> > 'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
> > 'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
> > 'TX0181', 'TX0182', 'TX0183',
> > 'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
> > 'WFS113'))
> > ORDER BY sites.client_site_id, sites_status_history.status_date DESC|||I thought I could figure out how to get rid of the other duplicate record
that is showing up for most of the records, but have not been able to figure
this out yet.
Can you please help again? The acct_number_description field causes two
records to be returned for most records. Here is the select statement
(modified a little for what I need today). How can this select be modified to
keep the acct_number_description from returning two records per site?
SELECT sites.site_id, sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, SH1.status_date, status_types.name,
acct_number.acct_number_description
FROM sites
INNER JOIN site_acct_number_join ON sites.site_id =site_acct_number_join.site_id
INNER JOIN acct_number ON site_acct_number_join.acct_number_id =acct_number.acct_number_id
INNER JOIN sites_status_history SH1 ON sites.site_id = SH1.site_id
INNER JOIN status_types ON SH1.status_type_id = status_types.type_id
WHERE SH1.status_date = (
select Top 1 SH2.status_date
from sites_status_history SH2
where SH1.site_id = SH2.site_id And SH2.status_type_id = 8)
Order By sites.client_site_id
Thank you so much for your assistance. I also see that you have looked at my
other problem of the day. One other question I have is how did you learn how
to do SQL statements like that? Is there a SQL Server book that covers these
techniques?
"Perayu" wrote:
> I don't understand why do you need to list the SiteIDs. Anyway, try this
> query \:
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> SH1.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id => site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id => acct_number.acct_number_id INNER JOIN
> sites_status_history SH1 ON sites.site_id => SH1.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id => status_types.type_id
> WHERE SH1.status_date = (select min(SH2.status_date)
> from
> sites_status_history SH2
> where SH1.site_id => SH2.site_id )
> and (your list of IDs)
>
> Perayu
>
> "Mike Collins" wrote:
> > My boss wants a list of sites from our table with other information. Below is
> > the query I am using to get the information. Trouble is, the query returns
> > between 3 to 5 rows per site because of the history table that keeps a
> > history of each site. How can I get it to return only the history of the
> > first site found? Thank you for any help.
> >
> > SELECT sites.client_site_id, sites.site_name, sites.address,
> > sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> > status_types.name,
> > sites_status_history.status_date
> > FROM sites INNER JOIN
> > site_acct_number_join ON sites.site_id => > site_acct_number_join.site_id INNER JOIN
> > acct_number ON site_acct_number_join.acct_number_id => > acct_number.acct_number_id INNER JOIN
> > sites_status_history ON sites.site_id => > sites_status_history.site_id INNER JOIN
> > status_types ON sites_status_history.status_type_id => > status_types.type_id
> > WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328',
> > '059505', '081101', '081111', '081183', '081196', '081200', '081205',
> > '081213',
> > '081242', '081248', '081340', '081992', '088477',
> > '091001', '091003', '091036', '091054', '091055', '091072', '091075',
> > '091082', '091310', '091878',
> > '092448', '095005', '095006', '095007', '095327',
> > '095470', '095514', '095676', '096049', '096057', '096214', '097011',
> > '097019', '097297', '097306',
> > '097320', '097356', '097428', '097470', '097472',
> > '097480', '097625', '097787', '097801', '097805', '097807', '097809',
> > '097813', '097822', '510245',
> > '510266', '510268', '510809', '512001', '512009',
> > '512012', '512023', '512032', '512035', '512037', '512041', '512042',
> > '512043', '512044', '512048',
> > '512058', '512060', '512068', '512070', '512071',
> > '512073', '512076', '512078', '512079', '512080', '512081', '512086',
> > '512088', '512089', '512090',
> > '512097', '512107', '512111', '512113', '512125',
> > '512131', '512145', '512159', '512166', '512176', '512218', '512235',
> > '512459', '512491', '512492',
> > '512639', '512695', '512696', '512697', '512742',
> > '512791', '512819', '512857', '513003', '513028', '513030', '513093',
> > '513095', '513103', '513131',
> > '513154', '513268', '513982', '514004', '514007',
> > '514012', '514014', '514015', '514017', '514018', '514023', '514025',
> > '514027', '514030', '514031',
> > '514032', '514033', '514035', '514036', '514037',
> > '514038', '514039', '514041', '514042', '514043', '514044', '514049',
> > '514050', '514053', '514054',
> > '514058', '514060', '514061', '514065', '514067',
> > '514068', '514070', '514075', '514078', '514079', '514082', '514084',
> > '514085', '514086', '514098',
> > '514166', '514177', '514206', '514245', '514456',
> > '514477', '515401', '515430', '515451', '515629', '515667', '515689',
> > '516562', '516565', '517711',
> > '517744', '517771', '517773', '517774', '517787',
> > '517788', '517790', '517794', '517796', '517801', '517807', '517825',
> > '517834', '517835', '517836',
> > '517840', '517848', '517849', '517871', '620600',
> > '621900', '632100', '632700', '639200', '641400', '643100', '643300',
> > '647800', '652101', '860201',
> > '865344', '865637', '865690', 'IL0082', 'IL0083',
> > 'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
> > 'IL0175', 'IL0178', 'IL0180', 'IL0181',
> > 'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
> > 'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
> > 'IN0041', 'IN0042', 'IN0043', 'IN0045',
> > 'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
> > 'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
> > 'IN0062', 'IN0064', 'IN0066',
> > 'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
> > 'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
> > 'IN0147', 'IN0149', 'IN0332',
> > 'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
> > 'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
> > 'JTX102', 'JTX103', 'JTX104',
> > 'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
> > 'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
> > 'TX0151', 'TX0157', 'TX0158',
> > 'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
> > 'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
> > 'TX0181', 'TX0182', 'TX0183',
> > 'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
> > 'WFS113'))
> > ORDER BY sites.client_site_id, sites_status_history.status_date DESC

Need more info on integrated reporting controls

I am looking for more detail on these "integrated reporting controls" mentioned below (from http://www.microsoft.com/sql/2005/productinfo/top30features.asp#B):

"With SQL Server 2005, Reporting Services will provide report controls that will be shipped with Visual Studio 2005. Integrated reporting controls will provide improved reporting capabilities for enterprise applications".

Specifically, I am looking for more detail on these new ASP.NET 2005 controls that make dynamic server side reports generation via the Reporting Services Web Service easier.

Thanks.The Report Controls are described at http://whidbey.msdn.microsoft.com/library/?url=/library/en-us/rs_vsrpts/html/0334cfba-2cb7-4d8b-8927-e08b888450cd.asp?frame=true. They are not focused on report generation (this is via RDL and the XML library), rather report execution.

Need more info on

I am looking for more detail on these "integrated reporting controls" mentioned below (from http://www.microsoft.com/sql/2005/productinfo/top30features.asp#B):

"With SQL Server 2005, Reporting Services will provide report controls that will be shipped with Visual Studio 2005. Integrated reporting controls will provide improved reporting capabilities for enterprise applications".

Specifically, I am looking for more detail on these new ASP.NET 2005 controls that make dynamic server side reports generation via the Reporting Services Web Service easier.

Thanks.The Report Controls are described at http://whidbey.msdn.microsoft.com/library/?url=/library/en-us/rs_vsrpts/html/0334cfba-2cb7-4d8b-8927-e08b888450cd.asp?frame=true. They are not focused on report generation (this is via RDL and the XML library), rather report execution.

Need help!!!! (SP or SSIS Task)

I have written the Dynamic TSQL S-Proc. Below is what i wanted to implement in SSIS using foreach loop container as a cursor. But i am little doubtful whether I can achieve the dynamics to this level. I know everything is possible but is it advisable to go for this simple Sproc or SSIS tasks.

I have some 15 tables being populated using this SPROC.

Here is some helpful description

ENTITYNAME gives me the table i need to work

FIELDNAME gives me the field i have to work on

CHANGEDVALUE gives me the value changed in that field

( This three i get from source table which is about 9000 rows and containing 15 possible ENTITY to be work on and 100's of their respective FIELD )

while in Cursors i need to get using these above variables other variables like

FLAG

KeyName

Thrugh SQL1 I get the KeyValue

then using this KeyValue check if the data exist update else insert new data.

QUESTION: IS THIS ADVISABLE to go for SSIS task or just carry with SPROC?

/*******************************************************************************************************/

DECLARE Table_Cursor CURSOR
FOR SELECT ENTITYNAME,FIELDNAME, KEYID, CHANGEDVALUE, UPDATEUSER, UPDATEDATE
FROM dbo.ChangedDimensionStage

OPEN Table_cursor

FETCH NEXT FROM Table_cursor INTO @.ENTITY, @.FIELD, @.KEYID, @.VALUE, @.USER, @.DATE

WHILE @.@.FETCH_STATUS = 0

BEGIN

DECLARE @.FLAG NVARCHAR(50);
SET @.FLAG = (SELECT LEFT(@.ENTITY, (SELECT CHARINDEX( 'DIM', @.ENTITY) -1)) )+ 'LastUpdateFlag';

DECLARE @.KeyName NVARCHAR(50);
SET @.KeyName = (SELECT LEFT(@.ENTITY, (SELECT CHARINDEX( 'DIM', @.ENTITY) -1)) )+ 'Key'

DECLARE @.KeyValue NVARCHAR(50)

DECLARE @.SQL1 NVARCHAR (1000)

SET @.SQL1 = N'Select @.KeyValueOUT = '+ @.KeyName + ' FROM DW_Integration.dbo.MangFact WHERE ClaKey = ' + @.KEYID + ' GROUP BY ' + @.KeyName + ' HAVING SUM(TotalClaCount) > 0 OR SUM(IncidentOnlyClaCount) > 0 '

EXECUTE sp_executesql @.SQL1, N'@.KeyValueOUT INT OUTPUT', @.KeyValue OUTPUT;

DECLARE @.WC_TABLE NVARCHAR(100)
SET @.WC_TABLE = 'WorkingCopy' + @.ENTITY

DECLARE @.SQL2 nvarchar (1000);
SET @.SQL2 = 'IF EXISTS (SELECT '+ @.KeyName +' FROM ' + @.WC_TABLE + ' WHERE ' + @.KeyName + ' = ' + @.KeyValue + ' )' +
' BEGIN UPDATE ' + @.WC_TABLE + ' SET '+ @.FIELD + ' = '''+ @.VALUE + ''' WHERE ' + @.KeyName + ' = ' + @.KeyValue +'; END' +
' ELSE BEGIN
INSERT INTO '+ @.WC_TABLE + ' SELECT * FROM DW_Integration.dbo.' + @.ENTITY + ' WHERE ' + @.Flag + ' = ' + '''Y''' + ' AND '+ @.KeyName + ' = ' + @.KeyValue + ';' +
'UPDATE ' + @.WC_TABLE + ' SET '+ @.FIELD + ' = '''+ @.VALUE + ''' WHERE ' + @.KeyName + ' = ' + @.KeyValue +'; END'

EXECUTE sp_executesql @.SQL2

FETCH NEXT FROM Table_cursor INTO @.ENTITY, @.FIELD, @.KEYID, @.VALUE, @.USER, @.DATE

END

CLOSE Table_cursor
DEALLOCATE Table_cursor

This is not a striaghtforward question, and there is no straightforward answer either.

The stored proc you have provided is dreadfully inefficient, and could be done much more efficiently. But that may not be an issue with the volumes of data you are working with.

An SSIS package is another approach, but do you need to re-develop this work? What is the justification for doing so? Do you need to be able to easily update the procedure? Perhaps using external configuration? In this case, an SSIS reimplementation may be justified. Do you need to hand it over to someone else to maintain? Then again, SSIS may be useful, especially if the maintainers have little experience with T-SQL.

Nobody here is going to say to you to do it one way or another way. I would ask the person who is paying for the job what it is they are after.