June 22, 2007 at 10:28 pm
I generate a first and last date in a SQL Task as a global variables that I want to pass to a Transform Data Task. I have the following SQL query in the properties of the Transform Data Task:
---- query in Transform Data Task ----
DECLARE @Ld datetime
DECLARE @HD datetime
select @Ld as StartDay, @HD as EndDay, a.strname, a.device, min(a.startdate) starttime, max(a.enddate) endtime, sum(a.amtPaid)/100 amtpaid, sum(a.amtprinted)/100 amtprinted, sum(a.no_sold) sold, sum(a.no_cashed) cashed, sum(a.amtvoid)/100 amtvoid, sum(a.no_void) void
from (
SELECT sum(convert(numeric, iAmount)) amtPrinted, sum(0) amtPaid, min(dtPrinted) startDate,max(dtprinted) enddate, strName, 0 no_cashed, count(*) no_sold, Devices.strdevicename device, 0 amtvoid , 0 no_void
FROM Voucher
INNER JOIN Sys_User ON Voucher.iPrinterID = Sys_User.iUserID
INNER JOIN Devices ON Voucher.iDeviceID = Devices.iDeviceID
WHERE dtPrinted >= @Ld AND dtPrinted < @HD
group by strname, Devices.strdevicename
UNION ALL
SELECT sum(0) amtPrinted, sum(convert(numeric,iAmount)) amtPaid, min(dtPaid) startDate,max(dtpaid) enddate, strName, count(*) no_cashed, 0 no_sold, Devices.strdevicename device , 0 amtvoid, 0 no_void
FROM Voucher
INNER JOIN Sys_User ON Voucher.iPayorID =Sys_User.iUserID
INNER JOIN Devices ON Voucher.iPayDeviceID = Devices.iDeviceID
WHERE dtPaid >= @Ld AND dtPaid < @HD and (strVoucherstatus = 'PD' OR strVoucherstatus = 'HP')
group by strname, Devices.strdevicename
UNION ALL
SELECT sum(0) amtPrinted,0 amtPaid,min(dtPaid) startDate,max(dtpaid) enddate, strName, 0 no_cashed, 0 no_sold, Devices.strdevicename device,sum(convert(numeric,iAmount)) amtvoid, count(*) no_void
FROM Voucher
INNER JOIN Sys_User ON Voucher.iPayorID =Sys_User.iUserID
INNER JOIN Devices ON Voucher.iPayDeviceID = Devices.iDeviceID
WHERE dtPaid >= @Ld AND dtPaid < @HD and strVoucherstatus = 'VD'
group by strname, Devices.strdevicename ) a
group by strname, device
order by strname, startTime
--- continued ---
How do I assign the global variables to the declared parameters @Ld and @HD in the Transform Data Task? use a SET @Ld = ? doesn't work. Inserting ? in place of @LD and @HD doesn't work either. Any suggestions would be very helpful. Thank you.
June 25, 2007 at 1:31 am
instead of using the trnasform data task use the execute sql task and pass the global variable values by using ?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply