kw_uh97
3/23/2007 9:07:00 PM
Hello All
Hope you can help with this query
Here are my requirements:
1) get rows greater than 2-1-2007 from #v (that first day of last month from
getdat())
2) include rows where displatindictor =1, also Cast all null values as
zeros(0) on Recordables column.
So my return set should be
xxx2, 1, F82722B9-22FC-49F7-AB40-D249B78EA52F, 0
xxx3, 1, B91B39E9-535B-4FE2-AA86-FFD1690E3252, 87
xxx4, 1, 3184035F-990D-4732-8B62-FC2BDF65DA3A, 0
xxx5, 1, 84F54188-D4AF-4D55-AEB3-E42E4225A78C, 0
xxx6, 1, C8C97905-98CE-48AC-858A-2870FC3EDF81, 0
Here is some DDL and sample data. I hope that you guys can create the table
and I tried to create some sample data hope you dont have many problems.
CREATE TABLE [#i] (
[Title] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[DisplayIndicator] [bit] NULL ,
[PrimKey] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT [DF_#i_PrimKey]
DEFAULT (newid()),
CONSTRAINT [PK_#i] PRIMARY KEY NONCLUSTERED
(
[PrimKey]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
insert into #i values('xxx1','0','442605D8-65E7-4F77-B78F-C9A079E0131D')
insert into #i values('xxx2','1','F82722B9-22FC-49F7-AB40-D249B78EA52F')
insert into #i values('xxx3','1','B91B39E9-535B-4FE2-AA86-FFD1690E3252')
insert into #i values('xxx4','1','3184035F-990D-4732-8B62-FC2BDF65DA3A')
insert into #i values('xxx5','1','84F54188-D4AF-4D55-AEB3-E42E4225A78C')
insert into #i values('xxx6','1','C8C97905-98CE-48AC-858A-2870FC3EDF81')
insert into #i values('xxx7','0','836A5582-EDFB-4DEA-A696-7DCF42804932')
insert into #i values('xxx8','0','7AB13E44-7C40-43BA-8C07-8F20675B3E13')
insert into #i values('xxx9','0','B5525431-0469-4ADE-AA55-4B55E6E7DBB3')
CREATE TABLE [#v] (
[IndicatorRef] [uniqueidentifier] NULL ,
[Recordable] [int] NULL ,
[Period] [datetime] NULL ,
[PrimKey] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT [DF_#v_PrimKey]
DEFAULT (newid()),
CONSTRAINT [PK_#v] PRIMARY KEY NONCLUSTERED
(
[PrimKey]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
insert into #v
values('836A5582-EDFB-4DEA-A696-7DCF42804932','11','2007-01-13','ADF0FCCD-7799-4AC5-9268-F4C0060815B6')
insert into #v
values('836A5582-EDFB-4DEA-A696-7DCF42804932','15','2007-02-09','035B396F-9096-4027-B184-49817C8FA7DD')
insert into #v
values('C8C97905-98CE-48AC-858A-2870FC3EDF81','19','2007-01-21','3D8CECA0-4A21-430C-BC9F-43543574EA4C')
insert into #v
values('442605D8-65E7-4F77-B78F-C9A079E0131D','9','2007-02-22','7F3F28D4-F7B8-4A90-85D9-B916C0DD87C9')
insert into #v
values('F82722B9-22FC-49F7-AB40-D249B78EA52F','3','2007-01-06','265B868E-D4C2-47C2-99F0-CCD74234050F')
insert into #v
values('B91B39E9-535B-4FE2-AA86-FFD1690E3252','87','2007-02-17','A059D230-FDAF-4D17-B346-C8FBDFE07ECA')
insert into #v
values('7AB13E44-7C40-43BA-8C07-8F20675B3E13','61','2007-01-30','BFF5C3CB-B32D-4036-B71E-AEE7F978200B')
insert into #v
values('7AB13E44-7C40-43BA-8C07-8F20675B3E13','31','2006-12-30','88D963A7-EE84-4099-9795-E53B64EF5241')
insert into #v
values('B5525431-0469-4ADE-AA55-4B55E6E7DBB3','31','2006-12-30','3F13475A-DDBE-4F49-8A23-4B9CEC7A60F0')
I hope I defined my requierments clearly. Its been a long week. Thanks in
advance for your help.
kw