Solved

TSQL: create aging report by 30, 60, 90 days

Posted on 2010-08-25
18
1,995 Views
Last Modified: 2012-05-10
Hello All

THanks for reading my question.  I want to create an aging report by 30, 60, 90 days (these #'s will be in dropdown box)

This is my code so far
SELECT sched_id, sched_date, pt_id
FROM     Schedule
WHERE  (sched_paid = @status) AND (sched_date < @today + @aging)
ORDER BY sched_date

@ aging is suppose to link to a dropdown box
But i keep getting this error message
"tsql string was not recognized as a valid datetime"

any ideas on how I can do this

Thanks in advance
0
Comment
Question by:ptemo
  • 10
  • 7
18 Comments
 
LVL 3

Expert Comment

by:omnimaven
ID: 33528053
instead of using @today + @aging, use something like

DATEADD(day, @aging, @today)

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33528120
I agree. please award points above, but the explanation is the concatenation operator will take precedence over the comparison.

http://www.e-e.com/A_3533-Effective-SQL-Understanding-Order-of-Operations.html

Therefore, you are ending up with something like '2008-08-2630' or '08/26/201030' which you can see isn't a valid datetime.

DateAdd should do the trick OR convert @today to datetime then +@aging will work.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33528168
Also, maybe I am reading this wrong, but normally aging is:

due_date > {30 days ago} == current
due_date <= {30 days ago} and > {60 days ago} == 30
due_date <= {60 days ago} and > {90 days ago} == 60
due_date <= {90 days ago} and > {120 days ago} == 90
due_date <= {120 days ago} == way past due *laughing*

If so then:
(sched_date < convert(datetime, @today) + @aging)

Would not yield the correct results as that will get you all records that are scheduled for a date older than 30 days from now. Therefore, you may want to switch that to -@aging and add a second check:

(sched_date <= convert(datetime, @today) - @aging
and sched_date > convert(datetime, @today) - (@aging+30))
0
 

Author Comment

by:ptemo
ID: 33528184
Thank you guys so much for your help

I tried this

WHERE  (sched_paid = @status) AND (sched_status = 'Yes') AND (sched_date < DATEADD(day, @aging, @today)

Sorry I'm new to TSQL, but i'm still getting an error message.  What do you think can be the problem?  Thanks again
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33528200
You left out a bracket )

i.e.,

WHERE  (sched_paid = @status) AND (sched_status = 'Yes') AND (sched_date < DATEADD(day, @aging, @today)

should be:

WHERE  (sched_paid = @status) AND (sched_status = 'Yes') AND (sched_date < DATEADD(day, @aging, @today))

Note: sched_paid is being filtered on @status and sched_status on literal 'Yes'. Just going by the name, did you possibly mean for those to be reversed (i.e., sched_status = @status).
0
 

Author Comment

by:ptemo
ID: 33528222
mwvisa1

Thanks for your help so far.  I tried your suggestion, but i now get a error message that says "Failed to conver parameter value from a decimal to datetime"

Should I convert it first

thanks
PS does it matter if a literal or variabl goes first if you use an "And"
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33528271
What are you passing as the value of @today ?

Re: PS does it matter if a literal or variabl goes first if you use an "And"
I was asking if the below is really what you meant to write:
(sched_paid = @status) AND (sched_status = 'Yes')

Or if you might have wanted this:
(sched_status = @status) AND (sched_paid = 'Yes')

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33528278
If your today value is something like 20100826 then you may have to do this:

convert(datetime, convert(char(8), @today), 112)
0
 

Author Comment

by:ptemo
ID: 33528354
today value is today's date from a label box

So this is what I got so far.
WHERE  (sched_paid = @status) AND (sched_status = 'Yes') AND (sched_date < DATEADD(day, @aging, CONVERT(datetime, CONVERT(char(8), @today), 112)))

does that appear right?

thanks
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33528442
Yes, it appears right if all assumptions are correct. Did it work without error this time?
0
 

Author Comment

by:ptemo
ID: 33530073
Hello again,

It actually didn't work.  I received an error message "failed to convert parameter value from a decimal to a datetime
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33530096
You need to find what the actual value of @today is coming across as to solve the issue. We cannot provide the correct syntax without knowing what that is for sure ... The other thing to check is if sched_date is actual a datetime in your system or is it stored as a decimal.
0
 

Author Comment

by:ptemo
ID: 33535164
Hello

sched_date is a datetime and the @today is just reading off a label box that is loaded with a page load event, that tells today's date.  Hope that helps.

thank you
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33535999
Yes, but what is the format. You need to ensure that it is coming over to SQL the way that you think it is. If this is .NET you may want to use the ToString("yyyy-MM-dd") as the format.
0
 

Author Comment

by:ptemo
ID: 33541287
Hello

I'm currently using this

date.Text = DateTime.Today.ToString("M/d/yyyy");

thanks
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33541333
What does the SQL query look like from the VB.NET (SqlCommand)?

And try that as :

date.Text = DateTime.Today.ToString("yyyy-MM-dd");

Also :

Since you just want to use today's date anyway, try it like this in the SQL.


(sched_date < DATEDIFF(day, 0, GETDATE())+@aging)

Open in new window

0
 

Author Comment

by:ptemo
ID: 33552824
Hello mwvisa1,

Thanks for all your help.  When I tried your latest code I get an error message "String was not recognized as a valid datetime"

So I then tried putting 1/30/1900 instead of 30 days.  And it worked, however when I tried to view it in the browswer I get "Argument data type nvarchar is invalid for argument 2 of dateadd function. "

Do you have any thoughts? Do you think I should abandon this route, and try another method?

thanks again for all your help
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 33552928
Did you try with current date using db functions as I showed? I really suspect you are having errors due to one system formatting the date as mm/dd/yyyy and other reading as dd/mm/yyyy but I may be wrong but it seems silly anyway if always passing today's date to not utilize the db's date and time as you avoid client specific language configuration differences which are what control the date format used by default.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now