Customer has shortcuts in their data where they have a quantity of records in a field. When I parse them, I need to manufacture records, one for each quantity, incrementing the "identifier" by 7 days (because the number represents a date.
Example: a single product that is on sale for four weeks and I need four records, one product for each week.
[Event Number]
[Classification]
[Weeks Running]
[Some Data]
2009 11 29 00
1
1
runs one week
2009 12 06 00
2
1
runs one week
2009 12 13 00
1
4
runs four weeks
2009 12 20 00
2
4
runs four weeks
Somehow I need to turn this data into the following with a view (sql select) (all in the same table, white space included to see parts:
[Event Number + Classification]
[Some Data]
2009 11 29 01
runs for one week One week thus one record.
2009 12 06 02
runs for one week
2009 12 13 01
runs for four weeks Repeats 4 times incrementing date by 7
2009 12 20 01
runs for four weeks
2009 12 27 01
runs for four weeks
2009 01 03 01
runs for four weeks
2009 12 20 02
runs for four weeks Repeats 4 times incrementing date by 7
2009 12 27 02
runs for four weeks
2009 01 03 02
runs for four weeks
2009 01 10 02
runs for four weeks
My thoughts are to have some sort of pivot cross apply sql code?
The CROSS APPLY operator is semantically similar to INNER JOIN operator. It retrieves those records from the table valued function and the table being joined, where it finds matching rows between the two.
You use the PIVOT operator within your query's FROM clause to rotate and aggregate the values in a dataset. The data is pivoted based on one of the columns in the dataset. Each unique value in that column becomes its own column, which contains aggregated pivoted data.
PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. And PIVOT runs aggregations where they're required on any remaining column values that are wanted in the final output.
This is something that would probably be simpler to do on application-side rather than database-side, but I'll give it a shot... This requires a database supporting CTEs, which I don't happen to have on hand, so this is untested.
WITH RECURSIVE expandedTable(eventNumber, classification, index, count, someData)
AS (
SELECT eventNumber, classification, 1, weeksRunning, someData
FROM originalTable
WHERE weeksRunning > 0
UNION ALL
SELECT eventNumber + 7, classification, index + 1, count, someData
FROM expandedTable
WHERE index < count
)
SELECT eventNumber, classification, someData
FROM expandedTable;
I have a cheap answer for you. You use a "pre-rendered" Weeks
table to create a query-based loop. You would need to put enough weeks into it to cover your expected range of scenarios.
[Week]
1
2
3
4
Then you join your query on [Weeks Running]
to this table, using the inequality OriginalTable.WeeksRunning <= Weeks.Week
. You end up with one row per week.
You deduce the date by adding Weeks.Week * 7
days to the date embedded in your event number.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With