Jack and Co.

[Zarcom]

I got a SQL question for you if you want to give it a wack.

Don't seem to be getting a response so I thought you could try if you don't mind.

Check it out at
thread183-351815
Thanx That'l do donkey, that'l do
Mark

 

[jfrost10]

Hey Zar,

Actually some guy responded to you, and came up with some case statement in t-sql.

But I'm more curious about this table structure you have.

Let me know what the surrounding objects/table/situation is, but here's my thoughts just on what I saw in your post:

Summing items horizontally is difficult to do normally (since you need to check if this column equals this and that column equals that, etc.) Assuming there is some invoice being taxed, let me give you another option of design:

object:Invoice
--------------
InvoiceID int
(insert other
invoice stuff
here)

object: Tax
--------------
TaxID
TaxName
TaxPercent

Association object: Invoice_Tax
---------------
TokenRowID
InvoiceID
TaxID
TaxAmount

ok, follow me here:
Lets say the invoice can have multiple taxes applied to it (and GST can come in twice...btw, i didn't know you were Canadian!) GST would be in the taxes table as

TaxID = 1
TaxName = 'GST'
TaxPercent = .07

Now lets say there are two instances of gst for Invoice 1.

In the association table (keep in mind that this scenario assumes the methodology of mapping classes to tables), there would be these two records:
RowID = 1
InvoiceID = 1
TaxID = 1
Amount = 50.00
RowID = 2
InvoiceID = 1
TaxID = 1
Amount = 50.00

So what does this get us? Well, now that we have it all normalized, running a query to find the total of any given tax is simple:

Select Sum(Amount) From Invoice_Tax Where InvoiceID = 1
And TaxID = 1

Let me know what you think, and if I'm way off on how your app is supposed to work.

And where in Canada are you?!

Jack

 

[Zarcom]

You bet I am Canadian Jack!
I live in Edmonton at the moment. Been here for a year now. Pretty nice city I like it.

Not sure if your solution would entirely work, prolly would if I bent it a little the brain is a little fuzzy today.

I think it might it is just that my system is capable of hosting multiple vendors - shall we call them - on it.
Umm... thinking ... OH yes here we go.

In the specs that I was given the invoice/charges need to be a snap shot. In case some bozo changes the taxes all about the charge needs to stay the same. AKA the govt decides to lower/raise the tax rate or something silly.
On a side note all lot of our customers are Americans.

Hmm I definetly see your solution working. In fact the more I look at it the more I like it. However, the system is close to completion and I don't want to make db structure changes. It would set me back a couple of days on my schedule. Basically I am making a report that has been requested. Total of Taxes over a time period.

Thanks for the help though. I'll let you know how it goes. That'l do donkey, that'l do
Mark

 

[qwert231]

JFrost, I posted to the SQL forum...

 

[jfrost10]

good God qwert, that was a NASTY looking sql statement!

I mean hey, it worked. But OMG, that was just NASTY!

Mark: Where were you before Edmonton? A buddy of mine just moved there for a gig, and he's liking it quite a bit.

Jack

 

[qwert231]

Well... it worked... right? I couldn't think of anything else that could do what he wanted...

If you can, I am using the same query in one of my jobs... only with different field names and tables. The alias names the wizard came up with.

 

[qwert231]

PS. My name is also Mark. But I'm from CT.

 

[Zarcom]

It did work other Mark. But look at the stuff that I ended up with HOLY MONKEY NOODLES it is so simple.

Thanks for posting though.

Jack: I was in Lloydminster before this, but came to Edmonton to go for some more school. I took the NAIT BAIST program (Bachelor of Applied Information Systems Technology) majoring in IS. Where are you from? I think you said somewhere but I have forgotten. That'l do donkey, that'l do
Mark

 

[jfrost10]

Winnipeg, home of the CFL's Wester Conference Champions (your Eskimo's time at the #1 spot is numbered!)


kewl. Lloydminster...thats in Alberta too, right?

Jack

 

[Zarcom]

We shall see about that. The rough riders are making a comeback hahaha. I grew up in small town Sk.

Lloyd is actually Canada's only border city. Half is in SK half in AB. That'l do donkey, that'l do
Mark

 

[jfrost10]

Oh right, I remember hearing about Lloydminster when I lived in Saskatoon (way back in the day, like grades 1 - 3).

Rough Riders. BAH! Another lucky labour day for the rough riders!


So how does that work for the town being on the border? I mean, everything in alberta would have less tax, right (only gst), so wouldn't everyone just shop there?

Jack

 

[Zarcom]

Can't remember anymore if the whole city is exempt or not. However if you live on the Saskatchewan side you get the healt care and most importantly the car insurance. Mwahahaha I was paying $42 a month for car insurance there.

I do know alot of the .. or most of the stores are located on the albertan side. That'l do donkey, that'l do
Mark