Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Query Help Needed

Posted on 2014-01-08
12
Medium Priority
?
244 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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 70

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 70

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 70

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 70

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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 response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

618 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