Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

looping through dates in ssis Expand / Collapse
Author
Message
Posted Tuesday, October 29, 2013 4:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 1:48 AM
Points: 71, Visits: 222
Good day all the clever ones

I have a table (stock) that I need to update every day from a certain date (the first day of the month) to the the present day - 1. In other words, the stock needs to be updated until yesterday. Is there anyone that can help me with this? I am working through a Calendar table where all the dates are already set up. Is there anyone out there with an idea of how I can go about doing this?

I need to loop through a container in ssis to do the following:

UpdateCubeOn |UpdateDateFrom |UpdateDateTo
2013/10/01 | 20131001 |20131001
2013/10/02 | 20131001 |20131002
2013/10/03 | 20131001 |20131003
2013/10/04 | 20131001 |20131004
2013/10/05 | 20131001 |20131005
.
.
.

So my stock will now be:
2013/10/01 = 100
2013/10/02 = 110
2013/10/03 = 90
2013/10/04 = 101
2013/10/05 = 92

Kind regards
Fred
Post #1509254
Posted Tuesday, October 29, 2013 6:59 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 11:25 AM
Points: 637, Visits: 2,143
You could start with an execute SQL task to query your calendar table for the current date (or current date - 1, not sure of your logic), and put the start and end dates into string variables.

Then you can set up another variable with your data source query, and append the date variables, and then set up an expression in your main data flow for the sql command in your data source, putting the string in.
Post #1509336
Posted Wednesday, October 30, 2013 1:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:15 PM
Points: 5,245, Visits: 12,163
frdrckmitchell7 (10/29/2013)
Good day all the clever ones

I have a table (stock) that I need to update every day from a certain date (the first day of the month) to the the present day - 1. In other words, the stock needs to be updated until yesterday. Is there anyone that can help me with this? I am working through a Calendar table where all the dates are already set up. Is there anyone out there with an idea of how I can go about doing this?

I need to loop through a container in ssis to do the following:

UpdateCubeOn |UpdateDateFrom |UpdateDateTo
2013/10/01 | 20131001 |20131001
2013/10/02 | 20131001 |20131002
2013/10/03 | 20131001 |20131003
2013/10/04 | 20131001 |20131004
2013/10/05 | 20131001 |20131005
.
.
.

So my stock will now be:
2013/10/01 = 100
2013/10/02 = 110
2013/10/03 = 90
2013/10/04 = 101
2013/10/05 = 92

Kind regards
Fred


I'm a bit confused by your question. Can you take a look at the link in my signature & attempt to provide sample data in a format which is more easily consumed? Along with expected results? From the above, I cannot see where the 100,110 etc results come from, for example.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1509670
Posted Wednesday, October 30, 2013 2:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 1:48 AM
Points: 71, Visits: 222
Hi Phil

I am not sure with the 'link in your signature' so I will respond this way:

The 100, 110, etc is the fluctuation of the stock, in other words the stock being sold on different day. The 100, 110, etc. is the stock that is left on that day. Every day the stock at a certain branch will be different. What essentially has to happen is I need to create in my ssis package a for loop that will populate a table from the dates stipulated what the stock at a certain branch are. This was I will see if the stock needs to be ordered, or if one branch has stock that the other don't have, then move the stock to the branch that do not have the stock.

Kind regards
Fred
Post #1509684
Posted Wednesday, October 30, 2013 3:12 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 1:48 AM
Points: 71, Visits: 222
Hi Phil

Here is additional sample data. This is what I get when I run a query.

facility ItemCode ItemDescription TxnQty FacOpb BalanceOnDate Date
4 1179 Gouda CL20Kg Cuts 2x10kg 3958 0 3958 20131011
4 1179 Gouda CL20Kg Cuts 2x10kg 0 0 0 20131002
4 1187 F/Value Gouda Round 2x3.5kg 1.5 0 1.5 20131017
4 1187 F/Value Gouda Round 2x3.5kg 0 0 0 20131011
4 1187 F/Value Gouda Round 2x3.5kg -328.7 0 -328.7 20131014
4 1187 F/Value Gouda Round 2x3.5kg 868.1 0 868.1 20131023
4 1187 F/Value Gouda Round 2x3.5kg 487.3 0 487.3 20131007
4 1187 F/Value Gouda Round 2x3.5kg 0 0 0 20131010
4 1187 F/Value Gouda Round 2x3.5kg -160.1 0 -160.1 20131019

This is the table I have created that will be populated with the above data:

CREATE TABLE [dbo].[DW_Fact_InventoryBalances](
[KeyDate] [int] NOT NULL,
[KeyDepot] [int] NOT NULL,
[KeyItem] [int] NOT NULL,
[TransactionQuantity] decimal(11,3) NULL,
[FacilityOpeningBalance] decimal(11,3) NULL,
[BalanceOnDate] decimal(11,3) NULL

In this table I want to store my data for a certain period (month). It will always have to populate the table from the first of the month to yesterday.

This query finds me the first day of the month.

Select TrxDateNumeric /*for example: 20131011*/
from dbo.View_Dim_CalendarDefinition as C WITH (NOLOCK)
inner join dbo.View_Dim_SystemParameters WITH (NOLOCK)
on Run_Type = 'SALES'
where TrxDate = RUN_DateHistoryFrom

I need to do a for loop that will take the above query and start populating my table as stipulated above.
This table will then have the available stock (BalanceOnDate) for a branch (facility) for a particular day. Now, looking at the data of my query I know on the 19th (as per the data above) I will have to get more stock.

Kind regards
Fred
Post #1509691
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse