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
 Fetch Last 2 Bills

Author  Topic 

nirene
Yak Posting Veteran

98 Posts

Posted - 2015-04-02 : 03:13:53
I want to fetch all bills (except cancelled bills) for a given month and fetch the previous bill date.
This is to find out the frequency of a vehicle visit to a workshop.

Sample Data

Division,ChasNo,BillDt,Status
D1,C1,01/01/2015,Null
D1,C1,15/01/2015,Null
D1,C1,23/02/2015,Null
D2,C1,26/02/2015,Null
D2,C2,02/01/2015,Null
D2,C2,16/01/2015,Null
D2,C2,21/02/2015,Null
D1,C3,24/01/2015,Cancelled

Result Expected
D1,C1,23/01/2015,15/01/2015
D2,C1,26/02/2015,Null
D2,C2,21/02/2015,16/01/2015

What I tried is

Declare @Mh smallint,@Yr smallint
Select @Mh=2,@Yr=2015

Select Division,ChasNo,BillDt Into #CMBills from Service_Bills Where Month(BillDt)=@Mh and Year(BillDt)=@Yr and Status Is Null

Select SB.Division,SB.ChasNo,CMB.BillDt as CurBillDt,Max(SB.BillDt) as PreBillDt,Datediff(day,Max(SB.BillDt),Max(CMB.BillDt)) as DiffDays from #CMBills CMB
Left Join Service_Bills SB On SB.Division=CMB.Division and SB.ChasNo=CMB.ChasNo and SB.Status Is Null and SB.BillDt<CMB.BillDt
Group By SB.Division,SB.ChasNo,CMB.BillDt

But I'm not getting details for all the bills I fetched.

Thanks in advance.

Regards
Eugene

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-04-02 : 04:51:23
Could be something like:
with cte(division,chasno,billdt,rn)
sd (select division
,chasno
,billdt
,row_number() over(partition by division,chasno order by billdt desc) as rn
from service_bills
where billdt>=convert(date,str(@yr*10000+@mh*100+1))
and billdt<dateadd(mm,1,convert(date,str(@yr*10000+@mh*100+1)))
and status is null
)
select a.division
,a.chasno
,a.billdt as curbilldt
,b.billdt as prebilldt
from cte as a
left outer join cte as b
on b.division=a.division
and b.chasno=a.chasno
and b.rn=a.rn+1
where a.rn=1
Go to Top of Page

nirene
Yak Posting Veteran

98 Posts

Posted - 2015-04-02 : 05:34:37
I'm using SQL Server 2005. When I tried it says 'Incorrect syntax near SD'



Go to Top of Page

huangchen
Starting Member

37 Posts

Posted - 2015-04-02 : 05:50:47
unspammed
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-04-02 : 08:53:47
Sorry, should have been:
with cte(division,chasno,billdt,rn)
as (select division
,chasno
,billdt
,row_number() over(partition by division,chasno order by billdt desc) as rn
from service_bills
where billdt>=convert(date,str(@yr*10000+@mh*100+1))
and billdt<dateadd(mm,1,convert(date,str(@yr*10000+@mh*100+1)))
and status is null
)
select a.division
,a.chasno
,a.billdt as curbilldt
,b.billdt as prebilldt
from cte as a
left outer join cte as b
on b.division=a.division
and b.chasno=a.chasno
and b.rn=a.rn+1
where a.rn=1
Go to Top of Page
   

- Advertisement -