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

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

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'

huangchen
Starting Member

37 Posts

Posted - 2015-04-02 : 05:50:47

unspammed

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources