is this quick?

Hello to all first...As the subject says I am interested ist my DB quick
enough, or better is there a good buisness logic.the next query is executed
on C600,192 MB Ram, Seagate HDD, with 7IE's opened, OExpress and EMS MS
Manager in 14.90 sec.The query is for reporting a warehouse components of
some computer shop.There are 3200+ pieces of equipment and 21 categories on
which are basedcalculating of the price..The qry is based on two
tablesTblRabat 21 entries, used for calculating (I don't know the exact
english wordof the phrase) a final price for computer equipments1.22 Is for
a Total price + taxPkey here is autoinc int fieldTblKomponenteVPC is table
with computer equipment (3200+ entries).Pkey here is autoinc int fieldHere
is the
qry----
--SELECT TblRabat.Rabat,
TblKomponenteVPC.SifKomp, TblKomponenteVPC.SifKat, TblKomponenteVPC.naziv,
TblKomponenteVPC.Dobavljac, TblKomponenteVPC.Cijena AS VPC,
Round(TblKomponenteVPC.Cijena * tblRabat.rabat,2) as MPC,
Round(TblKomponenteVPC.Cijena * tblrabat.Rabat * 1.22,2) AS MPCsPDV,
TblKomponenteVPC.Opis, TblKomponenteVPC.Promjena,
TblKomponenteVPC.JamstvoFROM TblRabat INNER JOIN TblKomponenteVPC ON
(TblRabat.SifKat =TblKomponenteVPC.SifKat) AND (TblRabat.Dobavljac =
TblKomponenteVPC.Dobavljac)ORDER BY /*uvjetom i opisom*/CASE
TblKomponenteVPC.SifKat WHEN 'MON' THEN 1 WHEN 'CPU' THEN 2 WHEN 'MBO' THEN
3 WHEN 'MEM' THEN 4 WHEN 'VGA' THEN 5 WHEN 'VGA/MBO/Memorija' THEN 5 WHEN
'CAS' THEN 6 WHEN 'FDD' THEN 7 WHEN 'HDD' THEN 8 WHEN 'OP' THEN 9 WHEN
'FDD/ZIP/DAT' THEN 10 WHEN 'ZVUK' THEN 11 WHEN 'MULT' THEN 12 WHEN 'MOD'
THEN 13 WHEN 'KEYB' THEN 14 WHEN 'MOU' THEN 15 WHEN 'CABL' THEN 16 ELSE
99END /*case*/,TblKomponenteVPC.Opis /*kraj
ordera*/----
Sorry for a non formated qry..
here is final post
Hello to all first...As the subject says I am interested ist my DB quick
enough, or better is there a good buisness logic.the next query is executed
on C600,192 MB Ram, Seagate HDD, with 7IE's opened, OExpress and EMS MS
Manager in 14.90 sec.
The query is for reporting a warehouse components of
some computer shop.There are 3200+ pieces of equipment and 21 categories on
which are basedcalculating of the price..
The qry is based on two
tablesTblRabat 21 entries, used for calculating (I don't know the exact
english wordof the phrase) a final price for computer equipments
1.22 Is for a Total price + tax
Pkey here is autoinc int field
TblKomponenteVPC is table
with computer equipment (3200+ entries).
Pkey here is autoinc int field, FKEY is field TblRabat.SifKat
(Cathegory of equipment on which is base a rabate) which is
linked to TblRabat table.
Here is the qry:
SELECT
TblRabat.Rabat,
TblKomponenteVPC.SifKomp,
TblKomponenteVPC.SifKat,
TblKomponenteVPC.naziv,
TblKomponenteVPC.Dobavljac,
TblKomponenteVPC.Cijena AS VPC,
Round(TblKomponenteVPC.Cijena * tblRabat.rabat,2) as MPC,
Round(TblKomponenteVPC.Cijena * tblrabat.Rabat * 1.22,2) AS MPCsPDV,
TblKomponenteVPC.Opis,
TblKomponenteVPC.Promjena,
TblKomponenteVPC.Jamstvo
FROM
TblRabat
INNER JOIN TblKomponenteVPC ON (TblRabat.SifKat =
TblKomponenteVPC.SifKat)
AND (TblRabat.Dobavljac = TblKomponenteVPC.Dobavljac)
ORDER BY /*uvjetom i opisom*/
CASE TblKomponenteVPC.SifKat
WHEN 'MON' THEN 1
WHEN 'CPU' THEN 2
WHEN 'MBO' THEN 3
WHEN 'MEM' THEN 4
WHEN 'VGA' THEN 5
WHEN 'VGA/MBO/Memorija' THEN 5
WHEN 'CAS' THEN 6
WHEN 'FDD' THEN 7
WHEN 'HDD' THEN 8
WHEN 'OP' THEN 9
WHEN 'FDD/ZIP/DAT' THEN 10
WHEN 'ZVUK' THEN 11
WHEN 'MULT' THEN 12
WHEN 'MOD' THEN 13
WHEN 'KEYB' THEN 14
WHEN 'MOU' THEN 15
WHEN 'CABL' THEN 16
ELSE 99
END /*case*/,
TblKomponenteVPC.Opis /*kraj ordera*/
Any comments...

Is there any easy way to evaluate complex date logic in expressions?

Hello all,

I am new to SSIS, so I am hoping there is an easier way to do this...

I need to evaluate a date in a field and determine if it is between the beginning and end of whatever the current month is... In Access, this was written as something like:

IIF(datevalue >= CDate(Format(Now(),"mm/01/yy")) AND datevalue < CDate(Format(DateAdd("m",1,Now()), "mm/01/yy)), value1, value2)

Trying to recreate this in SSIS using expressions during a derived transformation has been extremely difficult. Here is what I came up with:

(DUE_DATE >= (DT_DATE)( (DT_WSTR,2)MONTH(GETDATE())+"/01/"+ (DT_WSTR,2)YEAR(GETDATE()))) && (DUE_DATE<(DT_DATE)( (DT_WSTR,2)MONTH( DATEADD("m",1,GETDATE()) )+"/01/"+(DT_WSTR,2)YEAR( DATEADD("m",1,GETDATE() )))) ? value1 : value2

Any help you all could give would be appreciated.

Thanks!

Josh

I've just given this a go with a column called [OrderDate] (which comes from a SalesOrderHeader in AdventureWorks). The following works fine for me:

OrderDate < GETDATE() ? "Y" : "N"

Can you not do that? Why are you trying to parse out the different parts of GETDATE()?

-Jamie

|||This expression will determine if the DUE_DATE is in the current month, as defined by GETDATE().

YEAR(GETDATE()) + MONTH(GETDATE()) == YEAR(DUE_DATE) + MONTH(DUE_DATE) ? "value1" : "value2"

|||

jaegd wrote:

This expression will determine if the DUE_DATE is in the current month, as defined by GETDATE().

YEAR(GETDATE()) + MONTH(GETDATE()) == YEAR(DUE_DATE) + MONTH(DUE_DATE) ? "value1" : "value2"

I would suggest using :

(YEAR(GETDATE())*100) + MONTH(GETDATE()) == (YEAR(DUE_DATE)*100) + MONTH(DUE_DATE) ? "value1" : "value2"

That will result in 200612 for Dec 2006.

|||

Hi Jaimie,

Thanks for the advice. However, I need to be able to tell if the due_date is between the 1st and last day of the currernt (or any given) month. I think your suggestion would return true for all orders with a due_date less than today... which would exclude orders due in the remainder of the month and would include orders due prior to the beginning of the month.

Best regards,

Joshua

|||

Hi Tom,

Thanks for the help! I think that will do what I'm looking for and will be a lot easier...

Best regards,

Joshua

|||

jrbarker33 wrote:

Hi Jaimie,

Thanks for the advice. However, I need to be able to tell if the due_date is between the 1st and last day of the currernt (or any given) month. I think your suggestion would return true for all orders with a due_date less than today... which would exclude orders due in the remainder of the month and would include orders due prior to the beginning of the month.

Best regards,

Joshua

Whoops. Sorry. I need to read things more accurately!

Tom's code is definately the way to go then.

-Jamie