Select Query help 1

[kpetree10]

I'm trying to use a table 'oelinhst_sql' to calculate on time delivery. What I need it to do is count the number of records in the current month then count them again where the request date equals the shipping date then divide the count from where they match by the total number of records. This is what my query looks like right now...

select(select COUNT(line_seq_no) from oelinhst_sql where MONTH(GETDATE())=MONTH(shipped_dt) and YEAR(GETDATE())=YEAR(shipped_dt) and req_ship_dt=shipped_dt)/
(select COUNT(line_seq_no) from oelinhst_sql where MONTH(GETDATE())=MONTH(shipped_dt) and YEAR(GETDATE())=YEAR(shipped_dt))

However when I run it I either get 0 as the result, not the number it should be. Can someone tell me what I'm doing wrong? Thanks!

 

[gmmastros]

Count returns an integer. Since you are dividing 2 counts, you are experiencing integer math. Basically, when you divide one integer by another, the result is an integer.

For example.

Select 99 / 100

The select query returns 0.

However, if you introduce a decimal in to the math, the result will be a decimal. Ex:

Select 1.0 * 99 / 100

I suggest you try this:

select [!]1.0 * [/!](select COUNT(line_seq_no) from oelinhst_sql where MONTH(GETDATE())=MONTH(shipped_dt) and YEAR(GETDATE())=YEAR(shipped_dt) and req_ship_dt=shipped_dt)/
(select COUNT(line_seq_no) from oelinhst_sql where MONTH(GETDATE())=MONTH(shipped_dt) and YEAR(GETDATE())=YEAR(shipped_dt))

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kpetree10]

That works! Thank you!

 

[markros]

Try the following:

select SUM(case when Req_Ship_Dt = Shipped_dt then 1 else 0 end)*1.0/NULLIF(count(Line_Seq_No),0) as [Percent Of Shipped On Time]

FROM OeLinHst_SQL Where shipped_dt >=
dateadd(year,datediff(year, '19000101',CURRENT_TIMESTAMP),'19000101')
AND shipped_dt < dateadd(year,1+datediff(year, '19000101',CURRENT_TIMESTAMP),'19000101')

I changed your query to do only one scan, not two, also also I changed your WHERE condition to attempt to utilize indexes if you have then on Shipped_dt.

You may want to review this blog post
Only In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Code
to see why it's important to write sargable queries.





PluralSight Learning Library

 

[markros]

I forgot to mention the integer math problem and why I introduced 1.0 multiplier to perform implicit conversion to decimals but I see that George already explained that while I was writing my reply. Also, the above is not really percent, for percent we need 100.0.

I am just not sure how to name the result when it is not percent, but fraction.



PluralSight Learning Library