Nested SELECT ... FOR XML PATH Return ESCAPED <>s

Hi,
I'm trying to build a complicated web service request using the (much better than FOR XML EXPLICIT) PATH mode, and it's great and all except that when I nest them I am getting &lt, &gt for the nested nodes. Here's a snippit:

BEGIN

SET NOCOUNT ON;

SELECT
'P' AS "Item/DataBlkInd",
'A' AS "Item/PhoneQual/EditTypeInd",
'CITYCODE' AS "Item/PhoneQual/AddPhoneQual/City",
...
(SELECT DISTINCT
'R' AS "DataBlkInd",
'A' AS "EmailQual/EditTypeInd",
'1' AS "EmailQual/LineNum",
'T' AS "EmailQual/Type",
bpe.EmailAddress AS "EmailQual/EmailData"
FROM dbo.BPEmail bpe WHERE bpe.BusPartyId = @.CustomerId
FOR XML PATH('Item')) AS "node()",
...
FOR XML PATH('ItemAry'), ROOT('PNRBFSecondaryBldChgMods')
END

Any idea why the nested FOR XML PATH would be escaped, and how to return it as XML instead of "&lt Item &gt &lt ..."?

Many thanks!
Andy

Hi Andy

FOR XML queries per default return the resulting XML as a string value for backwards-compatibility reasons (regardless of the mode). So you should say

FOR XML PATH('Item'), TYPE

if you need the result to be XML. Also, I think you then will not need the AS "node()". Just leave the column alias away.

Best regards
Michael

Nested Recordsets

I need to decrease the amount of time it takes to return a set of data of the following format:

State[Provided vai user intput]->All Counties in State->All Cities in County->All Zones in City[usually < 50 or 75 per city]

current behavior:
1. currently user selects state all counties are returned from db
2. foreach county cities are returned
3. foreach city streets are returned
4. objects hydrated with data & display drawn

As you can see this gets expensive. I know there has to be a better way to do this. Any suggestions? We use sql server/c#.net.Unclear what you want here. If you just want to return all the counties, cities, streets, etc for a given State, just use a multi-table query with indexed foreign keys.|||Are you using Cursors or subqueries to retrive all those informations ?|||hydrated??|||Look it up in Books Online, Rudy.|||thank you, but i couldn't find it

would you be kind enough to give me the exact url|||Ha! Made you look!

Sorry, Rudy, but for some reason at 12:30 last night the idea of you searching Books Online for "hydrated" seemed hillariously funny to me.

D'oh!|||yeah, well, i even tried google, searching for "microsoft sql server hydrate"

found this -- http://www.devarticles.com/showblog/345/Could-you-pee-on-my-sandwich-please|||The order of data retrieval looks a little backwards, - should you let the user select a country first? And don't you have a relation between countries and states?

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?
>