Associating Data from Other Tables

Morning, all.

I have an input record that contains the following fields:

header_id_1
header_id_2
header_id_3
header_detail_1
header_detail_2

And I have a table, that contains the following fields

header_id_1
header_id_2
header_id_3
header_detail_1
header_detail_2

Okay. The kicker here is that in the table, the header_id's are already defined, but the details are blank. I need to insert the details where the header_id's of the input equal the header_id's of the table.

Is there a loop or something? I can *almost* see it in my head if I was just writing a SQL Query, but it involves a cursor, and I'm hoping SSIS will save me some of the aggravation...

Any ideas?
Isn't this just an update?

UPDATE TABLE
SET header_detail_1 = input_header_detail1,
header_detail_2 = input_header_detail2
WHERE header_id_1 = input_header_id_1
AND header_id_2 = input_header_id_2
AND header_id_3 = input_header_id_3

You could achieve this with an OLE DB Command transformation.|||Oh, hey! Yeah! Thanks!

Jim Work
|||You could also load the flat file (or whatever source you may have) to a staging table and then issue a batch update -- which would be faster than using the OLE DB Command to perform your updates...

To perform the batch update inside SSIS, you'd use an Execute SQL task in the control flow after you've loaded the staging table with a data flow.

Inside that Execute SQL task, your query would be something like:

UPDATE destination_table a, staging_table b
SET a.column1 = b.column1, a.column2 = b.column2
WHERE a.key1 = b.key1
AND a.key2 = b.key2
AND a.key3 = b.key3

It's real similar to the above query, except that you're using native SQL to perform the update rather than SSIS.

Assistance with selecting first visit record of patients

Hi all

I saw a similar query on another thread but was unable to use the answers to resolve my problem.

I have an access database with two tables - the first contains demographic data for patients (Initial visit table) and the second (followup visit) contains all visit records for these patients linked by a PID. Some of the data was entered retrospectively so the record number is not a reflection of date of visit. Have 25000 visits captured.

I need to devise a query to extract the first CD4 count (a blood result) available for each patient - cd4 not done on every visit. Have tried using:

SELECT InitialVisitID, VisitDatetime, CD4CountPercentage

FROM [FollowUpVisit] AS a

WHERE (((a.InitialVisitID)=(select top 1 InitialVisitID from [FollowUpVisit]b

where a.InitialVisitID = b.InitialVisitID

order by VisitDatetime desc)));

This initially generates a table with duplicates followed shortly thereafter by a warning that only one record can be returned by the subquery and then blanking out of all records in the query output.

Please help!

I believe you want something like this:

SELECT a.InitialVisitID, a.VisitDatetime, a.CD4CountPercentage

FROM FollowUpVisit a

inner join

(select Min(InitialVisitID) as InitialVisitID, VisitDatetime, CD4CountPercentage

from FollowUpVisit

group by InitialVisitID

) as b

on a.InitialVisitID = b.InitialVisitID

order by a.VisitDatetime desc

|||

Try:

SELECT InitialVisitID, VisitDatetime, CD4CountPercentage

FROM [FollowUpVisit] AS a

WHERE (((a.InitialVisitID)=(select top 1 InitialVisitID from [FollowUpVisit] b

where b.PID = a.PID

order by VisitDatetime desc)));

AMB

|||

Thanks for the quick response AMB

Your suggestion has the same result - except asks for the PID paramter to be entered.

Just for clarification InitialVisitID in this database is in fact the PID. So i tried it with changing PID to InitialVisitID but same outcome.

h

|||

Thanks for the response

For clarification the "InitialVisitId" is the PID - so selecting for min InitialVisitID does not work as every patient only has one InitialVisitID (is unique identifier and primary key). Your solution also gives a circular reference problem within the Selection list. Tried making it:

SELECT a.InitialVisitID, a.VisitDatetime, a.CD4CountPercentage
FROM FollowUpVisit a
inner join
(select Min(VisitDateTime) as VisitDateTime, InitialVisitID, CD4CountPercentage
from FollowUpVisit
group by InitialVisitID
) as b
on a.InitialVisitID = b.InitialVisitID
order by a.VisitDatetime desc

but got same problem

h

|||

Please, do not make us to guess your enviroment. Post some DDL, including constraints and indexes, sample data and expected result.

Can you post the error msg you are getting?. I do not think that (select top 1 c1, ..., cn from ... order by) can bring more than one row, without using keywords WITH TIES.

AMB

|||

I am confused by this table FollowUpVisit. If this was a Visit table, I might understand, but is the InitialVisit stored in the FollowUpVisit table?

And this seems to imply that you can have >1 InitialVisit?

In your query, be sure and use aliases for every column, just to be careful with the output:

SELECT a.InitialVisitID, a.VisitDatetime, a.CD4CountPercentage

FROM [FollowUpVisit] AS a

WHERE (((a.InitialVisitID)=(select top 1 b.InitialVisitID from [FollowUpVisit] b

where a.InitialVisitID = b.InitialVisitID

order by b.VisitDatetime desc)));

I agree with Hunchback, in that I don't know how that top 1 query can return > 1 row.

Assistance on SUM() statement

I have a table #changes with two columns: familyID, Versiontime

For each record in the #changes table I need to compute the income from another table FamilyIncome with colunms: familyID, IncomeType, EffectiveDate, Amt. Example Below:

FamID, IncType, EffDate, Amt
100, 10, 01/01/2003, $50.00
100, 20, 01/01/2003, $50.00
100, 30, 01/01/2003, $50.00
100, 10, 02/02/3003, $100.00
100, 20, 02/02/3003, $100.00
100, 30, 02/02/3003, $100.00
100, 40, 02/02/3003, $100.00
100, 20, 03/03/3003, $75.00
100, 30, 03/03/3003, $75.00
100, 40, 03/03/3003, $75.00

So if I'm looking for the Incomes on the following dates (which are in the #changes table) it should be:

01/02/2003 - $150.00 (The three records effective on 01/01/2003)
02/10/2003 - $400.00 (The four records effective on 02/02/2003)
04/01/2003 - $325.00 (One record from 02/02/2003 is still effective (Type 10) plus the three records effective on 03/03/2003)

Any help is greatly appreciated,

BrentCan you post the DDL?

But it seems like a join between the two and a GROUP by with a SUM

something like

SELECT EFF_DATE, SUM(AMT)
FROM myTable1 a myTable2 b
ON a.key = b.key
GROUP BY EFF_DATE|||Brett,
I have tried a couple variations on this theme and so far they do not deliver the desired results:

1. Select familyID, VersionTime, (Select Sum(Amt) from FamilyIncome where FamilyIncome.familyid = #changes.familyid and EFFECTIVEDATE < cast(#changes.versiontime as datetime)+1) as Income
from #changes
group by familyid, cast(versiontime as datetime),#changes.versiontime
order by familyid, versiontime desc
THIS CODE WORKS FOR THE FIRST DATE AND RETURNS THE $150.00 DESIRED, BUT ON ANY FUTURE DATES IT ADDS THE NEW INCOME AND KEEPS A RUNNING TOTAL (I.E. $550.00 INSTEAD OF $400.00)

2. Select familyID, VersionTime, (Select Sum(Amt) from FamilyIncome where FamilyIncome.familyid = #changes.familyid
HAVING EFFECTIVEDATE < cast(#changes.versiontime as datetime)+1) as Income
from #changes
group by familyid, cast(versiontime as datetime),#changes.versiontime
order by familyid, versiontime desc
THIS CODE ONLY RETURNS A RESULT SET FOR THE LAST DATE IN THE SEQUENCE AND THEN IT RETURNS $225.00 (ONLY THE RECORDS WITH A 03/03/2003) DATE)
At this time I'm just trying to isolate the effdate calculations. Also whoever reads this all the dates should be 2003 the 3003 for the year is a typo.

Thanks,

Brent
Originally posted by Brett Kaiser
Can you post the DDL?

But it seems like a join between the two and a GROUP by with a SUM

something like

SELECT EFF_DATE, SUM(AMT)
FROM myTable1 a myTable2 b
ON a.key = b.key
GROUP BY EFF_DATE|||I don't understand your results...where do these dates come from:

01/02/2003 - $150.00 (The three records effective on 01/01/2003)
02/10/2003 - $400.00 (The four records effective on 02/02/2003)
04/01/2003 - $325.00 (One record from 02/02/2003

They don't exists in your data...|||Originally posted by Brett Kaiser
I don't understand your results...where do these dates come from:

They don't exists in your data...

Those dates come out of the #changes table which I only included the fields not an example. What I have is 43,000 records in the #changes table that are familyID's and Dates on which I need to perform several calculations (size of the family, income, fee schedule, etc) right now I'm hung up on getting the income which I need in order to determine the fee (fee is based on family size and income)

Brent

Assinging a "rank" to a record

Hi all,

I don't know how to explain what I wanna do in english so here's a concrete example:

I have this data
Item - Qty
IT1 - 2
IT2 - 2
IT1 - 4
IT1 - 5
IT2 - 2

And I wanna do something like this
Item - Rank - Qty
IT1 - 1 - 2
IT1 - 2 - 4
IT1 - 3 - 5
IT2 - 1 - 2
IT2 - 2 - 2

So basically I want to assing a rank (on the fly) in a SELECT statement

Thanks in advance

Or Thowhat version of sql server?|||MS SQL SERVER 2005 SE SP2
(9.00.3054.00 SP2 Standard Edition)|||I found the way to do it:
RANK() OVER(PARTITION BY l.RealMP ORDER BY l.MP ASC, l.Color ASC, l.Thick DESC) AS RNK
But now I have an other plroblem, I need to use it in a ON statement like this:
LEFT JOIN ##tmp ON RANK() OVER(PARTITION BY l.RealMP ORDER BY l.MP ASC, l.Color ASC, l.Thick DESC)=1

But when I try to do that I get this error:
Msg 4108, Level 15, State 1, Procedure sp_RptProdLeatherPlanning, Line 35
Windowed functions can only appear in the SELECT or ORDER BY clauses.|||please show the real layouts of your tables

i don't recall seeing those column names in your first post

and what's with the join? do you just want the row with the min value?|||Actually the data comes from more than one tables

Here's my Query:

SELECT l.MP, LOWER(l.Color) AS Color, l.Thick, '' AS [ùSTOCKFCù], RANK() OVER(PARTITION BY l.RealMP ORDER BY l.MP ASC, l.Color ASC, l.Thick DESC) AS RNK, t.*, '' AS [ùSTOCKLCù], d.DEP,
COALESCE(s.OnHand,0) AS OnHand, COALESCE(s.Alloc,0) AS Alloc, COALESCE(s.BO,0) AS BO, COALESCE(s.Dispo,0) AS Dispo, COALESCE(s.Btq,0) AS Btq,
COALESCE(p.QTY,0) AS Prod, COALESCE(mb.MinBtq,0) AS MinBtq, COALESCE(sa.QTY,0) AS SalesASAT, COALESCE(s12.QTY/12,0) AS SalesLast12M,
COALESCE(ca.QTY,0) AS ConsoASAT, COALESCE(c12.QTY/12,0) AS ConsoLast12M
FROM ##ut_RptProdLeatherPlanningLeathers l
INNER JOIN ##ut_RptProdLeatherPlanningThicksPvt t ON l.RealMP=t.MP
LEFT JOIN ##ut_RptProdLeatherPlanningDEP d ON d.MP=l.RealMP
LEFT JOIN ##ut_RptProdLeatherPlanningStocks s ON s.RealMP=l.RealMP AND d.DEP=s.DEP
LEFT JOIN ##ut_RptProdLeatherPlanningProd p ON p.RealMP=l.RealMP AND p.DEP=d.DEP
LEFT JOIN ##ut_RptProdLeatherPlanningMinBtq mb ON mb.RealMP=l.RealMP AND mb.DEP=d.DEP
LEFT JOIN ##ut_RptProdLeatherPlanningSalesAsat sa ON sa.RealMP=l.RealMP AND sa.DEP=d.DEP
LEFT JOIN ##ut_RptProdLeatherPlanningSalesL12M s12 ON s12.RealMP=l.RealMP AND s12.DEP=d.DEP
LEFT JOIN ##ut_RptProdLeatherPlanningConsoASAT ca ON ca.RealMP=l.RealMP AND ca.DEP=d.DEP
LEFT JOIN ##ut_RptProdLeatherPlanningConsoL12M c12 ON c12.RealMP=l.RealMP AND c12.DEP=d.DEP
WHERE d.DEP IS NOT NULL
ORDER BY l.MP, l.Color, l.Thick DESC, d.DEP ASC

I want to use it to join ##ut_RptProdLeatherPlanningThicksPvt to ##ut_RptProdLeatherPlanningLeathers
Where RANK() OVER(PARTITION BY l.RealMP ORDER BY l.MP ASC, l.Color ASC, l.Thick DESC) is equal to 1

So... which table layout do you want me to show you?|||wow

just wow

my question is, how do you reconcile that query with the data you posted initially...

I have this data
Item - Qty
IT1 - 2
IT2 - 2
IT1 - 4
IT1 - 5
IT2 - 2

just curious, but are those all temp tables?

and what are you really trying to do with the rank number?|||I just wanted to simplify it to you...

What I wanna do with the rank is a bit tricky.
I have this MP, Color, Thick, STOCK .5, STOCK .7, STOCK .9, STOCK...

And the real data looks like this:

MP - Color - Thick - Stock.5 - Stock.7 - ...
aniline - black - 0.7 - NULL - 100.52
aniline - black - 0.7 - NULL - 100.52
aniline - black - 0.5 - 10 - NULL
aniline - black - 0.5 - 10 - NULL
soft - brown - 0.7 - NULL - 3039.42
soft - brown - 0.5 - 2039.42 - NULL
soft - brown - 0.5 - 2039.42 - NULL

And this is what I want.
MP - Color - Thick - Stock.5 - Stock.7 - ...
aniline - black - 0.7 - NULL - 100.52
aniline - black - 0.7 - NULL - NULL
aniline - black - 0.5 - 10 - NULL
aniline - black - 0.5 - NULL - NULL
soft - brown - 0.7 - NULL - 3039.42
soft - brown - 0.5 - 2039.42 - NULL
soft - brown - 0.5 - NULL - NULL

So if I add my rank it will looks like this
MP - Color - Thick - Rank - Stock.5 - Stock.7 - ...
aniline - black - 0.7 - 1 - NULL - 100.52
aniline - black - 0.7 - 2 - NULL - NULL
aniline - black - 0.5 - 1 - 10 - NULL
aniline - black - 0.5 - 2 -NULL - NULL
soft - brown - 0.7 - 1 - NULL - 3039.42
soft - brown - 0.5 - 1 - 2039.42 - NULL
soft - brown - 0.5 - 2 - NULL - NULL|||just curious, but are those all temp tables?

Exact I'm doing this before the select, to fill my temp tables

EXEC sp_RptProdLeatherPlanningLeathers
IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE type='U' AND name = '##ut_RptProdLeatherPlanningThicksPvt')
DROP TABLE ##ut_RptProdLeatherPlanningThicksPvt
EXEC sp_CrossTabIntoTable
@.select = 'SELECT MP, CAST(Thick AS varchar) AS Thick FROM ##ut_RptProdLeatherPlanningThicks GROUP BY MP, Thick',
@.sumfunc = 'SUM(PvtSum)',
@.pivot = 'Thick',
@.table = '##ut_RptProdLeatherPlanningThicks',
@.tbl_result = '##ut_RptProdLeatherPlanningThicksPvt',
@.fld_sufx = 'stk de'
EXEC sp_RptProdLeatherPlanningBOM
EXEC sp_RptProdLeatherPlanningStocks
EXEC sp_RptProdLeatherPlanningDEP
EXEC sp_RptProdLeatherPlanningProd
EXEC sp_RptProdLeatherPlanningMinBtq
EXEC sp_RptProdLeatherPlanningSales
EXEC sp_RptProdLeatherPlanningConso|||and what's with the join? do you just want the row with the min value?

No! I want all of the rows but I want no data on STOCK.* WHERE RANK>1 because the data is repeated ...|||I know this is not the best but I gonna SELECT to an OUTPUT table and do an UPDATE to have the result I need.

If someone finds a better way to do it... please tell me.

Regards

Or Tho

assigning each record to one string

hello,

i would like to loop through a record set and assign each value to the
same string, (example i would like to return all of the first name in
the authors table = Authors_total.)

should i use a cursor or just a loop to do this? I have had some
trouble with the syntax in a cursor.

nicholas.gadaczDear Nicholas,

I hope following will be help full for you.
---------------
Declare @.varstr as varchar(4000) -- declreation of
set @.varstr = ''; --initializing you know the fact Null + somhting =
Null
select @.varstr = @.varstr+','+isnull(ProductName,',') from Product;
Select @.varstr;
---------------

Best of Luck :) :) :)

Saghir Taj
MCDBA
www.dbnest.com: Home of DB Professionals.

ngadacz@.ftresearch.com wrote:
> hello,
> i would like to loop through a record set and assign each value to
the
> same string, (example i would like to return all of the first name in
> the authors table = Authors_total.)
> should i use a cursor or just a loop to do this? I have had some
> trouble with the syntax in a cursor.
> nicholas.gadacz|||Why not do that client-side? SQL isn't the best place for this kind of
presentational functionality.

--
David Portas
SQL Server MVP
--|||I am still not sure how i would loop through all of the records. if a
use a cursor i get an error variable assignment is not allowed in a
cursor declaration.

The reason why I don't put this functionality is the client side is
that I have multiple client sides: asp php and soon .aspx (.net) with
changes I want to have the code centralized.

nicholas.gadacz|||(ngadacz@.ftresearch.com) writes:
> I am still not sure how i would loop through all of the records. if a
> use a cursor i get an error variable assignment is not allowed in a
> cursor declaration.

DELARE @.str varchar(8000), @.col varchar(30)

DECLARE cur INSENSTIVE CURSOR FOR
SELECT col FROM tbl ORDER BY col
OPEN cur
WHILE 1 = 1
BEGIN
FETCH cur INTO @.col
IF @.@.fetch_status <> 0
BREAK

SELECT @.str = CASE WHEN @.str IS NULL
THEN @.col
ELSE @.str + ',' + @.col
EMD
END
DEALLOCATE cur

This is one of the few things where you must use a cursor. Another poster
showed an example with a SELECT statement. However, that is not guaranteed
to work.

> The reason why I don't put this functionality is the client side is
> that I have multiple client sides: asp php and soon .aspx (.net) with
> changes I want to have the code centralized.

Beware that the above solution has a hard limit of the output string of
8000 characters.

In SQL2005 there will actually be a way to do this in a single statement,
by some fairly funny usage of the new XML stuff.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Assigning a particular flat file record to a string variable

Hey all!

Okay, can I assign line x of a flat file to a variable, parse that line, do my data transformations, and then move on to line x+1?

Any thoughts?

In other words, I'm looking at using a for loop to cycle through a flat file. I have the for loop set up, and the counter's iterating correctly. How do I point at a particular line in a flat file?

Thanks for any suggestions!

Jim Work
You could do this in the data flow using a script component to keep track of which row you are on....|||Phil, that sounds like a great plan. I am very, very, very new at this, though, and I could use a little more detail?

Going through a flat file line-by-line seems like it would be a very straightforward thing to me...
|||The data flow on its own operates on a row by row basis. That's what it's designed to do, and very fast, by the way.|||Oh, you're kidding me!

Thanks!!

ASP: error when trying to insert record

I receive the following error when trying to INSERT INTO; am using Access db.

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

The code is as follows:

<%
Dim dbConn
set dbConn = server.CreateObject("adodb.connection")
dbConn.open("connect")
dbConn.Execute("INSERT INTO tbl_country (region, countryName, population, country_currency, description, imageURL, imageALT) VALUES ('" & Request.Form("region") & "', '" & Request.Form("countryName") & "', '" & Request.Form("population") & "', '" & Request.Form("country_currency") & "', '" & Request.Form("description") & "', '" & Request.Form("imageURL") & "', '" & Request.Form("imageALT") & "'),")
Response.Redirect("admin_master.asp")
%>

I would greatly appreciate any help you can give me.

Judging by the error message, you are likely trying to insert the wrong datatype into one of the columns..population maybe? I'd have to see the table definition to know for sure..|||

Please verify the Numeric data typed column's values on your query (Request.Form values). If the user given blank input then your query generate the blank string ('') & it will try to insert in the respective column which will throw error.

It always good idea to use the Stored Procedure or Parameterized query. Your code is violating the security standards it will allow the sql injection. Beware of SQL Injection.. Never use Dynamic Queries on your UI

|||+1 against dynamic sql and for paramtrized queries. If you are not sure which statement is executed against the database, put the whole build string into a string variable and output it to the Response stream, you will be easily able to find the error then. In most cases of dynamic SQL and the situations I have seen such implementation like you posted, the problem was based upon a wrong order of the parameters / columns.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
--