[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

SQL Query Help Needed

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
rustypoot
Asked:
rustypoot
  • 5
  • 4
  • 2
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
rustypootAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Jerry_JusticeCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
rustypootAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
rustypootAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
?

>>  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
 
rustypootAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
rustypootAuthor Commented:
Thanks Scott!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now