MS-SQL Query please help me.

[zippy]

Thanks Beachless.
I get an error

Msg 206, Level 16, State 2, Line 6
Operand type clash: date is incompatible with decimal

Note: OESHDT.TRANDATE, is not a date data type its decimal(9,0)

So you cant set @FromDate to a Date data type it has to be converted

hit the person who designed that over the head. Hard !

 

[Beachless]

Sorry for my Ignorance, dont know what you mean.

Do you mean this ?


Date gets saved as I.E 20130101 (YYYYMMDD)

Dont know why its decimal(9,0) and not decimal(8,0)

I was looking for the YYYYMMDD but the fact that it was decimal and its length was 9 was making me think it might be a unix time stamp which would have been the seconds from '19700101'.

Ill do the new version quick...

 

[SBSP]

hit the person who designed that over the head. Hard !

Now that you mention it, you are not the only one saying it, But! I actually prefer it that way, its easier, bit of a schlep when you need to display dates on a report formatted in a date format ect ect,

This table is a standard Accpac table, Accpac is a Yank product if i have it right.
So you are welcome to go tell them they are doing it wrong, they will probably say they are doing it right just like they are driving on the correct side of the road

 

[Shi]

hit the person who designed that over the head. Hard !

AccPac

 

[Beachless]

This should do it, it sucks not being able to run it first to check for mistakes but it should work...

DECLARE @FromDate as DATE
DECLARE @ToDate as DATE
SET @FromDate = DATEADD(MONTH,-3,GETDATE())
SET @FromDate = GETDATE()

select
ITEM,
[DESC],
LOCATION,
QTYONHAND,
(
	select SUM(OESHDT.QTYSOLD) 
	from OESHDT 
	where OESHDT.ITEM = zzActiveItems.ITEM 
	and	convert(date, OESHDT.TRANDATE) >= @FromDate 
	and	convert(date,OESHDT.TRANDATE) <= @ToDate 
	and	OESHDT.LOCATION = zzActiveItems.LOCATION 
) as 'QTY_SOLD'
from zzActiveItems 
inner join ICITEM on ICITEM.ITEMNO = zzActiveItems.ITEM

 

[shauntir]

Sorry for my Ignorance, dont know what you mean.

Do you mean this ?

View attachment 75681

Date gets saved as I.E 20130101 (YYYYMMDD)

Dont know why its decimal(9,0) and not decimal(8,0)

Oh. My. God.

That table design is bad.

Anything that says date in that table shold be of a date or datetime datatype. That's just one of the things wrong.

 

[Messugga]

Now that you mention it, you are not the only one saying it, But! I actually prefer it that way, its easier, bit of a schlep when you need to display dates on a report formatted in a date format ect ect,

This table is a standard Accpac table, Accpac is a Yank product if i have it right.
So you are welcome to go tell them they are doing it wrong, they will probably say they are doing it right just like they are driving on the correct side of the road

Yeah, one of the tools I'm currently supporting pulls crap like sticking numerical values into "date" columns as well. It makes your life a lot more complicated if you're reporting on the data or enriching with it.

 

[_kabal_]

You have to wonder who actually comes up with these tables. They guy that decided to use "YR" instead of "YEAR" should have been fired on the spot

OESHTD. Incredible table name

 

[Messugga]

You have to wonder who actually comes up with these tables. They guy that decided to use "YR" instead of "YEAR" should have been fired on the spot

OESHTD. Incredible table name

HTD stands for something transaction related. I've seen it in several commercial core banking systems, where the table contains what comes down to transaction headers.

 

[semaphore]

You have to wonder who actually comes up with these tables. They guy that decided to use "YR" instead of "YEAR" should have been fired on the spot

OESHTD. Incredible table name

And all that UPPERCASE naming convention.

 

[semaphore]

HTD stands for something transaction related. I've seen it in several commercial core banking systems, where the table contains what comes down to transaction headers.

Our transaction header tables look WAY neater.

 

[Messugga]

Our transaction header tables look WAY neater.

View attachment 75749

That's what our in-house stuff looks like as well. One example of where it doesn't look like that is on package called Finacle by Infosys. Hell, they don't even have primary keys on their databases. You can Google to find out who uses that software. They're big companies.

 

[ToxicBunny]

Fekking hell that table design is horrible (the AccPac one)...

 

[zippy]

Now that you mention it, you are not the only one saying it, But! I actually prefer it that way, its easier, bit of a schlep when you need to display dates on a report formatted in a date format ect ect,

This table is a standard Accpac table, Accpac is a Yank product if i have it right.
So you are welcome to go tell them they are doing it wrong, they will probably say they are doing it right just like they are driving on the correct side of the road

Display and reporting of dates is the easiest thing in world. As for them being yanks, well the yanks also sell other products which don't do stupid design. Accpac is from the stone age though, so not surprising. There was probably no such thing as a native date data type when they designed that.

 

[zippy]

Fekking hell that table design is horrible (the AccPac one)...

Place I work out now has c program's that where first written in 70's. Names like F2398.c

They converted to Oracle in the early 80's and the corresponding database package is called F2398

But at least the tables and columns are decently setup.

The source control system is Danish. All the tablenames and column names are in Danish. Userid = BRUGER. The column which stores the module name is NAVN. But this just the backend. The frontend is in English