?
Solved

SQL Query Help Needed

Posted on 2014-01-08
12
Medium Priority
?
243 Views
Last Modified: 2014-01-09
Hello,

I have the SQL below that gives me the Aggregations for Today. I need the same Aggregation ROLLING for past 5 days. how can I do this?

Example - If I run the report today, it will give me these Aggrgations for today and past 5 days. If I run the report tomorrow, then, I will get the aggregations for Tomorrow and past 5 days.

SELECT
GETDATE() as TodayDate,
AVG(DATEDIFF(day,BV.disdate,getdate())) AS AvgDaysSinceDischg,                                                                    
BV.FacilityName,                                                  
BV.PtStatus,                    
COUNT(BV.Number) as AccountNumberCount,                
SUM(BV.Balance) as CurrentBalanceSUM
   
FROM vw_BAR_Patient BV WITH(NOLOCK)
LEFT JOIN AbstractData AB WITH(NOLOCK)
ON BV.SourceID = AB.SourceID
AND BV.VisitID = AB.VisitID

WHERE
BV.BarStatus = 'UB'
AND BV.BarDisSer != ''
AND BV.BarDisSer != GETDATE()
AND ISNULL(BV.ChgTotal, 0) BETWEEN 0.01 AND  999999.99
AND ISNULL(DATEDIFF(day, BV.BarDisSer, GETDATE()),0) BETWEEN 5 AND 99999
AND BV.UnitNumber <> 'BO%'
AND BV.Facility = 'TPM'
AND 1 = CASE
                  WHEN BV.PtStatus = 'REC'
                  OR BV.AcctType = 'REC'
            THEN
                  CASE WHEN BV.DisDate IS NOT NULL
                        THEN 1
                        ELSE 0
                  END
            ELSE 1
            END  
AND AB.[Status] <> 'FINAL'
GROUP BY
BV.FacilityName,                                                
BV.PtStatus
0
Comment
Question by:rustypoot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39766593
do you mean that you want the results of that query to be "stored", once per day, and have the "new" query to fetch the resulting data for the last 5 days?
if that is the case, "all" you need to do is 4 things:
* create a table to hold the daily result
* create a daily job to insert the results of your query to that able
* create your query based on that new table
* create a daily job to eventually delete "old" data from that table
0
 

Author Comment

by:rustypoot
ID: 39766644
I need to do the logic in SQL itself! I do not have rights to database to create jobs or tables!

Can this be done in multiple Temp tables in SQL? If so how?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39766657
from what I can understand from the query, you cannot do it, because the rows will "change", and hence you cannot do it. but this is just an "educated guess", not really knowing the data you have.

and I don't think you "need" to do the logic in sql itself. if you are imposed such a restriction, it's not only likely a show-stopper, but also most likely blocking you from building the most efficient solution...
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 3

Expert Comment

by:Jerry_Justice
ID: 39766698
I would make  a sub query exactly the same as your main query duplicated 5 times.

(Select dateadd(d,-1, getdate()) as PriorDate1  ... etc etc..   )

Change all the occurences of GetDate to Select dateadd(d,-1, getdate()).

Then do it all again only change the -1 in the DateAdd function to -2 for PriorDate2..

etc.. etc..

That should get you started at least.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39766708
Try this; I can't test it, so you might need to make some adjustments to the date calcs.

The "DATEADD(DAY, DATEDIFF(DAY, 0, <datetime>), 0)" is just an efficient way to strip the time off a datetime.  I used that on BV.BarDisSer too, just in case it is a datetime -- it won't hurt anything if it's only a date.


SELECT
GETDATE() as TodayDate,
DATEADD(DAY, DATEDIFF(DAY, 0, BV.BarDisSer) - past_day, 0) AS Reporting_Date,
AVG(DATEDIFF(day,BV.disdate,getdate())) AS AvgDaysSinceDischg,                                                                    
BV.FacilityName,                                                  
BV.PtStatus,
COUNT(BV.Number) as AccountNumberCount,                
SUM(BV.Balance) as CurrentBalanceSUM

FROM vw_BAR_Patient BV WITH(NOLOCK)
CROSS JOIN (
    SELECT 0 AS past_day UNION ALL
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5
) AS past_days
   
LEFT JOIN AbstractData AB WITH(NOLOCK)
ON BV.SourceID = AB.SourceID
AND BV.VisitID = AB.VisitID

WHERE
BV.BarStatus = 'UB'
AND BV.BarDisSer != ''
AND BV.BarDisSer != GETDATE()
AND BV.ChgTotal BETWEEN 0.01 AND  999999.99
AND BV.BarDisSer >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - past_day - 5, 0)
AND BV.UnitNumber <> 'BO%'
AND BV.Facility = 'TPM'
AND 1 = CASE
                  WHEN BV.PtStatus = 'REC'
                  OR BV.AcctType = 'REC'
            THEN
                  CASE WHEN BV.DisDate IS NOT NULL
                        THEN 1
                        ELSE 0
                  END
            ELSE 1
            END  
AND AB.[Status] <> 'FINAL'
GROUP BY
DATEADD(DAY, DATEDIFF(DAY, 0, BV.BarDisSer) - past_day, 0),
BV.FacilityName,                                                
BV.PtStatus
0
 

Author Comment

by:rustypoot
ID: 39766756
Scott,

Thank you

Instead of  BV.BarDisSer I used GetDate() as the aggregations are based on GetDate(). But the Aggregations are repeating for each day! I am getting the same numbers for all the columns...
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39766782
Hard to do "air debugging" ... I don't see data or even the actual query that you are running!

I'm assuming you mean you changed this:

DATEADD(DAY, DATEDIFF(DAY, 0, BV.BarDisSer) - past_day, 0) AS Reporting_Date,

to this:

DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - past_day, 0) AS Reporting_Date,

and the GROUP BY likewise.  And that makes perfect sense.


I would still think this line:

AND BV.BarDisSer >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - past_day - 5, 0)

should produce different totals for different values of past_day, since more days should be included in the total for earlier days.  That is, 5 days ago should show a higher total than 1 day ago.
0
 

Author Comment

by:rustypoot
ID: 39766825
AND BV.BarDisSer >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - past_day - 5, 0)

does give different number for each day but they are incorrect numbers...
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39766833
?

>>  But the Aggregations are repeating for each day! I am getting the same numbers for all the columns... <<

I would think the code above would produce different aggregates, assuming that data is separately present for 5, 6, 7, 8, 9 and 10 days ago.
0
 

Author Comment

by:rustypoot
ID: 39766845
AND BV.BarDisSer >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - past_day - 5, 0)

This code does give different number for each day. With this code, it is not repeating the same numbers for each day. The numbers repeated when I Used GetDate().

But the code:
AND BV.BarDisSer >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - past_day - 5, 0)

does not give me the correct results. I am getting erroneous numbers for each day
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 39768202
I'm sorry, that's not enough detail for me to debug it.

You need to review the date arithmetic and its results, and adjust it until you get the totals you need.
0
 

Author Closing Comment

by:rustypoot
ID: 39769849
Thanks Scott!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

752 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