Solved

Find avg day in sql

Posted on 2014-10-02
1
131 Views
Last Modified: 2014-10-12
I want to find avg cid_date. @day_num is going to be 30, 60, 90, 120, 150, 180 days only. It will be from a dropdownlist from asp.net page.
Am I on the right track? To set @day_num to negative looks a bit odd. My result looks fine.

declare @ave_Days as int
DECLARE @day_num int
SET @day_num = -60

set ave_Days = (SELECT COUNT(DISTINCT cid_DATE) / case @day_num                                                              
                                                                    WHEN -60 THEN 2
                                                                    WHEN -90 THEN 3
                                                                    WHEN -120 THEN 4
                                                                    WHEN -150 THEN 5
                                                                    WHEN -180 THEN 6
                                                                    ELSE 1 end
                                    FROM         dbo.mytable
                                    where cid_DATE >= DATEADD(DD, @day_num,getdate()) and cid_DATE < GETDATE())
                                   
print ave_Days
0
Comment
Question by:VBdotnet2005
1 Comment
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40357901
You don't need a CASE statement:

set ave_Days = (SELECT COUNT(DISTINCT cid_DATE) / ABS(@day_num / 30)
                                     FROM         dbo.mytable
                                     where cid_DATE >= DATEADD(DD, @day_num,getdate()) and cid_DATE < GETDATE())
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore 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.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

832 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