|
|
You're in the
sql server reporting services
group:Getting A Query Parameter from Elsewhere
sql server reporting services:
I have another table. This table also has a datetime column. It has only one row. It basically to store some simple configuration data. There is no relation between the tables. I want to be able to limit the entries returned by the query on the first table by only returning rows that have a date that is less than the date in the row in the second table. I'm actually having trouble getting this to work properly. I've tried subqueries, passing it through the report as a parameter, etc... None are working.
If I use GROUP BY, will that not require aggregation of the results of the
query? Perhaps it would be helpful to see the existing query: SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS QTY, (SELECT SUM(MARKUPTRANS.VALUE) FROM MARKUPTRANS WHERE SALESTABLE.RECID = MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND MARKUPTRANS.MARKUPCODE = 'Freight') AS FreightValue, (SELECT SUM(INVENTSUM.POSTEDVALUE) FROM INVENTSUM WHERE SALESLINE.INVENTDIMID = INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS FROM SALESTABLE INNER JOIN SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID = 'acm') AND (SALESTABLE.SALESSTATUS = 1) That is table one. I want to limit on a column called CREATEDDATE. The other query looks like: SELECT CUTOFFDATE from CONFIGDATA [quoted text, click to view] "Rodney Landrum" wrote:
> It would be possible to use a GROUP BY / HAVING in the SELECT if that was a > possibility for you. I am sure there are much better ways, but this is off > the cuff: > > Select > MyDate, Field1, Field2 from table1 > Group by > MyDate,Field1,Field2 > HAVING MyDate < (Select LookUpDate from Table2) > > You could always do it in a stored procedure and call in the value of Table2 > into a variable @LokUpDate that yo could compare. I assume that the > LookUpDate in my example to match your description changes on a regular > basis? > > Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress) > http://www.apress.com > > "Hunter Hillegas" <HunterHillegas@discussions.microsoft.com> wrote in > message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@microsoft.com... > >I have a query on a table. This table has a datetime column. > > > > I have another table. This table also has a datetime column. It has only > > one > > row. It basically to store some simple configuration data. > > > > There is no relation between the tables. > > > > I want to be able to limit the entries returned by the query on the first > > table by only returning rows that have a date that is less than the date > > in > > the row in the second table. > > > > I'm actually having trouble getting this to work properly. I've tried > > subqueries, passing it through the report as a parameter, etc... None are > > working. > > > > How should i attack this? > >
If I use GROUP BY, will that not require aggregation of the results of the
query? Perhaps it would be helpful to see the existing query: SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS QTY, (SELECT SUM(MARKUPTRANS.VALUE) FROM MARKUPTRANS WHERE SALESTABLE.RECID = MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND MARKUPTRANS.MARKUPCODE = 'Freight') AS FreightValue, (SELECT SUM(INVENTSUM.POSTEDVALUE) FROM INVENTSUM WHERE SALESLINE.INVENTDIMID = INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS FROM SALESTABLE INNER JOIN SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID = 'acm') AND (SALESTABLE.SALESSTATUS = 1) That is table one. I want to limit on a column called CREATEDDATE. The other query looks like: SELECT CUTOFFDATE from CONFIGDATA [quoted text, click to view] "Rodney Landrum" wrote:
> It would be possible to use a GROUP BY / HAVING in the SELECT if that was a > possibility for you. I am sure there are much better ways, but this is off > the cuff: > > Select > MyDate, Field1, Field2 from table1 > Group by > MyDate,Field1,Field2 > HAVING MyDate < (Select LookUpDate from Table2) > > You could always do it in a stored procedure and call in the value of Table2 > into a variable @LokUpDate that yo could compare. I assume that the > LookUpDate in my example to match your description changes on a regular > basis? > > Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress) > http://www.apress.com > > "Hunter Hillegas" <HunterHillegas@discussions.microsoft.com> wrote in > message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@microsoft.com... > >I have a query on a table. This table has a datetime column. > > > > I have another table. This table also has a datetime column. It has only > > one > > row. It basically to store some simple configuration data. > > > > There is no relation between the tables. > > > > I want to be able to limit the entries returned by the query on the first > > table by only returning rows that have a date that is less than the date > > in > > the row in the second table. > > > > I'm actually having trouble getting this to work properly. I've tried > > subqueries, passing it through the report as a parameter, etc... None are > > working. > > > > How should i attack this? > >
It would be possible to use a GROUP BY / HAVING in the SELECT if that was a
possibility for you. I am sure there are much better ways, but this is off the cuff: Select MyDate, Field1, Field2 from table1 Group by MyDate,Field1,Field2 HAVING MyDate < (Select LookUpDate from Table2) You could always do it in a stored procedure and call in the value of Table2 into a variable @LokUpDate that yo could compare. I assume that the LookUpDate in my example to match your description changes on a regular basis? Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress) http://www.apress.com "Hunter Hillegas" <HunterHillegas@discussions.microsoft.com> wrote in message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@microsoft.com... [quoted text, click to view] >I have a query on a table. This table has a datetime column. > > I have another table. This table also has a datetime column. It has only > one > row. It basically to store some simple configuration data. > > There is no relation between the tables. > > I want to be able to limit the entries returned by the query on the first > table by only returning rows that have a date that is less than the date > in > the row in the second table. > > I'm actually having trouble getting this to work properly. I've tried > subqueries, passing it through the report as a parameter, etc... None are > working. > > How should i attack this?
You really do not have to add an aggregate function. You can always set a
variable and use that in the Where clause if you do not want to use the GROUP BY. Something like ( and I added CREATEDATE to the WHERE clause also) : Declare @CUTOFFDATE as DATETIME SELECT @CUTOFFDATE=CUTOFFDATE from CONFIGDATA SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS QTY, (SELECT SUM(MARKUPTRANS.VALUE) FROM MARKUPTRANS WHERE SALESTABLE.RECID = MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND MARKUPTRANS.MARKUPCODE = 'Freight') AS FreightValue, (SELECT SUM(INVENTSUM.POSTEDVALUE) FROM INVENTSUM WHERE SALESLINE.INVENTDIMID = INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS FROM SALESTABLE INNER JOIN SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID = 'acm') AND (SALESTABLE.SALESSTATUS = 1) AND CREATEDATE < @CUTOFFDATE You may have to make this a stored procedure if it will not work on the IDE for reporting Services. Rodney Landrum -Author, "Pro SQL Server Reporting Services" (Apress) http://www.apress.com "Hunter Hillegas" <HunterHillegas@discussions.microsoft.com> wrote in message news:211E0BCB-A461-485A-B888-333A33E33162@microsoft.com... [quoted text, click to view] > If I use GROUP BY, will that not require aggregation of the results of the > query? > > Perhaps it would be helpful to see the existing query: > > SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS > QTY, > (SELECT SUM(MARKUPTRANS.VALUE) > FROM MARKUPTRANS > WHERE SALESTABLE.RECID = > MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND > MARKUPTRANS.MARKUPCODE = > 'Freight') AS FreightValue, > (SELECT SUM(INVENTSUM.POSTEDVALUE) > FROM INVENTSUM > WHERE SALESLINE.INVENTDIMID = > INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS > FROM SALESTABLE INNER JOIN > SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID > WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID = > 'acm') > AND (SALESTABLE.SALESSTATUS = 1) > > That is table one. I want to limit on a column called CREATEDDATE. > > The other query looks like: > > SELECT CUTOFFDATE from CONFIGDATA > > "Rodney Landrum" wrote: > >> It would be possible to use a GROUP BY / HAVING in the SELECT if that was >> a >> possibility for you. I am sure there are much better ways, but this is >> off >> the cuff: >> >> Select >> MyDate, Field1, Field2 from table1 >> Group by >> MyDate,Field1,Field2 >> HAVING MyDate < (Select LookUpDate from Table2) >> >> You could always do it in a stored procedure and call in the value of >> Table2 >> into a variable @LokUpDate that yo could compare. I assume that the >> LookUpDate in my example to match your description changes on a regular >> basis? >> >> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress) >> http://www.apress.com >> >> "Hunter Hillegas" <HunterHillegas@discussions.microsoft.com> wrote in >> message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@microsoft.com... >> >I have a query on a table. This table has a datetime column. >> > >> > I have another table. This table also has a datetime column. It has >> > only >> > one >> > row. It basically to store some simple configuration data. >> > >> > There is no relation between the tables. >> > >> > I want to be able to limit the entries returned by the query on the >> > first >> > table by only returning rows that have a date that is less than the >> > date >> > in >> > the row in the second table. >> > >> > I'm actually having trouble getting this to work properly. I've tried >> > subqueries, passing it through the report as a parameter, etc... None >> > are >> > working. >> > >> > How should i attack this? >> >> >>
I might be missing something but this looks like the following to me:
select a.* from maintable a, configtable b where a.datetimecolumn < b.datetimecolumn -- Bruce Loehle-Conger MVP SQL Server Reporting Services "Hunter Hillegas" <HunterHillegas@discussions.microsoft.com> wrote in message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@microsoft.com... [quoted text, click to view] > I have a query on a table. This table has a datetime column. > > I have another table. This table also has a datetime column. It has only one > row. It basically to store some simple configuration data. > > There is no relation between the tables. > > I want to be able to limit the entries returned by the query on the first > table by only returning rows that have a date that is less than the date in > the row in the second table. > > I'm actually having trouble getting this to work properly. I've tried > subqueries, passing it through the report as a parameter, etc... None are > working. > > How should i attack this?
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |