Neither System.DbNull or NULL?

How to set NULL values inside VB Script .Net? Both causes error:

.FECHAAP = NULL

.FECHAAP = System.DBNull

.FECHAAP = Nothing

-Jamie

|||Thanks

negative values...

calculating profit... how do I write an update query that will correct the gross profit calculated column for all negative qty transactions

Basically in the stored procedure that creates the table the query includes:

Profit = CASE Sale WHEN 0 THEN 0 ELSE (Sale - Cost) END,
which is wrong when sale and cost is negative the formula becomes
(-Sale + Cost)... I want it to be -(Sale-Cost) (where sale any cost ignores negative sign....

but i dont know to write this...any ideas?which is wrong when sale and cost is negative the formula becomes
(-Sale + Cost)... I want it to be -(Sale-Cost) (where sale any cost ignores negative sign....

How could 'Cost' come negative..?|||How could 'Cost' come negative..?when the supplier pays you to take his product

next question: how can sale be 0?

answer: when you give your product away|||Sounds like an absolute value to me.

sale - abs(cost)|||calculating profit... how do I write an update query that will correct the gross profit calculated column for all negative qty transactions

Basically in the stored procedure that creates the table the query includes:

Profit = CASE Sale WHEN 0 THEN 0 ELSE (Sale - Cost) END,
which is wrong when sale and cost is negative the formula becomes
(-Sale + Cost)... I want it to be -(Sale-Cost) (where sale any cost ignores negative sign....

but i dont know to write this...any ideas?

Why would it be wrong? Sounds like simple accounting

My wife say I have a lot of negative values|||I worked my way around it... the profit was calculating correct, it was the Profit% that was wrong...soz:

UPDATE SALES
SET [Profit%] = [Profit%] * -1
FROM SALES
WHERE Qty < 0

So that when the profit% is negative when qty is negative... thanks :)|||So that when the profit% is negative when qty is negative... thanks :)you're welcome :)

you sell negative quantities?|||Those are called Returns!|||Those are called Returns!

yup :) :beer:|||Our sales monkeys are good at generating negative GP, too! :D

negative values when calculating percentages

I get negative values for percentage calculation in a MDX query. The MDX query has a crossjoin between two sets containing calculated members from the same dimension, one of the calculated members being a percentage value. I'm not sure why some of the percentage values are negative.

Another problem I'm facing is that the percentage value is not being displayed as per the FORMAT_STRING property, in my Reports in Reporting Services 2005 that use the data generated by the MDX query.

Any help or suggestion is appreciated.

Hi. Can we see the MDX query you're using and a small data sample which provides the negative percentage?

PGoldy

|||

Hope this will give you an idea:

WITH
MEMBER [ITEMA].[ITEMA].itemmember
as

' (ITEMA.ITEMA.&[itemmember], [Measures].[NUMBER]) '

MEMBER [ITEMA].[ITEMA].TOTAL
as

' SUM(ITEMA.ITEMA.Members, [Measures].[NUMBERS]) '

MEMBER [ITEMA].[ITEMA].ITEMPERC
as

' Iif(IsEmpty([ITEMA].[ITEMA].TOTAL),0,([ITEMA].[ITEMA].itemmember / [ITEMA].[ITEMA].TOTAL)) ', FORMAT_STRING = '#.#%'

select [Measures].[NUMBER] on columns,

non empty crossjoin({[ITEMA].[ITEMA].MEMBERS,[ITEMA].[ITEMA].OTHERS,[ITEMA].[ITEMA].itemmember,[ITEMA].[ITEMA].TOTAL,[ITEMA].[ITEMA].ITEMPERC},
crossjoin({ITEMB.ITEMB.children},{ITEMC.ITEMC.children,[ITEMC].[ITEMC].[others]})) on rows
FROM [MyCube]

Data Snapshot:

ITEMA_1 ITEMA_2 ........ ITEMA_itemmember ITEMA_TOTAL ITEMA_ITEMPERC

- ITEMB_1
ITEMC_1 10 20 10 100 -0.1
ITEMC_2 5 6 0 150 0.0
ITEMC_3 0 1 2 4 0.5

- ITEMB_2
ITEMC_1 ...................................................................
ITEMC_2 ...................................................................
ITEMC_3 ...................................................................
+ ITEMB_3
+ ITEMB_4
.
.
.

|||

Hi. Thanks for the detailed query and example.

I don't see why you get a negative percentage, but I see you're using the calculated members to get values which are normally available in the cube without the use of a calculated member when you construct the right query. I think you should reconstruct your query to use the WHERE clause and re-define, and eliminate, some calculated members to get the correct results Here are my recommendations:

(1) Use the WHERE cluase to slice your qeury by the desired measure: WHERE (Measures.Number)

(2) Reference ITEMA dimension on the columns.

(3) Eliminate the following calculated members because they are not needed and we can derive the dsired values from normal intersections in the cube: (a) MEMBER [ITEMA].[ITEMA].itemmember, (b) MEMBER [ITEMA].[ITEMA].TOTAL

(4) Change the definition of MEMBER [ITEMA].[ITEMA].ITEMPERC to reference the correct cube intersections.

Assumption: ITEMA hierarchy has an "all" member, aggregation type for Measures.Number is SUM.

Here's the new query with the recommended changes:

WITH
MEMBER [ITEMA].[ITEMA].ITEMPERC AS
' Iif(IsEmpty([ITEMA].[ITEMA].[All ITEMA]),0,([ITEMA].[ITEMA].CurrentMember / [ITEMA].[ITEMA].[All ITEMA])) ', FORMAT_STRING = '0.0%'

NON EMPTY {[ITEMA].[ITEMA].MEMBERS, [ITEMA].[ITEMA].[All ITEMA], [ITEMA].[ITEMA].ITEMPERC} ON COLUMNS,
crossjoin({ITEMB.ITEMB.children},{ITEMC.ITEMC.children,[ITEMC].[ITEMC].[others]}) on rows
FROM [MyCube]
WHERE ([Measures].[NUMBER])

Hoe this helps.

PGoldy

Negative values not displayed in series

Hi,
I am using graphical report to display data. If the values are negative (
i.e less than 0) then the graph is not displaying the series values. If the
values are positive then the graph is displaying series values.
I need to display the series values even though they are negative.
How to do this.Any url/help/suggestions urgently required.
Thanks and Regards,
Rajesh Yennam.
HA, India.I encountered this the other day. If you go to the chart properties' Y-Axis
tab the minimum scale is set to 0. To see negative numbers remove the 0
from the text box.
Matt
"Rajesh Yennam" <RajeshYennam@.discussions.microsoft.com> wrote in message
news:2AF40A1D-DF85-4B72-AE86-754502BFEA1F@.microsoft.com...
> Hi,
> I am using graphical report to display data. If the values are negative (
> i.e less than 0) then the graph is not displaying the series values. If
the
> values are positive then the graph is displaying series values.
> I need to display the series values even though they are negative.
> How to do this.Any url/help/suggestions urgently required.
> Thanks and Regards,
> Rajesh Yennam.
> HA, India.

Negative values for RowModCtr col in sysobjects tbl

Could anyone, please, explain to me why I have negative values in
RowModCtr column in sysobjects table? I have tested that after I update
statistics the RowModCtr column is reset to 0. But why do I have
negative values in the first place? Thx."luke" <svatik@.hotmail.com> wrote in message
news:1116361464.490333.196030@.g49g2000cwa.googlegr oups.com...
> Could anyone, please, explain to me why I have negative values in
> RowModCtr column in sysobjects table? I have tested that after I update
> statistics the RowModCtr column is reset to 0. But why do I have
> negative values in the first place? Thx.

No idea; and I guess you mean sysindexes, not sysobjects? But Books Online
says that the value should be correct since the last time the statistics
were updated (see "sysindexes"), so you might want to look at
"sp_updatestats" and "update statistics". See also p.848 of "Inside SQL
Server 2000", which mentions rowmodctr, and says that when you update
statistics, it's set to zero.

If this doesn't help, I suggest you give some more details - what version of
MSSQL, why the sysindexes value is a problem for you etc.

Simon|||I too have seen negative numbers in the sysindexes table. After reasearching I found:

There is one row in SYSINDEXES for each index and statistics set (if the table does not have a clustered index then there is a row corresponding to the heap as well), and SQL Server 2000 maintains the amount of change for indexes and statistics separately (although this was not true in SQL Server 7.0). Throughout the INSERT, UPDATE, and DELETE queries performed in a table, the rowmodctr value is increased only for the index ID 0 or 1 (there is always only one on a table). For the rest of the indexes and statistics, it shows only a relative value that has to be added to the rowmodctr of the index 0 or 1 to get the true number of changed rows for this index.

For examples and more info go to the following site, the info is near the bottom of the article :)
http://msdn.microsoft.com/library/d...l/statquery.asp

Negative values being summed incorrectly

I'm wondering if anyone has seen this before.

I have a cube in which the data is not being rolled up correctly when the values include negative numbers. The fact table and dimension tables are on an Oracle Server (9i). The fact table looks like:

Column Name

Data Type

Nullable

EDP_NO

NUMBER(10,0)

Yes

MONTH_YYYYMM

VARCHAR2(6 Bytes)

Yes

COGS

NUMBER

Yes

INV_VALUE

NUMBER

Yes

QTY_SOLD

NUMBER(10,0)

Yes

ITEM_NO

VARCHAR2(20 Bytes)

Yes

The dimension table looks like:

olumn Name

Data Type

Nullable

EDP_NO

NUMBER(10,0)

No

VENDOR_NAME

VARCHAR2(41 Bytes)

Yes

AP_VENDOR_NAME

VARCHAR2(41 Bytes)

Yes

DCS

VARCHAR2(4 Bytes)

Yes

DEPT_NAME

VARCHAR2(49 Bytes)

Yes

CLASS_NAME

VARCHAR2(49 Bytes)

Yes

SUB_CLASS_NAME

VARCHAR2(49 Bytes)

Yes

CATEGORY_NAME

VARCHAR2(49 Bytes)

Yes

BUYER

VARCHAR2(160 Bytes)

Yes

AP_VENDOR_NO_UI

VARCHAR2(10 Bytes)

Yes

VENDOR_NO_UI

VARCHAR2(10 Bytes)

Yes

ITEM_NAME

VARCHAR2(20 Bytes)

Yes

PLANNER

VARCHAR2(8 Bytes)

Yes

When I create the cube and browse

the data down to the most granular level (EDP_NO),and when the QTY_SOLD measure is less than

zero,SSAS reports it as being -1

larger.For example,if the value in the table is -3,then the cube browser reports it as -4.Summarized values are also off by a similar

amount.That is,if in the table the data is: -4, -3, -1,then the cube will report the total as

11.

I’m wondering if you’ve ever run

into a similar situation.I’ve verified

that the aggregation being used is SUM.

What’s weird about this is that I’m

trying to recreate a cube that already exists in SQL Server 2000.If I migrate the cube to 2005,it works correctly.It’s only when I create the cube manually

that I have this problem.

I appreciate any assistance you can offer.

Thanks,

Mike Hayes

Hi Mike,

Maybe there is incorrect integer truncation, when converting from the Oracle data type: NUMBER(10,0). Since a migrated cube works correctly, could you compare the DataType for the QTY_SOLD Measure in the 2 cubes? The possible DataType values are:

http://msdn2.microsoft.com/es-es/library/ms129408.aspx

>>

SQL Server 2005 Books Online

DataType Element (ASSL)

Defines the data type of the associated element.

...

The values for DataType are defined in the System.Data.OleDb.OleDbType enumeration. However, only the enumeration values in the following table are valid in the DataType element.

Value

Description

BigInt

A 64-bit signed integer. This data type maps to the Int64 data type in Microsoft .NET Framework and the DBTYPE_I8 data type in OLE DB.

Bool

A Boolean value. This data type maps to the Boolean data type in the .NET Framework and the DBTYPE_BOOL data type in OLE DB.

Currency

A currency value ranging from -2⁶³ (or -922,337,203,685,477.5808) to 2⁶³-1 (or +922,337,203,685,477.5807) with an accuracy to a ten-thousandth of a currency unit. This data type maps to the Decimal data type in the .NET Framework and the DBTYPE_CY data type in OLE DB.

Date

Date data, stored as a double-precision floating point number. The whole portion is the number of days since December 30, 1899, while the fractional portion is a fraction of a day. This data type maps to the DateTime data type in the .NET Framework and the DBTYPE_DATE data type in OLE DB.

Double

A double-precision floating point number within the range of -1.79E +308 through 1.79E +308. This data type maps to the Double data type in the .NET Framework and the DBTYPE_R8 data type in OLE DB.

Integer

A 32-bit signed integer. This data type maps to the Int32 data type in the .NET Framework and the DBTYPE_I4 data type in OLE DB.

Single

A single-precision floating point number within the range of -3.40E +38 through 3.40E +38. This data type maps to the Single data type in .NET Framework and the DBTYPE_R4 data type in OLE DB.

SmallInt

A 16-bit signed integer. This data type maps to the Int16 data type in the .NET Framework and the DBTYPE_I2 data type in OLE DB.

TinyInt

An 8-bit signed integer. This data type maps to the SByte data type in the .NET Framework and the DBTYPE_I1 data type in OLE DB.

UnsignedBigInt

A 64-bit unsigned integer. This data type maps to the UInt64 data type in .NET Framework and the DBTYPE_UI8 data type in OLE DB.

UnsignedInt

A 32-bit unsigned integer. This data type maps to the UInt32 data type in the .NET Framework and the DBTYPE_UI4 data type in OLE DB.

UnsignedSmallInt

A 16-bit unsigned integer. This data type maps to the UInt16 data type in the .NET Framework and the DBTYPE_UI2 data type in OLE DB.

WChar

A null-terminated stream of Unicode characters. This data type maps to the String data type in the .NET Framework and the DBTYPE_WSTR data type in OLE DB.

Inherited

The data type of the DataItem contained in the Source element of the Measure element.

Note:

Applicable only to Measure elements.

>>

|||Deepak,

Thanks for the information. I think you're on the right track. The data type of the column in the migrated cube is Double, and the data type of the created cube is Int64. They should both handle the math correctly. I'm in the process of testing this. I'm trying a different data source (using oledb drivers instead of the .net drivers). If that doesn't work, I'll explicitly change the data type using a query instead of a direct link to the table.

I appreciate the info. I'll let you know what happens.

Mike

Negative values

Hello Everyone,
I am writing a view and I have some negative values coming out of some
fields in one of my tables. They should be there, but when i retrive data i
need them to be non negative. I want to know if there is a function or if
there is a way to get rid of negaive sign.
Thank you allIf all are negatives, multiply the value with -1. Otherwise, you can use the
ABS function. Refer to SQL Server Books Online for more details.
--
-- Anith
( Please reply to newsgroups only )

negative space information

why does sp_spaceused report negative values for space?
exec sp_spaceused ResourceCompetency
name rows reserved
data index_size unused
----
ResourceCompetency 7602 -56 KB
816 KB 656 KB -1528 KBThis happens because of the inaccuracy exists between the sysindexes table.
Run
DBCC UPDATEUSAGE ... WITH COUNT_ROWS
to correct this inaccuracy.
--
-Vishal
"Will Mullen" <will.mullen@.windriver.com> wrote in message
news:01b101c3716c$768e9bd0$a101280a@.phx.gbl...
> why does sp_spaceused report negative values for space?
> exec sp_spaceused ResourceCompetency
> name rows reserved
> data index_size unused
> ----
> ResourceCompetency 7602 -56 KB
> 816 KB 656 KB -1528 KB
>

negative output from neural networks

I am getting negative predictions (continuous) from a neural network model that has been trained on data that only contains positive values or zeros (no nulls).

Is there a setting that can limit the lower end of the output range to zero?

Hello

There is no algorithm setting that can limit the outputs range. However, you could use a different query construct to get this result. Assuming that your predictable column is labeled [Petal Length], your query could look like:

SELECT VBA FROM [Model] PREDICTION JOIN ...

The [IIF] function is part of a set of built-in custom functions and it is defined as

IIF( boolean_condition, true_value, false_value)

Hope this helps

Needing 1st Non Null Value

I have a table that contains about 20 columns. However, each row may only have values in one or two columns while the rest are null. Basically, a new row is added each time a parameter is updated thus having a value for one column but not the others.

Here's an example of the data.

PatientID BloodPressure Pulse Temperature Location UpdateTime
1 120/80 NULL NULL NULL 3/8/07 11:33:00
1 NULL NULL 98.5 NULL 3/8/07 11:45:30
1 NULL 82 NULL NULL 3/9/07 6:20:15
1 NULL NULL NULL RM101 3/9/07 6:24:18
1 125/85 NULL NULL NULL 3/9/07 8:15:00
2 115/75 NULL NULL NULL 3/8/07 12:32:02

2 NULL NULL 99.5 NULL 3/8/07 12:45:30

2 NULL 88 NULL NULL 3/9/07 7:20:19

2 NULL NULL NULL RM108 3/9/07 6:24:18

2 119/85 NULL NULL NULL 3/9/07 8:16:00

I need an efficient query that will return the most recent non-null value for each patientID based on the UpdateTime. Efficiency is of utmost importance because there will be hundreds of patients with potentially thousands of records.

The result set should be:
PatientID BloodPressure Pulse Temperature Location
1 125/85 82 98.5 RM101
2 119/85 88 99.5 RM108

Any help will be greatly appreciated.

If you are using SQL Server 2005, this should work although I'm not sure how well it will perform

with cte as
(select PatientID,
BloodPressure,
row_number() over(partition by PatientID
order by case when BloodPressure is null then null else UpdateTime end desc) as BloodPressureRN,
Pulse,
row_number() over(partition by PatientID
order by case when Pulse is null then null else UpdateTime end desc) as PulseRN,
Temperature,
row_number() over(partition by PatientID
order by case when Temperature is null then null else UpdateTime end desc) as TemperatureRN,
Location,
row_number() over(partition by PatientID
order by case when Location is null then null else UpdateTime end desc) as LocationRN,
UpdateTime
from mytable)
select PatientID,
max(case when BloodPressureRN=1 then BloodPressure end) as BloodPressure,
max(case when PulseRN=1 then Pulse end) as Pulse,
max(case when TemperatureRN=1 then Temperature end) as Temperature,
max(case when LocationRN=1 then Location end) as Location
from cte
group by PatientID

|||

An alternative that works on SQL Server 2000 is:

declare @.basicStats table
( patientId integer not null,
bloodPressure varchar(8),
pulse smallint,
temperature numeric (4,1),
location varchar(12),
UpdateTime datetime
)

insert into @.basicStats values (1, '120/80', NULL, NULL, NULL, '3/8/07 11:33:00')
insert into @.basicStats values (1, NULL, NULL, 98.5, NULL, '3/8/07 11:45:30')
insert into @.basicStats values (1, NULL, 82, NULL, NULL, '3/9/07 6:20:15')
insert into @.basicStats values (1, NULL, NULL, NULL, 'RM101', '3/9/07 6:24:18')
insert into @.basicStats values (1, '125/85', NULL, NULL, NULL, '3/9/07 8:15:00')
insert into @.basicStats values (2, '115/75', NULL, NULL, NULL, '3/8/07 12:32:02')
insert into @.basicStats values (2, NULL, NULL, 99.5, NULL, '3/8/07 12:45:30')
insert into @.basicStats values (2, NULL, 88, NULL, NULL, '3/9/07 7:20:19')
insert into @.basicStats values (2, NULL, NULL, NULL, 'RM108', '3/9/07 6:24:18')
insert into @.basicStats values (2, '119/85', NULL, NULL, NULL, '3/9/07 8:16:00')

--select * from @.basicStats

select patientId,
( select top 1 BloodPressure from @.basicStats b
where b.patientId = a.patientId
and b.BloodPressure is not null
order by b.updateTime desc
) as BloodPressure,
( select top 1 pulse from @.basicStats c
where c.patientId = a.patientId
and c.pulse is not null
order by c.updateTime desc
) as pulse,
( select top 1 temperature from @.basicStats d
where d.patientId = a.patientId
and d.temperature is not null
order by d.updateTime desc
) as temperature,
( select top 1 location from @.basicStats e
where e.patientId = a.patientId
and e.location is not null
order by e.updateTime desc
) as location
from @.basicStats a
group by patientId

-- patientId BloodPressure pulse temperature location
-- -- - --
-- 1 125/85 82 98.5 RM101
-- 2 119/85 88 99.5 RM108

|||

And a third solution (sorry Kent, I pinched your table declaration and inserts for this one)...

This solution appears to be more expensive than Kent's solution, according to the execution plan on my machine. You'll probably experience the same, or you might not - it's just another option for you to try out on your hardware.

Chris

declare @.basicStats table

(

patientId integer not null,

bloodPressure varchar(8),

pulse smallint,

temperature numeric (4,1),

location varchar(12),

UpdateTime datetime

)

insert into @.basicStats values (1, '120/80', NULL, NULL, NULL, '3/8/07 11:33:00')

insert into @.basicStats values (1, NULL, NULL, 98.5, NULL, '3/8/07 11:45:30')

insert into @.basicStats values (1, NULL, 82, NULL, NULL, '3/9/07 6:20:15')

insert into @.basicStats values (1, NULL, NULL, NULL, 'RM101', '3/9/07 6:24:18')

insert into @.basicStats values (1, '125/85', NULL, NULL, NULL, '3/9/07 8:15:00')

insert into @.basicStats values (2, '115/75', NULL, NULL, NULL, '3/8/07 12:32:02')

insert into @.basicStats values (2, NULL, NULL, 99.5, NULL, '3/8/07 12:45:30')

insert into @.basicStats values (2, NULL, 88, NULL, NULL, '3/9/07 7:20:19')

insert into @.basicStats values (2, NULL, NULL, NULL, 'RM108', '3/9/07 6:24:18')

insert into @.basicStats values (2, '119/85', NULL, NULL, NULL, '3/9/07 8:16:00')

SELECT DISTINCT

p.PatientID,

pbp.BloodPressure,

ppu.Pulse,

pt.Temperature,

pl.Location

FROM @.basicStats p

INNER JOIN (SELECT PatientID, MAX(UpdateTime) AS UpdateTime FROM @.basicStats WHERE BloodPressure IS NOT NULL GROUP BY PatientID) bp ON bp.PatientID = p.PatientID

INNER JOIN @.basicStats pbp ON pbp.PatientID = bp.PatientID AND pbp.UpdateTime = bp.UpdateTime

INNER JOIN (SELECT PatientID, MAX(UpdateTime) AS UpdateTime FROM @.basicStats WHERE Pulse IS NOT NULL GROUP BY PatientID) pu ON pu.PatientID = p.PatientID

INNER JOIN @.basicStats ppu ON ppu.PatientID = bp.PatientID AND ppu.UpdateTime = pu.UpdateTime

INNER JOIN (SELECT PatientID, MAX(UpdateTime) AS UpdateTime FROM @.basicStats WHERE Temperature IS NOT NULL GROUP BY PatientID) t ON t.PatientID = p.PatientID

INNER JOIN @.basicStats pt ON pt.PatientID = bp.PatientID AND pt.UpdateTime = t.UpdateTime

INNER JOIN (SELECT PatientID, MAX(UpdateTime) AS UpdateTime FROM @.basicStats WHERE Location IS NOT NULL GROUP BY PatientID) l ON l.PatientID = p.PatientID

INNER JOIN @.basicStats pl ON pl.PatientID = bp.PatientID AND pl.UpdateTime = l.UpdateTime

|||

Gary:

Please forgive me, but this is a kind of query that I have struggled with. Since you are interested in optimum performance I think it woul be best if you could get Umachandar or someone similar to comment. I am just too afraid that I will get this wrong.

Kent

|||

Mark's solution will work fine except that you will end up sorting the entire table 4 times (once each to compute row number for bloodpressure, pulse, temperature and location). If you have a large table this will be problematic. The other solutions also suffer from the same problem. SQL Server unfortunately doesn't have a concept of filtered indexes which will help in this case. Database systems like PostgreSQL can do better job because you can create filtered index (on non-null value of each attribute you are interested). Similarly, in Oracle you can make use of index compression to reduce I/O costs due to repeated values (NULL) or use function-based indexes. DB2 also has some features that you can take advantage of for this type of schema.

In SQL Server you can use indexed views to achieve the filtered indexes functionality. So define one indexed view per property like:

create view basicStats_bloodPressure
with schemabinding
as
select patientId, bloodPressure, UpdateTime
from dbo.basicStats
where bloodPressure is not null
go
create unique clustered index idx_basicStats_bloodPressure on basicStats_bloodPressure(patientId, UpdateTime desc)
go

create view basicStats_pulse
with schemabinding
as
select patientId, pulse, UpdateTime
from dbo.basicStats
where pulse is not null
go
create unique clustered index idx_basicStats_pulse on basicStats_pulse(patientId, UpdateTime desc)
go

create view basicStats_temperature
with schemabinding
as
select patientId, temperature, UpdateTime
from dbo.basicStats
where temperature is not null
go
create unique clustered index idx_basicStats_temperature on basicStats_temperature(patientId, UpdateTime desc)
go

create view basicStats_location
with schemabinding
as
select patientId, location, UpdateTime
from dbo.basicStats
where location is not null
go
create unique clustered index idx_basicStats_location on basicStats_location(patientId, UpdateTime desc)
go

And create an index on the table too like:

create clustered index idx_basicstats_patientid on basicStats( patientId )

Now, your query is as simple as what Kent had and SQL Server will automatically use the indexed views.

select patientId,
( select top 1 BloodPressure from basicStats b
where b.patientId = a.patientId
and b.BloodPressure is not null
order by b.updateTime desc
) as BloodPressure,
( select top 1 pulse from basicStats c
where c.patientId = a.patientId
and c.pulse is not null
order by c.updateTime desc
) as pulse,
( select top 1 temperature from basicStats d
where d.patientId = a.patientId
and d.temperature is not null
order by d.updateTime desc
) as temperature,
( select top 1 location from basicStats e
where e.patientId = a.patientId
and e.location is not null
order by e.updateTime desc
) as location
from basicStats a
group by patientId;

-- or below referencing indexed views directly

select patientId
, (select top 1 t1.bloodPressure from basicStats_bloodPressure as t1 where t1.patientId = b.patientId order by t1.UpdateTime desc) as bloodPressure
, (select top 1 t1.pulse from basicStats_pulse as t1 where t1.patientId = b.patientId order by t1.UpdateTime desc) as pulse
, (select top 1 t1.temperature from basicStats_temperature as t1 where t1.patientId = b.patientId order by t1.UpdateTime desc) as temperature
, (select top 1 t1.location from basicStats_location as t1 where t1.patientId = b.patientId order by t1.UpdateTime desc) as location
from (
select distinct patientId
from basicStats
) as b

These indexed views will need to be maintained during DML operations. But this is probably the best way to get the performance for this query given the nature of your data. You do incur some additional storage costs.

Alternatively, you should simply partition the table by property and use my last query except each indexed view is a physical table and the main table only contains the patientId columns & others that are not partitioned:

select patientId
, (select top 1 t1.bloodPressure from basicStats_bloodPressure as t1 where t1.patientId = b.patientId order by t1.UpdateTime desc) as bloodPressure
, (select top 1 t1.pulse from basicStats_pulse as t1 where t1.patientId = b.patientId order by t1.UpdateTime desc) as pulse
, (select top 1 t1.temperature from basicStats_temperature as t1 where t1.patientId = b.patientId order by t1.UpdateTime desc) as temperature
, (select top 1 t1.location from basicStats_location as t1 where t1.patientId = b.patientId order by t1.UpdateTime desc) as location
from (
select distinct patientId
from basicStats
) as b

With this approach, you will save some storage also. DML operations are slightly complex and you need to do additional work instead of SQL Server with the indexed views approach. For example, if you insert any row then you need to first check if there is a row in the main table for that patientId first and then insert into the separate property table. This might work out better than the indexed views approach also. So test both and see. Indexed views approach however is straight forward and doesn't require much changes to your existing code.

|||( I knew it would be worth it; thanks, Umachandar. WOA! )|||Wow thanks guys for the advice. I'll try some of these and give some feedback.

Thanks again!

Gary B.

Need-a-Guru: Gaps in Sequences

I'm trying to find gaps in sequences of year + month values, but there's a
twist - I want to return the 'reverse' of the gaps; or the year + month
values that create a sequence Note below that a 'sequence' can be a single
month if it is bounded by 'gaps'; i.e., ('NJ', 2002, 5). Here's the DDL and
sample data:
-- DDL to create Coverage table
CREATE TABLE [dbo].[Coverage] (
[state] [char] (2),
[year] [smallint] NOT NULL ,
[month] [smallint] NOT NULL
)
GO
ALTER TABLE [dbo].[Coverage] WITH NOCHECK ADD
CONSTRAINT [PK_Coverage] PRIMARY KEY CLUSTERED
(
[state],
[year],
[month]
)
GO
--Sample Data
INSERT INTO Coverage VALUES ('NJ', 2001, 12)
INSERT INTO Coverage VALUES ('NJ', 2002, 1)
INSERT INTO Coverage VALUES ('NJ', 2002, 2)
INSERT INTO Coverage VALUES ('NJ', 2002, 3)
INSERT INTO Coverage VALUES ('NJ', 2002, 5)
INSERT INTO Coverage VALUES ('NJ', 2002, 9)
INSERT INTO Coverage VALUES ('NJ', 2002, 10)
INSERT INTO Coverage VALUES ('NJ', 2002, 11)
INSERT INTO Coverage VALUES ('NJ', 2002, 12)
INSERT INTO Coverage VALUES ('NJ', 2003, 1)
INSERT INTO Coverage VALUES ('NJ', 2003, 5)
INSERT INTO Coverage VALUES ('NY', 2001, 1)
INSERT INTO Coverage VALUES ('NY', 2001, 2)
INSERT INTO Coverage VALUES ('NY', 2002, 7)
INSERT INTO Coverage VALUES ('NY', 2002, 8)
GO
Here's the output I'm trying to get from the above data:
+--+--+--+--+--+
|State | StartYear | StartMonth | EndYear | EndMonth |
+--+--+--+--+--+
| NJ | 2001 | 12 | 2002 | 3 |
| NJ | 2002 | 5 | 2002 | 5 |
| NJ | 2002 | 9 | 2003 | 1 |
| NJ | 2003 | 5 | 2003 | 5 |
| NY | 2001 | 1 | 2001 | 2 |
| NY | 2002 | 7 | 2002 | 8 |
+--+--+--+--+--+
(Note: If the table doesn't line up right, copy and paste it into NotePad
in a fixed-width font).
As you can see from the above, I want to query and summarize the year +
month ranges for which coverage is available per state. TIA for any help on
this one!Ok, here's goes...
First you need to know the the entire sequence which you are interested in.
To Get this create a temporary table with the Year and Month Column. Then
insert the years and months which cover that entire range. The temp table
looks like this
Create Table #MyTemp( Year smallint not null, Month smallint not null )
Insert Into #MyTemp(Year, Month) Values (2001,1)
Insert Into #MyTemp(Year, Month) Values (2001,2)
:
Insert Into #MyTemp(Year, Month) Values (2003,11)
Insert Into #MyTemp(Year, Month) Values (2003,12)
Next you can perform a left outer join on your table to match your known
values to the range. Finally, adding a where clause to remove rows where
the joined table has no available row...
Select
a.Year,
a.Month
From #MyTemp a
Left Outer Join (Select Distinct
year,
month
From Coverage) c On c.year = a.Year
And c.month =
a.month
where c.year is null
Hope this helps
Regards
Colin Dawson
MCP.
www.cjdawson.com
"Michael C#" <howsa@.boutdat.com> wrote in message
news:eq$kbYFbFHA.1312@.TK2MSFTNGP09.phx.gbl...
> I'm trying to find gaps in sequences of year + month values, but there's a
> twist - I want to return the 'reverse' of the gaps; or the year + month
> values that create a sequence Note below that a 'sequence' can be a single
> month if it is bounded by 'gaps'; i.e., ('NJ', 2002, 5). Here's the DDL
> and sample data:
> -- DDL to create Coverage table
> CREATE TABLE [dbo].[Coverage] (
> [state] [char] (2),
> [year] [smallint] NOT NULL ,
> [month] [smallint] NOT NULL
> )
> GO
> ALTER TABLE [dbo].[Coverage] WITH NOCHECK ADD
> CONSTRAINT [PK_Coverage] PRIMARY KEY CLUSTERED
> (
> [state],
> [year],
> [month]
> )
> GO
> --Sample Data
> INSERT INTO Coverage VALUES ('NJ', 2001, 12)
> INSERT INTO Coverage VALUES ('NJ', 2002, 1)
> INSERT INTO Coverage VALUES ('NJ', 2002, 2)
> INSERT INTO Coverage VALUES ('NJ', 2002, 3)
> INSERT INTO Coverage VALUES ('NJ', 2002, 5)
> INSERT INTO Coverage VALUES ('NJ', 2002, 9)
> INSERT INTO Coverage VALUES ('NJ', 2002, 10)
> INSERT INTO Coverage VALUES ('NJ', 2002, 11)
> INSERT INTO Coverage VALUES ('NJ', 2002, 12)
> INSERT INTO Coverage VALUES ('NJ', 2003, 1)
> INSERT INTO Coverage VALUES ('NJ', 2003, 5)
> INSERT INTO Coverage VALUES ('NY', 2001, 1)
> INSERT INTO Coverage VALUES ('NY', 2001, 2)
> INSERT INTO Coverage VALUES ('NY', 2002, 7)
> INSERT INTO Coverage VALUES ('NY', 2002, 8)
> GO
> Here's the output I'm trying to get from the above data:
> +--+--+--+--+--+
> |State | StartYear | StartMonth | EndYear | EndMonth |
> +--+--+--+--+--+
> | NJ | 2001 | 12 | 2002 | 3 |
> | NJ | 2002 | 5 | 2002 | 5 |
> | NJ | 2002 | 9 | 2003 | 1 |
> | NJ | 2003 | 5 | 2003 | 5 |
> | NY | 2001 | 1 | 2001 | 2 |
> | NY | 2002 | 7 | 2002 | 8 |
> +--+--+--+--+--+
> (Note: If the table doesn't line up right, copy and paste it into NotePad
> in a fixed-width font).
> As you can see from the above, I want to query and summarize the year +
> month ranges for which coverage is available per state. TIA for any help
> on this one!
>|||How about this?
alter table Coverage add RangeDate smalldatetime null
go
update Coverage
set RangeDate = convert(smalldatetime, convert(varchar(4), year) + '-' +
convert(varchar(2), month) + '-01')
go
declare @.start table ([id] int identity(1, 1), state char(2), [year]
smallint, [month] tinyint)
declare @.end table ([id] int identity(1, 1), state char(2), [year] smallint,
[month] tinyint)
insert into @.start(state, [year], [month])
select c1.state, c1.[year], c1.[month]
from Coverage c1
left join Coverage c2 on c1.state = c2.state and c1.rangedate = dateadd(mm,
1, c2.rangedate)
where c2.state is null
insert into @.end(state, [year], [month])
select c1.state, c1.[year], c1.[month]
from Coverage c1
left join Coverage c2 on c1.state = c2.state and c1.rangedate = dateadd(mm,
-1, c2.rangedate)
where c2.state is null
select s.state, s.[year] as StartYear, s.[month] as StartMonth, e.[year] as
EndYear, e.[month] as EndMonth
from @.start s
join @.end e on s.[id] = e.[id] and s.state = e.state
"Michael C#" wrote:

> I'm trying to find gaps in sequences of year + month values, but there's a
> twist - I want to return the 'reverse' of the gaps; or the year + month
> values that create a sequence Note below that a 'sequence' can be a single
> month if it is bounded by 'gaps'; i.e., ('NJ', 2002, 5). Here's the DDL a
nd
> sample data:
> -- DDL to create Coverage table
> CREATE TABLE [dbo].[Coverage] (
> [state] [char] (2),
> [year] [smallint] NOT NULL ,
> [month] [smallint] NOT NULL
> )
> GO
> ALTER TABLE [dbo].[Coverage] WITH NOCHECK ADD
> CONSTRAINT [PK_Coverage] PRIMARY KEY CLUSTERED
> (
> [state],
> [year],
> [month]
> )
> GO
> --Sample Data
> INSERT INTO Coverage VALUES ('NJ', 2001, 12)
> INSERT INTO Coverage VALUES ('NJ', 2002, 1)
> INSERT INTO Coverage VALUES ('NJ', 2002, 2)
> INSERT INTO Coverage VALUES ('NJ', 2002, 3)
> INSERT INTO Coverage VALUES ('NJ', 2002, 5)
> INSERT INTO Coverage VALUES ('NJ', 2002, 9)
> INSERT INTO Coverage VALUES ('NJ', 2002, 10)
> INSERT INTO Coverage VALUES ('NJ', 2002, 11)
> INSERT INTO Coverage VALUES ('NJ', 2002, 12)
> INSERT INTO Coverage VALUES ('NJ', 2003, 1)
> INSERT INTO Coverage VALUES ('NJ', 2003, 5)
> INSERT INTO Coverage VALUES ('NY', 2001, 1)
> INSERT INTO Coverage VALUES ('NY', 2001, 2)
> INSERT INTO Coverage VALUES ('NY', 2002, 7)
> INSERT INTO Coverage VALUES ('NY', 2002, 8)
> GO
> Here's the output I'm trying to get from the above data:
> +--+--+--+--+--+
> |State | StartYear | StartMonth | EndYear | EndMonth |
> +--+--+--+--+--+
> | NJ | 2001 | 12 | 2002 | 3 |
> | NJ | 2002 | 5 | 2002 | 5 |
> | NJ | 2002 | 9 | 2003 | 1 |
> | NJ | 2003 | 5 | 2003 | 5 |
> | NY | 2001 | 1 | 2001 | 2 |
> | NY | 2002 | 7 | 2002 | 8 |
> +--+--+--+--+--+
> (Note: If the table doesn't line up right, copy and paste it into NotePad
> in a fixed-width font).
> As you can see from the above, I want to query and summarize the year +
> month ranges for which coverage is available per state. TIA for any help
on
> this one!
>
>|||Michael,
Here's one solution. I've added a computed column to hold the date
as one unit, so you'll have to stick in the expression if you can't do that.
CREATE TABLE [dbo].[Coverage] (
[state] [char] (2) NOT NULL,
[year] [smallint] NOT NULL ,
[month] [smallint] NOT NULL
)
GO
ALTER TABLE [dbo].[Coverage] WITH NOCHECK ADD
CONSTRAINT [PK_Coverage] PRIMARY KEY CLUSTERED
(
[state],
[year],
[month]
)
GO
--Sample Data
INSERT INTO Coverage VALUES ('NJ', 2001, 12)
INSERT INTO Coverage VALUES ('NJ', 2002, 1)
INSERT INTO Coverage VALUES ('NJ', 2002, 2)
INSERT INTO Coverage VALUES ('NJ', 2002, 3)
INSERT INTO Coverage VALUES ('NJ', 2002, 5)
INSERT INTO Coverage VALUES ('NJ', 2002, 9)
INSERT INTO Coverage VALUES ('NJ', 2002, 10)
INSERT INTO Coverage VALUES ('NJ', 2002, 11)
INSERT INTO Coverage VALUES ('NJ', 2002, 12)
INSERT INTO Coverage VALUES ('NJ', 2003, 1)
INSERT INTO Coverage VALUES ('NJ', 2003, 5)
INSERT INTO Coverage VALUES ('NY', 2001, 1)
INSERT INTO Coverage VALUES ('NY', 2001, 2)
INSERT INTO Coverage VALUES ('NY', 2002, 7)
INSERT INTO Coverage VALUES ('NY', 2002, 8)
GO
alter table Coverage add ymd as
dateadd(year,[year]-1990,dateadd(month,[month]-1,'19900101'))
go
select
state,
min(ymd) as Startym,
max(ymd) as Endym
from (
select
C1.state,
C1.ymd,
count(C2.ymd)-datediff(month,'19900101',C1.ymd) as rk
from Coverage as C1
join Coverage as C2
on C1.state = C2.state
and C2.ymd <= C1.ymd
group by
C1.state,
C1.ymd
) T
group by state, rk
order by state, Startym
go
drop table Coverage
-- Steve Kass
-- Drew University
Michael C# wrote:

>I'm trying to find gaps in sequences of year + month values, but there's a
>twist - I want to return the 'reverse' of the gaps; or the year + month
>values that create a sequence Note below that a 'sequence' can be a single
>month if it is bounded by 'gaps'; i.e., ('NJ', 2002, 5). Here's the DDL an
d
>sample data:
>-- DDL to create Coverage table
>CREATE TABLE [dbo].[Coverage] (
> [state] [char] (2),
> [year] [smallint] NOT NULL ,
> [month] [smallint] NOT NULL
> )
>GO
>ALTER TABLE [dbo].[Coverage] WITH NOCHECK ADD
> CONSTRAINT [PK_Coverage] PRIMARY KEY CLUSTERED
> (
> [state],
> [year],
> [month]
> )
>GO
>--Sample Data
>INSERT INTO Coverage VALUES ('NJ', 2001, 12)
>INSERT INTO Coverage VALUES ('NJ', 2002, 1)
>INSERT INTO Coverage VALUES ('NJ', 2002, 2)
>INSERT INTO Coverage VALUES ('NJ', 2002, 3)
>INSERT INTO Coverage VALUES ('NJ', 2002, 5)
>INSERT INTO Coverage VALUES ('NJ', 2002, 9)
>INSERT INTO Coverage VALUES ('NJ', 2002, 10)
>INSERT INTO Coverage VALUES ('NJ', 2002, 11)
>INSERT INTO Coverage VALUES ('NJ', 2002, 12)
>INSERT INTO Coverage VALUES ('NJ', 2003, 1)
>INSERT INTO Coverage VALUES ('NJ', 2003, 5)
>INSERT INTO Coverage VALUES ('NY', 2001, 1)
>INSERT INTO Coverage VALUES ('NY', 2001, 2)
>INSERT INTO Coverage VALUES ('NY', 2002, 7)
>INSERT INTO Coverage VALUES ('NY', 2002, 8)
>GO
>Here's the output I'm trying to get from the above data:
>+--+--+--+--+--+
>|State | StartYear | StartMonth | EndYear | EndMonth |
>+--+--+--+--+--+
>| NJ | 2001 | 12 | 2002 | 3 |
>| NJ | 2002 | 5 | 2002 | 5 |
>| NJ | 2002 | 9 | 2003 | 1 |
>| NJ | 2003 | 5 | 2003 | 5 |
>| NY | 2001 | 1 | 2001 | 2 |
>| NY | 2002 | 7 | 2002 | 8 |
>+--+--+--+--+--+
>(Note: If the table doesn't line up right, copy and paste it into NotePad
>in a fixed-width font).
>As you can see from the above, I want to query and summarize the year +
>month ranges for which coverage is available per state. TIA for any help o
n
>this one!
>
>|||Thanks Colin, but that actually just returns a simple list of the year +
month combos that are not in the Coverage table, like this:
+--+--+
| Year | Month |
+--+--+
| 2001 | 3 |
| 2001 | 4 |
| 2001 | 5 |
| 2001 | 6 |
| 2001 | 7 |
| ... | ... |
+--+--+
This might be a first step toward getting the output I'm looking for (see
below), but it's not quite the same. Thanks.
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:ENGpe.16958$bl3.10718@.fe1.news.blueyonder.co.uk...
> Ok, here's goes...
> First you need to know the the entire sequence which you are interested
> in. To Get this create a temporary table with the Year and Month Column.
> Then insert the years and months which cover that entire range. The
> temp table looks like this
> Create Table #MyTemp( Year smallint not null, Month smallint not null )
> Insert Into #MyTemp(Year, Month) Values (2001,1)
> Insert Into #MyTemp(Year, Month) Values (2001,2)
> :
> Insert Into #MyTemp(Year, Month) Values (2003,11)
> Insert Into #MyTemp(Year, Month) Values (2003,12)
> Next you can perform a left outer join on your table to match your known
> values to the range. Finally, adding a where clause to remove rows where
> the joined table has no available row...
> Select
> a.Year,
> a.Month
> From #MyTemp a
> Left Outer Join (Select Distinct
> year,
> month
> From Coverage) c On c.year = a.Year
> And c.month =
> a.month
> where c.year is null
> Hope this helps
> Regards
> Colin Dawson
> MCP.
> www.cjdawson.com
>
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:eq$kbYFbFHA.1312@.TK2MSFTNGP09.phx.gbl...
>|||Perfect! Thank you!
"Cris_Benge" <CrisBenge@.discussions.microsoft.com> wrote in message
news:1EDB2A8A-E817-43EA-95C1-491ABB3D2995@.microsoft.com...
> How about this?
> alter table Coverage add RangeDate smalldatetime null
> go
> update Coverage
> set RangeDate = convert(smalldatetime, convert(varchar(4), year) + '-' +
> convert(varchar(2), month) + '-01')
> go
> declare @.start table ([id] int identity(1, 1), state char(2), [year]
> smallint, [month] tinyint)
> declare @.end table ([id] int identity(1, 1), state char(2), [year]
> smallint,
> [month] tinyint)
> insert into @.start(state, [year], [month])
> select c1.state, c1.[year], c1.[month]
> from Coverage c1
> left join Coverage c2 on c1.state = c2.state and c1.rangedate =
> dateadd(mm,
> 1, c2.rangedate)
> where c2.state is null
> insert into @.end(state, [year], [month])
> select c1.state, c1.[year], c1.[month]
> from Coverage c1
> left join Coverage c2 on c1.state = c2.state and c1.rangedate =
> dateadd(mm,
> -1, c2.rangedate)
> where c2.state is null
> select s.state, s.[year] as StartYear, s.[month] as StartMonth, e.[year]
> as
> EndYear, e.[month] as EndMonth
> from @.start s
> join @.end e on s.[id] = e.[id] and s.state = e.state
>
> "Michael C#" wrote:
>|||Thanks Steve! Excellent solution!
"Steve Kass" <skass@.drew.edu> wrote in message
news:uzjPu6FbFHA.3328@.TK2MSFTNGP10.phx.gbl...
> Michael,
> Here's one solution. I've added a computed column to hold the date
> as one unit, so you'll have to stick in the expression if you can't do
> that.
> CREATE TABLE [dbo].[Coverage] (
> [state] [char] (2) NOT NULL,
> [year] [smallint] NOT NULL ,
> [month] [smallint] NOT NULL
> )
> GO
> ALTER TABLE [dbo].[Coverage] WITH NOCHECK ADD
> CONSTRAINT [PK_Coverage] PRIMARY KEY CLUSTERED
> (
> [state],
> [year],
> [month]
> )
> GO
> --Sample Data
> INSERT INTO Coverage VALUES ('NJ', 2001, 12)
> INSERT INTO Coverage VALUES ('NJ', 2002, 1)
> INSERT INTO Coverage VALUES ('NJ', 2002, 2)
> INSERT INTO Coverage VALUES ('NJ', 2002, 3)
> INSERT INTO Coverage VALUES ('NJ', 2002, 5)
> INSERT INTO Coverage VALUES ('NJ', 2002, 9)
> INSERT INTO Coverage VALUES ('NJ', 2002, 10)
> INSERT INTO Coverage VALUES ('NJ', 2002, 11)
> INSERT INTO Coverage VALUES ('NJ', 2002, 12)
> INSERT INTO Coverage VALUES ('NJ', 2003, 1)
> INSERT INTO Coverage VALUES ('NJ', 2003, 5)
> INSERT INTO Coverage VALUES ('NY', 2001, 1)
> INSERT INTO Coverage VALUES ('NY', 2001, 2)
> INSERT INTO Coverage VALUES ('NY', 2002, 7)
> INSERT INTO Coverage VALUES ('NY', 2002, 8)
> GO
> alter table Coverage add ymd as
> dateadd(year,[year]-1990,dateadd(month,[month]-1,'19900101'))
> go
> select
> state,
> min(ymd) as Startym,
> max(ymd) as Endym
> from (
> select
> C1.state,
> C1.ymd,
> count(C2.ymd)-datediff(month,'19900101',C1.ymd) as rk
> from Coverage as C1
> join Coverage as C2
> on C1.state = C2.state
> and C2.ymd <= C1.ymd
> group by
> C1.state,
> C1.ymd
> ) T
> group by state, rk
> order by state, Startym
> go
> drop table Coverage
> -- Steve Kass
> -- Drew University
> Michael C# wrote:
>|||Hey Steve,
I was playing around with your solution, and I added a county column to the
table as well as the state. Problem is that now for some reason it kicks
back many of the dates separately (but not all of them?) For instance:
+-+--+--+--+--+--+--+
|State | County | StartYear | StartMonth | EndYear | EndMonth |
+--+--+--+--+--+--+
| NJ | 001 | 2001 | 12 | 2001 | 12 |
| NJ | 001 | 2002 | 1 | 2002 | 1 |
| NJ | 001 | 2002 | 2 | 2002 | 2 |
| ... | ... | ... | ... | ... | ... |
+--+--+--+--+--+--+
I'm using a CHAR(3) standard 3-digit FIPS code for the County column. For
some reason I'm getting some dates that should be rolled up as one range
coming out as multiple one-month ranges. I can't post the code right now,
but will get it up there as soon as I get a chance. Thanks!
"Steve Kass" <skass@.drew.edu> wrote in message
news:uzjPu6FbFHA.3328@.TK2MSFTNGP10.phx.gbl...
> Michael,
> Here's one solution. I've added a computed column to hold the date
> as one unit, so you'll have to stick in the expression if you can't do
> that.
> CREATE TABLE [dbo].[Coverage] (
> [state] [char] (2) NOT NULL,
> [year] [smallint] NOT NULL ,
> [month] [smallint] NOT NULL
> )
> GO
> ALTER TABLE [dbo].[Coverage] WITH NOCHECK ADD
> CONSTRAINT [PK_Coverage] PRIMARY KEY CLUSTERED
> (
> [state],
> [year],
> [month]
> )
> GO
> --Sample Data
> INSERT INTO Coverage VALUES ('NJ', 2001, 12)
> INSERT INTO Coverage VALUES ('NJ', 2002, 1)
> INSERT INTO Coverage VALUES ('NJ', 2002, 2)
> INSERT INTO Coverage VALUES ('NJ', 2002, 3)
> INSERT INTO Coverage VALUES ('NJ', 2002, 5)
> INSERT INTO Coverage VALUES ('NJ', 2002, 9)
> INSERT INTO Coverage VALUES ('NJ', 2002, 10)
> INSERT INTO Coverage VALUES ('NJ', 2002, 11)
> INSERT INTO Coverage VALUES ('NJ', 2002, 12)
> INSERT INTO Coverage VALUES ('NJ', 2003, 1)
> INSERT INTO Coverage VALUES ('NJ', 2003, 5)
> INSERT INTO Coverage VALUES ('NY', 2001, 1)
> INSERT INTO Coverage VALUES ('NY', 2001, 2)
> INSERT INTO Coverage VALUES ('NY', 2002, 7)
> INSERT INTO Coverage VALUES ('NY', 2002, 8)
> GO
> alter table Coverage add ymd as
> dateadd(year,[year]-1990,dateadd(month,[month]-1,'19900101'))
> go
> select
> state,
> min(ymd) as Startym,
> max(ymd) as Endym
> from (
> select
> C1.state,
> C1.ymd,
> count(C2.ymd)-datediff(month,'19900101',C1.ymd) as rk
> from Coverage as C1
> join Coverage as C2
> on C1.state = C2.state
> and C2.ymd <= C1.ymd
> group by
> C1.state,
> C1.ymd
> ) T
> group by state, rk
> order by state, Startym
> go
> drop table Coverage
> -- Steve Kass
> -- Drew University
<<SNIP>>|||>> I was playing around with your solution, and I added a county column to
You can use the same logic as Steve posted for additional columns:
CREATE VIEW Coverages ( state, county, dt, diff )
AS
SELECT state, County,
CAST( STR( c1."month", 2 ) + '/01/'
+ STR( c1."year", 4 ) AS DATETIME ),
( SELECT COUNT( * )
FROM Coverage c2
WHERE c1.state = c2.state AND c1.County = c2.County
AND CAST( STR( c2."month", 2 ) + '/01/'
+ STR( c2."year", 4 ) AS DATETIME )
<= CAST( STR( c1."month", 2 ) + '/01/'
+ STR( c1."year", 4 ) AS DATETIME ) )
FROM Coverage c1
GO
Now you can do:
SELECT state, county,
YEAR( MIN( dt ) ) AS "StartYear",
MONTH( MIN( dt ) ) AS "StartMonth",
YEAR( MAX( dt ) ) AS "EndYear",
MONTH( MAX( dt ) ) AS "EndMonth",
FROM Coverages
GROUP BY state, county, diff - DATEDIFF( m, 0, dt ) ;
Anith|||Just got home and saw your message. I'll try your view tomorrow. I tried
adding the County to Steve's code very similarly to what you did in the
view, but for some reason it kicked out the strange results I posted
previously. I'll post more information and the results of trying your
method when I get into the office tomorrow. I'm thinking that the data I'm
using might be screwy, I'm missing something in the DateDiff operation, or I
just completely screwed the pooch on this one :). I'll post more tomorrow.
Thanks for the help guys!
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:u$pQceHbFHA.724@.TK2MSFTNGP12.phx.gbl...
> You can use the same logic as Steve posted for additional columns:
> CREATE VIEW Coverages ( state, county, dt, diff )
> AS
> SELECT state, County,
> CAST( STR( c1."month", 2 ) + '/01/'
> + STR( c1."year", 4 ) AS DATETIME ),
> ( SELECT COUNT( * )
> FROM Coverage c2
> WHERE c1.state = c2.state AND c1.County = c2.County
> AND CAST( STR( c2."month", 2 ) + '/01/'
> + STR( c2."year", 4 ) AS DATETIME )
> <= CAST( STR( c1."month", 2 ) + '/01/'
> + STR( c1."year", 4 ) AS DATETIME ) )
> FROM Coverage c1
> GO
> Now you can do:
> SELECT state, county,
> YEAR( MIN( dt ) ) AS "StartYear",
> MONTH( MIN( dt ) ) AS "StartMonth",
> YEAR( MAX( dt ) ) AS "EndYear",
> MONTH( MAX( dt ) ) AS "EndMonth",
> FROM Coverages
> GROUP BY state, county, diff - DATEDIFF( m, 0, dt ) ;
> --
> Anith
>

Need View to return "NULL" values

I have a view that is joining multiple tables. How do I modify this view so
that it also returns when the value is "NULL" for some of the join table
fields?
Any help would be greatly appreciated!!!
Thank you,
-Valerie
SELECT TOP 100 PERCENT dbo.PATIENTMEDICATION.PatientMedicationID,
dbo.MEDICATION.MedicationDS, dbo.MEDICATION.MedicationID,
dbo.DOSAGEUNIT.DosageUnitDS,
dbo.PATIENTMEDICATION.DosageUnitID, dbo.PATIENTMEDICATION.Dosage,
dbo.DURATIONUNIT.DurationUnitDS,
dbo.PATIENTMEDICATION.DurationUnitID,
dbo.PATIENTMEDICATION.Duration, dbo.PATIENTMEDICATION.BegunDT,
dbo.PATIENTMEDICATION.DiscontinuedDT,
dbo.PATIENTMEDICATION.ActiveYN, dbo.PATIENTMEDICATION.VisitID,
dbo.PATIENTMEDICATION.PatientID
FROM dbo.PATIENTMEDICATION INNER JOIN
dbo.MEDICATION ON dbo.PATIENTMEDICATION.MedicationID =
dbo.MEDICATION.MedicationID INNER JOIN
dbo.DOSAGEUNIT ON dbo.PATIENTMEDICATION.DosageUnitID =
dbo.DOSAGEUNIT.DosageUnitID INNER JOIN
dbo.DURATIONUNIT ON
dbo.PATIENTMEDICATION.DurationUnitID = dbo.DURATIONUNIT.DurationUnitID
ORDER BY dbo.PATIENTMEDICATION.VisitID, dbo.PATIENTMEDICATION.ActiveYN DESC,
dbo.PATIENTMEDICATION.PatientMedicationIDTake a look at LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL OUTER JOIN
instructions. This is probably what you want.
The use of an UNION query can also be usefull for this kind of problem.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"kvrdev1" <kvrdev1@.discussions.microsoft.com> wrote in message
news:B11D4FE8-5F1F-462D-8BC6-DDD168C7A641@.microsoft.com...
>I have a view that is joining multiple tables. How do I modify this view
>so
> that it also returns when the value is "NULL" for some of the join table
> fields?
> Any help would be greatly appreciated!!!
> Thank you,
> -Valerie
> SELECT TOP 100 PERCENT dbo.PATIENTMEDICATION.PatientMedicationID,
> dbo.MEDICATION.MedicationDS, dbo.MEDICATION.MedicationID,
> dbo.DOSAGEUNIT.DosageUnitDS,
> dbo.PATIENTMEDICATION.DosageUnitID, dbo.PATIENTMEDICATION.Dosage,
> dbo.DURATIONUNIT.DurationUnitDS,
> dbo.PATIENTMEDICATION.DurationUnitID,
> dbo.PATIENTMEDICATION.Duration, dbo.PATIENTMEDICATION.BegunDT,
> dbo.PATIENTMEDICATION.DiscontinuedDT,
> dbo.PATIENTMEDICATION.ActiveYN, dbo.PATIENTMEDICATION.VisitID,
> dbo.PATIENTMEDICATION.PatientID
> FROM dbo.PATIENTMEDICATION INNER JOIN
> dbo.MEDICATION ON dbo.PATIENTMEDICATION.MedicationID
> =
> dbo.MEDICATION.MedicationID INNER JOIN
> dbo.DOSAGEUNIT ON dbo.PATIENTMEDICATION.DosageUnitID
> =
> dbo.DOSAGEUNIT.DosageUnitID INNER JOIN
> dbo.DURATIONUNIT ON
> dbo.PATIENTMEDICATION.DurationUnitID = dbo.DURATIONUNIT.DurationUnitID
> ORDER BY dbo.PATIENTMEDICATION.VisitID, dbo.PATIENTMEDICATION.ActiveYN
> DESC,
> dbo.PATIENTMEDICATION.PatientMedicationID|||>> I have a view that is joining multiple tables. How do I modify this vie=
w so that it also returns when the value is "NULL" for some of the join tab=
le fields [sic]? <<
Fileds and columns are different; one of the MANY differences is that a
column can have a NULL. You probably want an OUTER JOIN, but what you
posted looks wrong from a design viewpoint. If we had DDL we could
more.
1) What is a "medication_id" -- don't you have an industry standard
drug code? I seem to remember that such a thing exists.
2) What makes "medication_id" in medication a totally different thing
from a "patient_m=ADedication_id"? You never, never give the same
data elements different names in the same schema. And doing by
physical storage locations is really bad.
3) Why are units of measuresment modeled as entities and not
attributes? Do you see a gram walking around, independent of an
entity? Unless units of time and medication change independently and
frequently, they ought to be part of the dosage, not entities. You
might want to a add a CHECK() constrint to the DDL to assure this
attribute is correct.
4) Did you actually use an 'y/n' flag in SQL like we did with punch
cards? Remember the rule about storing computed data values? Do not
do it.
5) Can you explain the logical differences between a
"duration=AD_unit_id" and mere "duration=AD_unit" (ditto dosage)? If
you read any book on data modeling or ISO-11179 that first name is
absurd. An identifier gives you unique entity and unit is a scale for
an attribute; entities are not attributes.
6) Why do you have duration, start and discontinue times in the table?
You can compute duration, can't you?|||Thank you! The LEFT OUTER JOIN was exactly what I needed. You have made my
day. :-)
Thanks again,
Valerie
"Sylvain Lafontaine" wrote:

> Take a look at LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL OUTER JOIN
> instructions. This is probably what you want.
> The use of an UNION query can also be usefull for this kind of problem.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
> "kvrdev1" <kvrdev1@.discussions.microsoft.com> wrote in message
> news:B11D4FE8-5F1F-462D-8BC6-DDD168C7A641@.microsoft.com...
>
>|||Thank you for your input - you mentioned many good points/topics; most of
which are already taken into consideration with our current data model.
"--CELKO--" wrote:

> Fileds and columns are different; one of the MANY differences is that a
> column can have a NULL. You probably want an OUTER JOIN, but what you
> posted looks wrong from a design viewpoint. If we had DDL we could
> more.
> 1) What is a "medication_id" -- don't you have an industry standard
> drug code? I seem to remember that such a thing exists.
> 2) What makes "medication_id" in medication a totally different thing
> from a "patient_m_edication_id"? You never, never give the same
> data elements different names in the same schema. And doing by
> physical storage locations is really bad.
> 3) Why are units of measuresment modeled as entities and not
> attributes? Do you see a gram walking around, independent of an
> entity? Unless units of time and medication change independently and
> frequently, they ought to be part of the dosage, not entities. You
> might want to a add a CHECK() constrint to the DDL to assure this
> attribute is correct.
> 4) Did you actually use an 'y/n' flag in SQL like we did with punch
> cards? Remember the rule about storing computed data values? Do not
> do it.
> 5) Can you explain the logical differences between a
> "duration__unit_id" and mere "duration__unit" (ditto dosage)? If
> you read any book on data modeling or ISO-11179 that first name is
> absurd. An identifier gives you unique entity and unit is a scale for
> an attribute; entities are not attributes.
> 6) Why do you have duration, start and discontinue times in the table?
> You can compute duration, can't you?
>

need to write from asp.net to XML type in SQL Server 2005

Hello, I have worked with SQL Server 2000 but now we have a requirement where I need to write values from an asp.net form into an XML type in SQL Server 2005.

I have never used XML as a type in SQL Server 2005. How do we write xml into xml type.

For example the structure of XML is something like:

<application>

<applicationID = "value"></applicationID>

<customerName="value></customerName>

</application>

I have to write this kind of XML into the XML type and later retrieve these XML values and populate the form again.

Kindly suggest. Thanks a lot.

You will need to look into several things to help you achieve this.

Look into the System.Xml.XmlTextWriter to help you construct XML valid strings

Also look into System.IO.StreamWriter to write the stream of data

You can then use methods of the XmlTextWriter to write the elements, attributes based on your XML structure; WriteStartDocument(), WriteStartElement(), WriteElementString();

Need to update all but first record in group by

I'd like to use an UPDATE statement to set 'Y' and 'N' values to a flag
field in a table called SVCrossSales for each set of records keyed by
the CSNum field. The first record in each group would get a CSFlg
value of 'N' and the subsequent records would get a 'Y'. Here's a
sample of what the CSFlg values should end up being for each CSNum:
INum CSNum CSFlg
-- -- --
2 1 N
2 1 Y
2 1 Y
2 2 N
2 2 Y
2 3 N
2 3 Y
2 3 Y
Instead of using a cursor to cycle through each CSNum, I'd like to use
an UPDATE statement with an aggregate join. I can't think of anything
else except using TOP to pick out the first record in the set.
Obviously, if I can set the 'N' values for CSFlg, I would do a simple
UPDATE to set the 'Y' values for the rest of the records.
This UPDATE fails:
UPDATE
SVCrossSales
SET
CSFlg = 'N'
FROM
SVCrossSales
INNER JOIN (
SELECT TOP 1 CSNum
FROM SVCrossSales
WHERE INum = 2
GROUP BY CSNum) GroupedSales ON (
SVCrossSales.CSNum = GroupedSales.CSNum)
WHERE
SVCrossSales.INum = 2
What is the proper syntax to do my UPDATE?>> I'd like to use an UPDATE statement to set 'Y' and 'N' values to a flag
Since there is no column values that can uniquely identify a row, this is
logically impossible.
Since a well designed table is a set of rows, the concept of
first/second/last etc. does not really apply. In a relational database,
you'd identify a row using the key values, not by positional descriptions.
TOP 1 without ORDER BY clause relies on certain internal materialization of
values, and it is much better to have
Once you have the keys & constraints explicitly declared, you can formulate
an UPDATE statement along the lines of:
UPDATE SVCrossSales
SET CSFlg = 'N'
WHERE EXISTS ( SELECT *
FROM SVCrossSales s1
WHERE s1.INum = SVCrossSales .INum
AND <predicate involving keys> )
AND INum = 2 ;
Anith|||Anith, I'm not sure how a WHERE EXISTS will help. It returns a boolean
result (true or false). How can that help identify the first record of
each set of CSNums? I need something to pick out the records marked
with an x below and set the CSFlg = 'N':
INum CSNum CSFlg
-- -- --
x 2 1 N
2 1 Y
2 1 Y
x 2 2 N
2 2 Y
x 2 3 N
2 3 Y
2 3 Y|||Steve,
I am not sure if you have taken time to read what I wrote. Note that it is
impossible to accomplish what you are asking to do, since there is no
logical way of identify a row without a key.
Also, you have not posted you table structures & sample data (
www.aspfaq.com/5006 ). It is not clear whether the sample data you posted
includes all the columns in the table or not. It is not clear whether you
have keys and constraints in your table.
The UPDATE statement I posted include a correlation denoted as <predicate
involving keys>. This is the most critical part is solving your problem. If
you have no keys, all bets are off.
Anith|||Sorry...should have posted this to begin with...
DDL for the SVCrossSales table:
CREATE TABLE [dbo].[SVCrossSales] (
[INum] [int] NOT NULL ,
[SalesNum] [int] NOT NULL ,
[CSNum] [int] NULL ,
[CSFlg] [char] (1) NULL ,
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SVCrossSales] WITH NOCHECK ADD
CONSTRAINT [PK_SVCrossSales] PRIMARY KEY CLUSTERED
(
[INum],
[SalesNum]
) ON [PRIMARY]
GO
The INum is an ID field used in various tables of ours. The SalesNum
field is like an indentity seed in that it's a unique value for each
record in the table. Together, with INum, it forms the primary key for
the SVCrossSales table. I have a stored procedure that updates the
CSNum and then cycles through a cursor to update the CSFlg for each set
of recods with the same CSNum.
Sample data:
INum SalesNum CSNum CSFlg
-- -- -- --
2 333898 13295 N
2 333899 13295 Y
2 334366 13295 Y
2 335567 13269 N
2 335578 13269 Y
2 335579 13269 Y
2 336120 13255 N
2 336121 13255 Y
2 336122 13255 Y
So, is there a way to use a single update to set the 'N' values?|||I should have also mentioned that the INum and SalesNum fields link to
a sales table (SVSales) where my stored procedure's logic figures out
which sales to group together and assign the next available CSNum to in
SVCrossSales. The last step in the sproc is to update the CSFlg.|||Based on the sample data you posted, if the CSFlag column already has the
values 'Y', then do:
UPDATE SVCrossSales
SET CSFlg = 'N'
WHERE NOT EXISTS ( SELECT *
FROM SVCrossSales s1
WHERE s1.INum = SVCrossSales.INum
AND s1.CSNum = SVCrossSales.CSNum
AND s1.SalesNum < SVCrossSales.SalesNum )
AND INum = 2 ;
If both the values 'Y' and 'N' are to be set, then do:
UPDATE SVCrossSales
SET CSFlg = CASE WHEN EXISTS ( SELECT *
FROM SVCrossSales s1
WHERE s1.INum = SVCrossSales.INum
AND s1.CSNum = SVCrossSales.CSNum
AND s1.SalesNum <
SVCrossSales.SalesNum )
THEN 'Y' ELSE 'N' END
WHERE INum = 2 ;
You can re-write both these statements using a maxima function, but this
should give you what you are looking for.
Anith|||I replaced the cursor logic for updating CSFlg with the UPDATE
statement above. It didn't work. Also, it more than doubled the
execution time of the sproc. I don't understand what the EXISTS
subquery does. Shouldn't there be something to select the top 1 record
for each group set of records with the same CSNum?|||>> I replaced the cursor logic for updating CSFlg with the UPDATE statement
You will have to expand on that. Here is the data before & after update
based on the sample data you posted.
-- Before update
SELECT * FROM SVCrossSales ORDER BY INum, CSNum DESC, SalesNum
INum SalesNum CSNum CSFlg
-- -- -- --
2 333898 13295 Y
2 333899 13295 Y
2 334366 13295 Y
2 335567 13269 Y
2 335578 13269 Y
2 335579 13269 Y
2 336120 13255 Y
2 336121 13255 Y
2 336122 13255 Y
-- Do the update
UPDATE SVCrossSales
SET CSFlg = 'N'
WHERE NOT EXISTS ( SELECT *
FROM SVCrossSales s1
WHERE s1.INum = SVCrossSales.INum
AND s1.CSNum = SVCrossSales.CSNum
AND s1.SalesNum < SVCrossSales.SalesNum )
AND INum = 2 ;
-- After update
SELECT * FROM SVCrossSales ORDER BY INum, CSNum DESC, SalesNum
INum SalesNum CSNum CSFlg
-- -- -- --
2 333898 13295 N
2 333899 13295 Y
2 334366 13295 Y
2 335567 13269 N
2 335578 13269 Y
2 335579 13269 Y
2 336120 13255 N
2 336121 13255 Y
2 336122 13255 Y
That might have something to do with the overall construction of the stored
procedure and suboptimal indexing.
Well, as I said, there are several ways you can derive the solution. In this
case, you can use TOP 1 as well like:
UPDATE SVCrossSales
SET CSFlg = 'N'
WHERE SalesNum = ( SELECT TOP 1 s1.SalesNum
FROM SVCrossSales s1
WHERE s1.INum = SVCrossSales.INum
AND s1.CSNum = SVCrossSales.CSNum
ORDER BY s1.SalesNum )
AND INum = 2 ;
Anith|||Hi There,
Let us See if this can help your cause
--
Update yourTable Set CSFlag = 'N' Where SalesNum In (Select
Min(SalesNum) From yourTable Group By INum,CSNum)
--
Please let me know if it worked for You.
INum SalesNum CSNum CSFlg
-- -- -- --
2 333898 13295 N
2 333899 13295 Y
2 334366 13295 Y
2 335567 13269 N
2 335578 13269 Y
2 335579 13269 Y
2 336120 13255 N
2 336121 13255 Y
2 336122 13255 Y
With Warm regards
Jatinder Singh

Need to suppress repeating values in parameter drop-down list

I'm using a Report Parameter within a SQL Reporting Services Report, and the appropriate values are populating the parameter drop-down list. However, one of my tables has several records with the Service Provider ID. My question is how can repeating values be suppressed in the parameter drop-down list. See below for example. Any insight/advice is appreciated!
Service Provider ID 21
50
60
60
60

you can do a SELECT DISTINCT in your query to get unique records.

Need to store duplicate values to DB

Hi,

I have written a stored procedure to store values from a report i generated to the DB. Now there is a column PKID which is the primary key but also needs to be repeated at times. I tried to clear the memory that the same PKID has already been entered for which I wrote another SP.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spCRMPublisherSummaryUpdate](
@.ReportDate smalldatetime,
@.SiteID int,
@.DataFeedID int,
@.FromCode varchar,
@.Sent int,
@.Delivered int,
@.TotalOpens REAL,
@.UniqueUserOpens REAL,
@.UniqueUserMessageClicks REAL,
@.Unsubscribes REAL,
@.Bounces REAL,
@.UniqueUserLinkClicks REAL,
@.TotalLinkClicks REAL,
@.SpamComplaints int,
@.Cost int
)
AS
SET NOCOUNT ON

DECLARE @.PKID INT
DECLARE @.TagID INT

SELECT @.TagID=ID FROM Tag WHERE SiteID=@.SiteID AND FromCode=@.FromCode

SELECT @.PKID=PKID FROM DimTag
WHERE TagID=@.TagID AND StartDate<=@.ReportDate AND @.ReportDate< ISNULL(EndDate,'12/31/2050')
IF @.PKID IS NULL BEGIN
SELECT TOP 1 @.PKID=PKID FROM DimTag WHERE TagID=@.TagID AND SiteID=@.SiteID

DECLARE @.LastReportDate smalldatetime, @.LastSent INT, @.LastDelivered INT, @.LastTotalOpens Real,
@.LastUniqueUserOpens Real, @.LastUniqueUserMessageClicks Real, @.LastUniqueUserLinkClicks Real,
@.LastTotalLinkClicks Real, @.LastUnsubscribes Real, @.LastBounces Real, @.LastSpamComplaints INT, @.LastCost INT

SELECT @.Sent=@.Sent-Sent,@.Delivered=@.Delivered-Delivered,@.TotalOpens=@.TotalOpens-TotalOpens,
@.UniqueUserOpens=@.UniqueUserOpens-UniqueUserOpens,@.UniqueUserMessageClicks=@.UniqueUserMessageClicks-UniqueUserMessageClicks,
@.UniqueUserLinkClicks=@.UniqueUserLinkClicks-UniqueUserLinkClicks,@.TotalLinkClicks=@.TotalLinkClicks-TotalLinkClicks,
@.Unsubscribes=@.Unsubscribes-Unsubscribes,@.Bounces=@.Bounces-Bounces,@.SpamComplaints=@.SpamComplaints-SpamComplaints,
@.Cost=@.Cost-Cost
FROM CrmPublisherSummary
WHERE @.LastReportDate < @.ReportDate
AND SiteID=@.SiteID
AND TagPKID=@.PKID

UPDATE CrmPublisherSummary SET
Sent=@.Sent,
Delivered=@.Delivered,
TotalOpens=@.TotalOpens,
UniqueUserOpens=@.UniqueUserOpens,
UniqueUserMessageClicks=@.UniqueUserMessageClicks,
UniqueUserLinkClicks=@.UniqueUserLinkClicks,
TotalLinkClicks=@.TotalLinkClicks,
Unsubscribes=@.Unsubscribes,
Bounces=@.Bounces,
SpamComplaints=@.SpamComplaints,
Cost=@.Cost,
TagID=@.TagID
WHERE ReportDate=@.ReportDate
AND SiteID=@.SiteID
AND TagPKID=@.PKID
END

ELSE

INSERT INTO CrmPublisherSummary(
ReportDate, SiteID, TagPKID, Sent, Delivered, TotalOpens, UniqueUserOpens,
UniqueUserMessageClicks, UniqueUserLinkClicks, TotalLinkClicks, Unsubscribes,
Bounces, SpamComplaints, Cost, DataFeedID, TagID)

VALUES(
@.ReportDate, @.SiteID, @.PKID, @.Sent, @.Delivered, @.TotalOpens, @.UniqueUserOpens,
@.UniqueUserMessageClicks, @.UniqueUserLinkClicks, @.TotalLinkClicks, @.Unsubscribes,
@.Bounces, @.SpamComplaints, @.Cost, @.DataFeedID, @.TagID)

SET NOCOUNT OFF

this is the one to clear:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[spCRMPublisherSummaryClear](
@.SiteID INT,
@.DataFeedID INT,
@.ReportDate SMALLDATETIME) AS

DELETE LandingSiteSummary
WHERE SiteID=@.SiteID AND ReportDate=@.ReportDate

but it doesnt seem to be working.

Please suggest.

avidyarthi:

I have written a stored procedure to store values from a report i generated to the DB. Now there is a column PKID which is the primary key but also needs to be repeated at times.

you cannot repeat a value in a column that is set as your primary key. If you need to repeat values in that column, then you need to remove its designation as your primary key.

|||

hey,

thanks i worked my way around it.