Joining to Fiscal Table

Thanks Charles. I would have looked at that for an hour and thought that I had it right. ;-)

--
Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix / Picometrix
    bspolarich@... ~ 734-864-5618 ~ www.advancedphotonix.com


-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Charles Carden
Sent: Saturday, October 24, 2009 11:43 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Joining to Fiscal Table

If your query is as written below then you are correct, you will not get any records. Since you are going from the Fiscal to the Ship Head you need to reverse your >= and <=, i.e. Fiscal.StartDate <= ShipHead.ShipDate and Fiscal.EndDate >= ShipHead.ShipDate. If your query was the other way you would be correct, e.g. ShipHead.ShipDate >= Fiscal.StartDate and ShipHead.ShipDate <= Fiscal.EndDate.

It appears that if you removed one of the criteria you would get records but they would not be of the correct fiscal period.

If I am an idiot then someone please let me know. It is late and I have not slept much.

Charles Carden
IT Manager
Manitex, Inc.
Georgetown, Texas
----- Original Message -----
From: Brian W. Spolarich
To: vantage@yahoogroups.com
Sent: Friday, October 23, 2009 2:55 PM
Subject: [Vantage] Joining to Fiscal Table


405. I want to determine the actual fiscal period for each shipment.

So I do this:

for each ShipHead no-lock , each ShipDtl where (ShipHead.Company =
ShipDtl.Company and ShipHead.PackNum = ShipDtl.PackNum) no-lock , each
Fiscal where ( Fiscal.StartDate >= ShipHead.ShipDate AND
Fiscal.EndDate <= ShipHead.ShipDate) and (ShipHead.Company =
Fiscal.Company) no-lock .

This returns nothing.

If I remove *either* of the Fiscal table filter criteria (either start
or end date) the query returns what I expect, duplicated rows for each
shipdtl based on each of the matching Fiscal rows. If I remove both
criteria, I get all of the Fiscal rows for the Company.

I could do the filtering in Crystal, but this doesn't make any sense.

I can do this from the Invoice table, which does store fiscal period,
but I thought 'well what if it hasn't been invoiced yet?'.

-bws

--

Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix /
Picometrix

bspolarich@...
<mailto:bspolarich@...> ~ 734-864-5618 ~
www.advancedphotonix.com <http://www.advancedphotonix.com>

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]



------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
405. I want to determine the actual fiscal period for each shipment.



So I do this:



for each ShipHead no-lock , each ShipDtl where (ShipHead.Company =
ShipDtl.Company and ShipHead.PackNum = ShipDtl.PackNum) no-lock , each
Fiscal where ( Fiscal.StartDate >= ShipHead.ShipDate AND
Fiscal.EndDate <= ShipHead.ShipDate) and (ShipHead.Company =
Fiscal.Company) no-lock .



This returns nothing.



If I remove *either* of the Fiscal table filter criteria (either start
or end date) the query returns what I expect, duplicated rows for each
shipdtl based on each of the matching Fiscal rows. If I remove both
criteria, I get all of the Fiscal rows for the Company.



I could do the filtering in Crystal, but this doesn't make any sense.



I can do this from the Invoice table, which does store fiscal period,
but I thought 'well what if it hasn't been invoiced yet?'.



-bws



--

Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix /
Picometrix

bspolarich@...
<mailto:bspolarich@...> ~ 734-864-5618 ~
www.advancedphotonix.com <http://www.advancedphotonix.com>





[Non-text portions of this message have been removed]
If your query is as written below then you are correct, you will not get any records. Since you are going from the Fiscal to the Ship Head you need to reverse your >= and <=, i.e. Fiscal.StartDate <= ShipHead.ShipDate and Fiscal.EndDate >= ShipHead.ShipDate. If your query was the other way you would be correct, e.g. ShipHead.ShipDate >= Fiscal.StartDate and ShipHead.ShipDate <= Fiscal.EndDate.

It appears that if you removed one of the criteria you would get records but they would not be of the correct fiscal period.

If I am an idiot then someone please let me know. It is late and I have not slept much.

Charles Carden
IT Manager
Manitex, Inc.
Georgetown, Texas
----- Original Message -----
From: Brian W. Spolarich
To: vantage@yahoogroups.com
Sent: Friday, October 23, 2009 2:55 PM
Subject: [Vantage] Joining to Fiscal Table


405. I want to determine the actual fiscal period for each shipment.

So I do this:

for each ShipHead no-lock , each ShipDtl where (ShipHead.Company =
ShipDtl.Company and ShipHead.PackNum = ShipDtl.PackNum) no-lock , each
Fiscal where ( Fiscal.StartDate >= ShipHead.ShipDate AND
Fiscal.EndDate <= ShipHead.ShipDate) and (ShipHead.Company =
Fiscal.Company) no-lock .

This returns nothing.

If I remove *either* of the Fiscal table filter criteria (either start
or end date) the query returns what I expect, duplicated rows for each
shipdtl based on each of the matching Fiscal rows. If I remove both
criteria, I get all of the Fiscal rows for the Company.

I could do the filtering in Crystal, but this doesn't make any sense.

I can do this from the Invoice table, which does store fiscal period,
but I thought 'well what if it hasn't been invoiced yet?'.

-bws

--

Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix /
Picometrix

bspolarich@...
<mailto:bspolarich@...> ~ 734-864-5618 ~
www.advancedphotonix.com <http://www.advancedphotonix.com>

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]