Comparing dates with today dates

I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date. the datatype that I'm using is smalldatetime.
The statement I used is:
Select Date from Table where Date > 'Today.now'
I get an error
Could this be done or is there another approach?Select Date from Table where Date > getdate()
If you just want to match dates and not times:
Select Date from Table where convert(varchar(10), Date, 101) > convert(varchar(10), getDate(), 101)

Nick

Compare row data and input data into a new field

Dear All
I have a simple query which I cannot seem to work out and would like some
help.
I have a small table with data like such
IDWeek_as_IntWeekEnding Check
ASome 5235/06/2005
ASome 53131/07/2005
ASome 54827/11/2005
ASome 5494/12/2005
ASome 60422/01/2006 1
what I need to do is to create a new column called check and put a "1" check
filed in the last one of the selection. In the example above it would be the
row with "604" in it but it is not always going to be "604" it could be
anything. So what I need to do is to look at each row and when it gets to the
last row that has the same id then to place a "1" in a check field.
The answer is easy using excel but I would like to take that intervension
out an incorporate it in a SQL script as part of the automation
Thanking you in advance
Regards
Newman
>>when it gets to the
last row that has the same id then to place a "1" in a check field. <<
With ref. to your above statement, how will you be defining last row, will
it be on the basis of the maximum number of week_as_int column for respective
id
OR will it be on the basis of maximum date from "weekending" column for
respective id?
If its on the basis of max of week_As_int column your query will look like
as shown in the following example, you can change the same query by selecting
max of weekending column.
--table and sample data
create table t
(IDvarchar(50),Week_as_Int int,WeekEnding datetime, chk int)
insert into t (id, week_As_int,weekending)
select 'ASome',523,'2005/06/5' union all
select 'ASome',548,'2005/11/27' union all
select 'ASome',549,'2005/12/4' union all
select 'ASome',604,'2005/01/22' union all
select 'bSome',700,'2005/06/5' union all
select 'bSome',549,'2005/12/4' union all
select 'bSome',604,'2005/01/22' union all
select 'cSome',700,'2005/06/5' union all
select 'cSome',701,'2005/12/4' union all
select 'cSome',704,'2005/01/22'
--update statement to update the column chk .
update t
set chk = 1
from t a join
(select "id", max(week_as_int) week_as_int
from t
group by "id") b on a.id = b.id and a.week_as_int = b.week_as_int
"Newman Emanouel" wrote:

> Dear All
> I have a simple query which I cannot seem to work out and would like some
> help.
> I have a small table with data like such
> IDWeek_as_IntWeekEnding Check
> ASome 5235/06/2005
> ASome 53131/07/2005
> ASome 54827/11/2005
> ASome 5494/12/2005
> ASome 60422/01/2006 1
> what I need to do is to create a new column called check and put a "1" check
> filed in the last one of the selection. In the example above it would be the
> row with "604" in it but it is not always going to be "604" it could be
> anything. So what I need to do is to look at each row and when it gets to the
> last row that has the same id then to place a "1" in a check field.
> The answer is easy using excel but I would like to take that intervension
> out an incorporate it in a SQL script as part of the automation
> Thanking you in advance
> Regards
> Newman
>
|||On Thu, 26 Jan 2006 19:45:02 -0800, Newman Emanouel wrote:

>Dear All
>I have a simple query which I cannot seem to work out and would like some
>help.
>I have a small table with data like such
>IDWeek_as_IntWeekEnding Check
>ASome 5235/06/2005
>ASome 53131/07/2005
>ASome 54827/11/2005
>ASome 5494/12/2005
>ASome 60422/01/2006 1
>what I need to do is to create a new column called check and put a "1" check
>filed in the last one of the selection. In the example above it would be the
>row with "604" in it but it is not always going to be "604" it could be
>anything. So what I need to do is to look at each row and when it gets to the
>last row that has the same id then to place a "1" in a check field.
>The answer is easy using excel but I would like to take that intervension
>out an incorporate it in a SQL script as part of the automation
>Thanking you in advance
>Regards
>Newman
>
Hi Newman,
Why store it in the table and recalculate it each time you need it?
CREATE VIEW YourView
AS
SELECT a.ID, a.Week_as_Int, a.WeekEnding,
CASE WHEN Week_as_Int = (SELECT MAX(b.Week_as_Int)
FROM YourTable AS b
WHERE b.ID = a.ID)
THEN 1
END AS Check
FROM YourTable AS a
Of course, if you insist on storing it in the table:
UPDATE YourTable
SET Check = 1
WHERE Week_as_Int = (SELECT MAX(b.Week_as_Int)
FROM YourTable AS b
WHERE b.ID = YourTable.ID)
Hugo Kornelis, SQL Server MVP
|||Hugo/Vishal
Thank you
your suggestions worked well
"Hugo Kornelis" wrote:

> On Thu, 26 Jan 2006 19:45:02 -0800, Newman Emanouel wrote:
>
> Hi Newman,
> Why store it in the table and recalculate it each time you need it?
> CREATE VIEW YourView
> AS
> SELECT a.ID, a.Week_as_Int, a.WeekEnding,
> CASE WHEN Week_as_Int = (SELECT MAX(b.Week_as_Int)
> FROM YourTable AS b
> WHERE b.ID = a.ID)
> THEN 1
> END AS Check
> FROM YourTable AS a
>
> Of course, if you insist on storing it in the table:
> UPDATE YourTable
> SET Check = 1
> WHERE Week_as_Int = (SELECT MAX(b.Week_as_Int)
> FROM YourTable AS b
> WHERE b.ID = YourTable.ID)
> --
> Hugo Kornelis, SQL Server MVP
>

Compare 2 IDs in Conditional Split

