July 21, 2014 at 3:01 am
Dear all
I have a data table which has list of bills with date and outstanding total.
If the bills dates between 0 and 30 I like to insert the Outstanding total into Colum K ,
If the bills dates between 31 and 60 I like to insert the Outstanding total into Colum L ,
If the bills dates between 61 and 90 I like to insert the Outstanding total into Colum M and so on ..
I tried below formula but it is not working
CASE DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 0 and 30 THEN Insert into (Ac_Billbook.OutstandingTotal) Value ('K')
CASE DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 31 and 60 THEN Insert into (Ac_Billbook.OutstandingTotal) Value ('L')
CASE DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 61 and 90 THEN Insert into (Ac_Billbook.OutstandingTotal) Value ('M')
Can you please help?
Thanks in advance
July 21, 2014 at 3:14 am
1) Share table definition
2) Sample data
3) your desired output
This will help us to provide a better response against the question.
July 21, 2014 at 3:17 am
rtopal1907 (7/21/2014)
Dear allI have a data table which has list of bills with date and outstanding total.
If the bills dates between 0 and 30 I like to insert the Outstanding total into Colum K ,
If the bills dates between 31 and 60 I like to insert the Outstanding total into Colum L ,
If the bills dates between 61 and 90 I like to insert the Outstanding total into Colum M and so on ..
I tried below formula but it is not working
CASE DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 0 and 30 THEN Insert into (Ac_Billbook.OutstandingTotal) Value ('K')
CASE DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 31 and 60 THEN Insert into (Ac_Billbook.OutstandingTotal) Value ('L')
CASE DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 61 and 90 THEN Insert into (Ac_Billbook.OutstandingTotal) Value ('M')
Can you please help?
Thanks in advance
whats in columns A-J in your spreadsheet?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 21, 2014 at 3:25 am
This is my whole formula
SELECT Entities.Code, Entities.Name, Ac_Billbook.MatterRef, Ac_Billbook.BillDate, Ac_Billbook.Ref, Matters.FeeEarnerRef, Users.FullName, Ac_Billbook.OutstandingTotal, CaseTypes.CodeName, CaseTypes.Description,
CASE DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 0 and 30 THEN Insert into (Ac_Billbook.OutstandingTotal) Value ('K')
CASE DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 31 and 60 THEN Insert into (Ac_Billbook.OutstandingTotal) Value ('L')
CASE DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 61 and 90 THEN Insert into (Ac_Billbook.OutstandingTotal) Value ('M')
END AS Aged
FROM Partner.dbo.Ac_Billbook Ac_Billbook, Partner.dbo.CaseTypes CaseTypes, Partner.dbo.Entities Entities, Partner.dbo.Matters Matters, Partner.dbo.Users Users
thanks
July 21, 2014 at 3:42 am
as a start...does the following query give you the results you expect?
SELECT
Entities.Code
, Entities.Name
, Ac_Billbook.MatterRef
, Ac_Billbook.BillDate
, Ac_Billbook.Ref
, Matters.FeeEarnerRef
, Users.FullName
, Ac_Billbook.OutstandingTotal
, CaseTypes.CodeName
, CaseTypes.Description,
, CASE WHEN DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 0 and 30 THEN Ac_Billbook.OutstandingTotal else 0 end age0to30
, CASE WHEN DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 31 and 60 THEN Ac_Billbook.OutstandingTotal else 0 end age31to60
, CASE WHEN DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 61 and 90 THEN Ac_Billbook.OutstandingTotal else 0 end age61to90
FROM <YOUR TABLES AND JOINS HERE>
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 21, 2014 at 4:19 am
thanks for your reply I get these error messages
1- incorrect syntax near the keyword'CASE'
2- incorrect syntax near the keyword'age31to60'
3- incorrect syntax near the keyword'age61to90'
I have put the formula as below
SELECT Entities.Code, Entities.Name, Ac_Billbook.MatterRef, Ac_Billbook.BillDate, Ac_Billbook.Ref, Matters.FeeEarnerRef, Users.FullName, Ac_Billbook.OutstandingTotal, CaseTypes.CodeNam, CaseTypes.Description,
CASE WHEN DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 0 and 30 THEN Ac_Billbook.OutstandingTotal else 0 end age0to30
CASE WHEN DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 31 and 60 THEN Ac_Billbook.OutstandingTotal else 0 end age31to60
CASE WHEN DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 61 and 90 THEN Ac_Billbook.OutstandingTotal else 0 end age61to90
FROM Partner.dbo.Ac_Billbook Ac_Billbook, Partner.dbo.CaseTypes CaseTypes, Partner.dbo.Entities Entities, Partner.dbo.Matters Matters, Partner.dbo.Users Users
WHERE Ac_Billbook.EntityRef = Entities.Code AND Entities.Code = Matters.EntityRef AND Ac_Billbook.MatterRef = Matters.Number AND Matters.CaseTypeRef = CaseTypes.Code AND Matters.FeeEarnerRef = Users.Code
July 21, 2014 at 5:41 am
rtopal1907 (7/21/2014)
thanks for your reply I get these error messages1- incorrect syntax near the keyword'CASE'
2- incorrect syntax near the keyword'age31to60'
3- incorrect syntax near the keyword'age61to90'
I have put the formula as below
SELECT Entities.Code, Entities.Name, Ac_Billbook.MatterRef, Ac_Billbook.BillDate, Ac_Billbook.Ref, Matters.FeeEarnerRef, Users.FullName, Ac_Billbook.OutstandingTotal, CaseTypes.CodeNam, CaseTypes.Description,
CASE WHEN DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 0 and 30 THEN Ac_Billbook.OutstandingTotal else 0 end age0to30
CASE WHEN DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 31 and 60 THEN Ac_Billbook.OutstandingTotal else 0 end age31to60
CASE WHEN DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 61 and 90 THEN Ac_Billbook.OutstandingTotal else 0 end age61to90
FROM Partner.dbo.Ac_Billbook Ac_Billbook, Partner.dbo.CaseTypes CaseTypes, Partner.dbo.Entities Entities, Partner.dbo.Matters Matters, Partner.dbo.Users Users
WHERE Ac_Billbook.EntityRef = Entities.Code AND Entities.Code = Matters.EntityRef AND Ac_Billbook.MatterRef = Matters.Number AND Matters.CaseTypeRef = CaseTypes.Code AND Matters.FeeEarnerRef = Users.Code
you are missing commas before the second and third CASE statements.....see my previous post
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 21, 2014 at 5:49 am
you were right I missed commas, it has been fixed now and working perfectly thank you very much for your help.
Also how do I change Colum header name? For example instead of "aged0to30" I like to put "Current"
is it possible?
thanks again
July 21, 2014 at 5:54 am
rtopal1907 (7/21/2014)
you were right I missed commas, it has been fixed now and working perfectly thank you very much for your help.Also how do I change Colum header name? For example instead of "aged0to30" I like to put "Current"
is it possible?
thanks again
no problem....just change it
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 21, 2014 at 6:08 am
If I change it gives me syntax error. I wondered if I need to use some kinde of brackets or comas before and after "Current"
July 21, 2014 at 6:16 am
oops...too hasty in my response...."Current" is a reserved word...this can be overcome by enclosing in square brackets or alter the name to something that isn't reserved...
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 21, 2014 at 6:32 am
you are a genius J Livingston SQL, square brackets worked. Thank you for your time.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply