Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Pulling daily data

Author  Topic 

wsilage
Yak Posting Veteran

82 Posts

Posted - 2014-09-16 : 09:20:33
I have this query that I need to have run everyday, but they only need 5 days worth. I am trying to figure out the best way to pull the data. This is the way they want it pulled.

Example

Current data on top 9/16/2014
Monday - 09/15/2014
Friday - 09/12/2014
Thursday - 09/11/2014
Wednesday - 09/10/2014

How can I easily do this I started putting it into a table, but how would I take out the oldest data? I guess I could do a Delete statement to delete the MAX date

what it will look like

Todays data
Total Count chartes
HCFA 2 10.00
UB 5 30.00



Date: 9/15/2014
Total Count chartes
HCFA 4 15.00
UB 6 20.00

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-16 : 09:38:31
add a WHERE clause:


WHERE myDate > dateadd(days, -5, getdate())
Go to Top of Page

wsilage
Yak Posting Veteran

82 Posts

Posted - 2014-09-16 : 10:35:35
I just actually realized something. I can't do it this way. I have to make sure in my table that I am inserting into I keep the data but only pull out the ones that I need.

My questions is, How can I use this update statement to it only populates certain fields. See below.

Here is my update statement, So it goes into a specific column/row. I need to make sure the have in there the query this [Todays Date] = '09/09/2014'. Where would put that? This field is in the tabled called T_AXA_BreakDown_Claims


update T_AXA_BreakDown_Claims set [Claim Count Cons] = a.[Claim Count Con]
from
(
select

case
when CLM_form = 'H' then 'HCFA'
When clm_form = 'U' then 'UB'
else clm_form
end as clm_form,
count (clm_id1) as [Claim Count Con], sum (clm_tchg) as [Charges Con]
from impact.dbo.clm
where
clm_cc1 = 13490
and clm_rcvd > '09/09/2014'
and clm_61a = 'Con'
and clm_clir <> 7974
group by clm_form
) a

Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-16 : 11:32:27
You already have a where clause in the subquery. that should do it
Go to Top of Page
   

- Advertisement -