MS-SQL Query please help me.

S

SBSP

Senior Member
Joined
Sep 7, 2007
Messages
667
Reaction score
16
Hi Hope someone can help.

My Query below semi works :-)

FromDate formats the date like this '20131011' and To date does the same format minus 2 Months like this.
'20130711'


The nested query gets QTY Sold during FromDate & ToDate.
It returns 'NULL' when i run it as is in the below query.

If i replace the date query with '20130701' to '20131011' which is in the nested query, it returns the correct values.

What am i doing wrong ??? :sick:

Code: 
DECLARE @FromDate as VARCHAR(8)
DECLARE @ToDate as VARCHAR(8)
set @FromDate = (select CONVERT(VARCHAR(8), GETDATE(), 112))
set @ToDate = (select CONVERT(VARCHAR(8), DATEADD(month, -3, GETDATE()), 112))

select 
ITEM,
[DESC],
LOCATION,
QTYONHAND,

(
	select  SUM(OESHDT.QTYSOLD) from OESHDT 
	where OESHDT.ITEM = zzActiveItems.ITEM and 
	(OESHDT.TRANDATE >= (select CONVERT(VARCHAR(8), GETDATE(), 112)) and 
	OESHDT.TRANDATE <= (select CONVERT(VARCHAR(8), DATEADD(month, -3, GETDATE()), 112))) and 
	OESHDT.LOCATION = zzActiveItems.LOCATION 
) as 'QTY_SOLD'

from zzActiveItems inner join ICITEM on ICITEM.ITEMNO = zzActiveItems.ITEM
 
You're not using correct datatypes for your dates, for one. So without running the query, my first thought would be that you're comparing varchars and not dates, which would run on binary value and screw your results up.

I'd suggest rewriting your query. If one of my guys gave me this, I'd throw it out as useless.
 
Just had a quick look but :
OESHDT.TRANDATE >= (select CONVERT(VARCHAR(8), GETDATE(), 112)) and
OESHDT.TRANDATE <= (select CONVERT(VARCHAR(8), DATEADD(month, -3, GETDATE()), 112)

Is saying:
TRANDATE >= today AND TRANDATE <= today-3months

I imagine it should be:

TRANDATE <= today AND TRANDATE >= today-3months

???

Otherwise give us the tables and we can write it quickly.
 
Your to date should be after your from date? This is where I am confused, substituting for your values:

select SUM(OESHDT.QTYSOLD) from OESHDT
where OESHDT.ITEM = zzActiveItems.ITEM and
(OESHDT.TRANDATE >= 20131011) and
OESHDT.TRANDATE <= 20130701


which defines a non-existent period. Date is after 11 October yet somehow before 1 July.
 
Beachless said:
Just had a quick look but :
OESHDT.TRANDATE >= (select CONVERT(VARCHAR(8), GETDATE(), 112)) and
OESHDT.TRANDATE <= (select CONVERT(VARCHAR(8), DATEADD(month, -3, GETDATE()), 112)

Is saying:
TRANDATE >= today AND TRANDATE <= today-3months

I imagine it should be:

TRANDATE <= today AND TRANDATE >= today-3months

???

Otherwise give us the tables and we can write it quickly.
Click to expand...

You are right its supposed to be the other way around, I pasted that part the wrong way around.


@Messugga, Relax its friday :-)

Will send them shortly
 
Why declare variables and then not use them? :confused:

Why bother to declare forDate and toDate if you aren't going to refer to them again?
 
porchrat said:
Why declare variables and then not use them? :confused:

Why bother to declare forDate and toDate if you aren't going to refer to them again?
Click to expand...

Ok Wait , I'm getting ahead of my self here, the query is bogus, before i posted it here i messed around with it.

Code: 
DECLARE @FromDate as VARCHAR(8)
DECLARE @ToDate as VARCHAR(8)
SET @FromDate = (select CONVERT(decimal(9,0), GETDATE(), 112))
SET @ToDate = (select CONVERT(decimal(9,0), DATEADD(month, -3, GETDATE()), 112))

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

from zzActiveItems inner join ICITEM on ICITEM.ITEMNO = zzActiveItems.ITEM

The date field in OESHDT's data type is decimal(9,0) so i'm converting the date to .IE 20131011 then to decimal(9,0)
 
errr, converting it to decimal(9,2) gives me a different number , Geez!
 
Code: 
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 
	(OESHDT.TRANDATE >= @FromDate and 
	OESHDT.TRANDATE <= @ToDate) and 
	OESHDT.LOCATION = zzActiveItems.LOCATION 
) as 'QTY_SOLD'

from zzActiveItems inner join ICITEM on ICITEM.ITEMNO = zzActiveItems.ITEM

Try that one

Edit; Use "cast() as DATE" to convert your decimal to a date.
 
Last edited:
Ah OK I see.

Also you realise it subtracts 3 months for toDate and not 2 right?

Just a thought but have you checked that you are getting the values you expect for the forDate and toDate queries in isolation?


EDIT: Ah nevermind I see you are doing that now :p:
SBSP said:
errr, converting it to decimal(9,2) gives me a different number , Geez!
Click to expand...

I don't use MS-SQL so I'm not familiar with the datatypes but surely there is a DATE datatype of some sort you could use? (obviously this depends on your tables but it seems safer to me rather than casting to a decimal or varchar)
 
Last edited:
Thanks Beachless.
I get an error

Code: 
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
 
Last edited:
porchrat said:
Ah OK I see.

Also you realise it subtracts 3 months for toDate and not 2 right?

Just a thought but have you checked that you are getting the values you expect for the forDate and toDate queries in isolation?


EDIT: Ah nevermind I see you are doing that now :p:
Click to expand...

In isolation they return
20131011 and 20130711 when declaring them as varchar, but when i declare them to decimal i get something like '4546436'
 
SBSP said:
errr, converting it to decimal(9,2) gives me a different number , Geez!
Click to expand...
\
Always cast to a date when comparing dates.
You can also use the between keyword instead of >= <= so..
trandate between fromdate and todate.
 
SBSP said:
Thanks Beachless.
I get an error

Code: 
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
Click to expand...

Then you need to use "CONVERT" or "CAST as" to get TRANDATE into a date format.

Something like:

CONVERT(DATE, OESHDT.TRANDATE)
CAST (OESHDT.TRANDATE as DATE)

It depends on the format of the date in your decimal field.
 
Thanks , someone from another forum managed to help me.


the below is working

Code: 
select
ITEM,
[DESC],
LOCATION,
QTYONHAND,
(
	select  SUM(OESHDT.QTYSOLD) from OESHDT 
	where OESHDT.ITEM = zzActiveItems.ITEM and 
	(OESHDT.TRANDATE >= (cast(convert(varchar, DATEADD(month, -4, GETDATE()),112) as int)) and 
	OESHDT.TRANDATE <= (cast(convert(varchar, GETDATE(),112) as int)) ) and 
	OESHDT.LOCATION = zzActiveItems.LOCATION 
) as 'QTY_SOLD'

from zzActiveItems inner join ICITEM on ICITEM.ITEMNO = zzActiveItems.ITEM
 
That should work but it might get you some critique anyways as long as you are happy.
 
I'm all for doing things right so if any one can do it like it should be done, then hey please be my guest :-)
 
The correct way is to compare DATE to DATE in this:
(cast(convert(varchar, DATEADD(month, -4, GETDATE()),112) as int)
You are casting from a DATE to an VARCHAR to a INTEGER and then comparing it to a DECIMAL.

So CONVERT your OESHDT.TRANDATE to a DATE and just compare that to a DATE.

If you give me the OESHDT.TRANDATE format I can finish off the other one I did which was not the best(I would need all the tables to do better) but better than the above.
 
Beachless said:
If you give me the OESHDT.TRANDATE format I can finish off the other one I did which was not the best(I would need all the tables to do better) but better than the above.
Click to expand...

Sorry for my Ignorance, dont know what you mean.

Do you mean this ?

table.png

Date gets saved as I.E 20130101 (YYYYMMDD)

Dont know why its decimal(9,0) and not decimal(8,0)
 
Last edited:
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X