Pulling daily data - SQL Server Forums

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())

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources