Collation problem: record

Showing posts with label record. Show all posts

Thursday, March 29, 2012

Combining the results of a cursor loop

Need a little help here.

I have a set of product ids fed in as a delimited string and for each I need to extract the top 1 record from another query based on the id.

I need the results as one table.

Here is my code.
___________________________________
SET NOCOUNT ON

DECLARE @.IdsString VARCHAR(255), @.Id int

SELECT @.IdsString = '918|808|1214|89|995|300|526|1207'

DECLARE GetData CURSOR
FOR Select s.ProductID FROM dbo.SplitProductIDs(@.IdsString) as s

OPEN GetData

FETCH NEXT FROM GetData
INTO @.Id

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT TOP 1 v.*
FROM dbo.GetProductRateView as v
WHERE v.[id] = @.Id

FETCH NEXT FROM GetData
INTO @.Id
END

CLOSE GetData
DEALLOCATE GetData
_____________________________________

Do I need to create a temp table and do an 'Insert Into(Select...' with each cusor result or is there a better way?

Any help would be much appreciated.

NB Database was not designed and the client will not tolerate any changes to structure of the tables :eek:

Regards

Shaun McGuileSET NOCOUNT ON
CREATE TABLE #CurrentRates
(
AccountType VARCHAR(50),
EffectiveDate DATETIME,
tier INT,
gross FLOAT,
net FLOAT,
aer FLOAT,
footnotes VARCHAR(2000),
[id] INT
)
GO

DECLARE @.IdsString VARCHAR(255), @.Id int

SELECT @.IdsString = '918|808|1214|89|995|300|526|1207'

DECLARE GetData CURSOR
FOR Select s.ProductID FROM dbo.SplitProductIDs(@.IdsString) as s

OPEN GetData

FETCH NEXT FROM GetData
INTO @.Id

WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT #CurrentRates
SELECT TOP 1 v.*
FROM dbo.GetProductRateView as v
WHERE v.[id] = @.Id

FETCH NEXT FROM GetData
INTO @.Id
END

CLOSE GetData
DEALLOCATE GetData

SELECT * FROM #CurrentRates

Works, but is it good? ;)

Regards

Shaun McGuile|||Dump the cursor and use a split function so you can do this in a set based fashion.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&whichpage=2
See if you can get your string into a table of rows and then we can move on.

BTW - have you changed your handle? What was it before?|||Works, but is it good? ;) It uses cursors :o|||Dump the cursor and use a split function so you can do this in a set based fashion.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&whichpage=2
See if you can get your string into a table of rows and then we can move on.

BTW - have you changed your handle? What was it before?

The line

...Select s.ProductID FROM dbo.SplitProductIDs(@.IdsString) as s...

Splits the ids into a table of column product id.

Have I understood your question?

Regards|||I have and always will be the one and only Shaun McGuile ;)

Lost my dbforums password/email combination somehow.|||Forgot to add the 'drop table #CurrentRates' at the end of the code

Doh!|||Beg your pardon - I thought you were parsing as a string.

Ok - what's the pk of dbo.GetProductRateView?|||pk ha ha ha ha ha ha ha - er..sorry Pootle you had me there.

The db has no pk's nor integrity of any type its real bad

dbo.GetProductRateView is a View pulling data from three non normalised tables its really evil - your heart and that of other members of the community might not take the shock of seeing them.

Its like 'The Ring' of databases (like the film - you see it then you die) lol.|||Well brace yourself

SELECT TOP 100 PERCENT dbo.saving_product.name AS AccountType, dbo.saving_product_variant.from_date AS EffectiveDate,
dbo.saving_product_variant.tier, dbo.saving_product_variant.gross, dbo.saving_product_variant.net, dbo.saving_product_variant.aer,
dbo.saving_date.footnotes, dbo.saving_product.id
FROM dbo.saving_product INNER JOIN
dbo.saving_product_variant ON dbo.saving_product.id = dbo.saving_product_variant.link_id INNER JOIN
dbo.saving_date ON dbo.saving_product.id = dbo.saving_date.link_id
GROUP BY dbo.saving_product.name, dbo.saving_product_variant.from_date, dbo.saving_product_variant.tier, dbo.saving_product_variant.gross,
dbo.saving_product_variant.net, dbo.saving_product_variant.aer, dbo.saving_date.footnotes, dbo.saving_product.id
ORDER BY MAX(dbo.saving_product_variant.date_id) DESC|||date_id? :S

Are you getting the most recent row based on the value date_id? If so then you should know that order by clauses are not guaranteed to work in views. Better to create a view with no order by clause and order it when required.

From BoL:
The ORDER BY clause is used only to determine the rows that are returned by the TOP clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself.|||What version are you running BTW?|||INSERT #CurrentRates
SELECT TOP 1 v.*
FROM ( SELECT * FROM dbo.GetProductRateView Order By date_id desc) as v
WHERE v.[id] = @.Id

and remove the order by clause from the view?

Regards

Shaun McGuile|||You can do - no need for the inner query BTW. I'm thinking more than that though.

What version are you running?|||SQLServer 2000 is the db.|||Heh - turns out I didn't need it - apols.

SELECT v.*
FROM dbo.GetProductRateView as v
INNER JOIN--"Last" date per product.
(SELECT dbo.saving_product.id
, MAX(dbo.saving_product_variant.date_id) AS last_date_id
FROM dbo.saving_product
INNER JOIN
dbo.saving_product_variant
ON dbo.saving_product.id = dbo.saving_product_variant.link_id
INNER JOIN
(SELECT *
FROM dbo.split_function(@.IdsString)) AS ids
ON ids.Value = dbo.saving_product.id
GROUP BY dbo.saving_product.id) AS last_prods
ON last_prods.id = v.id
AND last_prods.last_date_id = v.date_id
How is that for the data?|||I'll give it a go and let you know.

Cheers Pootle.

Haven't looked in on Yak Coral in ages. Might do it today if I get time.|||Yeah that works splen-diddly (its how you say it out loud that gets ya).

Only modifictions I had to make were field name for the productID instead of value, altered the view to return date_id field and a DISTINCT is needed as in
SELECT DISTINCT v.* ...

Bril thats my homework done! On with the next assignment!

lol only joking! I dont do homework!

Cheers Pootle

Shaun McGuile|||Kills the cursor/temp table method on speed

Virtually instant vs 2 - 3 seconds

Amazing!|||Kills the cursor/temp table method on speed

Virtually instant vs 2 - 3 secondsThat's set based programming for you. The other thing to remember is that speed of the cursor will be linear. Each additional iteration will take ~ as long as the last. Set based stuff mitigates against changes in scale much better.

Tuesday, March 27, 2012

Combining record sets from two tables

Friends,
Can anyone give me the best way to solve the following challenge.
I have two tables in my MS SQLserver database. Table A has this dataset:
code_id code operations_descr
1 M Mob/Demob
2 T Transit
3 O Operation
4 S Standby
5 W WOW
6 C Crew Change
7 B Breakdown
8 R Maintenance
Table B has this dataset:
code_id operations_duration
3 125
3 10
2 1304
I want a new recordset where the sum of the operations_duration from table B
grouped on code_id is combined with the right code_id in table A, like this:
code_id code operations_descr total_operations_duration
1 M Mob/Demob 0
2 T Transit 1304
3 O Operation 135
4 S Standby 0
5 W WOW 0
6 C Crew Change 0
7 B Breakdown 0
8 R Maintenance 0
Thank you for any assistance!
/Leif S
--
Systems AnalystI've assumed that the first table is called "Operations" and that the second
one is called "OperationsDuration". Here is the query:
Select O.Code_ID, O.Code, O.Operations_Descr,
Sum(OD.Total_Operations_Duration)
From Operations O, OperationsDuration OD
Where O.Code_ID = OD.Code_ID
Group By O.Code_ID, O.Codel O.Operations_Descr
Best regards,
Sami Samir
ITWorx
"Leif S" wrote:

> Friends,
> Can anyone give me the best way to solve the following challenge.
> I have two tables in my MS SQLserver database. Table A has this dataset:
> code_id code operations_descr
> 1 M Mob/Demob
> 2 T Transit
> 3 O Operation
> 4 S Standby
> 5 W WOW
> 6 C Crew Change
> 7 B Breakdown
> 8 R Maintenance
> Table B has this dataset:
> code_id operations_duration
> 3 125
> 3 10
> 2 1304
> I want a new recordset where the sum of the operations_duration from table
B
> grouped on code_id is combined with the right code_id in table A, like thi
s:
> code_id code operations_descr total_operations_duration
> 1 M Mob/Demob 0
> 2 T Transit 1304
> 3 O Operation 135
> 4 S Standby 0
> 5 W WOW 0
> 6 C Crew Change 0
> 7 B Breakdown 0
> 8 R Maintenance 0
> Thank you for any assistance!
> /Leif S
> --
> Systems Analyst|||Hi
Select O.Code_ID, O.Code, O.Operations_Descr,
Sum(ISNULL(OD.Total_Operations_Duration,0)) as Total_Operations_Duration
From [Table A] O
LEFT JOIN [Table B] OD ON O.Code_ID = OD.Code_ID
Group By O.Code_ID, O.Codel O.Operations_Descr
John
"Leif S" wrote:

> Friends,
> Can anyone give me the best way to solve the following challenge.
> I have two tables in my MS SQLserver database. Table A has this dataset:
> code_id code operations_descr
> 1 M Mob/Demob
> 2 T Transit
> 3 O Operation
> 4 S Standby
> 5 W WOW
> 6 C Crew Change
> 7 B Breakdown
> 8 R Maintenance
> Table B has this dataset:
> code_id operations_duration
> 3 125
> 3 10
> 2 1304
> I want a new recordset where the sum of the operations_duration from table
B
> grouped on code_id is combined with the right code_id in table A, like thi
s:
> code_id code operations_descr total_operations_duration
> 1 M Mob/Demob 0
> 2 T Transit 1304
> 3 O Operation 135
> 4 S Standby 0
> 5 W WOW 0
> 6 C Crew Change 0
> 7 B Breakdown 0
> 8 R Maintenance 0
> Thank you for any assistance!
> /Leif S
> --
> Systems Analyst|||Hi John,
The code you gave me works well. But it is more to it.
The data is collected from a survey vessel working in the North Sea and the
tables contains data from daily reports sent from the vessel. Table B stores
operations duration for each day where records from a given day share the
same report_id. When I take your code and add: "Where OD.report_id =
@.reportId" and supply report_id = 2, the result set exclude code lines from
table A that are not in table B like this:
code_id code operations_descr Total_Oper
ations_Duration
2 T Transit 1304
3 O Operation 135
Your advice is appreciated!
/Leif S.
--
Systems Analyst
"John Bell" wrote:
> Hi
> Select O.Code_ID, O.Code, O.Operations_Descr,
> Sum(ISNULL(OD.Total_Operations_Duration,0)) as Total_Operations_Duration
> From [Table A] O
> LEFT JOIN [Table B] OD ON O.Code_ID = OD.Code_ID
> Group By O.Code_ID, O.Codel O.Operations_Descr
> John
> "Leif S" wrote:
>|||See if this works...
SELECT code_id, code, operations_descr,
operations_duration = (SELECT SUM(total_operations_duration)
FROM TABLEB
WHERE code_id = TABLEA.code_id
GROUP BY code_id
)
FROM TABLEA
"Leif S" <LeifS@.discussions.microsoft.com> wrote in message
news:5E01F636-997E-47A6-A844-206B6DBBE0CA@.microsoft.com...
> Friends,
> Can anyone give me the best way to solve the following challenge.
> I have two tables in my MS SQLserver database. Table A has this dataset:
> code_id code operations_descr
> 1 M Mob/Demob
> 2 T Transit
> 3 O Operation
> 4 S Standby
> 5 W WOW
> 6 C Crew Change
> 7 B Breakdown
> 8 R Maintenance
> Table B has this dataset:
> code_id operations_duration
> 3 125
> 3 10
> 2 1304
> I want a new recordset where the sum of the operations_duration from table
> B
> grouped on code_id is combined with the right code_id in table A, like
> this:
> code_id code operations_descr total_operations_duration
> 1 M Mob/Demob 0
> 2 T Transit 1304
> 3 O Operation 135
> 4 S Standby 0
> 5 W WOW 0
> 6 C Crew Change 0
> 7 B Breakdown 0
> 8 R Maintenance 0
> Thank you for any assistance!
> /Leif S
> --
> Systems Analyst|||Hi
I am not sure where report_id comes from as it was not mentioned in the
original post. You may want to check out
http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and example
data.
Try:
Select O.Code_ID, O.Code, O.Operations_Descr,
Sum(ISNULL(OD.Total_Operations_Duration,0)) as Total_Operations_Duration
From [Table A] O
LEFT JOIN [Table B] OD ON O.Code_ID = OD.Code_ID AND OD.report_id = @.reporti
d
Group By O.Code_ID, O.Codel O.Operations_Descr
John
"Leif S" wrote:
> Hi John,
> The code you gave me works well. But it is more to it.
> The data is collected from a survey vessel working in the North Sea and th
e
> tables contains data from daily reports sent from the vessel. Table B stor
es
> operations duration for each day where records from a given day share the
> same report_id. When I take your code and add: "Where OD.report_id =
> @.reportId" and supply report_id = 2, the result set exclude code lines fro
m
> table A that are not in table B like this:
> code_id code operations_descr Total_Oper
ations_Duration
> 2 T Transit 1304
> 3 O Operation 135
> Your advice is appreciated!
> /Leif S.
> --
> Systems Analyst
>
> "John Bell" wrote:
>|||Thanks, John. So easy!
When I moved the report_id qualifyer away from the "Where" clause and into
the Join statement I got what I wanted.
/Leif S.
--
Systems Analyst
"John Bell" wrote:
> Hi
> I am not sure where report_id comes from as it was not mentioned in the
> original post. You may want to check out
> http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and example
> data.
> Try:
> Select O.Code_ID, O.Code, O.Operations_Descr,
> Sum(ISNULL(OD.Total_Operations_Duration,0)) as Total_Operations_Duration
> From [Table A] O
> LEFT JOIN [Table B] OD ON O.Code_ID = OD.Code_ID AND OD.report_id = @.repor
tid
> Group By O.Code_ID, O.Codel O.Operations_Descr
> John
> "Leif S" wrote:
>

Thursday, March 22, 2012

combine values in view?

Is there an easy way to do this?
I need to pull sales values for each store and make one record per month.
This used to be easy, as I was provided only one sales record per month per
store, but now I've been told that SOME of the stores have a "secondary" ID
from which to pull sales numbers, and these numbers need to be added to the
number from their PRIMARY number before displaying them. I'm not sure how
to do this.
Here is some sample data (cols don't align well here - sorry):
STORES:
ID1 ID2 STORE
100 AAA A-STORE
200 B-STORE
300 CCC C-STORE
400 D-STORE
(note that B-Store and D-Store have only ONE ID, not a secondary ID)
SALES:
ID SALES MONTH YEAR
100 5.00 01 2004
AAA 5.00 01 2004
200 5.00 01 2004
300 5.00 01 2004
CCC 5.00 01 2004
400 5.00 01 2004
(note that sales values from IDs "100" and "AAA" both belong to "A-Store",
and "300" and "CCC" belong to "C-Store"; also that there is a set of sales
records for each month & year so those fields need to be accounted for.)
I need a view that will show:
ID SALES MONTH YEAR
100 10.00 01 2004
200 5.00 01 2004
300 10.00 01 2004
400 5.00 01 2004
(note that IDs 100 and 300 show the combined sales of both their primary AND
secondary IDs)
Is this easily doable? If yes, how'!!
Thanks!
-RThere are some data integrity problems here, and in particular,
if the ID2 values are not unique, this is something of a mess, but
if the data is not messed up, something like this should work:
select ID, sum(SALES) as SALES, [MONTH], [YEAR]
from (
select ID, SALES, [MONTH], [YEAR]
from SALES
union all
select ST.ID1, SA.SALES, SA.[MONTH], SA.[YEAR]
from SALES AS SA join STORES AS ST
on SA.ID = ST.ID2
) S
group by ID, [MONTH], [YEAR]
If the data is messed up, this query could provide completely
wrong information. You would be better off keeping track
of the store IDs differently:
-- primary IDs only, with store attributes
CREATE TABLE STORES (
ID char(3) primary key,
StoreName varchar(30),
.. other attributes of a store
)
-- all IDs, primary and alternate, for stores, with
-- the primary storeID for each
CREATE TABLE STORE_IDS (
ID char(3) primary key,
storeID char(3) references STORES(ID)
-- put an index on storeID to support the FK
)
The foreign key on SALES would now link to this
second table instead of the first. The query would
be different, too - something like this:
select ST.ID, sum(SA.SALES) as SALES, SA.[MONTH], SA.[YEAR]
from STORES as ST
join STORE_IDS as I
on I.storeID = ST.ID
join SALES as SA
on SA.ID = I.ID
group by ST.ID, SA.[MONTH], SA.[YEAR]
Steve Kass
Drew University
r wrote:

>Is there an easy way to do this?
>I need to pull sales values for each store and make one record per month.
>This used to be easy, as I was provided only one sales record per month per
>store, but now I've been told that SOME of the stores have a "secondary" ID
>from which to pull sales numbers, and these numbers need to be added to the
>number from their PRIMARY number before displaying them. I'm not sure how
>to do this.
>Here is some sample data (cols don't align well here - sorry):
>STORES:
>ID1 ID2 STORE
>100 AAA A-STORE
>200 B-STORE
>300 CCC C-STORE
>400 D-STORE
>(note that B-Store and D-Store have only ONE ID, not a secondary ID)
>SALES:
>ID SALES MONTH YEAR
>100 5.00 01 2004
>AAA 5.00 01 2004
>200 5.00 01 2004
>300 5.00 01 2004
>CCC 5.00 01 2004
>400 5.00 01 2004
>(note that sales values from IDs "100" and "AAA" both belong to "A-Store",
>and "300" and "CCC" belong to "C-Store"; also that there is a set of sales
>records for each month & year so those fields need to be accounted for.)
>I need a view that will show:
>ID SALES MONTH YEAR
>100 10.00 01 2004
>200 5.00 01 2004
>300 10.00 01 2004
>400 5.00 01 2004
>(note that IDs 100 and 300 show the combined sales of both their primary AN
D
>secondary IDs)
>Is this easily doable? If yes, how'!!
>Thanks!
>-R
>
>
>sql sql

Tuesday, March 20, 2012

combine table data fields from two records into a record

Dear helper,

I have the question of T-SQL.

I have a table original is:

and want to use Sql to make it becomes:

Concrete_Grade Mix_Code RM_ID RM_Name RM_Value UnitType_ID RMTypeType_Name RMType_Name
10P/20 10P kfdn_100 KFDN-100 2.24 kg Set Retarding Admixture
10P/20 10P kfdn_100 KFDN-100 2.24 kg Water-reducing Admixture
10P/20 10P kfdn_100 KFDN-100 1.95 lit Set Retarding Admixture
10P/20 10P kfdn_100 KFDN-100 1.95 lit Water-reducing Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.93 kg Set Retarding Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.93 kg Water-reducing Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.76 lit Set Retarding Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.76 lit Water-reducing Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.43 kg Set Retarding Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.43 kg Water-reducing Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.21 lit Set Retarding Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.21 lit Water-reducing Admixture
10P/20 10PC kfdn_100 KFDN-100 2.33 kg Set Retarding Admixture
10P/20 10PC kfdn_100 KFDN-100 2.33 kg Water-reducing Admixture
10P/20 10PC kfdn_100 KFDN-100 2.03 lit Set Retarding Admixture
10P/20 10PC kfdn_100 KFDN-100 2.03 lit Water-reducing Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.59 kg Set Retarding Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.59 kg Water-reducing Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.25 lit Set Retarding Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.25 lit Water-reducing Admixture

It is better to make it becomes a view for table joining.

Concrete_Grade Mix_Code RM_ID RM_Name RM_Value UnitType_ID RMTypeType_Name RMType_Name
10P/20 10P kfdn_100 KFDN-100 2.24 kg Set Retarding, Water-reducing Admixture
10P/20 10P kfdn_100 KFDN-100 1.95 lit Set Retarding, Water-reducing Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.93 kg Set Retarding, Water-reducing Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.76 lit Set Retarding, Water-reducing Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.43 kg Set Retarding, Water-reducing Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.21 lit Set Retarding, Water-reducing Admixture
10P/20 10PC kfdn_100 KFDN-100 2.33 kg Set Retarding, Water-reducing Admixture
10P/20 10PC kfdn_100 KFDN-100 2.03 lit Set Retarding, Water-reducing Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.59 kg Set Retarding, Water-reducing Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.25 lit Set Retarding, Water-reducing Admixture

Regards,

Man Pak Hong, Dave

try this..

SELECT a.Concrete_Grade
, a.Mix_Code
, a.RM_ID
, a.RM_Name
, a.RM_Value
, a.UnitType_ID
, a.RMTypeType_Name
, b.RMType_Name
FROM YourTable a INNER JOIN
YourTable b ON a.Concrete_Grade = b.Concrete_Grade
AND a.Mix_Code = b.Mix_Code
AND a.RM_ID = b.RM_ID
AND a.RM_Name = b.RM_Name
AND a.RM_Value = b.RM_Value
AND a.UnitType_ID = b.UnitType_ID|||

So your point is creating view which holds the values [RMTypeType_Name]='Water-reducing', lets say vw_MyData_WaterReducing. Later you want to join this view with outher tables on your query.

One way it is good if you use INDEXED VIEW. You have to create a index on this new view. It will increase the performance well.

But if you try to use with out index (only the filtered query), it may decrease the performance. You may unknowingly use Self join on your query...

To know better abotu indexed view visit here ... http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

Combine record?

I want a string that consist full account number as follow:
account_num = 300.111000.10
But table only consist of the following:
-gbmcu : 300
-gbobj : 111000
-gbsub : 10
So how to combine gbmcu,gbobj and gbsub into account_num?
I had tried this T-SQL but fail :
update Bank_Account
set account_num = ltrim(gbmcu)+'.'+ltrim(gbobj)+'.'+ltrim(gbsub)
go
Error Message
--
Server: Msg 8152, Level 16, State 4, Line 2
String or binary data would be truncated.
The statement has been terminated.
Please help.Sam wrote:
> I want a string that consist full account number as follow:
> account_num = 300.111000.10
> But table only consist of the following:
> -gbmcu : 300
> -gbobj : 111000
> -gbsub : 10
> So how to combine gbmcu,gbobj and gbsub into account_num?
> I had tried this T-SQL but fail :
> update Bank_Account
> set account_num = ltrim(gbmcu)+'.'+ltrim(gbobj)+'.'+ltrim(gbsub)
> go
> Error Message
> --
> Server: Msg 8152, Level 16, State 4, Line 2
> String or binary data would be truncated.
> The statement has been terminated.
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
You don't indicate the data types for the columns gbmcu, gbobj & gbsub.
If they are numeric you'll have to convert them to varchar:
set account_num = cast(gbmcu as varchar) + '.'
+ cast(gbobj as varchar) + '.'
+ cast(gbsub as varchar)
This assumes that columns gbmcu, gbobj & gbsub are in the table being
updated. It also assumes that column account_num is a varchar data type
column.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQhqwS4echKqOuFEgEQJMPACfSUlNvfxcbuSt
iWy8RFVdCqRy43sAnj/Z
vlg9s7fQcepcdpIQ8bu8Bi1o
=LMc6
--END PGP SIGNATURE--|||The problem could be that your account_number definition is too short to
hold the resulting string. Else, yours and MGFoster's solution should work.
-oj
"Sam" <cybersam88@.hotmail.com> wrote in message
news:%23N4UkqIGFHA.3732@.TK2MSFTNGP14.phx.gbl...
>I want a string that consist full account number as follow:
> account_num = 300.111000.10
> But table only consist of the following:
> -gbmcu : 300
> -gbobj : 111000
> -gbsub : 10
> So how to combine gbmcu,gbobj and gbsub into account_num?
> I had tried this T-SQL but fail :
> update Bank_Account
> set account_num = ltrim(gbmcu)+'.'+ltrim(gbobj)+'.'+ltrim(gbsub)
> go
> Error Message
> --
> Server: Msg 8152, Level 16, State 4, Line 2
> String or binary data would be truncated.
> The statement has been terminated.
> Please help.
>

Combine record

Hi guys..
is there any query to do this action:
i want to combine view record into a single record.
exm.

table 1
Name A B
Jack 10 22
jack 12 21
jack ... ...
jack 1 11
ben 12 2
ben 3 2
ben ... ...

into:
View 1
Name combine
jack 10,22 and 12,21and1,11 and ....
ben 12,2 and 3,2 and.....

thx before..dede (neolempires2@.gmail.com) writes:

Quote:

Originally Posted by

is there any query to do this action:
i want to combine view record into a single record.
exm.
>
table 1
Name A B
Jack 10 22
jack 12 21
jack ... ...
jack 1 11
ben 12 2
ben 3 2
ben ... ...
>
>
into:
View 1
Name combine
jack 10,22 and 12,21and1,11 and ....
ben 12,2 and 3,2 and.....

Check out http://www.projectdmx.com/tsql/rowconcatenate.aspx for
suggestions.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql sql

Combine Multiple Results into 1 RecordSet

Hello All
I have the following SPROC Below which I want to return the results of the 3
Querries in a single record Set which I can use in my webapp
/ ****************************************
****************
CREATE PROCEDURE dbo.sp_StatsSQLVersionCount
AS
SELECT Count(*) As Total FROM vSQLInv_VersionString
SELECT Count(*) As Vulnerable FROM vSQLInv_VersionString
WHERE Status = 'Vulnerable' OR Status = 'EOF'
SELECT Count(*) As Valid FROM vSQLInv_VersionString
WHERE Status <> 'Vulnerable'
GO
****************************************
***************/
-- Desired Results --
Total Vulnerable Valid
80 5 75
Thanks
StuartSELECT Count(*) As Total,
SUM(CASE WHEN Status = 'Vulnerable' OR Status = 'EOF' THEN 1 ELSE 0 END)
As Vulnerable ,
SUM(CASE WHEN Status <> 'Vulnerable' THEN 1 ELSE 0 END) As Valid
FROM vSQLInv_VersionString
Jacco Schalkwijk
SQL Server MVP
"Stuart Shay" <sshay@.j51.com> wrote in message
news:umqEkOZMFHA.2384@.tk2msftngp13.phx.gbl...
> Hello All
> I have the following SPROC Below which I want to return the results of the
> 3 Querries in a single record Set which I can use in my webapp
> / ****************************************
****************
> CREATE PROCEDURE dbo.sp_StatsSQLVersionCount
> AS
> SELECT Count(*) As Total FROM vSQLInv_VersionString
> SELECT Count(*) As Vulnerable FROM vSQLInv_VersionString
> WHERE Status = 'Vulnerable' OR Status = 'EOF'
> SELECT Count(*) As Valid FROM vSQLInv_VersionString
> WHERE Status <> 'Vulnerable'
> GO
> ****************************************
***************/
> -- Desired Results --
> Total Vulnerable Valid
> 80 5 75
> Thanks
> Stuart
>|||SELECT
(
SELECT Count(*) FROM vSQLInv_VersionString
) AS Total ,
(
SELECT Count(*) FROM vSQLInv_VersionString
WHERE Status = 'Vulnerable' OR Status = 'EOF'
) As Vulnerable ,
(
SELECT Count(*) FROM vSQLInv_VersionString
WHERE Status <> 'Vulnerable'
) As Valid
FROM
vSQLInv_VersionString
Cheers,
Greg Jackson
PDX, Oregon|||SELECT Count(*) As Total,
Sum(Case WHen Status In ('Vulnerable', 'EOF') Then 1 End) as Vulnerable,
Sum(Case WHen Status <> 'Vulnerable' Then 1 End) as Valid
FROM vSQLInv_VersionString
"Stuart Shay" wrote:

> Hello All
> I have the following SPROC Below which I want to return the results of the
3
> Querries in a single record Set which I can use in my webapp
> / ****************************************
****************
> CREATE PROCEDURE dbo.sp_StatsSQLVersionCount
> AS
> SELECT Count(*) As Total FROM vSQLInv_VersionString
> SELECT Count(*) As Vulnerable FROM vSQLInv_VersionString
> WHERE Status = 'Vulnerable' OR Status = 'EOF'
> SELECT Count(*) As Valid FROM vSQLInv_VersionString
> WHERE Status <> 'Vulnerable'
> GO
> ****************************************
***************/
> -- Desired Results --
> Total Vulnerable Valid
> 80 5 75
> Thanks
> Stuart
>
>|||Thanks & Have A GREAT Day !!!!!
Stuart
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:41A33FEB-3805-411F-A03D-459849349C05@.microsoft.com...
> SELECT Count(*) As Total,
> Sum(Case WHen Status In ('Vulnerable', 'EOF') Then 1 End) as
> Vulnerable,
> Sum(Case WHen Status <> 'Vulnerable' Then 1 End) as Valid
> FROM vSQLInv_VersionString
>
> "Stuart Shay" wrote:
>

Combine multiple lines for one record into one comma-delimted field

Here is a query I have: It's long, so bear with me.
Scroll to the bottom to see what question I have, please.
This is a SQL 2003 db. In the select statement I am only asking for 2
fields which in reality, I am asking for about 40 (hence the long FROM
section)
SELECT v_basic_booking_data_ODBC.ptt_last_name,
dbo.v_basic_case_rec_data.cr_urn, dbo.v_CRA_cpt_code.cpt_code
FROM dbo.v_basic_case_rec_data INNER JOIN
dbo.v_CRA_BO_01_Journaling_Data
v_CRA_BO_01_Journaling_Data ON
dbo.v_basic_case_rec_data.cr_urn =
v_CRA_BO_01_Journaling_Data.cr_urn LEFT OUTER JOIN
dbo.v_CRA_cpt_code ON
dbo.v_basic_case_rec_data.cr_urn = dbo.v_CRA_cpt_code.cr_urn RIGHT OUTER
JOIN
reportuser.v_BKA_02_Booking_Data
v_BKA_02_Booking_Data INNER JOIN
dbo.v_basic_booking_data_ODBC
v_basic_booking_data_ODBC ON
v_BKA_02_Booking_Data.book_urn =
v_basic_booking_data_ODBC.book_urn INNER JOIN
dbo.v_Surg_Proc_BK_ODBC v_Surg_Proc_BK_ODBC ON
v_BKA_02_Booking_Data.book_urn = v_Surg_Proc_BK_ODBC.book_urn INNER JOIN
dbo.v_PTA_01_Patient_Data v_PTA_01_Patient_Data ON
v_basic_booking_data_ODBC.ptt_urn = v_PTA_01_Patient_Data.ptt_urn INNER
JOIN
dbo.book_audref book_audref ON
v_basic_booking_data_ODBC.book_urn = book_audref.or_book_urn ON
v_CRA_BO_01_Journaling_Data.cr_urn =
book_audref.cr_urn
WHERE (v_basic_booking_data_ODBC.book_date >= CONVERT(DATETIME,
'2006-03-10 00:00:00', 102)) AND
(v_basic_booking_data_ODBC.book_date <
CONVERT(DATETIME, '2006-03-16 00:00:00', 102))
ORDER BY v_basic_booking_data_ODBC.room_descr,
v_basic_booking_data_ODBC.book_date, v_basic_booking_data_ODBC.room_mnc
I get results like
lname cptcode
DOE 1111111
DOE 4343445
SMITH 5456544
RALF 4789008
what I want is for Doe's line to read
DOE 111111,4343445
How can I do this with my extensive query above?
Thank you.
*** Sent via Developersdex http://www.examnotes.net ***Joe , since you have mot posetd DDL+ sample data I did some testing on my
own. Just be aware that the below approach isn't reliable and shoud be
avoided ,instead do such reports on the client side
create table w
(
id int not null,
t varchar(50) not null
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+t+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"Joey Martin" <joey@.kytechs.com> wrote in message
news:uwKf88$UGHA.2444@.TK2MSFTNGP14.phx.gbl...
> Here is a query I have: It's long, so bear with me.
> Scroll to the bottom to see what question I have, please.
> This is a SQL 2003 db. In the select statement I am only asking for 2
> fields which in reality, I am asking for about 40 (hence the long FROM
> section)
> SELECT v_basic_booking_data_ODBC.ptt_last_name,
> dbo.v_basic_case_rec_data.cr_urn, dbo.v_CRA_cpt_code.cpt_code
> FROM dbo.v_basic_case_rec_data INNER JOIN
> dbo.v_CRA_BO_01_Journaling_Data
> v_CRA_BO_01_Journaling_Data ON
> dbo.v_basic_case_rec_data.cr_urn =
> v_CRA_BO_01_Journaling_Data.cr_urn LEFT OUTER JOIN
> dbo.v_CRA_cpt_code ON
> dbo.v_basic_case_rec_data.cr_urn = dbo.v_CRA_cpt_code.cr_urn RIGHT OUTER
> JOIN
> reportuser.v_BKA_02_Booking_Data
> v_BKA_02_Booking_Data INNER JOIN
> dbo.v_basic_booking_data_ODBC
> v_basic_booking_data_ODBC ON
> v_BKA_02_Booking_Data.book_urn =
> v_basic_booking_data_ODBC.book_urn INNER JOIN
> dbo.v_Surg_Proc_BK_ODBC v_Surg_Proc_BK_ODBC ON
> v_BKA_02_Booking_Data.book_urn = v_Surg_Proc_BK_ODBC.book_urn INNER JOIN
> dbo.v_PTA_01_Patient_Data v_PTA_01_Patient_Data ON
> v_basic_booking_data_ODBC.ptt_urn = v_PTA_01_Patient_Data.ptt_urn INNER
> JOIN
> dbo.book_audref book_audref ON
> v_basic_booking_data_ODBC.book_urn = book_audref.or_book_urn ON
> v_CRA_BO_01_Journaling_Data.cr_urn =
> book_audref.cr_urn
> WHERE (v_basic_booking_data_ODBC.book_date >= CONVERT(DATETIME,
> '2006-03-10 00:00:00', 102)) AND
> (v_basic_booking_data_ODBC.book_date <
> CONVERT(DATETIME, '2006-03-16 00:00:00', 102))
> ORDER BY v_basic_booking_data_ODBC.room_descr,
> v_basic_booking_data_ODBC.book_date, v_basic_booking_data_ODBC.room_mnc
>
> I get results like
> lname cptcode
> DOE 1111111
> DOE 4343445
> SMITH 5456544
> RALF 4789008
> what I want is for Doe's line to read
> DOE 111111,4343445
> How can I do this with my extensive query above?
> Thank you.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||In my experience of trying it, dynamic columns in SQL is horrible, and
I've only achieved it with some messy Dynamic SQL. What are you
outputting to? if it's going out to a web app or something then I'd
suggest using the c# (or whatever language) to clean up your result set
there. SQL is more geared to obtaining rather than formatting data.
Cheers
Will|||http://www.aspfaq.com/2529
"Joey Martin" <joey@.kytechs.com> wrote in message
news:uwKf88$UGHA.2444@.TK2MSFTNGP14.phx.gbl...
> Here is a query I have: It's long, so bear with me.
> Scroll to the bottom to see what question I have, please.
> This is a SQL 2003 db. In the select statement I am only asking for 2
> fields which in reality, I am asking for about 40 (hence the long FROM
> section)
> SELECT v_basic_booking_data_ODBC.ptt_last_name,
> dbo.v_basic_case_rec_data.cr_urn, dbo.v_CRA_cpt_code.cpt_code
> FROM dbo.v_basic_case_rec_data INNER JOIN
> dbo.v_CRA_BO_01_Journaling_Data
> v_CRA_BO_01_Journaling_Data ON
> dbo.v_basic_case_rec_data.cr_urn =
> v_CRA_BO_01_Journaling_Data.cr_urn LEFT OUTER JOIN
> dbo.v_CRA_cpt_code ON
> dbo.v_basic_case_rec_data.cr_urn = dbo.v_CRA_cpt_code.cr_urn RIGHT OUTER
> JOIN
> reportuser.v_BKA_02_Booking_Data
> v_BKA_02_Booking_Data INNER JOIN
> dbo.v_basic_booking_data_ODBC
> v_basic_booking_data_ODBC ON
> v_BKA_02_Booking_Data.book_urn =
> v_basic_booking_data_ODBC.book_urn INNER JOIN
> dbo.v_Surg_Proc_BK_ODBC v_Surg_Proc_BK_ODBC ON
> v_BKA_02_Booking_Data.book_urn = v_Surg_Proc_BK_ODBC.book_urn INNER JOIN
> dbo.v_PTA_01_Patient_Data v_PTA_01_Patient_Data ON
> v_basic_booking_data_ODBC.ptt_urn = v_PTA_01_Patient_Data.ptt_urn INNER
> JOIN
> dbo.book_audref book_audref ON
> v_basic_booking_data_ODBC.book_urn = book_audref.or_book_urn ON
> v_CRA_BO_01_Journaling_Data.cr_urn =
> book_audref.cr_urn
> WHERE (v_basic_booking_data_ODBC.book_date >= CONVERT(DATETIME,
> '2006-03-10 00:00:00', 102)) AND
> (v_basic_booking_data_ODBC.book_date <
> CONVERT(DATETIME, '2006-03-16 00:00:00', 102))
> ORDER BY v_basic_booking_data_ODBC.room_descr,
> v_basic_booking_data_ODBC.book_date, v_basic_booking_data_ODBC.room_mnc
>
> I get results like
> lname cptcode
> DOE 1111111
> DOE 4343445
> SMITH 5456544
> RALF 4789008
> what I want is for Doe's line to read
> DOE 111111,4343445
> How can I do this with my extensive query above?
> Thank you.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***

Monday, March 19, 2012

Combine columns from Two SELECT Statements

I have a database that tracks billing and payment history records against a "relationship" record (the "relationship" maps a many-to-many relationship between employees and cell phone numbers).

I have two statements that look like this:

SELECT CellPhone.PhoneNumber, SUM(BillingHistory.AmountOwed) AS TotalOwed
FROM Relationship
INNER JOIN CellPhone ON CellPhone.PKCellPhone = Relationship.FKCellPhone
INNER JOIN BillingHistory ON Relationship.PKRelationship = BillingHistory.FKRelationship
GROUP BY Relationship.PKRelationship, CellPhone.PhoneNumber

SELECT CellPhone.PhoneNumber, SUM(PaymentHistory.AmountPaid) AS TotalPaid
FROM Relationship
INNER JOIN CellPhone ON CellPhone.PKCellPhone = Relationship.FKCellPhone
INNER JOIN PaymentHistoryON Relationship.PKRelationship = PaymentHistory.FKRelationship
GROUP BY Relationship.PKRelationship, CellPhone.PhoneNumber

Each statement correctly aggregates the sums, but I need a record that shows me:

CellPhone.PhoneNumber, SUM(BillingHistory.AmountOwed) AS TotalOwed, SUM(PaymentHistory.AmountPaid) AS TotalPaid

I can't figure out how to join or merge the statements together to get all of this information into one record without ruining the sums (I can't seem to correctly join the PaymentHistory table to the BillingHistory table without the sums going haywire).

Any help is appreciated.

Use each query as a derived table.

select

coalesce(a.PhoneNumber, b.PhoneNumber) as PhoneNumber,

a.TotalOwed,

b.TotalPaid

from

(

query A

) as a

full join

(

query B

) as b

on a.PhoneNumber = b.PhoneNumber

AMB

|||

You could try this. It might be less efficient, but you never know.

select

cellPhone.PhoneNumber,

(select sum(BillingHistory.AmountOwed)

from RelationShip

join BillingHistory

on Relationship.PKRelationship = BillingHistory.FKRelationship

where CellPhone.PKCellPhone= Relationship.FKCellPhone) as TotalOwed,

(select sum(PaymentHistory.AmountPaid)

from Relationship

join PaymentHistory

on Relationship.PKRelationship = PaymentHistory.FKRelationship

where CellPhone.PKCelPhone = Relatinship.FKCellPhone) as TotalPaid

from CellPhone

I'm not sure I see where GROUP BY Relationship.PKRelationship helps you here, but it could be needed somewhere.

Steve Kass

Drew University

www.stevekass.com

|||Why not just do something like this?

Code Snippet

SELECT CellPhone.PhoneNumber, ISNULL(SUM(BillingHistory.AmountOwed), 0) AS TotalOwed, ISNULL(SUM(PaymentHistory.AmountPaid), 0) AS TotalPaid
FROM CellPhone
LEFT OUTER JOIN OwedRelationship
ON CellPhone.PKCellPhone = OwedRelationship.FKCellPhone
LEFT OUTER JOIN BillingHistory
ON OwedRelationship.PKRelationship = BillingHistory.FKRelationship
LEFT OUTER JOIN PaidRelationship
ON CellPhone.PKCellPhone = PaidRelationship.FKCellPhone
LEFT OUTER JOIN PaymentHistory
ON PaidRelationship.PKRelationship = PaymentHistory.FKRelationship
GROUP BY OwedRelationship.PKRelationship, PaidRelationship.PKRelationship, CellPhone.PhoneNumber

|||David,

If you join all the tables together this way, the "sums will go haywire," as noted in the original post. Each AmountOwed value will appear multiple times in the sum - once for each AmountPaid value for the same account - and vice versa, so the query will not produce the desired result.

SK
|||

Steve Kass wrote:

You could try this. It might be less efficient, but you never know.
select
cellPhone.PhoneNumber,
(select sum(BillingHistory.AmountOwed)
from RelationShip
join BillingHistory
on Relationship.PKRelationship = BillingHistory.FKRelationship
where CellPhone.PKCellPhone= Relationship.FKCellPhone) as TotalOwed,
(select sum(PaymentHistory.AmountPaid)
from Relationship
join PaymentHistory
on Relationship.PKRelationship = PaymentHistory.FKRelationship
where CellPhone.PKCelPhone = Relatinship.FKCellPhone) as TotalPaid
from CellPhone

I'm not sure I see where GROUP BY Relationship.PKRelationship helps you here, but it could be needed somewhere.
Steve Kass
Drew University
www.stevekass.com

This (correctly) sums up the totals by phone number, but I need them summed up by Relationship (a relationship between an employee and a phone number), to distinguish the different owners of a single cell phone number.
|||

Steve Kass wrote:
David,
If you join all the tables together this way, the "sums will go haywire," as noted in the original post. Each AmountOwed value will appear multiple times in the sum - once for each AmountPaid value for the same account - and vice versa, so the query will not produce the desired result.
SK

This is exactly what does happen when I try David's solution.
|||

hunchback wrote:

Use each query as a derived table.
select
coalesce(a.PhoneNumber, b.PhoneNumber) as PhoneNumber,
a.TotalOwed,
b.TotalPaid
from
(
query A
) as a
full join
(
query B
) as b
on a.PhoneNumber = b.PhoneNumber

AMB

This seems almost correct, because the result set contains all of the rows I need, but it contains a lot of extra ones too. with erroneous data.

For example, I may get set that looks like:

Phone1 Owed1 Paid1
Phone2 Owed2 Paid1
Phone2 Owed2 Paid2
Phone3 Owed2 Paid3
Phone3 Owed3 Paid3

etc... with the bold rows being correct. The "correct" rows are all over the result set so I can't just cut out every other row.

|||You should be able to adapt it to sum by whatever you want. For example, if you want it summed by PhoneNumber and Relationship, proceed as follows.

1. Write a query that produces all the groups you want data for

select -- no sums of money data yet
cellPhone.PhoneNumber,
Relationship.PKRelationship
from <whatever is needed>

Then add the sums - figure out just how to get the sum for a specific PhoneNumber and Relationship and that will basicaly be your subquery. You will need to match both phone number and relationship with the outer tables, not just phone number. The results should look like this in outline:

select
C.PhoneNumber,
R.PKRelationship,
(
select sum(AmountOwed)
from ...
where CellPhone.PhoneNumber = C.PhoneNumber
and Relationship.PKRelationship = R.PKRelationship
)
from Relationship as R
join CellPhone as C
on ...

SK
|||

Steve Kass wrote:
You should be able to adapt it to sum by whatever you want. For example, if you want it summed by PhoneNumber and Relationship, proceed as follows.
1. Write a query that produces all the groups you want data for
select -- no sums of money data yet
cellPhone.PhoneNumber,
Relationship.PKRelationship
from <whatever is needed>
Then add the sums - figure out just how to get the sum for a specific PhoneNumber and Relationship and that will basicaly be your subquery. You will need to match both phone number and relationship with the outer tables, not just phone number. The results should look like this in outline:
select
C.PhoneNumber,
R.PKRelationship,
(
select sum(AmountOwed)
from ...
where CellPhone.PhoneNumber = C.PhoneNumber
and Relationship.PKRelationship = R.PKRelationship
)
from Relationship as R
join CellPhone as C
on ...
SK

Steve, you and I are now best friends. As soon as I dropped the INNER JOINs from the sub queries and used WHERE clauses, your solution worked.

Thanks a million!
|||

Comming from Steve Kass, no doubt it will work. Did you try using the queries as derived tables?

Code Snippet

select

coalesce(a.PhoneNumber, b.PhoneNumber) as PhoneNumber,
coalesce(a.PKRelationship, b.PKRelationship) as PKRelationship,
a.TotalOwed,
b.TotalPaid

from

(
SELECT
Relationship.PKRelationship,
CellPhone.PhoneNumber,
SUM(BillingHistory.AmountOwed) AS TotalOwed
FROM
Relationship
INNER JOIN
CellPhone
ON CellPhone.PKCellPhone = Relationship.FKCellPhone
INNER JOIN
BillingHistory
ON Relationship.PKRelationship = BillingHistory.FKRelationship
GROUP BY
Relationship.PKRelationship, CellPhone.PhoneNumber
) as a

full outer join
(
SELECT
Relationship.PKRelationship,
CellPhone.PhoneNumber,
SUM(PaymentHistory.AmountPaid) AS TotalPaid
FROM
Relationship
INNER JOIN
CellPhone
ON CellPhone.PKCellPhone = Relationship.FKCellPhone
INNER JOIN
PaymentHistory
ON Relationship.PKRelationship = PaymentHistory.FKRelationship
GROUP BY
Relationship.PKRelationship, CellPhone.PhoneNumber
) as b
on a.PKRelationship = b.PKRelationship
and a.PhoneNumber = b.PhoneNumber

AMB

|||Tested and this solution works, too!

Combine Add/Edit SP's?

Rather than having 2 separate stored procedures to add and update something
like a customer record, are there any drawbacks to having one stored proc
that does both? If the custID is passed in, then it would do the update, and
if the custID param is NULL than it would do an insert. Would this approach
have any performance implications?Personally, I like that design (which some refer to as "upsert"). An
efficient pattern you can follow is:
UPDATE Tbl
SET ...
WHERE custID = @.custID
--This means no row exists already
IF @.@.ROWCOUNT = 0
BEGIN
INSERT Tbl (...)
VALUES (...)
END
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:BAF1BD9F-C872-455F-8C30-A081B9B21231@.microsoft.com...
> Rather than having 2 separate stored procedures to add and update
something
> like a customer record, are there any drawbacks to having one stored proc
> that does both? If the custID is passed in, then it would do the update,
and
> if the custID param is NULL than it would do an insert. Would this
approach
> have any performance implications?|||I Use this ALL the time, but add to it using the following "design pattern"
If @.PK Is Null
Begin
Insert (ColA, ColB, ColC, ...)
Values(@.ParameterA, @.ParameterB, @.ParameterC, ...)
Set @.PK = ScopeIdentity()
End
Else If Exists (Select * From Table
Where PK = @.PK)
Begin
-- Using IsNull allows you to NOT pass in a parameter
-- and thereby effectively NOT update it (Set Null
Default values)
Update Table Set
ColA = IsNull (@.ParameterA, ColA),
ColB = IsNull (@.ParameterB, ColB),
ColC = IsNull (@.ParameterC, ColC),
..
Where PK = @.PK
End
Else
Begin
Set Identity_Insert TableName On -- When PK Is IDentity
Insert (PK, ColA, ColB, ColC, ...)
Values(@.PK, @.ParameterA, @.ParameterB, @.ParameterC, ...)
Set Identity_Insert TableName Off -- When PK Is IDentity
End
-- And then at the end, regardless of which path was taken,
Select @.PK As PK
"Adam Machanic" wrote:

> Personally, I like that design (which some refer to as "upsert"). An
> efficient pattern you can follow is:
>
> UPDATE Tbl
> SET ...
> WHERE custID = @.custID
> --This means no row exists already
> IF @.@.ROWCOUNT = 0
> BEGIN
> INSERT Tbl (...)
> VALUES (...)
> END
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:BAF1BD9F-C872-455F-8C30-A081B9B21231@.microsoft.com...
> something
> and
> approach
>
>

Sunday, March 11, 2012

Columns_Updated () and Update() functions

Hello all,

I'm trying to apply an audit DB trigger on a master-detail tables ,I do not need to insert record in the audit master every time an update happend in the audit detail,I tried to use columns_updated() > 0 but it didn't work the way I axpected it to be ..it stopped inserting into the audit master even if an update was applied against the master table ...any help please and I use the Update() function ? is their any major difference?

your help is appreciated

Thanks

Alaa M

COLUMNS_UPDATED() returns a value as a VARBINARY datatype, so for your comparison to work you need to convert the value to an INT first, like this:

CAST(COLUMNS_UPDATED() AS INT)

Chris

|||

Thanks CHRIS

Actually it didn't work ..

or may be i'm missing something here ,anyway this is the code I'm using if you or anyone can point the wrong thing that I'm doing here will be great.

this is the trigger for the master table ,and I only need to insert the changes applied against this table into Audit master no matter how many updates applied to the detail table.

ALTER Trigger trig_Audit_Upd
on [dbo].[Master]

For Update
--WITH ENCRYPTION
AS
IF EXISTS
(
SELECT 'True'
FROM deleted d
LEFT JOIN [Master] A
ON d.MasterID = A.MasterID
)

if (CAST(COLUMNS_UPDATED() AS INT ) & 255) > 0

begin
INSERT INTO [dbo].[syslogAuditMaster](
[MasterID], [Name], [CodeIDType], [DateStarted], [DateDue], [CodeIDReminderType],
[CodeIDAuditStatus], [NoteID], [DeletedOnDate], [Event])
Select del.[MasterID],del.[Name],del.[CodeIDType],del.[Datestarted],del.[Datedue],del.[CodeIDReminderType],
del.[CodeIDAuditStatus], del.[NoteID], del.[DeletedOnDate], 'UPDATE'
FROM deleted del
WHERE del.MasterID = MastertId

end;

thank

|||

I get the feeling that you might be trying to over-complicate things. I can't see the point of explicitly checking for updated columns using the COLUMNS_UPDATED() function - the fact that at least one row has been updated is implied by the existence of rows in the 'deleted' virtual table.

I'm also confused by the WHERE clause in the INSERT statement:

WHERE del.MasterID = MastertId

Does the MastertId column exist in the 'deleted' virtual table? If so does MastertId ever equal del.MasterID?

Would the re-worked example below work for you?

Chris

ALTER TRIGGER trig_Audit_Upd

ON [dbo].[Master]

FOR UPDATE

--WITH ENCRYPTION

IF EXISTS (SELECT 1 FROM deleted)

BEGIN

INSERT INTO [dbo].[syslogAuditMaster]
([MasterID],
[Name],
[CodeIDType],
[DateStarted],
[DateDue],
[CodeIDReminderType],
[CodeIDAuditStatus],
[NoteID],
[DeletedOnDate],
[Event])
SELECT del.[MasterID],
del.[Name],
del.[CodeIDType],
del.[Datestarted],
del.[Datedue],
del.[CodeIDReminderType],
del.[CodeIDAuditStatus],
del.[NoteID],
del.[DeletedOnDate],
'UPDATE'
FROM deleted del

END

|||

Thnaks for your replay,

I think you are missing the whole point of my question here ,which is to avoid the repetition in the Header or Master table when any update done against the Detailed table ..

I'm not sure if the "deleted and insered" tables hold a data record at the time or do they hold a set of records till the commit time .

it helped when I compared the "deleted " against the "inserted" data and by setting flag for any changes the insert to the Audit table was done.

although I'm not quite sure that is a good solution ,that I always think of using the functions will be more reliable and that was my resaon of asking about the Columns_Updated () and how far can I use it .

Thanks for your replies ..

|||

Just to clarify your requirement could you post examples of data in your Master table before and after the data has been updated, along with an example of the data that you would expect to be inserted into the syslogAuditMaster table as a result of the update?

Also if you could post the DDL statements used to create the Master and syslogAuditMaster tables then that would be extremely useful.

Thanks

Chris

|||

Hi Chris,

Thanks for your reply,

the DDL statements used to create the Master ,Detail and syslogAuditMaster are

--for the Master table

CREATE TABLE [dbo].[Master] (
[MasterID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CodeIDType] [int] NOT NULL ,
[DateStarted] [datetime] NULL ,
[DateDue] [datetime] NULL ,
[CodeIDReminderType] [int] NULL ,
[CodeIDAuditStatus] [int] NULL ,
[NoteID] [int] NULL ,
[DeletedOnDate] [datetime] NULL
) ON [PRIMARY]

--for the Detail table

CREATE TABLE [dbo].[Detail] (
[DetailID] [int] IDENTITY (1, 1) NOT NULL ,
[MasterID] [int] NOT NULL ,
[Datestarted] [datetime] NULL , [NoteID] [int] NULL,[ColA] ...[ColB]..etc

) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Detail] ADD
CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED
(
[DetailID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AuditDetail] ADD
CONSTRAINT [Master_Detail_FK1] FOREIGN KEY
(
[MasterID]
) REFERENCES [dbo].[Master] (
[MasterID]
)GO

-- for the Audit table

CREATE TABLE [dbo].[syslogAuditMaster] (
[MasterID] [int] NOT NULL,
[Name] [nvarchar] (254) NOT NULL ,
[CodeIDType] [int] NOT NULL ,
[DateStarted] [datetime] NULL ,
[DateDue] [datetime] NULL ,
[CodeIDReminderType] [int] NULL ,
[CodeIDAuditStatus] [int] NULL ,
[NoteID] [int] NULL ,
[DeletedOnDate] [datetime] NULL ,
[ChangedOnDate] [datetime] NOT NULL DEFAULT GETDATE(),
[UserName] [nvarchar] (150) NOT NULL DEFAULT suser_sname(),
[HostName] [nvarchar] (150) NOT NULL DEFAULT HOST_NAME(),
[Event] [nvarchar] (150) NOT NULL
) ON [PRIMARY]

when the user applied anychanges to the detail table through the .NET application , the store proc sp_Update which has the UPDATE statement for both the Master and Detail tables will executed,and as a result the DB trigget will be fired .

--Currently

if the field [NoteId] for [MasterID] = 3344 and [DetailID]=1122 has been changed in the Detail table ,I'll get the following in the syslogAuditMaster

[MasterID] = 3344,with the event type 'UPDATE'

--what to excpect

I should not get anything in the syslogAuditMaster ,cuz there is nothing new with the Master table to be added only if I changed anything within the Master I should get a record inserted in the AuditMaster

this is the DB trigger I'm using currently

Create Trigger trig_Audit_Upd
on [dbo].[Master]

For Update
--WITH ENCRYPTION
AS
IF EXISTS
(
SELECT 'True'
FROM deleted d inner join inserted i on i.MasterID = d.MasterID
where ltrim(rtrim(i.MasterID))+ltrim(rtrim(isnull(i.Name,'')))+ltrim(rtrim(isnull(i.CodeIDtype,'')))+ltrim(rtrim(isnull(i.DateStarted,'')))+ltrim(rtrim(isnull(i.DateDue,'')))+ltrim(rtrim(isnull(i.codeIDReminderType,'')))+ltrim(rtrim(isnull(i.CodeIDAuditStatus,'')))+ltrim(rtrim(isnull(i.NoteID,'')))+ltrim(rtrim(isnull(i.DeletedonDate,'')))
<> ltrim(rtrim(d.MasterID))+ltrim(rtrim(isnull(d.Name,'')))+ltrim(rtrim(isnull(d.CodeIDtype,'')))+ltrim(rtrim(isnull(d.DateStarted,'')))+ltrim(rtrim(isnull(d.DateDue,'')))+ltrim(rtrim(isnull(d.codeIDReminderType,'')))+ltrim(rtrim(isnull(d.CodeIDAuditStatus,'')))+ltrim(rtrim(isnull(d.NoteID,'')))+ltrim(rtrim(isnull(d.DeletedonDate,'')))
)
begin
INSERT INTO [dbo].[syslogAuditMaster](
[MasterID], [Name], [CodeIDType], [DateStarted], [DateDue], [CodeIDReminderType], [CodeIDAuditStatus], [NoteID], [DeletedOnDate], [Event])
Select del.[MasterID],del.[Name],del.[CodeIDType],del.[Datestarted],del.[Datedue],del.[CodeIDReminderType],
del.[CodeIDAuditStatus], del.[NoteID], del.[DeletedOnDate], 'UPDATE'
FROM deleted del
WHERE del.MasterID = MasterId

end;

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

hope this will help ..

Thank again

Ala'a M

|||

Right, I think I now understand where you are going with this.

Basically, even though you are issuing an UPDATE statement to update a row in the Master table, you might actually not be changing any of the values - in which case you don't want to write an audit row to the syslogAuditMaster table. You, therefore, only want to write a row to the syslogAuditMaster table if at least one of the columns values changes as a result of the update. Is this correct?

The problem with the COLUMNS_UPDATED() and UPDATED() functions is that they will report a column as being updated even if the column's value doesn't actually change as a result of the UPDATE statement. For this reason, if the solution you are using works fine then I see no reason why you shouldn't continue to use it.

Personally, I would move away from the text-based comparison that you are doing and would have explicit checks for each of the columns, see the example below. Note that there is no need for the IF EXISTS statement in this example. Also, if you update several Master rows at a time then the example below will only write audit rows for the Master rows that have actually changed.

Chris

CREATE TRIGGER trig_Audit_Upd

ON [dbo].[Master]

FOR UPDATE

--WITH ENCRYPTION

INSERT INTO [dbo].[syslogAuditMaster](

[MasterID],
[Name],
[CodeIDType],
[DateStarted],
[DateDue],
[CodeIDReminderType],
[CodeIDAuditStatus],
[NoteID],
[DeletedOnDate],
[Event])

SELECT del.[MasterID],

del.[Name],
del.[CodeIDType],
del.[Datestarted],
del.[Datedue],
del.[CodeIDReminderType],
del.[CodeIDAuditStatus],
del.[NoteID],
del.[DeletedOnDate],
'UPDATE'

FROM deleted del

INNER JOIN inserted ins ON ins.MasterID = del.MasterID

WHERE ((del.[Name] <> ins.[Name])

OR (del.CodeIDType <> ins.CodeIDType)
OR ((del.[DateStarted] <> ins.[DateStarted]) OR (del.[DateStarted] IS NULL AND ins.[DateStarted] IS NOT NULL) OR (del.[DateStarted] IS NOT NULL AND ins.[DateStarted] IS NULL))
OR ((del.[DateDue] <> ins.[DateDue]) OR (del.[DateDue] IS NULL AND ins.[DateDue] IS NOT NULL) OR (del.[DateDue] IS NOT NULL AND ins.[DateDue] IS NULL))
OR ((del.[CodeIDReminderType] <> ins.[CodeIDReminderType]) OR (del.[CodeIDReminderType] IS NULL AND ins.[CodeIDReminderType] IS NOT NULL) OR (del.[CodeIDReminderType] IS NOT NULL AND ins.[CodeIDReminderType] IS NULL))
OR ((del.[CodeIDAuditStatus] <> ins.[CodeIDAuditStatus]) OR (del.[CodeIDAuditStatus] IS NULL AND ins.[CodeIDAuditStatus] IS NOT NULL) OR (del.[CodeIDAuditStatus] IS NOT NULL AND ins.[CodeIDAuditStatus] IS NULL))
OR ((del.[NoteID] <> ins.[NoteID]) OR (del.[NoteID] IS NULL AND ins.[NoteID] IS NOT NULL) OR (del.[NoteID] IS NOT NULL AND ins.[NoteID] IS NULL))
OR ((del.[DeletedOnDate] <> ins.[DeletedOnDate]) OR (del.[DeletedOnDate] IS NULL AND ins.[DeletedOnDate] IS NOT NULL) OR (del.[DeletedOnDate] IS NOT NULL AND ins.[DeletedOnDate] IS NULL)))

|||

Thanks alot for giving me the chance to think loudly and exchange thoughts helped me alot ..

thanx Chris

Columns_Updated () and Update() functions

Hello all,

your help is appreciated

Thanks

Alaa M

COLUMNS_UPDATED() returns a value as a VARBINARY datatype, so for your comparison to work you need to convert the value to an INT first, like this:

CAST(COLUMNS_UPDATED() AS INT)

Chris

|||

Thanks CHRIS

Actually it didn't work ..

or may be i'm missing something here ,anyway this is the code I'm using if you or anyone can point the wrong thing that I'm doing here will be great.

this is the trigger for the master table ,and I only need to insert the changes applied against this table into Audit master no matter how many updates applied to the detail table.

ALTER Trigger trig_Audit_Upd
on [dbo].[Master]

For Update
--WITH ENCRYPTION
AS
IF EXISTS
(
SELECT 'True'
FROM deleted d
LEFT JOIN [Master] A
ON d.MasterID = A.MasterID
)

if (CAST(COLUMNS_UPDATED() AS INT ) & 255) > 0

end;

thank

|||

I'm also confused by the WHERE clause in the INSERT statement:

WHERE del.MasterID = MastertId

Does the MastertId column exist in the 'deleted' virtual table? If so does MastertId ever equal del.MasterID?

Would the re-worked example below work for you?

Chris

ALTER TRIGGER trig_Audit_Upd

ON [dbo].[Master]

FOR UPDATE

--WITH ENCRYPTION

IF EXISTS (SELECT 1 FROM deleted)

BEGIN

INSERT INTO [dbo].[syslogAuditMaster]
([MasterID],
[Name],
[CodeIDType],
[DateStarted],
[DateDue],
[CodeIDReminderType],
[CodeIDAuditStatus],
[NoteID],
[DeletedOnDate],
[Event])
SELECT del.[MasterID],
del.[Name],
del.[CodeIDType],
del.[Datestarted],
del.[Datedue],
del.[CodeIDReminderType],
del.[CodeIDAuditStatus],
del.[NoteID],
del.[DeletedOnDate],
'UPDATE'
FROM deleted del

END

|||

Thnaks for your replay,

I think you are missing the whole point of my question here ,which is to avoid the repetition in the Header or Master table when any update done against the Detailed table ..

I'm not sure if the "deleted and insered" tables hold a data record at the time or do they hold a set of records till the commit time .

it helped when I compared the "deleted " against the "inserted" data and by setting flag for any changes the insert to the Audit table was done.

Thanks for your replies ..

|||

Also if you could post the DDL statements used to create the Master and syslogAuditMaster tables then that would be extremely useful.

Thanks

Chris

|||

Hi Chris,

Thanks for your reply,

the DDL statements used to create the Master ,Detail and syslogAuditMaster are

--for the Master table

--for the Detail table

CREATE TABLE [dbo].[Detail] (
[DetailID] [int] IDENTITY (1, 1) NOT NULL ,
[MasterID] [int] NOT NULL ,
[Datestarted] [datetime] NULL , [NoteID] [int] NULL,[ColA] ...[ColB]..etc

) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Detail] ADD
CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED
(
[DetailID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AuditDetail] ADD
CONSTRAINT [Master_Detail_FK1] FOREIGN KEY
(
[MasterID]
) REFERENCES [dbo].[Master] (
[MasterID]
)GO

-- for the Audit table

--Currently

if the field [NoteId] for [MasterID] = 3344 and [DetailID]=1122 has been changed in the Detail table ,I'll get the following in the syslogAuditMaster

[MasterID] = 3344,with the event type 'UPDATE'

--what to excpect

this is the DB trigger I'm using currently

Create Trigger trig_Audit_Upd
on [dbo].[Master]

end;

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

hope this will help ..

Thank again

Ala'a M

|||

Right, I think I now understand where you are going with this.

Chris

CREATE TRIGGER trig_Audit_Upd

ON [dbo].[Master]

FOR UPDATE

--WITH ENCRYPTION

INSERT INTO [dbo].[syslogAuditMaster](

[MasterID],
[Name],
[CodeIDType],
[DateStarted],
[DateDue],
[CodeIDReminderType],
[CodeIDAuditStatus],
[NoteID],
[DeletedOnDate],
[Event])

SELECT del.[MasterID],

del.[Name],
del.[CodeIDType],
del.[Datestarted],
del.[Datedue],
del.[CodeIDReminderType],
del.[CodeIDAuditStatus],
del.[NoteID],
del.[DeletedOnDate],
'UPDATE'

FROM deleted del

INNER JOIN inserted ins ON ins.MasterID = del.MasterID

WHERE ((del.[Name] <> ins.[Name])

OR (del.CodeIDType <> ins.CodeIDType)
OR ((del.[DateStarted] <> ins.[DateStarted]) OR (del.[DateStarted] IS NULL AND ins.[DateStarted] IS NOT NULL) OR (del.[DateStarted] IS NOT NULL AND ins.[DateStarted] IS NULL))
OR ((del.[DateDue] <> ins.[DateDue]) OR (del.[DateDue] IS NULL AND ins.[DateDue] IS NOT NULL) OR (del.[DateDue] IS NOT NULL AND ins.[DateDue] IS NULL))
OR ((del.[CodeIDReminderType] <> ins.[CodeIDReminderType]) OR (del.[CodeIDReminderType] IS NULL AND ins.[CodeIDReminderType] IS NOT NULL) OR (del.[CodeIDReminderType] IS NOT NULL AND ins.[CodeIDReminderType] IS NULL))
OR ((del.[CodeIDAuditStatus] <> ins.[CodeIDAuditStatus]) OR (del.[CodeIDAuditStatus] IS NULL AND ins.[CodeIDAuditStatus] IS NOT NULL) OR (del.[CodeIDAuditStatus] IS NOT NULL AND ins.[CodeIDAuditStatus] IS NULL))
OR ((del.[NoteID] <> ins.[NoteID]) OR (del.[NoteID] IS NULL AND ins.[NoteID] IS NOT NULL) OR (del.[NoteID] IS NOT NULL AND ins.[NoteID] IS NULL))
OR ((del.[DeletedOnDate] <> ins.[DeletedOnDate]) OR (del.[DeletedOnDate] IS NULL AND ins.[DeletedOnDate] IS NOT NULL) OR (del.[DeletedOnDate] IS NOT NULL AND ins.[DeletedOnDate] IS NULL)))

|||

Thanks alot for giving me the chance to think loudly and exchange thoughts helped me alot ..

thanx Chris

Wednesday, March 7, 2012

column type

I am putting together a SQL table which will hold a latitude and
longitude for each record. An example of a latitude is: 47 05 36.5
Which column type would best represent this and retain the spaces
between degrees, minutes, seconds? Text, varchar, char?

Thanks
Jeff

jeff-godfrey@.wa.nacdnet.orgDon't use Text. Text is only useful for large texts and needs special
treatment.

Since the variability in value length will be small I would opt for the
char datatype. Other than that varchar is a fine choice as well. If you
choose char, make sure you don't overdimension it (you would waste
space).

Gert-Jan

Jeff Godfrey wrote:
> I am putting together a SQL table which will hold a latitude and
> longitude for each record. An example of a latitude is: 47 05 36.5
> Which column type would best represent this and retain the spaces
> between degrees, minutes, seconds? Text, varchar, char?
> Thanks
> Jeff
> jeff-godfrey@.wa.nacdnet.org|||Unless you are doing any computations on the values in this column, you can
use CHAR datatype. If each portion of this data has operational significance
or need any computations, you may be better off storing them as separate
numeric columns.

--
- Anith
( Please reply to newsgroups only )|||>> a latitude and longitude ... Which column type would best
represent this and retain the spaces between degrees, minutes,
seconds? Text, varchar, char? <<

If you have to compute with them, you might want to use FLOAT and
convert things to radians, and display them with a UDF.

Column to store last time record was changed

In a table I have a column called ModifyDateTime which stores the last time
that record was modified. When a record is inserted this column is updated
through a default and when a record is updated a trigger is used. This is
all working fairly well except I would like the ModifyDateTime column to not
change if the data in the other columns is updated to the same thing.
Currently I need to make checks in my different place to ensure that an
update actually contains new data but if the trigger could do it then I'd
only need to check in one place.
Many thanks
Michael
eg
CREATE TABLE ABC(ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, SomeValue
NVARCHAR(5), ModifyDateTime DateTime NOT NULL DEFAULT GETDATE())
GO
CREATE TRIGGER [tgABC_Update] ON [dbo].[ABC]
FOR UPDATE
AS
UPDATE ABC SET ModifyDateTime = GETDATE() WHERE ID IN (SELECT ID FROM
INSERTED)
GO
INSERT INTO ABC (SomeValue) VALUES ('1')
SELECT * FROM ABC
--might need a delay here.
UPDATE ABC SET SomeValue = 1 --should not cause ModifyDateTime column to
change
SELECT * FROM ABC--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
IF Update(SomeValue)
RETURN
ELSE
UPDATE ABC SET ModifyDateTime = GETDATE() WHERE ID IN (SELECT ID FROM
inserted)
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQlDEIYechKqOuFEgEQKR0gCg2CL/V2dejzcsa164lHtJV8pWGssAniBt
RTNymjjW6r627hdwwiZQBamQ
=bfki
--END PGP SIGNATURE--
Michael C wrote:
> In a table I have a column called ModifyDateTime which stores the last tim
e
> that record was modified. When a record is inserted this column is updated
> through a default and when a record is updated a trigger is used. This is
> all working fairly well except I would like the ModifyDateTime column to n
ot
> change if the data in the other columns is updated to the same thing.
> Currently I need to make checks in my different place to ensure that an
> update actually contains new data but if the trigger could do it then I'd
> only need to check in one place.
> Many thanks
> Michael
> eg
> CREATE TABLE ABC(ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, SomeValue
> NVARCHAR(5), ModifyDateTime DateTime NOT NULL DEFAULT GETDATE())
> GO
> CREATE TRIGGER [tgABC_Update] ON [dbo].[ABC]
> FOR UPDATE
> AS
> UPDATE ABC SET ModifyDateTime = GETDATE() WHERE ID IN (SELECT ID FROM
> INSERTED)
> GO
> INSERT INTO ABC (SomeValue) VALUES ('1')
> SELECT * FROM ABC
> --might need a delay here.
> UPDATE ABC SET SomeValue = 1 --should not cause ModifyDateTime column to
> change
> SELECT * FROM ABC
>
In your trigger use the IF Update(column) construct. E.g.:|||Michael
Look at IF UPDATE(column name) command to be used inside the trigger.
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:e4$T%235MOFHA.3296@.TK2MSFTNGP15.phx.gbl...
> In a table I have a column called ModifyDateTime which stores the last
time
> that record was modified. When a record is inserted this column is updated
> through a default and when a record is updated a trigger is used. This is
> all working fairly well except I would like the ModifyDateTime column to
not
> change if the data in the other columns is updated to the same thing.
> Currently I need to make checks in my different place to ensure that an
> update actually contains new data but if the trigger could do it then I'd
> only need to check in one place.
> Many thanks
> Michael
> eg
> CREATE TABLE ABC(ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, SomeValue
> NVARCHAR(5), ModifyDateTime DateTime NOT NULL DEFAULT GETDATE())
> GO
> CREATE TRIGGER [tgABC_Update] ON [dbo].[ABC]
> FOR UPDATE
> AS
> UPDATE ABC SET ModifyDateTime = GETDATE() WHERE ID IN (SELECT ID FROM
> INSERTED)
> GO
> INSERT INTO ABC (SomeValue) VALUES ('1')
> SELECT * FROM ABC
> --might need a delay here.
> UPDATE ABC SET SomeValue = 1 --should not cause ModifyDateTime column to
> change
> SELECT * FROM ABC
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23c8snCNOFHA.3984@.TK2MSFTNGP12.phx.gbl...
> Michael
> Look at IF UPDATE(column name) command to be used inside the trigger.
Thanks Uri and MG,
IF Update(SomeValue)
RETURN
ELSE
UPDATE ABC SET ModifyDateTime = GETDATE() WHERE ID IN (SELECT ID FROM
inserted)
I'm presuming this won't work as I would like if the change was initiated
from an UPDATE statement and that causes only some of the data to change? eg
UPDATE ABC SET SomeValue = 'Z'
where half the rows already contain the value 'Z' in somefield.
I'm guessing the only method would be to use a cursor then to go through
each row. I can just use my existing method of checking everywhere I do an
update, which is probably the most efficient anyway because it never updates
the row at all.
Regards,
Michael|||Michael
Perhaps you need
UPDATE ABC SET SomeValue = 'Z' WHERE SomeValue <>'Z'
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:OeDmtqNOFHA.3336@.TK2MSFTNGP09.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23c8snCNOFHA.3984@.TK2MSFTNGP12.phx.gbl...
> Thanks Uri and MG,
> IF Update(SomeValue)
> RETURN
> ELSE
> UPDATE ABC SET ModifyDateTime = GETDATE() WHERE ID IN (SELECT ID FROM
> inserted)
> I'm presuming this won't work as I would like if the change was initiated
> from an UPDATE statement and that causes only some of the data to change?
eg
> UPDATE ABC SET SomeValue = 'Z'
> where half the rows already contain the value 'Z' in somefield.
> I'm guessing the only method would be to use a cursor then to go through
> each row. I can just use my existing method of checking everywhere I do an
> update, which is probably the most efficient anyway because it never
updates
> the row at all.
> Regards,
> Michael
>|||On Mon, 4 Apr 2005 15:59:34 +1000, Michael C wrote:

>"Uri Dimant" <urid@.iscar.co.il> wrote in message
>news:%23c8snCNOFHA.3984@.TK2MSFTNGP12.phx.gbl...
>Thanks Uri and MG,
>IF Update(SomeValue)
> RETURN
>ELSE
> UPDATE ABC SET ModifyDateTime = GETDATE() WHERE ID IN (SELECT ID FROM
>inserted)
>I'm presuming this won't work as I would like if the change was initiated
>from an UPDATE statement and that causes only some of the data to change? e
g
>UPDATE ABC SET SomeValue = 'Z'
>where half the rows already contain the value 'Z' in somefield.
>I'm guessing the only method would be to use a cursor then to go through
>each row. I can just use my existing method of checking everywhere I do an
>update, which is probably the most efficient anyway because it never update
s
>the row at all.
Hi Michael,
Oh no, there absolutely no need to slow things down by using a cursor;
this can easily be accomplished with setbased instructions:
IF UPDATE(SomeColumn) -- Saves time if the colummn's not updated at all
UPDATE ABC
SET ModifyDateTime = CURRRENT_TIMESTAMP
WHERE EXISTS
(SELECT *
FROM deleted AS d -- Holds old vales (before update)
WHERE d.KeyCol1 = ABC.KeyCol1 -- Repeat for all columns that
AND d.KeyCol2 = ABD.KeyCol2 -- make up the primary key
AND d.SomeColumn <> ABC.SomeColumn) -- Actually changed?
The above assumes that SomeColumn will never be NULL. If it can be NULL,
you'll have to change the last line. Either like this, using a value
that will never be really used in the column:
AND COALESCE(d.SomeColumn, -123)
<> COALESCE(ABC.SomeColumn, -123)
Or, if you don't have any value that will never be in the column (or
prefer not to rely on such assumptions), you can use this not very
intuuitive but very reliable version:
AND NULLIF(d.SomeColumn, ABC.SomeColumn) IS NULL
AND NULLIF(ABC.SomeColumn, d.SomeColumn) IS NULL
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uZAnIEOOFHA.2936@.TK2MSFTNGP10.phx.gbl...
> Michael
> Perhaps you need
> UPDATE ABC SET SomeValue = 'Z' WHERE SomeValue <>'Z'
Thanks Uri. Unfortunately, that is what I'm trying to avoid. I have a few
statements like what you've written here that update the same table but they
are much more complicated, if the trigger does the checking then I will only
need the check in one central location. In the end it is not really that
important and I might do it either way, or even both for efficiency (as your
statement updates less records).
Regards,
Michael|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:3n5351pi5ggb1dov6qqej33k9dq9esnvnm@.
4ax.com...
> On Mon, 4 Apr 2005 15:59:34 +1000, Michael C wrote:
Thank Hugo, I'll have a look at it now. I really like the NULLIF idea, i've
been using
ISNULL(X, '{00000000-0000-0000-0000-00000000}') = ISNULL(Y,
'{00000000-0000-0000-0000-00000000}')
for guids, which I've always thought could be simpler.
Michael|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:3n5351pi5ggb1dov6qqej33k9dq9esnvnm@.
4ax.com...
> Oh no, there absolutely no need to slow things down by using a cursor;
> this can easily be accomplished with setbased instructions:
Thanks Hugo, that worked perfectly after lots of playing around. I had a
couple of questions if you don't mind. Is it preferrable to use
CURRENT_TIMESTAMP instead of GETDATE()? Ditto for EXISTS instead of an IN
clause?

> AND NULLIF(d.SomeColumn, ABC.SomeColumn) IS NULL
> AND NULLIF(ABC.SomeColumn, d.SomeColumn) IS NULL
Is this something they overlooked in SQLServer? Seems to me there should
something built in for comparing 2 values that you want to consider equal
when both are null.
Thanks,
Michael|||On Tue, 5 Apr 2005 14:20:32 +1000, Michael C wrote:
(snip)
> I had a
>couple of questions if you don't mind.
Hi Michael,
I don't mind :-)

> Is it preferrable to use
>CURRENT_TIMESTAMP instead of GETDATE()?
GETDATE() is the name chosen for the function in Sybase and SQL Server
before there were any standards to adhere to. The ANSI standard settled
for CURRENT_TIMESTAMP. Since either 7.0 or 2000, both are recognised by
SQL Server. Many people still use GETDATE (I do - old habits die hard),
but for portability, CURRENT_TIMESTAMP is prefered.

> Ditto for EXISTS instead of an IN
>clause?
I prefer to use IN only with a list of constants (as a shorthand for a
range of expressions OR'ed together), and EXISTS in all other cases. Not
for portability (both conform to ANSI standard), not for performance
(though I've read reports claiming that EXISTS is often faster), but for
another reason.
If you combine the IN operator with NOT (i.e. NOT IN), and the subquery
after NOT IN returns one or more NULL values, then the NOT IN operator
will never evaluate to true. This surprises lots of people, because many
people have trouble understanding the role NULL plays in the relational
model. What would you answer if I asked you if the number 8 is IN
(current monthnumber, age of my daughter, #days per w

)?

>Is this something they overlooked in SQLServer? Seems to me there should
>something built in for comparing 2 values that you want to consider equal
>when both are null.
This is not an oversight, it's a feature (and no, this is not intended
as a cynical remark, I'm serious). NULL means that the data is missing,
not available, unknown. I don't think that you should ever want a
database to conclude that two unknown values are equal. Is my daughter's
age equal to the age of the girl living next door to me?
And even if it is an oversight, then definitely not in SQL Server. In
this regard, SQL Server strictly adheres to the ANSI standard handling
of NULL.
Besides, there IS a way to compare 2 values and consider them equal of
both are NULL. Several ways, in fact. You can use a combination of two
NULLIF expressions like the one above. Or you can use COALESCE to
replace NULL with a value that won't ever appear in regular data, then
compare the resutls. Or you could use an expression such as "col1 = col2
OR (col1 IS NULL AND col2 IS NULL)"
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Sunday, February 19, 2012

Column headers for Matrix

I am developing a matrix report in SRS. In columns group there are several values. When report runs they apper in any order based on the first record in row group. I want colums to apeear in specific order all the time. For example the column sequence in one out put is Follwup 1, Initial , Followup 2. I want to column header to be in order of Initial, Folloup 1, Followup 2.

Can someone help?

You'll need to add another column like Sort or Rank and populate that accordingly. Then sort the output based on that column. If you have several columns that are dynamic that would be best.

pseudo-code for the sort column in your SQL would be:
Case
When "Followup 1" Then 1
When "Initial" Then 2
When "Followup 2" Then 3
End as MyCustomRank

You can also use an expression in the matrix sort...same concept. The pseduo-code for that would be

=IIF(Fields!YOURFIELD.Value = "Followup 1", 1, IIF(Fields!YOURField.Value = "Initial", 2..... and so on

|||

Thank You very much

It worked!

Column headers for Matrix

Can someone help?

You'll need to add another column like Sort or Rank and populate that accordingly. Then sort the output based on that column. If you have several columns that are dynamic that would be best.

pseudo-code for the sort column in your SQL would be:
Case
When "Followup 1" Then 1
When "Initial" Then 2
When "Followup 2" Then 3
End as MyCustomRank

You can also use an expression in the matrix sort...same concept. The pseduo-code for that would be

=IIF(Fields!YOURFIELD.Value = "Followup 1", 1, IIF(Fields!YOURField.Value = "Initial", 2..... and so on

|||

Thank You very much

It worked!

Thursday, February 16, 2012

Column Conflict error

Hi;

I am getting this error while I am trying to delete a record from "MediaTypes" table
Media Types table and DVBTestCOutputs table have related through MediaID (cascade on update only not delete)

MediaTypes DVBTestCOutputs
---- -------
MediaID int ....................
MType char(10) ....................
MediaType int

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_DVBTestCOutputs_MediaTypes1'. The conflict occurred in database 'Test', table 'DVBTestCOutputs', column 'MediaType'.

can you help me please I do not want the related record deleted also from DVBTestCOutputs table so I didn't choose the cascade on delete checkbox is this the problem??If there is a PK-FK constraint, then you are not allowed to have child records that have no parent record. You will need to update the value of the ParentID in the child table to another ParentID, before deleting the parent record.

Otherwise, you need to remove your constraint.

Cheers
Ken

Tuesday, February 14, 2012

Coloring the mx value in a record set

Hi,
I have a simple report that displays several records having 2 fields, date
field and Quantity field
I ma,aged to alternate the colors of line (RosntBrown and black) bu I need
also to display the max Qty in a color different of the other ones, how this
can be achieved
ThanksHello eliassal,
You could add an Expression in the backgroundColor properties of the
textbox like this:
=IIF(ReportItems!Quantity.Value=MAX(Fields!Quantity.Value),"Black","Transpar
ent")
If is the max Qty, the background will be black and other will be
Transparent.
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||So many thankls, it works like a charm. Now, how about displaying different
colors for Max and Min values for the same text box. can we use 2 expressions
at the same time on the same textbox containing thye field.
Thanks
"Wei Lu [MSFT]" wrote:
> Hello eliassal,
> You could add an Expression in the backgroundColor properties of the
> textbox like this:
>
> =IIF(ReportItems!Quantity.Value=MAX(Fields!Quantity.Value),"Black","Transpar
> ent")
> If is the max Qty, the background will be black and other will be
> Transparent.
> Hope this will be helpful.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello eliassal,
Of course. You could do like this:
=IIF(ReportItems!Quantity.Value=MAX(Fields!Quantity.Value),"Black",IIF(Repor
tItems!Quantity.Value=min(fields!Quantity.Value),"Red","Transparent"))
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi, I was in vacation, I will check next week and let you know
Thanks
"Wei Lu [MSFT]" wrote:
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>

Thursday, March 29, 2012

Combining the results of a cursor loop

Tuesday, March 27, 2012

Combining record sets from two tables

Thursday, March 22, 2012

combine values in view?

Tuesday, March 20, 2012

combine table data fields from two records into a record

Combine record?

Combine record

Combine Multiple Results into 1 RecordSet

Combine multiple lines for one record into one comma-delimted field

Monday, March 19, 2012

Combine columns from Two SELECT Statements

Combine Add/Edit SP's?

Sunday, March 11, 2012

Columns_Updated () and Update() functions

Columns_Updated () and Update() functions

Wednesday, March 7, 2012

column type

Column to store last time record was changed

Sunday, February 19, 2012

Column headers for Matrix

Column headers for Matrix

Thursday, February 16, 2012

Column Conflict error

Tuesday, February 14, 2012

Coloring the mx value in a record set

Collation problem

Blog Archive

About Me