Select Query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Select Query

Author

Topic

visa123
Yak Posting Veteran

54 Posts

Posted - 2010-12-08 : 06:21:29

I have query for below scenario

prodid posteddate
1000 2010-12-05 00:00:00.000
1001 2010-12-05 00:00:00.000
1001 2010-12-06 00:00:00.000
1000 2010-12-07 00:00:00.000
1001 2010-12-07 00:00:00.000

the above scenario prodid 1000 expired on 6th dec.
again they will put in 7 th dec. but 1001 its not expired.so i have to retrieve 5 th dec.

Here i need to get

1000 2010-12-07 00:00:00.000
1001 2010-12-05 00:00:00.000

How can i do for the above scenario?

Visa.G

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-08 : 06:32:58

How do you know that 1000 is expired and 1001 hasn't?
Because 1000 has an even number of entries and 1001 has an odd number?
Or maybe because there are no days missing for 1001 and there is a day missing for 1000?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visa123
Yak Posting Veteran

54 Posts

Posted - 2010-12-08 : 06:37:55

Hi

Thanks for your reply.Date missing.So we can identify through that.

if the date is missing the product expired on that date or not available on that date.

if they put next day the product is came.

Visa.G

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-08 : 06:56:10

So you want the first day after a missing day.

you will need a date table - which you can generate using a cte - but will need to specify a date range.
something like

;with dte as
(
select d = dateadd(dd,datediff(dd,0,getdate()),0) - 100
union all
select d = d + 1 from dte where d < getdate()
),
prodid as
(select distinct prodid from tbl)
select prodid, coalesce(a.posteddate + 1,(sleect max(dte) from dte))
from prodid p
left join
(
select prodic, posteddate = max(posteddate) -- last missing day
from dte
cross join
prodid p
left join tbl t2
on dte.dte = t2.posteddate
and p.prodid = t2.prodid
where t2.prodid is null
group by prodid
) a
on a.prodid = p.prodid

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visa123
Yak Posting Veteran

54 Posts

Posted - 2010-12-08 : 07:34:01

Hi

I have only one table. i dont have de and tbl.

tablename : prodid

prodid posteddate
1000 2010-12-05 00:00:00.000
1001 2010-12-05 00:00:00.000
1001 2010-12-06 00:00:00.000
1000 2010-12-07 00:00:00.000
1001 2010-12-07 00:00:00.000

help me for this

Visa.G

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-08 : 07:35:30

tbl is your table.
de is a misstype for dte.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visa123
Yak Posting Veteran

54 Posts

Posted - 2010-12-08 : 07:57:38

Thanks for your reply.

I have created the below. however i am getting error like Invalid column name 'dte'.

CREATE TABLE [dbo].[tbl](
[prodid] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[posteddate] [datetime] NULL
) ON [PRIMARY]

Insert into tbl values('1000','2010-12-05 00:00:00.000')
Insert into tbl values('1001','2010-12-05 00:00:00.000')
Insert into tbl values('1001','2010-12-06 00:00:00.000')
Insert into tbl values('1000','2010-12-07 00:00:00.000')
Insert into tbl values('1001','2010-12-07 00:00:00.000')

Select * from tbl

;with dte as
(
select d = dateadd(dd,datediff(dd,0,getdate()),0) - 100
union all
select d = d + 1 from dte where d < getdate()
),
prodid as
(select distinct prodid from tbl)
select p.prodid, coalesce(a.posteddate + 1,(select max(dte) from dte))
from prodid p
left join
(
select p.prodid, posteddate = max(posteddate) -- last missing day
from dte
cross join
prodid p
left join tbl t2
on dte.dte = t2.posteddate
and p.prodid = t2.prodid
where t2.prodid is null
group by t2.prodid
) a
on a.prodid = p.prodid

Can u please help for this ?

Visa.G

visa123
Yak Posting Veteran

54 Posts

Posted - 2010-12-08 : 09:38:33

Hi nigelrivett

Can u please help me for above scenario?

Visa.G

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-08 : 09:41:35

sorry been away
There were a number of problems with it
This one should work

CREATE TABLE [dbo].[tbl](
[prodid] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[posteddate] [datetime] NULL
) ON [PRIMARY]

Insert into tbl values('1000','2010-12-05 00:00:00.000')
Insert into tbl values('1001','2010-12-05 00:00:00.000')
Insert into tbl values('1001','2010-12-06 00:00:00.000')
Insert into tbl values('1000','2010-12-07 00:00:00.000')
Insert into tbl values('1001','2010-12-07 00:00:00.000')
Insert into tbl values('1002','2010-12-03 00:00:00.000')
Insert into tbl values('1002','2010-12-04 00:00:00.000')
Insert into tbl values('1002','2010-12-06 00:00:00.000')
Insert into tbl values('1002','2010-12-07 00:00:00.000')

;with dte as
(
select d = dateadd(dd,datediff(dd,0,getdate()),0) - 10
union all
select d = d + 1 from dte where d < '20101207'
),
prodid as
(select distinct prodid from tbl)

select p.prodid, coalesce(a.posteddate + 1,(select max(d) from dte))
from prodid p
left join
(
select p.prodid, posteddate = max(dte.d) -- last missing day
from dte
cross join
prodid p
left join tbl t2
on dte.d = t2.posteddate
and p.prodid = t2.prodid
where t2.prodid is null
group by p.prodid
) a
on a.prodid = p.prodid

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

TimSman
Posting Yak Master

127 Posts

Posted - 2010-12-08 : 09:43:27

Try this:



;with dte as
(
select d = dateadd(dd,datediff(dd,0,getdate()),0) - 100
union all
select d = d + 1 from dte where d < getdate()
),
prodid as
(select distinct prodid from tbl)
select p.prodid, coalesce(a.posteddate + 1,(select max(dte) from dte))
from prodid p
left join
(
select p.prodid, posteddate = max(posteddate) -- last missing day
from dte
cross join
prodid p
left join tbl t2
on dte.d = t2.posteddate
and p.prodid = t2.prodid
where t2.prodid is null
group by t2.prodid
) a
on a.prodid = p.prodid

visa123
Yak Posting Veteran

54 Posts

Posted - 2010-12-08 : 09:56:10

Hi

Thanks for your reply.

I tried.But now i am getting this error "Column 'prodid.prodid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." :(

Help for this pls

Visa.G

TimSman
Posting Yak Master

127 Posts

Posted - 2010-12-08 : 09:58:58

Did you try Nigel's?

visa123
Yak Posting Veteran

54 Posts

Posted - 2010-12-08 : 10:17:24

Yes I tried. that time I faced d issue which u explained. i also replaced that d and that error. and after that
"Column 'prodid.prodid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." after that i have removed group by also

and faced same issue :(

Visa.G

visa123
Yak Posting Veteran

54 Posts

Posted - 2010-12-08 : 10:37:02

Sorry i didnt see nigelrivett thread.

Thanks a lot nigelrivett.Its working Fine. :) :)

Visa.G

visa123
Yak Posting Veteran

54 Posts

Posted - 2010-12-09 : 07:13:28

Sorry I have tried but showing some wrong the below scenario

CREATE TABLE [dbo].[tbl](
[prodid] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[posteddate] [datetime] NULL
) ON [PRIMARY]

Insert into tbl values('3384BR','2010-12-07 00:00:00.000')
Insert into tbl values('3527BR','2010-12-07 00:00:00.000')
Insert into tbl values('3561BR','2010-12-07 00:00:00.000')
Insert into tbl values('3594BR','2010-12-07 00:00:00.000')
Insert into tbl values('3613BR','2010-12-07 00:00:00.000')
Insert into tbl values('3627BR','2010-12-07 00:00:00.000')
Insert into tbl values('3628BR','2010-12-07 00:00:00.000')
Insert into tbl values('3653BR','2010-12-07 00:00:00.000')
Insert into tbl values('3673BR','2010-12-07 00:00:00.000')
Insert into tbl values('3697BR','2010-12-07 00:00:00.000')
Insert into tbl values('3698BR','2010-12-07 00:00:00.000')
Insert into tbl values('3699BR','2010-12-07 00:00:00.000')
Insert into tbl values('3733BR','2010-12-07 00:00:00.000')
Insert into tbl values('3734BR','2010-12-07 00:00:00.000')
Insert into tbl values('3735BR','2010-12-07 00:00:00.000')
Insert into tbl values('3784BR','2010-12-07 00:00:00.000')
Insert into tbl values('3785BR','2010-12-07 00:00:00.000')
Insert into tbl values('3788BR','2010-12-07 00:00:00.000')
Insert into tbl values('3791BR','2010-12-07 00:00:00.000')
Insert into tbl values('3802BR','2010-12-07 00:00:00.000')
Insert into tbl values('3812BR','2010-12-07 00:00:00.000')
Insert into tbl values('3814BR','2010-12-07 00:00:00.000')
Insert into tbl values('3815BR','2010-12-07 00:00:00.000')
Insert into tbl values('3817BR','2010-12-07 00:00:00.000')
Insert into tbl values('3828BR','2010-12-07 00:00:00.000')
Insert into tbl values('3833BR','2010-12-07 00:00:00.000')
Insert into tbl values('3845BR','2010-12-07 00:00:00.000')
Insert into tbl values('3846BR','2010-12-07 00:00:00.000')
Insert into tbl values('3859BR','2010-12-07 00:00:00.000')
Insert into tbl values('3861BR','2010-12-07 00:00:00.000')
Insert into tbl values('3863BR','2010-12-07 00:00:00.000')
Insert into tbl values('3867BR','2010-12-07 00:00:00.000')
Insert into tbl values('3868BR','2010-12-07 00:00:00.000')
Insert into tbl values('3877BR','2010-12-07 00:00:00.000')
Insert into tbl values('3887BR','2010-12-07 00:00:00.000')
Insert into tbl values('3890BR','2010-12-07 00:00:00.000')
Insert into tbl values('3913BR','2010-12-07 00:00:00.000')
Insert into tbl values('3914BR','2010-12-07 00:00:00.000')
Insert into tbl values('3915BR','2010-12-07 00:00:00.000')
Insert into tbl values('3917BR','2010-12-07 00:00:00.000')
Insert into tbl values('3920BR','2010-12-07 00:00:00.000')
Insert into tbl values('3922BR','2010-12-07 00:00:00.000')
Insert into tbl values('3930BR','2010-12-07 00:00:00.000')
Insert into tbl values('3937BR','2010-12-07 00:00:00.000')
Insert into tbl values('3938BR','2010-12-07 00:00:00.000')
Insert into tbl values('3940BR','2010-12-07 00:00:00.000')
Insert into tbl values('3942BR','2010-12-07 00:00:00.000')
Insert into tbl values('3943BR','2010-12-07 00:00:00.000')
Insert into tbl values('3945BR','2010-12-07 00:00:00.000')
Insert into tbl values('3946BR','2010-12-07 00:00:00.000')
Insert into tbl values('3947BR','2010-12-07 00:00:00.000')
Insert into tbl values('3948BR','2010-12-07 00:00:00.000')
Insert into tbl values('3949BR','2010-12-07 00:00:00.000')
Insert into tbl values('3950BR','2010-12-07 00:00:00.000')
Insert into tbl values('3951BR','2010-12-07 00:00:00.000')
Insert into tbl values('3952BR','2010-12-07 00:00:00.000')
Insert into tbl values('3954BR','2010-12-07 00:00:00.000')
Insert into tbl values('3955BR','2010-12-07 00:00:00.000')
Insert into tbl values('3956BR','2010-12-07 00:00:00.000')
Insert into tbl values('3957BR','2010-12-07 00:00:00.000')
Insert into tbl values('3960BR','2010-12-07 00:00:00.000')
Insert into tbl values('3961BR','2010-12-07 00:00:00.000')
Insert into tbl values('3962BR','2010-12-07 00:00:00.000')
Insert into tbl values('3963BR','2010-12-07 00:00:00.000')
Insert into tbl values('3965BR','2010-12-07 00:00:00.000')
Insert into tbl values('3970BR','2010-12-07 00:00:00.000')
Insert into tbl values('3971BR','2010-12-07 00:00:00.000')
Insert into tbl values('3972BR','2010-12-07 00:00:00.000')
Insert into tbl values('3973BR','2010-12-07 00:00:00.000')
Insert into tbl values('3980BR','2010-12-07 00:00:00.000')
Insert into tbl values('3982BR','2010-12-07 00:00:00.000')
Insert into tbl values('3985BR','2010-12-07 00:00:00.000')
Insert into tbl values('3992BR','2010-12-07 00:00:00.000')
Insert into tbl values('3995BR','2010-12-07 00:00:00.000')
Insert into tbl values('4002BR','2010-12-07 00:00:00.000')
Insert into tbl values('4003BR','2010-12-07 00:00:00.000')
Insert into tbl values('4004BR','2010-12-07 00:00:00.000')
Insert into tbl values('4009BR','2010-12-07 00:00:00.000')
Insert into tbl values('4010BR','2010-12-07 00:00:00.000')
Insert into tbl values('4012BR','2010-12-07 00:00:00.000')
Insert into tbl values('4013BR','2010-12-07 00:00:00.000')
Insert into tbl values('4017BR','2010-12-07 00:00:00.000')
Insert into tbl values('4018BR','2010-12-07 00:00:00.000')
Insert into tbl values('4019BR','2010-12-07 00:00:00.000')
Insert into tbl values('3556BR','2010-12-07 00:00:00.000')
Insert into tbl values('3578BR','2010-12-07 00:00:00.000')
Insert into tbl values('3670BR','2010-12-07 00:00:00.000')
Insert into tbl values('3792BR','2010-12-07 00:00:00.000')
Insert into tbl values('3871BR','2010-12-07 00:00:00.000')
Insert into tbl values('3884BR','2010-12-07 00:00:00.000')
Insert into tbl values('3903BR','2010-12-07 00:00:00.000')
Insert into tbl values('3906BR','2010-12-07 00:00:00.000')
Insert into tbl values('3911BR','2010-12-07 00:00:00.000')
Insert into tbl values('3926BR','2010-12-07 00:00:00.000')
Insert into tbl values('3927BR','2010-12-07 00:00:00.000')
Insert into tbl values('3935BR','2010-12-07 00:00:00.000')
Insert into tbl values('3936BR','2010-12-07 00:00:00.000')
Insert into tbl values('3966BR','2010-12-07 00:00:00.000')
Insert into tbl values('3967BR','2010-12-07 00:00:00.000')
Insert into tbl values('3987BR','2010-12-07 00:00:00.000')
Insert into tbl values('3988BR','2010-12-07 00:00:00.000')
Insert into tbl values('3998BR','2010-12-07 00:00:00.000')
Insert into tbl values('4001BR','2010-12-07 00:00:00.000')
Insert into tbl values('4011BR','2010-12-07 00:00:00.000')
Insert into tbl values('4014BR','2010-12-07 00:00:00.000')
Insert into tbl values('4008BR','2010-12-07 00:00:00.000')
Insert into tbl values('3598BR','2010-12-07 00:00:00.000')
Insert into tbl values('3990BR','2010-12-07 00:00:00.000')
Insert into tbl values('3661BR','2010-12-07 00:00:00.000')
Insert into tbl values('3944BR','2010-12-07 00:00:00.000')
Insert into tbl values('3501BR','2010-12-07 00:00:00.000')
Insert into tbl values('3502BR','2010-12-07 00:00:00.000')
Insert into tbl values('3582BR','2010-12-07 00:00:00.000')
Insert into tbl values('3901BR','2010-12-07 00:00:00.000')
Insert into tbl values('3933BR','2010-12-07 00:00:00.000')
Insert into tbl values('3934BR','2010-12-07 00:00:00.000')
Insert into tbl values('3893BR','2010-12-07 00:00:00.000')
Insert into tbl values('3894BR','2010-12-07 00:00:00.000')
Insert into tbl values('3900BR','2010-12-07 00:00:00.000')
Insert into tbl values('3692BR','2010-12-07 00:00:00.000')
Insert into tbl values('3864BR','2010-12-07 00:00:00.000')
Insert into tbl values('3517BR','2010-12-07 00:00:00.000')
Insert into tbl values('3740BR','2010-12-07 00:00:00.000')
Insert into tbl values('3968BR','2010-12-07 00:00:00.000')
Insert into tbl values('3969BR','2010-12-07 00:00:00.000')
Insert into tbl values('3974BR','2010-12-07 00:00:00.000')
Insert into tbl values('3975BR','2010-12-07 00:00:00.000')
Insert into tbl values('3977BR','2010-12-07 00:00:00.000')
Insert into tbl values('3978BR','2010-12-07 00:00:00.000')
Insert into tbl values('3983BR','2010-12-07 00:00:00.000')
Insert into tbl values('3984BR','2010-12-07 00:00:00.000')
Insert into tbl values('3999BR','2010-12-07 00:00:00.000')
Insert into tbl values('4000BR','2010-12-07 00:00:00.000')
Insert into tbl values('4005BR','2010-12-07 00:00:00.000')
Insert into tbl values('4006BR','2010-12-07 00:00:00.000')
Insert into tbl values('3384BR','2010-12-09 00:00:00.000')
Insert into tbl values('3527BR','2010-12-09 00:00:00.000')
Insert into tbl values('3561BR','2010-12-09 00:00:00.000')
Insert into tbl values('3594BR','2010-12-09 00:00:00.000')
Insert into tbl values('3613BR','2010-12-09 00:00:00.000')
Insert into tbl values('3627BR','2010-12-09 00:00:00.000')
Insert into tbl values('3628BR','2010-12-09 00:00:00.000')
Insert into tbl values('3653BR','2010-12-09 00:00:00.000')
Insert into tbl values('3673BR','2010-12-09 00:00:00.000')
Insert into tbl values('3697BR','2010-12-09 00:00:00.000')
Insert into tbl values('3698BR','2010-12-09 00:00:00.000')
Insert into tbl values('3699BR','2010-12-09 00:00:00.000')
Insert into tbl values('3734BR','2010-12-09 00:00:00.000')
Insert into tbl values('3735BR','2010-12-09 00:00:00.000')
Insert into tbl values('3784BR','2010-12-09 00:00:00.000')
Insert into tbl values('3785BR','2010-12-09 00:00:00.000')
Insert into tbl values('3788BR','2010-12-09 00:00:00.000')
Insert into tbl values('3791BR','2010-12-09 00:00:00.000')
Insert into tbl values('3802BR','2010-12-09 00:00:00.000')
Insert into tbl values('3812BR','2010-12-09 00:00:00.000')
Insert into tbl values('3814BR','2010-12-09 00:00:00.000')
Insert into tbl values('3815BR','2010-12-09 00:00:00.000')
Insert into tbl values('3817BR','2010-12-09 00:00:00.000')
Insert into tbl values('3828BR','2010-12-09 00:00:00.000')
Insert into tbl values('3833BR','2010-12-09 00:00:00.000')
Insert into tbl values('3845BR','2010-12-09 00:00:00.000')
Insert into tbl values('3846BR','2010-12-09 00:00:00.000')
Insert into tbl values('3859BR','2010-12-09 00:00:00.000')
Insert into tbl values('3861BR','2010-12-09 00:00:00.000')
Insert into tbl values('3863BR','2010-12-09 00:00:00.000')
Insert into tbl values('3867BR','2010-12-09 00:00:00.000')
Insert into tbl values('3877BR','2010-12-09 00:00:00.000')
Insert into tbl values('3887BR','2010-12-09 00:00:00.000')
Insert into tbl values('3913BR','2010-12-09 00:00:00.000')
Insert into tbl values('3914BR','2010-12-09 00:00:00.000')
Insert into tbl values('3915BR','2010-12-09 00:00:00.000')
Insert into tbl values('3920BR','2010-12-09 00:00:00.000')
Insert into tbl values('3922BR','2010-12-09 00:00:00.000')
Insert into tbl values('3930BR','2010-12-09 00:00:00.000')
Insert into tbl values('3937BR','2010-12-09 00:00:00.000')
Insert into tbl values('3938BR','2010-12-09 00:00:00.000')
Insert into tbl values('3940BR','2010-12-09 00:00:00.000')
Insert into tbl values('3942BR','2010-12-09 00:00:00.000')
Insert into tbl values('3943BR','2010-12-09 00:00:00.000')
Insert into tbl values('3945BR','2010-12-09 00:00:00.000')
Insert into tbl values('3946BR','2010-12-09 00:00:00.000')
Insert into tbl values('3947BR','2010-12-09 00:00:00.000')
Insert into tbl values('3948BR','2010-12-09 00:00:00.000')
Insert into tbl values('3949BR','2010-12-09 00:00:00.000')
Insert into tbl values('3951BR','2010-12-09 00:00:00.000')
Insert into tbl values('3952BR','2010-12-09 00:00:00.000')
Insert into tbl values('3954BR','2010-12-09 00:00:00.000')
Insert into tbl values('3955BR','2010-12-09 00:00:00.000')
Insert into tbl values('3956BR','2010-12-09 00:00:00.000')
Insert into tbl values('3957BR','2010-12-09 00:00:00.000')
Insert into tbl values('3960BR','2010-12-09 00:00:00.000')
Insert into tbl values('3961BR','2010-12-09 00:00:00.000')
Insert into tbl values('3962BR','2010-12-09 00:00:00.000')
Insert into tbl values('3963BR','2010-12-09 00:00:00.000')
Insert into tbl values('3965BR','2010-12-09 00:00:00.000')
Insert into tbl values('3970BR','2010-12-09 00:00:00.000')
Insert into tbl values('3971BR','2010-12-09 00:00:00.000')
Insert into tbl values('3972BR','2010-12-09 00:00:00.000')
Insert into tbl values('3973BR','2010-12-09 00:00:00.000')
Insert into tbl values('3980BR','2010-12-09 00:00:00.000')
Insert into tbl values('3982BR','2010-12-09 00:00:00.000')
Insert into tbl values('3985BR','2010-12-09 00:00:00.000')
Insert into tbl values('3992BR','2010-12-09 00:00:00.000')
Insert into tbl values('4002BR','2010-12-09 00:00:00.000')
Insert into tbl values('4003BR','2010-12-09 00:00:00.000')
Insert into tbl values('4004BR','2010-12-09 00:00:00.000')
Insert into tbl values('4009BR','2010-12-09 00:00:00.000')
Insert into tbl values('4010BR','2010-12-09 00:00:00.000')
Insert into tbl values('4012BR','2010-12-09 00:00:00.000')
Insert into tbl values('4013BR','2010-12-09 00:00:00.000')
Insert into tbl values('4017BR','2010-12-09 00:00:00.000')
Insert into tbl values('4018BR','2010-12-09 00:00:00.000')
Insert into tbl values('4019BR','2010-12-09 00:00:00.000')
Insert into tbl values('4021BR','2010-12-09 00:00:00.000')
Insert into tbl values('4022BR','2010-12-09 00:00:00.000')
Insert into tbl values('3556BR','2010-12-09 00:00:00.000')
Insert into tbl values('3578BR','2010-12-09 00:00:00.000')
Insert into tbl values('3670BR','2010-12-09 00:00:00.000')
Insert into tbl values('3792BR','2010-12-09 00:00:00.000')
Insert into tbl values('3871BR','2010-12-09 00:00:00.000')
Insert into tbl values('3884BR','2010-12-09 00:00:00.000')
Insert into tbl values('3903BR','2010-12-09 00:00:00.000')
Insert into tbl values('3906BR','2010-12-09 00:00:00.000')
Insert into tbl values('3911BR','2010-12-09 00:00:00.000')
Insert into tbl values('3926BR','2010-12-09 00:00:00.000')
Insert into tbl values('3927BR','2010-12-09 00:00:00.000')
Insert into tbl values('3935BR','2010-12-09 00:00:00.000')
Insert into tbl values('3936BR','2010-12-09 00:00:00.000')
Insert into tbl values('3966BR','2010-12-09 00:00:00.000')
Insert into tbl values('3967BR','2010-12-09 00:00:00.000')
Insert into tbl values('3987BR','2010-12-09 00:00:00.000')
Insert into tbl values('3988BR','2010-12-09 00:00:00.000')
Insert into tbl values('3998BR','2010-12-09 00:00:00.000')
Insert into tbl values('4001BR','2010-12-09 00:00:00.000')
Insert into tbl values('4011BR','2010-12-09 00:00:00.000')
Insert into tbl values('4014BR','2010-12-09 00:00:00.000')
Insert into tbl values('4008BR','2010-12-09 00:00:00.000')
Insert into tbl values('3598BR','2010-12-09 00:00:00.000')
Insert into tbl values('3990BR','2010-12-09 00:00:00.000')
Insert into tbl values('3661BR','2010-12-09 00:00:00.000')
Insert into tbl values('3944BR','2010-12-09 00:00:00.000')
Insert into tbl values('3582BR','2010-12-09 00:00:00.000')
Insert into tbl values('3901BR','2010-12-09 00:00:00.000')
Insert into tbl values('3893BR','2010-12-09 00:00:00.000')
Insert into tbl values('3894BR','2010-12-09 00:00:00.000')
Insert into tbl values('3692BR','2010-12-09 00:00:00.000')
Insert into tbl values('3517BR','2010-12-09 00:00:00.000')
Insert into tbl values('3740BR','2010-12-09 00:00:00.000')
Insert into tbl values('3968BR','2010-12-09 00:00:00.000')
Insert into tbl values('3969BR','2010-12-09 00:00:00.000')
Insert into tbl values('3974BR','2010-12-09 00:00:00.000')
Insert into tbl values('3975BR','2010-12-09 00:00:00.000')
Insert into tbl values('3977BR','2010-12-09 00:00:00.000')
Insert into tbl values('3978BR','2010-12-09 00:00:00.000')
Insert into tbl values('3999BR','2010-12-09 00:00:00.000')
Insert into tbl values('4000BR','2010-12-09 00:00:00.000')
Insert into tbl values('4005BR','2010-12-09 00:00:00.000')

declare @lpostdate table
(
prodid nvarchar(500),
posteddate datetime
)

DECLARE @poste VARCHAR(8)
SELECT @poste = CONVERT(VARCHAR(8),min(posteddate),112) FROM tbl
PRINT @poste

;with dte as
(
select d = dateadd(dd,datediff(dd,0,getdate()),0) - 10
union all
select d = d + 1 from dte where d < @poste
),
prodid as
(select distinct prodid from tbl)

INSERT INTO @lpostdate(prodid,posteddate)

(select p.prodid, coalesce(a.posteddate + 1,(select max(d) from dte)) as posteddate
from prodid p
left join
(
select p.prodid, posteddate = max(dte.d) -- last missing day
from dte
cross join
prodid p
left join tbl t2
on dte.d = t2.posteddate
and p.prodid = t2.prodid
where t2.prodid is null
group by p.prodid
) a
on a.prodid = p.prodid)

Select * from @lpostdate

4021BR 2010-12-08 00:00:00.000
4022BR 2010-12-08 00:00:00.000

Showing 8 th date for 4021BR,4022BR.But there is no record for 8 th.

Can u please help me for this?

Visa.G

visa123
Yak Posting Veteran

54 Posts

Posted - 2010-12-09 : 07:42:50

i think the below query is working fine

SELECT p_o.prodid, MAX( p_o.posteddate ) posteddate
FROM prodid p_o
WHERE NOT EXISTS
(
SELECT *
FROM prodid p_i
WHERE p_i.prodid = p_o.prodid
AND p_o.posteddate = DATEADD( DAY, 1, p_i.posteddate )
)
GROUP BY p_o.prodid

Visa.G

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-09 : 07:49:17

You are setting @poste to the min date in the table and generating the cte dte up to that date - so you will only ever match on the fisrt rows.
Try changing the min to max.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources