Solved

SQL Query Help Needed

Posted on 2014-01-08
12
233 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
 
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:ScottPletcher
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 69

Expert Comment

by:ScottPletcher
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:ScottPletcher
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:
ScottPletcher 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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 …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now