If I have 2 input fields to my conditional split, how can I do a compare based on if they are alike. Example, I have 2 IDs, I want to see if the IDs match for a PK/FK relationship, if they match, then output those rows to the conditional's output stream. Do I literally do this or is this not right for the expression? Is there a like statement I should be using instead?

[IDName] == [IDName]

Basically I have 2 OLE DB sources coming in, 2 sets of columns, and both tables behind each OLE DB souce have an ID field to determine the PK/FK relationship. Out of all the records going through from the OLE DB source to the conditional split, I want to output each set of records where the IDs are equal...thuse after my conditional split, I could then take those records and input them into another txt file....and then the process would repeat for the next records in the pipe where IDs are the same...

If you want to test 2 fields for equality then yes:

<field1> == <field2>

Is the correct syntax.

-Jamie

comboboxes input parameter

Is it possible to configure combo box with values depending on another
combobox selection in Reporting Parameters
e.g.
Input1: a combobox with country
Input2: should dynamically display states in the selected country, which
user can select.
appreciate any pointers . thxOn Apr 19, 12:06 pm, "nkg" <x...@.yahoo.com> wrote:
> Is it possible to configure combo box with values depending on another
> combobox selection in Reporting Parameters
> e.g.
> Input1: a combobox with country
> Input2: should dynamically display states in the selected country, which
> user can select.
> appreciate any pointers . thx
Sure. What you want to do is create a dataset to use for the second
report parameter (in the Data tab, select the <New Dataset...> option
from the drop-down list to the right of Dataset) and include the usual
query as well as the place holder parameter (i.e., select States from
StateTable where Country = @.Country). Then select the 'Edit Selected
Dataset' button [...] and on the Parameters tab enter in the Name of
the parameter (in this case, @.Country) [it is case sensitive by the
way] and select the Parameter that @.Country should be dependent on
(most likely, something like: Parameters!Country.Value). hope this
helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||thx. a lot that worked.
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1177009678.611966.300310@.b58g2000hsg.googlegroups.com...
> On Apr 19, 12:06 pm, "nkg" <x...@.yahoo.com> wrote:
>> Is it possible to configure combo box with values depending on another
>> combobox selection in Reporting Parameters
>> e.g.
>> Input1: a combobox with country
>> Input2: should dynamically display states in the selected country, which
>> user can select.
>> appreciate any pointers . thx
>
> Sure. What you want to do is create a dataset to use for the second
> report parameter (in the Data tab, select the <New Dataset...> option
> from the drop-down list to the right of Dataset) and include the usual
> query as well as the place holder parameter (i.e., select States from
> StateTable where Country = @.Country). Then select the 'Edit Selected
> Dataset' button [...] and on the Parameters tab enter in the Name of
> the parameter (in this case, @.Country) [it is case sensitive by the
> way] and select the Parameter that @.Country should be dependent on
> (most likely, something like: Parameters!Country.Value). hope this
> helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Apr 20, 2:45 pm, "nkg" <x...@.yahoo.com> wrote:
> thx. a lot that worked."EMartinez" <emartinez...@.gmail.com> wrote in message
> news:1177009678.611966.300310@.b58g2000hsg.googlegroups.com...
> > On Apr 19, 12:06 pm, "nkg" <x...@.yahoo.com> wrote:
> >> Is it possible to configure combo box with values depending on another
> >> combobox selection in Reporting Parameters
> >> e.g.
> >> Input1: a combobox with country
> >> Input2: should dynamically display states in the selected country, which
> >> user can select.
> >> appreciate any pointers . thx
> > Sure. What you want to do is create a dataset to use for the second
> > report parameter (in the Data tab, select the <New Dataset...> option
> > from the drop-down list to the right of Dataset) and include the usual
> > query as well as the place holder parameter (i.e., select States from
> > StateTable where Country = @.Country). Then select the 'Edit Selected
> > Dataset' button [...] and on the Parameters tab enter in the Name of
> > the parameter (in this case, @.Country) [it is case sensitive by the
> > way] and select the Parameter that @.Country should be dependent on
> > (most likely, something like: Parameters!Country.Value). hope this
> > helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Glad I could be of assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Combo box to exclude blank input

Hi
I have a combo box that displays a list of company names. Some entries do
not have a company name on the form so the textbox remains blank. Therefore
in the combo box there are alot of blanks in the list. How do I get the
combo box to just list out the company names without the blanks?
At the moment I have this:
SELECT Contacts.ID, Contacts.CompanyName
FROM Contacts;
Thanks in advance
SeanThe following will exclude NULL or blank CompanyName rows:
SELECT Contacts.ID, Contacts.CompanyName
FROM Contacts
WHERE
Contacts.CompanyName IS NOT NULL AND
Contacts.CompanyName <> '';
Hope this helps.
Dan Guzman
SQL Server MVP
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:9930ACDB-8E70-45B8-94B1-89A8DF57B60F@.microsoft.com...
> Hi
> I have a combo box that displays a list of company names. Some entries do
> not have a company name on the form so the textbox remains blank.
> Therefore
> in the combo box there are alot of blanks in the list. How do I get the
> combo box to just list out the company names without the blanks?
> At the moment I have this:
> SELECT Contacts.ID, Contacts.CompanyName
> FROM Contacts;
> Thanks in advance
> Sean|||Isn't there a Company table that, by definition, wouldn't have empty
company names?
If not, then one should be added - basic normalization.
Sean wrote:
> Hi
> I have a combo box that displays a list of company names. Some entries do
> not have a company name on the form so the textbox remains blank. Therefor
e
> in the combo box there are alot of blanks in the list. How do I get the
> combo box to just list out the company names without the blanks?
> At the moment I have this:
> SELECT Contacts.ID, Contacts.CompanyName
> FROM Contacts;
> Thanks in advance
> Sean