Having trouble with this query.

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 2005 Forums

Transact-SQL (2005)

Having trouble with this query.

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-03-03 : 12:26:45

Here's the Tables and existing query, which I am having trouble getting to work right.



Declare @DateToCheck datetime 
set @DateToCheck ='6/1/2009'
Declare @Dates table (MyDate datetime)
Declare @Invoice table (DateReceived datetime,InvoiceDate datetime)
Declare @Interest table (StartDate datetime,EndDate datetime)
Declare @Close table (DateClosed datetime)


insert into @Dates
select '1/1/2009' Union all
select '2/1/2009' Union all
select '3/1/2009' Union All
select '4/1/2009' Union all
select '5/1/2009'  Union all
select '6/1/2009'  Union all
select '7/1/2009' Union all
select '8/1/2009' Union all
select '9/1/2009' Union all
select '10/1/2009' Union all
select '11/1/2009' Union all
select '12/1/2009'  Union all
select '1/1/2010'  Union all
select '2/1/2010'


insert into @Invoice
select '1/1/2009','1/1/2009' Union all
select '2/1/2009','2/1/2009' Union all
select '8/1/2009','3/1/2009' Union all
select '8/1/2009','4/1/2009' Union all
select '8/1/2009','5/1/2009' Union all
select '2/1/2010','2/1/2010'

Insert into @Interest 
select '1/1/2009', '5/1/2009' Union all
select '6/1/2009', '12/1/2009'

insert into @Close
select '3/1/2009' 

Select * from
@Dates a
Left Join
@Invoice b
on a.MyDate = b.InvoiceDate
Left Join
@Interest c
on 
		case 
		 when b.DateReceived > @Datetocheck then b.DateReceived
		 else a.Mydate
		 END between c.StartDate and c.EndDate
Left Join
@Close d
on
 (isnull(b.DateReceived,1) > @DateToCheck 
	and a.MyDate = d.DateClosed
	and isnull(b.DateReceived,0) = isnull(b.InvoiceDate,0)
	)
or 
 (isnull(b.DateReceived,1) <= @DateToCheck 
	and a.Mydate = d.DateClosed )

[code]

Now what I need is to somehow take the @Close table and have it create a extra record with null values accross the board using a conditional statement.

The logic is as follows.  

If the a.datereceived in the above query is <= the @datetocheck, I just do a join on a.Mydate = @Close.DateClosed

However if the a.DateReceived >= the @DateToCheck, and the INVOICEDate <> the DATERECEIVED then I want it to create a blank record. 

So here are my current results.

[code]
MyDate                  DateReceived            InvoiceDate             StartDate               EndDate                 DateClosed
----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -----------------------
2009-01-01 00:00:00.000 2009-01-01 00:00:00.000 2009-01-01 00:00:00.000 2009-01-01 00:00:00.000 2009-05-01 00:00:00.000 NULL
2009-02-01 00:00:00.000 2009-02-01 00:00:00.000 2009-02-01 00:00:00.000 2009-01-01 00:00:00.000 2009-05-01 00:00:00.000 NULL
2009-03-01 00:00:00.000 2009-08-01 00:00:00.000 2009-03-01 00:00:00.000 2009-06-01 00:00:00.000 2009-12-01 00:00:00.000 NULL
2009-04-01 00:00:00.000 2009-08-01 00:00:00.000 2009-04-01 00:00:00.000 2009-06-01 00:00:00.000 2009-12-01 00:00:00.000 NULL
2009-05-01 00:00:00.000 2009-08-01 00:00:00.000 2009-05-01 00:00:00.000 2009-06-01 00:00:00.000 2009-12-01 00:00:00.000 NULL
2009-06-01 00:00:00.000 NULL                    NULL                    2009-06-01 00:00:00.000 2009-12-01 00:00:00.000 NULL
2009-07-01 00:00:00.000 NULL                    NULL                    2009-06-01 00:00:00.000 2009-12-01 00:00:00.000 NULL
2009-08-01 00:00:00.000 NULL                    NULL                    2009-06-01 00:00:00.000 2009-12-01 00:00:00.000 NULL
2009-09-01 00:00:00.000 NULL                    NULL                    2009-06-01 00:00:00.000 2009-12-01 00:00:00.000 NULL
2009-10-01 00:00:00.000 NULL                    NULL                    2009-06-01 00:00:00.000 2009-12-01 00:00:00.000 NULL
2009-11-01 00:00:00.000 NULL                    NULL                    2009-06-01 00:00:00.000 2009-12-01 00:00:00.000 NULL
2009-12-01 00:00:00.000 NULL                    NULL                    2009-06-01 00:00:00.000 2009-12-01 00:00:00.000 NULL
2010-01-01 00:00:00.000 NULL                    NULL                    NULL                    NULL                    NULL
2010-02-01 00:00:00.000 2010-02-01 00:00:00.000 2010-02-01 00:00:00.000 NULL                    NULL                    NULL

Here are the Results I want (SEE THE LAST RECORD)

MyDate                  DateReceived            InvoiceDate             StartDate               EndDate                 DateClosed
----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -----------------------
2009-01-01 00:00:00.000 2009-01-01 00:00:00.000 2009-01-01 00:00:00.000 2009-01-01 00:00:00.000 2009-05-01 00:00:00.000 NULL
2009-02-01 00:00:00.000 2009-02-01 00:00:00.000 2009-02-01 00:00:00.000 2009-01-01 00:00:00.000 2009-05-01 00:00:00.000 NULL
2009-03-01 00:00:00.000 2009-08-01 00:00:00.000 2009-03-01 00:00:00.000 2009-06-01 00:00:00.000 2009-12-01 00:00:00.000 NULL
2009-04-01 00:00:00.000 2009-08-01 00:00:00.000 2009-04-01 00:00:00.000 2009-06-01 00:00:00.000 2009-12-01 00:00:00.000 NULL
2009-05-01 00:00:00.000 2009-08-01 00:00:00.000 2009-05-01 00:00:00.000 2009-06-01 00:00:00.000 2009-12-01 00:00:00.000 NULL
2009-06-01 00:00:00.000 NULL                    NULL                    2009-06-01 00:00:00.000 2009-12-01 00:00:00.000 NULL
2009-07-01 00:00:00.000 NULL                    NULL                    2009-06-01 00:00:00.000 2009-12-01 00:00:00.000 NULL
2009-08-01 00:00:00.000 NULL                    NULL                    2009-06-01 00:00:00.000 2009-12-01 00:00:00.000 NULL
2009-09-01 00:00:00.000 NULL                    NULL                    2009-06-01 00:00:00.000 2009-12-01 00:00:00.000 NULL
2009-10-01 00:00:00.000 NULL                    NULL                    2009-06-01 00:00:00.000 2009-12-01 00:00:00.000 NULL
2009-11-01 00:00:00.000 NULL                    NULL                    2009-06-01 00:00:00.000 2009-12-01 00:00:00.000 NULL
2009-12-01 00:00:00.000 NULL                    NULL                    2009-06-01 00:00:00.000 2009-12-01 00:00:00.000 NULL
2010-01-01 00:00:00.000 NULL                    NULL                    NULL                    NULL                    NULL
2010-02-01 00:00:00.000 2010-02-01 00:00:00.000 2010-02-01 00:00:00.000 NULL                    NULL                    NULL
2009-03-01 00:00:00.000 NULL                    NULL                    NULL                    NULL                    2009-03-01 00:00:00.000

See how even though the dateclosed matched the mydate on the 3/1/2009 record, since the datereceived on the 3/1/2009 record was > than the @datetocheck and also the datereceived <> invoicedate mean I do not want it to link. When there is no match I want a blank record as illustrated at the end, not NO record as it is currently doing.

I spent a while on this and it's one of those things that has become far more difficult than I hoped

Thanks

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-03-03 : 12:42:25

Well you can perform a UNION on you results to get that last line, your condition may be confusing though, since there are multiple Invoice Dates to check against.

To append the last line just do

Union All
Select DateClosed, NULL, NULL, NULL, NULL, DateClosed From @Close

Declare @DateToCheck datetime 
set @DateToCheck ='6/1/2009'
Declare @Dates table (MyDate datetime)
Declare @Invoice table (DateReceived datetime,InvoiceDate datetime)
Declare @Interest table (StartDate datetime,EndDate datetime)
Declare @Close table (DateClosed datetime)


insert into @Dates
select '1/1/2009' Union all
select '2/1/2009' Union all
select '3/1/2009' Union All
select '4/1/2009' Union all
select '5/1/2009' Union all
select '6/1/2009' Union all
select '7/1/2009' Union all
select '8/1/2009' Union all
select '9/1/2009' Union all
select '10/1/2009' Union all
select '11/1/2009' Union all
select '12/1/2009' Union all
select '1/1/2010' Union all
select '2/1/2010'


insert into @Invoice
select '1/1/2009','1/1/2009' Union all
select '2/1/2009','2/1/2009' Union all
select '8/1/2009','3/1/2009' Union all
select '8/1/2009','4/1/2009' Union all
select '8/1/2009','5/1/2009' Union all
select '2/1/2010','2/1/2010'

Insert into @Interest 
select '1/1/2009', '5/1/2009' Union all
select '6/1/2009', '12/1/2009'

insert into @Close
select '3/1/2009' 

Select * from
@Dates a
Left Join
@Invoice b
on a.MyDate = b.InvoiceDate
Left Join
@Interest c
on 
case 
when b.DateReceived > @Datetocheck then b.DateReceived
else a.Mydate
END between c.StartDate and c.EndDate
Left Join
@Close d
on
(isnull(b.DateReceived,1) > @DateToCheck 
and a.MyDate = d.DateClosed
and isnull(b.DateReceived,0) = isnull(b.InvoiceDate,0)
)
or 
(isnull(b.DateReceived,1) <= @DateToCheck 
and a.Mydate = d.DateClosed )

Union All
Select  DateClosed, NULL, NULL, NULL, NULL, DateClosed From @Close

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-03-03 : 12:55:46

I don't want to use a union I want the query to run as a dependent since there are numerous other factors in the production query than I mentioned..

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-03-03 : 13:29:24

I used a recursive CTE to solve




Declare @DateToCheck datetime 
set @DateToCheck ='5/1/2009'
Declare @Dates table (MyDate datetime)
Declare @Invoice table (DateReceived datetime,InvoiceDate datetime)
Declare @Interest table (StartDate datetime,EndDate datetime)
Declare @Close table (DateClosed datetime)


insert into @Dates
select '1/1/2009' Union all
select '2/1/2009' Union all
select '3/1/2009' Union All
select '4/1/2009' Union all
select '5/1/2009'  Union all
select '6/1/2009'  Union all
select '7/1/2009' Union all
select '8/1/2009' Union all
select '9/1/2009' Union all
select '10/1/2009' Union all
select '11/1/2009' Union all
select '12/1/2009'  Union all
select '1/1/2010'  Union all
select '2/1/2010'


insert into @Invoice
select '1/1/2009','1/1/2009' Union all
select '2/1/2009','2/1/2009' Union all
select '8/1/2009','3/1/2009' Union all
select '8/1/2009','4/1/2009' Union all
select '8/1/2009','5/1/2009' Union all
select '2/1/2010','2/1/2010'

Insert into @Interest 
select '1/1/2009', '5/1/2009' Union all
select '6/1/2009', '12/1/2009'

insert into @Close
select '3/1/2009' 

;With t (MyDate,DateReceived,InvoiceDate,StartDate,EndDate,DateClosed)
as (
Select Mydate,DateReceived,InvoiceDate,StartDate,EndDate,DateClosed from
@Dates a
Left Join
@Invoice b
on a.MyDate = b.InvoiceDate
Left Join
@Interest c
on 
		case 
		 when b.DateReceived > @Datetocheck then b.DateReceived
		 else a.Mydate
		 END between c.StartDate and c.EndDate
Left Join
@Close d
on
(isnull(b.DateReceived,1) <= @DateToCheck 
and a.Mydate = d.DateClosed )
Union All
Select t.mydate,null,null,null,null,c.dateclosed
from t 
Inner Join
@Close c
on t.Mydate = c.DateClosed
where
t.DateReceived > @Datetocheck
)
select * from t
OPTION (MAXRECURSION 0)

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources