Help with SQL statement please.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with SQL statement please.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-10-04 : 15:36:22

[code]Hi,

I need to run an update with the following business rules below.
Thank you in advance. SQL 2005.

DROP TABLE dbo.T1;
go
CREATE TABLE [dbo].T1
(
[IDPatInsur] [int] NULL,
[PID] [int] NULL,
[ValidFrom] [datetime] NULL,
[ValidTo] [datetime] NULL,
[Inactive] [bit] NULL,
[GenInsurType] [varchar](9) NOT NULL
)
GO

INSERT INTO [dbo].[T1]([IDPatInsur], [PID], [ValidFrom], [ValidTo], [Inactive], [GenInsurType])
SELECT 1000, 100, '20091101 00:00:00.000', NULL, 0, N'Primary' UNION ALL
SELECT 1002, 100, '20091101 00:00:00.000', NULL, 0, N'Secondary' UNION ALL
SELECT 1003, 100, '20091101 00:00:00.000', '20091106 00:00:00.000', 1, N'Primary' UNION ALL
SELECT 3992, 2350, '20110101 00:00:00.000', NULL, 0, N'Primary' UNION ALL
SELECT 3993, 2631, '20110101 00:00:00.000', NULL, 0, N'Quinary' UNION ALL
SELECT 1039, 137, '2009-08-01', NULL, 0, N'Secondary' UNION ALL
SELECT 1350, 137, '2009-01-01', NULL, 0, N'Primary' UNION ALL
SELECT 3329, 144, '2011-01-01', NULL, 0, N'Primary' UNION ALL
SELECT 2100, 144, '2009-10-01', NULL, 1, N'Primary' UNION ALL
SELECT 3996, 2359, NULL, NULL, 0, N'Primary'
go

SELECT *
FROM T1;
go

Inactive:
-----------
1 = Inactive
0 = Active

Business Rule: If Multiple Same PID has 1 = Inactive set ValidFrom - 1 day, in this
case 10/31/2009. Only select rows which has the type Primary, Secondary or Tertiary in GenInsurType field.

IDPatInsur PID ValidFrom ValidTo Inactive GenInsurType
----------- ----------- ----------------------- ----------------------- -------- ------------
1000 100 2009-11-01 00:00:00.000 NULL 0 Primary
1002 100 2009-11-01 00:00:00.000 NULL 0 Secondary
1003 100 2009-11-01 00:00:00.000 2009-11-06 00:00:00.000 1 Primary

3992 2350 2011-01-01 00:00:00.000 NULL 0 Primary
3993 2631 2011-01-01 00:00:00.000 NULL 0 Quinary

1039 137 2009-08-01 00:00:00.000 NULL 0 Secondary
1350 137 2009-01-01 00:00:00.000 NULL 0 Primary

3329 144 2011-01-01 00:00:00.000 NULL 0 Primary
2100 144 2009-10-01 00:00:00.000 NULL 1 Primary

3996 2359 NULL NULL 0 Primary

-- Result want:

IDPatInsur PID ValidFrom ValidTo Inactive GenInsurType
----------- ----------- ----------------------- ----------------------- -------- ------------
1000 100 2009-11-01 00:00:00.000 2009-10-31 0 Primary
1002 100 2009-11-01 00:00:00.000 2009-10-31 0 Secondary
1003 100 2009-11-01 00:00:00.000 2009-11-06 00:00:00.000 1 Primary

3329 144 2011-01-01 00:00:00.000 2010-12-31 0 Primary
2100 144 2009-10-01 00:00:00.000 NULL 1 Primary[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 00:39:08

[code]UPDATE t
SET t.ValidTo=DATEADD(dd,-1,t.ValidFrom)
FROM (SELECT COUNT(1) OVER (PARTITION BY PID) AS Cnt,
COUNT(CASE WHEN Inactive =1 THEN 1 ELSE NULL END) OVER (PARTITION BY PID) AS CntInact,
ValidTo,ValidFrom
FROM T1
WHERE GenInsurType IN('Primary','Secondary','Tertiary')
)t
WHERE Cnt>1
AND CntInact>0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-10-05 : 11:40:58

Thank you Visakh16 but I just got a new business rules:

I just clarify some of the business rules need some help with the query. Also the new business rules are provide below.

Thanks in advance.

DROP TABLE dbo.T1;
go
CREATE TABLE [dbo].T1
(
[IDPatInsur] [int] NULL,
[PID] [int] NULL,
[ValidFrom] [datetime] NULL,
[ValidTo] [datetime] NULL,
[Inactive] [bit] NULL,
[GenInsurType] [varchar](9) NOT NULL
)
GO

INSERT INTO [dbo].[T1]([IDPatInsur], [PID], [ValidFrom], [ValidTo], [Inactive], [GenInsurType])
SELECT 1000, 100, '20110901 00:00:00.000', NULL, 0, N'Primary' UNION ALL
SELECT 1002, 100, '20091101 00:00:00.000', NULL, 0, N'Secondary' UNION ALL
SELECT 1003, 100, '20091101 00:00:00.000', NULL, 1, N'Primary' UNION ALL
SELECT 3992, 2350, '20110101 00:00:00.000', NULL, 0, N'Primary' UNION ALL

SELECT 3329, 144, '2011-01-01', NULL, 0, N'Primary' UNION ALL
SELECT 2100, 144, '2009-10-01', NULL, 1, N'Primary' UNION ALL

SELECT 1011, 106, '2008-08-01', NULL, 1, N'Primary' UNION ALL
SELECT 3106, 106, '2010-10-01', NULL, 0, N'Primary' UNION ALL

SELECT 2648, 184, '2009-10-01', '2009-12-31', 1, N'Primary' UNION ALL
SELECT 1080, 184, '2010-01-01', NULL, 0, N'Primary' UNION ALL

SELECT 3996, 2359, NULL, NULL, 0, N'Primary'
go

--Business Rule: take the row which has Inactive = 1 and get the previous row validFrom - 1 day and
-- update in the ValidTo column.

-- Brief description:
if the insurance is Inactive I want to take previous record which has Inactive = 0 (active) minus
one day update on row which has Inactive = 1. I am only looking for a repairs which has
0 and 1 to satify the condition. If you look at the PID = 144 and 106 on the ValidTo column is
make more sense.

-- SQL 2005.

Thanks so much in advance.

SELECT *
FROM T1
ORDER BY PID ASC, Inactive ASC ;
go

-- Result want:
-- Please look at the ValidTo which has Inactive = 1.

IDPatInsur PID ValidFrom ValidTo Inactive GenInsurType
----------- ----------- ----------------------- ----------------------- -------- ------------
1000 100 2011-09-01 00:00:00.000 NULL 0 Primary
1002 100 2009-11-01 00:00:00.000 NULL 0 Secondary
1003 100 2009-11-01 00:00:00.000 2011-08-31 1 Primary

3106 106 2010-10-01 00:00:00.000 NULL 0 Primary
1011 106 2008-08-01 00:00:00.000 2010-09-30 1 Primary

3329 144 2011-01-01 00:00:00.000 NULL 0 Primary
2100 144 2009-10-01 00:00:00.000 2010-12-31 1 Primary

-------------------------------------------------------------------------

-- Testing...

SELECT a.ValidFrom,
a.ValidTo,
DATEADD ([day], -1, a.ValidFrom),
a.Inactive,
a.pid,
a.IDPatInsur,
a.GenInsurType
FROM T1 AS a
WHERE Inactive = 1
AND ValidTo IS NULL
AND GenInsurType = 'Primary'
AND EXISTS ( SELECT 1
FROM T1 AS b
WHERE b.PID = a.PID
AND b.Inactive = 0
AND b.IDPatInsur <> a.IDPatInsur )
AND PID IN (144, 106)
go

SELECT *
FROM T1
ORDER BY PID ASC ;
go

IDPatInsur PID ValidFrom ValidTo Inactive GenInsurType
----------- ----------- ----------------------- ----------------------- -------- ------------
3329 144 2011-01-01 00:00:00.000 NULL 0 Primary
2100 144 2009-10-01 00:00:00.000 2010-12-31 1 Primary

1011 106 2008-08-01 00:00:00.000 2010-09-30 1 Primary
3106 106 2010-10-01 00:00:00.000 NULL 0 Primary

-- 1
--hun
BEGIN TRAN

UPDATE a
SET ValidTo = DATEADD ([day], -1, a.ValidFrom)
FROM T1 AS a
WHERE Inactive = 1
AND ValidTo IS NULL
AND GenInsurType = 'Primary'
AND EXISTS ( SELECT 1
FROM T1 AS b
WHERE b.PID = a.PID
AND b.Inactive = 0
AND b.IDPatInsur <> a.IDPatInsur ) ;
ROLLBACK TRAN
COMMIT TRAN
PRINT @@TRANCOUNT;
GO

---------------------------------------------------------
--2
BEGIN TRAN
Update t
Set ValidTo = DateAdd(day, -1, t2.ValidFrom)
From T1 t
Inner Join T1 t2 On t.PID = t2.PID
Where t.GenInsurType In ('Primary', 'Secondary', 'Tertiary')
And t2.Inactive = 1
And t.Inactive = 0;

ROLLBACK TRAN
COMMIT TRAN
PRINT @@TRANCOUNT;
GO

SELECT t.*,
t2.Inactive
FROM T1 AS t
JOIN T1 t2
ON t.PID = t2.PID
WHERE t.GenInsurType In ('Primary', 'Secondary', 'Tertiary')
AND t2.Inactive = 1
AND t.Inactive = 0;

SELECT *
FROM T1;
go

quote:
Originally posted by visakh16

UPDATE t
SET t.ValidTo=DATEADD(dd,-1,t.ValidFrom)
FROM (SELECT COUNT(1) OVER (PARTITION BY PID) AS Cnt,
COUNT(CASE WHEN Inactive =1 THEN 1 ELSE NULL END) OVER (PARTITION BY PID) AS CntInact,
ValidTo,ValidFrom
FROM T1
WHERE GenInsurType IN('Primary','Secondary','Tertiary')
)t
WHERE Cnt>1
AND CntInact>0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 13:19:28

[code]
UPDATE t
SET t.ValidTo=DATEADD(dd,-1,t1.ValidFrom)
FROM (SELECT
COUNT(DISTINCT CASE WHEN Inactive in (0,1) THEN Inactive ELSE NULL END) OVER (PARTITION BY PID) AS CntInact,
ValidTo,ValidFrom,PID
FROM T1
WHERE GenInsurType IN('Primary','Secondary','Tertiary')
WHERE Inactive=1
)t
CROSS APPLY(SELECT TOP 1 ValidFrom
FROM T1
WHERE PID=t.PID
AND Inactive=0
AND ValidFrom< t.ValidFrom
ORDER BY ValidFrom DESC)t1
WHERE t.CntInact=2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-10-05 : 15:58:22

[code]I am getting the error:

Msg 4418, Level 16, State 1, Line 2
Derived table 't' is not updatable because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator.

Thanks for trying to help.

BEGIN TRAN;
UPDATE t
SET t.ValidTo = DATEADD(dd,-1,t1.ValidFrom)
FROM ( SELECT DISTINCT COUNT( CASE
WHEN Inactive in (0,1) THEN Inactive ELSE NULL END) OVER (PARTITION BY PID) AS CntInact,
ValidTo,ValidFrom,PID, Inactive
FROM T1
WHERE GenInsurType IN('Primary','Secondary','Tertiary')

) AS t

CROSS APPLY(SELECT TOP 1 ValidFrom
FROM T1
WHERE PID=t.PID
AND Inactive=0
AND ValidFrom < t.ValidFrom
ORDER BY ValidFrom DESC)t1
WHERE t.CntInact=2
AND t.Inactive = 1 ;

ROLLBACK TRAN
COMMIT TRAN
PRINT @@TRANCOUNT;
GO
--end

quote:
Originally posted by visakh16

[code]
UPDATE t
SET t.ValidTo=DATEADD(dd,-1,t1.ValidFrom)
FROM (SELECT
COUNT(DISTINCT CASE WHEN Inactive in (0,1) THEN Inactive ELSE NULL END) OVER (PARTITION BY PID) AS CntInact,
ValidTo,ValidFrom,PID
FROM T1
WHERE GenInsurType IN('Primary','Secondary','Tertiary')
WHERE Inactive=1
)t
CROSS APPLY(SELECT TOP 1 ValidFrom
FROM T1
WHERE PID=t.PID
AND Inactive=0
AND ValidFrom< t.ValidFrom
ORDER BY ValidFrom DESC)t1
WHERE t.CntInact=2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-06 : 00:55:56

[code]
UPDATE t
SET t.ValidTo=DATEADD(dd,-1,t1.ValidFrom)
FROM (SELECT
ValidTo,ValidFrom,PID
FROM T1
WHERE GenInsurType IN('Primary','Secondary','Tertiary')
WHERE Inactive=1
)t
CROSS APPLY(SELECT TOP 1 ValidFrom
FROM T1
WHERE PID=t.PID
AND Inactive=0
AND ValidFrom< t.ValidFrom
ORDER BY ValidFrom DESC)t1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-10-06 : 15:09:26

[code]Thanks so much Visakh16. I have to twist it a little but now I try to understand your queries.
again, thank you.

New

SELECT DATEADD(dd,-1, t.ValidFrom) AS NewValidTo,
t.*
FROM ( SELECT PID,
IDPatInsur,
ValidTo,
ValidFrom,
Inactive
FROM T1
WHERE GenInsurType IN ('Primary', 'Secondary', 'Tertiary')
AND Inactive = 0
AND ValidTo IS NULL ) AS t
CROSS APPLY ( SELECT TOP 1 ValidFrom
FROM T1
WHERE PID = t.PID
AND Inactive = 1
AND ValidTo IS NULL
AND ValidFrom < t.ValidFrom
ORDER BY ValidFrom DESC ) AS t1
go

--end[/code]

quote:
Originally posted by visakh16

[code]
UPDATE t
SET t.ValidTo=DATEADD(dd,-1,t1.ValidFrom)
FROM (SELECT
ValidTo,ValidFrom,PID
FROM T1
WHERE GenInsurType IN('Primary','Secondary','Tertiary')
WHERE Inactive=1
)t
CROSS APPLY(SELECT TOP 1 ValidFrom
FROM T1
WHERE PID=t.PID
AND Inactive=0
AND ValidFrom< t.ValidFrom
ORDER BY ValidFrom DESC)t1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 01:34:39

welcome
lemme know if you need more help on this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources