Solved

SQL Query Help Needed

Posted on 2014-01-08
12
237 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
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 142

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 142

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

772 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