Anything wrong with the SQL ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Anything wrong with the SQL ?

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2009-07-13 : 10:39:56

this is used with Microsoft Reporting services. It works as far at the data connection and presenting the data, but the parameters are not working at all. they are defined in the SSRS as well (as Datetimes)

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SELECT
IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
RM00101.CPRCSTNM,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr,
SOP10200.ITEMNMBR,
SOP10200.SOPNUMBE,
SOP10200.QUANTITY,
SOP10200.OXTNDPRC,
SOP10200.SOPTYPE,
SOP10100.DOCDATE,
'Current' as source

FROM ((((SOP10200
SOP10200 INNER JOIN IV00101 IV00101 ON
SOP10200.ITEMNMBR=IV00101.ITEMNMBR)

INNER JOIN
SOP10100 SOP10100 ON
(SOP10200.SOPTYPE=SOP10100.SOPTYPE) AND
(SOP10200.SOPNUMBE=SOP10100.SOPNUMBE))

INNER JOIN
IV40600 IV40600 ON
IV00101.ITMGEDSC=IV40600.USCATVAL)

INNER JOIN
IV40600 CATS ON
IV00101.USCATVLS_2=CATS.USCATVAL)

INNER JOIN
RM00101 RM00101 ON
SOP10100.CUSTNMBR=RM00101.CUSTNMBR

Where SOP10100.DOCDATE>=@StartDate and SOP10100.DOCDATE <=@EndDate

UNION ALL

SELECT
IV00101.ITMGEDSC,
IV00101.ITEMDESC,
RM00101.CUSTNAME,
RM00101.CPRCSTNM,
IV40600.UserCatLongDescr,
CATS.UserCatLongDescr,
SOP30300.ITEMNMBR,
SOP30300.SOPNUMBE,
SOP30300.QUANTITY,
SOP30300.OXTNDPRC,
SOP30300.SOPTYPE,
SOP30200.DOCDATE,
'History' as source

FROM ((((SOP30300
SOP30300 LEFT OUTER JOIN IV00101 IV00101 ON
SOP30300.ITEMNMBR=IV00101.ITEMNMBR)

INNER JOIN
SOP30200 SOP30200 ON
(SOP30300.SOPTYPE=SOP30200.SOPTYPE) AND
(SOP30300.SOPNUMBE=SOP30200.SOPNUMBE))

LEFT OUTER JOIN
IV40600 IV40600 ON
IV00101.ITMGEDSC=IV40600.USCATVAL)

LEFT OUTER JOIN
IV40600 CATS ON
IV00101.USCATVLS_2=CATS.USCATVAL)

INNER JOIN
RM00101 RM00101 ON
SOP30200.CUSTNMBR=RM00101.CUSTNMBR
Where SOP30200.DOCDATE >= @StartDate and SOP30200.DOCDATE <= @EndDate

chris_cs
Posting Yak Master

223 Posts

Posted - 2009-07-13 : 11:06:12

I may we worth trying something like this:

and cast((str(year(wh.transdate)) + '/' + str(month(wh.transdate)) + '/' + str(day(wh.transdate))) as DATETIME ) <= cast((str(year(@endDate)) + '/' + str(month(@endDate)) + '/' + str(day(@endDate))) as DATETIME )

I had to change the conditions in my SQL to handle a date/time parameter in RS.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-13 : 11:07:08

What datatype is DOCDATE column?

N 56°04'39.26"
E 12°55'05.63"

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-07-13 : 11:08:40

What do you mean by "the parameters don't work"? In your above query, StartDate and EndDate are both null and so won't return any records.

Jim

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2009-07-13 : 11:24:57

Jim, I am new to this, what can I do about the null factor you mentioned?

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2009-07-13 : 11:33:09

Peso, DocDate is a Datetime field.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-07-13 : 12:30:18

They have to be set to something. If this is part of a stored procedure then they can be passed in as variables, or if you always want the last month for reporting. For example, this would give all the docdates for June (@date is used just for demo purposes):

DECLARE @date datetime
DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @date = isnull(@date,getdate())
SET @EndDate = DATEADD(month,datediff(month,0,@date),0)
SET @StartDate = DATEADD(month,-1,@EndDate)

Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-13 : 12:47:33

[code]DECLARE @StartDate DATETIME,
@EndDate DATETIME

SELECT @StartDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0),
@EndDate = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), 0)

SELECT ...
...
WHERE DocDate >= @StartDate
AND DocDate < @EndDate[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources