January 24, 2013 at 8:25 am
I have table that stores a TransDate, GroupCode, Symbol and Price. I have already created my SSRS report to export the data into Excel where each GroupCode is its own tab with dates going down and Symbols across with amounts in the middle.
What I'm needing is for every tab to have all dates line up. The issue is not all Symbols in a Group Code have data for every date.
I was going to create a table to store 2 years worth of dates from the current date. I would then do 2 nest cursors (yes I know avoid cursors like the plague hence my post). One cursor to loop through all GroupCodes and Symbols and the other for all dates. If Count of Transdate, GroupCode and Symbol = 0 then insert a new record otherwise it exists and move on to the next date.
2 years of dates = 732 records and distinct GroupCodes and Symbols = 9387
Is cursor my only option? This will then be a daily process to fill in the gaps. Initial load is the slow part with minimal daily updates for missing records.
TIA
Jeff
January 24, 2013 at 11:40 am
You're forcing us to guess just a bit as you haven't supplied DDL code and some data, but my guess is that what you need is not cursors but left outer joins.
You would need a table of all the dates, and then do a LEFT OUTER JOIN to your data table; you can use ISNULL to transform NULLs to some other value like 0.
Hope this helps.
January 24, 2013 at 12:31 pm
You absolutely do not need a single cursor, let alone nested cursors for this. You need to use a calendar table. Just do a quick search on this site and you will find a great article about what a calendar table is and how to use it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 24, 2013 at 3:58 pm
Here is some sample data to help with what I'm trying to achieve.
TransDate GroupCode Symbol Price
1/21/2013 AG AG123 95.00
1/21/2013 AG AG456 35.00
1/22/2013 AG AG456 35.25
1/23/2013 AG AG123 95.10
1/23/2013 AG AG456 35.55
I want my output to display
TransDate GroupCode Symbol Price
1/21/2013 AG AG123 95.00
1/21/2013 AG AG456 35.00
1/22/2013 AG AG123 Null
1/22/2013 AG AG456 35.25
1/23/2013 AG AG123 95.10
1/23/2013 AG AG456 35.55
Note the added row for the one Symbol on 1/22/2013 with a null price. This lets me know nothing was reported that date for the particular Symbol. It also will allow my data to line up so that each symbol has 3 rows of data instead of 2 and 3 per the original data import.
sqlgreg (1/24/2013)
You're forcing us to guess just a bit as you haven't supplied DDL code and some data, but my guess is that what you need is not cursors but left outer joins.You would need a table of all the dates, and then do a LEFT OUTER JOIN to your data table; you can use ISNULL to transform NULLs to some other value like 0.
Hope this helps.
January 24, 2013 at 4:03 pm
jeff-weigang (1/24/2013)
Here is some sample data to help with what I'm trying to achieve.TransDate GroupCode Symbol Price
1/21/2013 AG AG123 95.00
1/21/2013 AG AG456 35.00
1/22/2013 AG AG456 35.25
1/23/2013 AG AG123 95.10
1/23/2013 AG AG456 35.55
I want my output to display
TransDate GroupCode Symbol Price
1/21/2013 AG AG123 95.00
1/21/2013 AG AG456 35.00
1/22/2013 AG AG123 Null
1/22/2013 AG AG456 35.25
1/23/2013 AG AG123 95.10
1/23/2013 AG AG456 35.55
Note the added row for the one Symbol on 1/22/2013 with a null price. This lets me know nothing was reported that date for the particular Symbol. It also will allow my data to line up so that each symbol has 3 rows of data instead of 2 and 3 per the original data import.
sqlgreg (1/24/2013)
You're forcing us to guess just a bit as you haven't supplied DDL code and some data, but my guess is that what you need is not cursors but left outer joins.You would need a table of all the dates, and then do a LEFT OUTER JOIN to your data table; you can use ISNULL to transform NULLs to some other value like 0.
Hope this helps.
This really isn't very useful. What we would like to see is ddl (create table scripts) and sample data (insert statements) along with desired output based on your sample data. Take a look at the first in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 24, 2013 at 8:24 pm
Okay, I took the time to do what you should have done. Take a close look at the code below and see how I set things up to work on your problem.
create table #TestData (
TransDate date,
GroupCode char(2),
Symbol char(5),
Price numeric(9,2)
);
go
insert into #TestData
values
('1/21/2013','AG','AG123',95.00),
('1/21/2013','AG','AG456',35.00),
('1/22/2013','AG','AG456',35.25),
('1/23/2013','AG','AG456',35.55),
('1/23/2013','AG','AG123',95.10);
go
with
e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
e2(n) as (select 1 from e1 a cross join e1 b),
eTally(n) as (select top(select datediff(dd,min(TransDate),max(TransDate)) + 1 from #TestData) row_number() over (order by (select null)) n from e2 a cross join e2 b),
GrpSym (
GroupCode,
Symbol
) as (
select distinct
GroupCode,
Symbol
from
#TestData
),
GrpSymDates (
CalDate,
GroupCode,
Symbol
) as (
select
dateadd(dd, t.n - 1, m.mdate),
GroupCode,
Symbol
from
GrpSym gs
cross join eTally t
cross apply (select min(TransDate) from #TestData) m(mdate)
)
select -- * from GrpSymDates
gsd.CalDate as TransDate,
gsd.GroupCode as GroupCode,
gsd.Symbol as Symbol,
td.Price
from
GrpSymDates gsd
left outer join #TestData td
on (gsd.CalDate = td.TransDate and
gsd.GroupCode = td.GroupCode and
gsd.Symbol = td.Symbol)
order by
gsd.CalDate,
gsd.GroupCode,
gsd.Symbol
;
go
drop table #TestData;
go
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply