Query a Date Filed in SQL Server 2000

I want to query a date filed in SQL Server 2000 which its default value is set to GetDate().
My problem is that the data filed saves data and time for example "3/26/2003 5:34:34 PM", Now when I query the table and:

SELECT * FROM tblX WHERE Date='3/26/03' it won't return the record.

However if I use the below:

SELECT * FROM tblX WHERE Date='3/26/2003 5:34:34 PM'

It will return the record.

What should I do so that when I use only date and not time it retuns all records on the specified Data ignoring the time?

Thanksessentialy you want all the records for Mar 26th?

1. If you don't need time only store the date portion of GetDate(). Probably not an option.

select convert(varchar,getdate(),101)

2. Remove the time component in your date attribute in your search. This approach will probably cause a table scan.

WHERE convert(varchar,date,101)='3/26/2003'

3. Use the between test. Some times can be extra work.

WHERE date between '3/26/2003' and '3/26/2003 23:59:59'

Query "hightest value" contents of each "folder"?

Hi,

Can anyone tell me how to select the "most recent" date values from a
grouped query? Consider the following:

CREATE TABLE [dbo].[TestQuery] ( [ID] [int] NOT NULL , [ID_Parent] [int] NOT
NULL , [Date] [datetime] NOT NULL ) ON [PRIMARY]

This is a simplified adjacency list. What I want to do is find the highest
valued item by date for each sub-tree. In other words, the single highest
date item when This.ID_Parent = That.ID_Parent. I think I first need to
group by ID_Parent, then select the TOP 1 from this query, but how to
aggregate them so I get the TOP 1 for each ID_Parent?

Thanks for any help you can give me,

RobinAnother way:

SELECT T1.id_parent, MAX(T1.id) AS id, T1.date
FROM TestQuery AS T1
WHERE T1.date =
(SELECT MAX(date)
FROM TestQuery AS T2
WHERE T1.id_parent = T2.id_parent)
GROUP BY T1.id_parent, T1.date

--
David Portas
SQL Server MVP
--

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Try this:

SELECT T1.id_parent, MAX(T1.id) AS id, T1.date
FROM TestQuery AS T1
LEFT JOIN TestQuery AS T2
ON T1.id_parent = T2.id_parent
AND T1.date < T2.date
WHERE T2.date IS NULL
GROUP BY T1.id_parent, T1.date

--
David Portas
SQL Server MVP
--

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||here is a related article:
subject: select first record from a group ordered by 3 columns
date: Nov 27 2002, 5:01 am

http://groups-beta.google.com/group...23b16be2b945c0f

David Portas wrote:
> Another way:
> SELECT T1.id_parent, MAX(T1.id) AS id, T1.date
> FROM TestQuery AS T1
> WHERE T1.date =
> (SELECT MAX(date)
> FROM TestQuery AS T2
> WHERE T1.id_parent = T2.id_parent)
> GROUP BY T1.id_parent, T1.date
> --
> David Portas
> SQL Server MVP
> --
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

query - which came last?

Hi,
Query puzzler here.
I have a table of PEOPLE.
Besides the Person ID, each record has five Date fields- Married ,
Graduated, Started Job, Got Famous, Got Rich
I want to write a query that shows what the current status of each person
is.
In other words, show which event has happened most recently for each person
Any help would be , and might keep me from getting fired.No datefields in the table? Can you show one or two rows as sample in your
table?
Thanks,
Sree
"Tales Mein" wrote:

> Hi,
> Query puzzler here.
> I have a table of PEOPLE.
> Besides the Person ID, each record has five Date fields- Married ,
> Graduated, Started Job, Got Famous, Got Rich
> I want to write a query that shows what the current status of each person
> is.
> In other words, show which event has happened most recently for each perso
n
> Any help would be , and might keep me from getting fired.
>
>
>
>|||Hi, no-one wants to see you get fired...
SELECT PersonID, max(Married) FROM (
select PersonID, Married from PEOPLE
union
select PersonID, Graduated from PEOPLE
union
select PersonID, [Started Job] from PEOPLE
union
select PersonID, [Got Famous] from PEOPLE
union
select PersonID, [Got Rich] from PEOPLE) as Derived
GROUP BY PersonID
HTH. Ryan
"Tales Mein" <toddough@.doughpdu.com> wrote in message
news:IuednZ9bsJhDO0neRVn-oQ@.comcast.com...
> Hi,
> Query puzzler here.
> I have a table of PEOPLE.
> Besides the Person ID, each record has five Date fields- Married ,
> Graduated, Started Job, Got Famous, Got Rich
> I want to write a query that shows what the current status of each person
> is.
> In other words, show which event has happened most recently for each
> person
> Any help would be , and might keep me from getting fired.
>
>
>
>|||you can use this code:
SELECT ID,
Case When Married IS NOT NULL
And Married >= Coalesce (Graduated,'19000101')
And Married >= Coalesce ([Started Job],'19000101')
And Married >= Coalesce ([Got Famous],'19000101')
And Married >= Coalesce ([Got Rich],'19000101')
Then 'Married'
When Graduated IS NOT NULL
And Graduated >= Coalesce (Married,'19000101')
And Graduated >= Coalesce ([Started Job],'19000101')
And Graduated >= Coalesce ([Got Famous],'19000101')
And Graduated >= Coalesce ([Got Rich],'19000101')
Then 'Graduated'
When [Started Job] IS NOT NULL
And [Started Job] >= Coalesce (Graduated,'19000101')
And [Started Job] >= Coalesce (Married,'19000101')
And [Started Job] >= Coalesce ([Got Famous],'19000101')
And [Started Job] >= Coalesce ([Got Rich],'19000101')
Then '[Started Job]'
When [Got Famous] IS NOT NULL
And [Got Famous] >= Coalesce (Graduated,'19000101')
And [Got Famous] >= Coalesce ([Started Job],'19000101')
And [Got Famous] >= Coalesce (Married,'19000101')
And [Got Famous] >= Coalesce ([Got Rich],'19000101')
Then '[Got Famous]'
When [Got Rich] IS NOT NULL
And [Got Rich] >= Coalesce (Graduated,'19000101')
And [Got Rich] >= Coalesce ([Started Job],'19000101')
And [Got Rich] >= Coalesce ([Got Famous],'19000101')
And [Got Rich] >= Coalesce (Married,'19000101')
Then '[Got Rich]'
Else 'nothing happend'
End
As LastEvent,
Case When Married IS NOT NULL
And Married >= Coalesce (Graduated,'19000101')
And Married >= Coalesce ([Started Job],'19000101')
And Married >= Coalesce ([Got Famous],'19000101')
And Married >= Coalesce ([Got Rich],'19000101')
Then Married
When Graduated IS NOT NULL
And Graduated >= Coalesce (Married,'19000101')
And Graduated >= Coalesce ([Started Job],'19000101')
And Graduated >= Coalesce ([Got Famous],'19000101')
And Graduated >= Coalesce ([Got Rich],'19000101')
Then Graduated
When [Started Job] IS NOT NULL
And [Started Job] >= Coalesce (Graduated,'19000101')
And [Started Job] >= Coalesce (Married,'19000101')
And [Started Job] >= Coalesce ([Got Famous],'19000101')
And [Started Job] >= Coalesce ([Got Rich],'19000101')
Then [Started Job]
When [Got Famous] IS NOT NULL
And [Got Famous] >= Coalesce (Graduated,'19000101')
And [Got Famous] >= Coalesce ([Started Job],'19000101')
And [Got Famous] >= Coalesce (Married,'19000101')
And [Got Famous] >= Coalesce ([Got Rich],'19000101')
Then [Got Famous]
When [Got Rich] IS NOT NULL
And [Got Rich] >= Coalesce (Graduated,'19000101')
And [Got Rich] >= Coalesce ([Started Job],'19000101')
And [Got Rich] >= Coalesce ([Got Famous],'19000101')
And [Got Rich] >= Coalesce (Married,'19000101')
Then [Got Rich]
End
As LastEventDate
FROM PEOPLE
"Tales Mein" wrote:

> Hi,
> Query puzzler here.
> I have a table of PEOPLE.
> Besides the Person ID, each record has five Date fields- Married ,
> Graduated, Started Job, Got Famous, Got Rich
> I want to write a query that shows what the current status of each person
> is.
> In other words, show which event has happened most recently for each perso
n
> Any help would be , and might keep me from getting fired.
>
>
>
>|||Thanks - this seems to be the shorter of the two, saving typing, allowing
more time to beg for my job.
Both are instructive.
thnanks a lot
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23iR7usAIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hi, no-one wants to see you get fired...
> SELECT PersonID, max(Married) FROM (
> select PersonID, Married from PEOPLE
> union
> select PersonID, Graduated from PEOPLE
> union
> select PersonID, [Started Job] from PEOPLE
> union
> select PersonID, [Got Famous] from PEOPLE
> union
> select PersonID, [Got Rich] from PEOPLE) as Derived
> GROUP BY PersonID
> --
> HTH. Ryan
> "Tales Mein" <toddough@.doughpdu.com> wrote in message
> news:IuednZ9bsJhDO0neRVn-oQ@.comcast.com...
>|||Hi this is almost there, but it returns dates. I need the column name
returened.
Like: Married
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23iR7usAIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hi, no-one wants to see you get fired...
> SELECT PersonID, max(Married) FROM (
> select PersonID, Married from PEOPLE
> union
> select PersonID, Graduated from PEOPLE
> union
> select PersonID, [Started Job] from PEOPLE
> union
> select PersonID, [Got Famous] from PEOPLE
> union
> select PersonID, [Got Rich] from PEOPLE) as Derived
> GROUP BY PersonID
> --
> HTH. Ryan
> "Tales Mein" <toddough@.doughpdu.com> wrote in message
> news:IuednZ9bsJhDO0neRVn-oQ@.comcast.com...
>|||I need the status, not the date
OK I could use both.
I may get fired after all
"Tales Mein" <toddough@.doughpdu.com> wrote in message
news:IuednZ9bsJhDO0neRVn-oQ@.comcast.com...
> Hi,
> Query puzzler here.
> I have a table of PEOPLE.
> Besides the Person ID, each record has five Date fields- Married ,
> Graduated, Started Job, Got Famous, Got Rich
> I want to write a query that shows what the current status of each person
> is.
> In other words, show which event has happened most recently for each
> person
> Any help would be , and might keep me from getting fired.
>
>
>
>|||On Mon, 23 Jan 2006 02:50:28 -0700, Tales Mein wrote:

>Hi,
>Query puzzler here.
>I have a table of PEOPLE.
>Besides the Person ID, each record has five Date fields- Married ,
>Graduated, Started Job, Got Famous, Got Rich
>I want to write a query that shows what the current status of each person
>is.
>In other words, show which event has happened most recently for each person
>Any help would be , and might keep me from getting fired.
Hi Tales,
The person who designed that table should get fired, not you.
First, let's create a view to get a better normalised version of the
data:
CREATE VIEW Normalized
AS
SELECT PersonID, Married AS EventDate, 'Married' AS Event
FROM PEOPLE
UNION ALL
SELECT PersonID, Graduated, 'Graduated'
FROM PEOPLE
UNION ALL
SELECT PersonID, "Started Job", 'Started Job'
FROM PEOPLE
UNION ALL
SELECT PersonID, "Got Famous", 'Got Famous'
FROM PEOPLE
UNION ALL
SELECT PersonID, "Got Rich", 'Got Rich'
FROM PEOPLE
go
With this, getting the required information becomes a lot easier:
SELECT a.PersonID, a.EventDate, a.Event
FROM Normalized AS a
WHERE a.EventDate = (SELECT MAX(b.EventDate)
FROM Normalized AS b
WHERE b.PeopleID = a.PeopleID)
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP|||thanks a lot|||Hugo,
Is it just me, or are the columns Married, Graduated, StartedJob, GotFamous,
and GotRich unlikely columns in any database?
Unlikely, of course, unless the database is academic only.
Maybe Tales simply made up these column names because they couldn't tell us
the actual columns for proprietary reasons, but I am willing to bet this is
a classroom example, and no one's job is at risk.
I believe Tales just got this newsgroup to do his/her homework.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
news:r3adt15gapiv1bvvkcitvvrfq33rg850d8@.
4ax.com...
> On Mon, 23 Jan 2006 02:50:28 -0700, Tales Mein wrote:
> Hi Tales,
> The person who designed that table should get fired, not you.
>

Query

I am trying to make a query to get the date and time a row and the date and
time from the next row:
Source_Index Source_TDate Source_MSecs Note_TDate Note_MSecs Note_Text
Batch_Text Lot_Text Author Batch_Note Line
0 7/11/2005 14:59 816 7/11/2005 14:59 816 5 2443 LotTag 1 1
0 7/11/2005 15:00 636 7/11/2005 15:00 636 5 9987 LotTag 1 2
0 7/11/2005 15:01 211 7/11/2005 15:01 211 6 BatchTag 1 3
0 7/11/2005 15:14 736 7/11/2005 15:14 736 7 BatchTag 1 4
This is what I came up with.
USE Iconics_Data
Select Source_TDate as 'Start Date'
From IHZONE1_Notes
where Batch_text='5' and Lot_Text='2443'
I get
Start Date
2005-07-11 14:59:20.000
I also need the query to give me a column
End Date
2005-07-11 15:00:50.00
How could I do this?
I got it wirking.
Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
From IHZONE1_Notes N1, IHZONE1_Notes N2
Where N1.line=N2.line + 1 and N1.Batch_text='5' and N1.Lot_Text='9987'
<Chris Gay> wrote in message news:upPlYhyhFHA.4048@.TK2MSFTNGP10.phx.gbl...
>I am trying to make a query to get the date and time a row and the date and
>time from the next row:
> Source_Index Source_TDate Source_MSecs Note_TDate Note_MSecs
> Note_Text Batch_Text Lot_Text Author Batch_Note Line
> 0 7/11/2005 14:59 816 7/11/2005 14:59 816 5 2443 LotTag 1 1
> 0 7/11/2005 15:00 636 7/11/2005 15:00 636 5 9987 LotTag 1 2
> 0 7/11/2005 15:01 211 7/11/2005 15:01 211 6 BatchTag 1 3
> 0 7/11/2005 15:14 736 7/11/2005 15:14 736 7 BatchTag 1 4
> This is what I came up with.
> USE Iconics_Data
> Select Source_TDate as 'Start Date'
> From IHZONE1_Notes
> where Batch_text='5' and Lot_Text='2443'
> I get
> Start Date
> 2005-07-11 14:59:20.000
> I also need the query to give me a column
> End Date
> 2005-07-11 15:00:50.00
> How could I do this?
>
|||A little typo:
Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
From IHZONE1_Notes N1, IHZONE1_Notes N2
Where N1.line=N2.line + 1 and N2.Batch_text='5' and N2.Lot_Text='9987'
"Chris Gay" <ChrisGay@.RMTWoodworth.com> wrote in message
news:ekWcBC0hFHA.328@.tk2msftngp13.phx.gbl...
>I got it wirking.
> Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
> From IHZONE1_Notes N1, IHZONE1_Notes N2
> Where N1.line=N2.line + 1 and N1.Batch_text='5' and N1.Lot_Text='9987'
>
> <Chris Gay> wrote in message news:upPlYhyhFHA.4048@.TK2MSFTNGP10.phx.gbl...
>

query

There are two tables
Expense Table
Amount date
5000 01/01/2004
5000 01/01/2004
100 01/01/2004
Sal Table
Amount date
10000 01/01/2004
400 01/01/2004
500 01/01/2004
100 01/01/2004
expense amount sale amount
5000 10000
5000 400
100 500
100
I want the data like this against 01/01/2004 date, can any body give me
query that how I fulfill that one.
Thanks
NOORYou would be best laying it out in this format client side. Just write
two stored procedures, one to retrieve the expenses and one to retrieve
the sales, both that take a data as a parameter and then process them
client side.
Regards,
William D. Bartholomew
http://blog.bartholomew.id.au/
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||What logic would you use to join these two tables? How do you know, for
instance, that either of the given 5000 expense amounts corresponds to a
given sale (say, the 10000 item you have matched up in your sample output?)
Are there more columns (like, a primary key?)
Please post DDL (full table definitions in SQL), as well as sample data in
the form of INSERT statements.
"Noorali Issani" <naissani@.softhome.net> wrote in message
news:%23FcnRIWPEHA.3748@.TK2MSFTNGP09.phx.gbl...
> There are two tables
> Expense Table
> Amount date
> 5000 01/01/2004
> 5000 01/01/2004
> 100 01/01/2004
> Sal Table
> Amount date
> 10000 01/01/2004
> 400 01/01/2004
> 500 01/01/2004
> 100 01/01/2004
>
> expense amount sale amount
> 5000 10000
> 5000 400
> 100 500
> 100
> I want the data like this against 01/01/2004 date, can any body give me
> query that how I fulfill that one.
> Thanks
> NOOR
>

Query

I am trying to make a query to get the date and time a row and the date and
time from the next row:
Source_Index Source_TDate Source_MSecs Note_TDate Note_MSecs Note_Text
Batch_Text Lot_Text Author Batch_Note Line
0 7/11/2005 14:59 816 7/11/2005 14:59 816 5 2443 LotTag 1 1
0 7/11/2005 15:00 636 7/11/2005 15:00 636 5 9987 LotTag 1 2
0 7/11/2005 15:01 211 7/11/2005 15:01 211 6 BatchTag 1 3
0 7/11/2005 15:14 736 7/11/2005 15:14 736 7 BatchTag 1 4
This is what I came up with.
USE Iconics_Data
Select Source_TDate as 'Start Date'
From IHZONE1_Notes
where Batch_text='5' and Lot_Text='2443'
I get
Start Date
2005-07-11 14:59:20.000
I also need the query to give me a column
End Date
2005-07-11 15:00:50.00
How could I do this?I got it wirking.
Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
From IHZONE1_Notes N1, IHZONE1_Notes N2
Where N1.line=N2.line + 1 and N1.Batch_text='5' and N1.Lot_Text='9987'
<Chris Gay> wrote in message news:upPlYhyhFHA.4048@.TK2MSFTNGP10.phx.gbl...
>I am trying to make a query to get the date and time a row and the date and
>time from the next row:
> Source_Index Source_TDate Source_MSecs Note_TDate Note_MSecs
> Note_Text Batch_Text Lot_Text Author Batch_Note Line
> 0 7/11/2005 14:59 816 7/11/2005 14:59 816 5 2443 LotTag 1 1
> 0 7/11/2005 15:00 636 7/11/2005 15:00 636 5 9987 LotTag 1 2
> 0 7/11/2005 15:01 211 7/11/2005 15:01 211 6 BatchTag 1 3
> 0 7/11/2005 15:14 736 7/11/2005 15:14 736 7 BatchTag 1 4
> This is what I came up with.
> USE Iconics_Data
> Select Source_TDate as 'Start Date'
> From IHZONE1_Notes
> where Batch_text='5' and Lot_Text='2443'
> I get
> Start Date
> 2005-07-11 14:59:20.000
> I also need the query to give me a column
> End Date
> 2005-07-11 15:00:50.00
> How could I do this?
>|||A little typo:
Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
From IHZONE1_Notes N1, IHZONE1_Notes N2
Where N1.line=N2.line + 1 and N2.Batch_text='5' and N2.Lot_Text='9987'
"Chris Gay" <ChrisGay@.RMTWoodworth.com> wrote in message
news:ekWcBC0hFHA.328@.tk2msftngp13.phx.gbl...
>I got it wirking.
> Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
> From IHZONE1_Notes N1, IHZONE1_Notes N2
> Where N1.line=N2.line + 1 and N1.Batch_text='5' and N1.Lot_Text='9987'
>
> <Chris Gay> wrote in message news:upPlYhyhFHA.4048@.TK2MSFTNGP10.phx.gbl...
>>I am trying to make a query to get the date and time a row and the date
>>and time from the next row:
>> Source_Index Source_TDate Source_MSecs Note_TDate Note_MSecs
>> Note_Text Batch_Text Lot_Text Author Batch_Note Line
>> 0 7/11/2005 14:59 816 7/11/2005 14:59 816 5 2443 LotTag 1 1
>> 0 7/11/2005 15:00 636 7/11/2005 15:00 636 5 9987 LotTag 1 2
>> 0 7/11/2005 15:01 211 7/11/2005 15:01 211 6 BatchTag 1 3
>> 0 7/11/2005 15:14 736 7/11/2005 15:14 736 7 BatchTag 1 4
>> This is what I came up with.
>> USE Iconics_Data
>> Select Source_TDate as 'Start Date'
>> From IHZONE1_Notes
>> where Batch_text='5' and Lot_Text='2443'
>> I get
>> Start Date
>> 2005-07-11 14:59:20.000
>> I also need the query to give me a column
>> End Date
>> 2005-07-11 15:00:50.00
>> How could I do this?
>sql

Query

I am trying to make a query to get the date and time a row and the date and
time from the next row:
Source_Index Source_TDate Source_MSecs Note_TDate Note_MSecs Note_Text
Batch_Text Lot_Text Author Batch_Note Line
0 7/11/2005 14:59 816 7/11/2005 14:59 816 5 2443 LotTag 1 1
0 7/11/2005 15:00 636 7/11/2005 15:00 636 5 9987 LotTag 1 2
0 7/11/2005 15:01 211 7/11/2005 15:01 211 6 BatchTag 1 3
0 7/11/2005 15:14 736 7/11/2005 15:14 736 7 BatchTag 1 4
This is what I came up with.
USE Iconics_Data
Select Source_TDate as 'Start Date'
From IHZONE1_Notes
where Batch_text='5' and Lot_Text='2443'
I get
Start Date
2005-07-11 14:59:20.000
I also need the query to give me a column
End Date
2005-07-11 15:00:50.00
How could I do this?I got it wirking.
Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
From IHZONE1_Notes N1, IHZONE1_Notes N2
Where N1.line=N2.line + 1 and N1.Batch_text='5' and N1.Lot_Text='9987'
<Chris Gay> wrote in message news:upPlYhyhFHA.4048@.TK2MSFTNGP10.phx.gbl...
>I am trying to make a query to get the date and time a row and the date and
>time from the next row:
> Source_Index Source_TDate Source_MSecs Note_TDate Note_MSecs
> Note_Text Batch_Text Lot_Text Author Batch_Note Line
> 0 7/11/2005 14:59 816 7/11/2005 14:59 816 5 2443 LotTag 1 1
> 0 7/11/2005 15:00 636 7/11/2005 15:00 636 5 9987 LotTag 1 2
> 0 7/11/2005 15:01 211 7/11/2005 15:01 211 6 BatchTag 1 3
> 0 7/11/2005 15:14 736 7/11/2005 15:14 736 7 BatchTag 1 4
> This is what I came up with.
> USE Iconics_Data
> Select Source_TDate as 'Start Date'
> From IHZONE1_Notes
> where Batch_text='5' and Lot_Text='2443'
> I get
> Start Date
> 2005-07-11 14:59:20.000
> I also need the query to give me a column
> End Date
> 2005-07-11 15:00:50.00
> How could I do this?
>|||A little typo:
Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
From IHZONE1_Notes N1, IHZONE1_Notes N2
Where N1.line=N2.line + 1 and N2.Batch_text='5' and N2.Lot_Text='9987'
"Chris Gay" <ChrisGay@.RMTWoodworth.com> wrote in message
news:ekWcBC0hFHA.328@.tk2msftngp13.phx.gbl...
>I got it wirking.
> Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
> From IHZONE1_Notes N1, IHZONE1_Notes N2
> Where N1.line=N2.line + 1 and N1.Batch_text='5' and N1.Lot_Text='9987'
>
> <Chris Gay> wrote in message news:upPlYhyhFHA.4048@.TK2MSFTNGP10.phx.gbl...
>

Queries in SQL compact Edition

If my research is correct SQL CE as of this date does not support STORED PROCEDURES (queries).

If this is true, how can it hope to compete with MS ACCESS or MS Foxpro as a Desktop database.

This seems to be a lot of hype for something that appears to be totally in-adequate.

I thought this was a way to wean off of MS ACCESS.

Can someone tell me what I am missing ?

Thanks

John

SQL CE is not a replacment for MS Access, but can be used as small footprint, in-process database engine together with yur custom application. Queries (meaning select statements in your code) is supported. The storage of your query strings must be handled by your application, not by SQL CE.|||

Access is a front-end database application tool--not a DBMS. Access by default uses JET as its DBMS engine. SQLCe is not an Access replacement but it can be a faster, lighter, more secure alternative to JET.

AFA stored procedures, these can be implemented in your code but they are not stored in the database or exectuted by the DBMS engine. This gives you the ability to create your own common code routines to replace SP functionality--something JET does not have. While JET supports canned queries, these do not support the logic afforeded by a real SP as supported by TSQL and SQL Server.

For many situations even JET is overkill--SQLCe is designed for these situations--especially when security of the stored data is an issue. JET databases cannot be encrypted--SQLCe databases can.

See my EBook FMI: www.hitchhikerguides.net.

Queried parameters

Hello,

I need to be able to set the date parameters of a report dynamically when it is run based on system time. The problem I am having is being able to compare the dates (StartDate & EndDate) against [Service Date 1]. Essentially this report will only pull the current month's data.

The date fields being created with the GETDATE, DATEADD & DATEDIFF functions are working correctly. Do I need to create a separate dataset to be able to run the parameters automatically in the actual report?

Any help would be greatly appreciated!

SELECT TodaysDate =GetDate()-2,dbo.[Billing Detail].[Service Date 1], DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0) AS StartDate, DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0)) AS EndDate, dbo.[Billing Detail].Billing, dbo.[Billing Detail].Chart, dbo.[Billing Detail].Item,
dbo.[Billing Detail].[Sub Item], dbo.Patient.[Patient Code], dbo.Patient.[Patient Type], dbo.[Billing Header].Charges, dbo.Practice.Name
FROM dbo.[Billing Detail] INNER JOIN
dbo.Patient ON dbo.[Billing Detail].Chart = dbo.Patient.[Chart Number] INNER JOIN
dbo.[Billing Header] ON dbo.[Billing Detail].Billing = dbo.[Billing Header].Billing CROSS JOIN
dbo.Practice
WHERE (dbo.[Billing Detail].Item = 0) AND (dbo.[Billing Detail].[Sub Item] = 0) AND (dbo.[Billing Detail].[Service Date 1] Between StartDate AND EndDate

Phorest,

You should be able to add the parameters to your query. If you are going against SQL Server, you can replace your parameters with @.StartDate AND @.EndDate. Then in the properies of the dataset, you can assign those parameters to Parameters!StartDate.Value and Parameters!EndDate.Value, respectively.

Jessica

|||

Thanks for your reply!

OK,

I think what I need to do is write the expression as a non-queried default value. However when I paste in what I know works in SQL Management Studio it returns an error "Name 'mm' is not declared"

<@.StartDate> =DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0)

<@.EndDate> =DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0))

I tried putting an integer after DATEADD(mm, X , 102 DATEDIFF... but i can't get beyond intellisense. How can I fix my expression to work with Reporting Services?

What I need is to have expressions to choose the first day of the month to the last day of the same month compared to NOW()

|||

Apparently that is the trick to use non-queried default values as an expression, However what I posted yesterday will not work as an expression due to the expressions limitations in SSRS:

<@.StartDate> =DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0)

<@.EndDate> =DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0))

Now I am using:

<@.StartDate> =DATEADD("D", -30, NOW())

<@.EndDate> =DATEADD("D", 1, NOW())

After much searching and experimentation I can get this to work well, but it isn't exactly what I want. Does any one have any tips as to being able to write the expression to select the first day of the current month and last day of the month?

It seems to be just beyond my grasp at this time...

Thanks!

|||

Phorest,

I'm afraid I misunderstood what you're trying to do. If you want a query that returns rows where the [Billing Detail].[Service Date 1] is between the start and the end of the current month, you can do that all in SQL.

It would look something similar to:

WHERE dbo.[Billing Detail].[Service Date 1]

BETWEEN dateadd(mm, datediff(mm,0,getdate()), 0)

AND dateadd(ms,-3,dateadd(mm, datediff(m,0,getdate() ) + 1, 0))

Does that work for you?

Jessica

|||

I'll have to try that in the SQL, though I was more after an expression more as a datetime datatype so it picks all the dates in the current month only and the user can then adjust the parameter manually after the initial running of the report if they so choose.

Thanks!

|||

I found what I was looking for here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1581230&SiteID=1

In the Report Parameters properties I set the DataType to DateTime and using the Default Values, Non-Queried radio button set the expressions like the following:

@.StartDate =DateSerial(Year(NOW()), Month(NOW)) +0,1) gives me the first date of the current month.

@.EndDate =DateSerial(Year(NOW()), Month(NOW)) +1,0) gives me the last date of the current month.

All is wellnow!

Quarterly Values

i have a date heirarchy

dim date

Year

Quarter

Month

and measures SalesVolume

how can i create salesvolume calculated measure for prev quarter, prev 3 quarters and so on.

Dear Chilukurisri,

try to use the same logic of the other post and try to use the function QTD too.

Helped?

Regards!

|||thanx pedroCGD, but QTD will give Quarters to date value so if we select the current member then it will give the sum of quarters for all the values starting beginning of the year but what i am looking is for a formula to calculate last 3 quarters irrespective of wheather i select what member in the current year i.e end of the quarter or middle of the quarter. How can i achieve that?

Quarterly trending

I have a reporting query that I need to do a quarterly trending on. A
user enters a begining date and end date, and from there, I have to
determine the closest quarter to it.

Also, the actual Quarter starts on Sept 4th, then add 13 weeks to that,
will be the second quarter.

I was able to do the trending for week/month/year, but I'm stuck on
this quarterly trending.

Any help would be greatly appreciated.

Example of the monthly trending that I have written:

SELECT [VALUE],DATEPART(MONTH,DATESAMPLE) AS[MONTH]
FROM #TABLE
GROUP BY [VALUE], DATEPART(MONTH,DATESAMPLE)
ORDER BY EDIT_SP, DATEPART(MONTH,DATESAMPLE)This would be easy if your business used calendar quarters instead;
can't you make them change? <G> You could then group by
DATEPART(Quarter, DateSample)

Since that's not likely, a solution might be to use a calendar table
that takes a key Date and maps it to the appropriate quarter, eg:

CREATE TABLE Calendar (DateKey smalldatetime, PeriodQuarter int...
(other columns))

INSERT INTO Calendar (DateKey, PeriodQuarter)
SELECT '9/1/2005', 2
UNION ALL
SELECT '9/4/2005', 3

etc

You could then join and group on the PeriodQuarter value.

Just an idea.
Stu|||create a table of the quarters:

CREATE TABLE Quarters
(quarter_name CHAR(20) NOT NULL PRIMARY KEY,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date ));

>> .. determine the closest quarter to it. <<

Whatr does thst mean? give me an algorithm.

quarter date

How can I calculate the first day of the quarter based on the current date?
Ex. Today's date is 5/30/06. I'm trying to get 4/1/06.
Thanks!maybe something like this..
declare @.a datetime
set @.a = getdate()
select @.a
select cast(cast(year(@.a) as varchar)+ '0' + cast((datepart(qq,@.a)-1)*3 + 1
as varchar) + '01' as datetime)|||Like this...
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE())+0, 0)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Boy.. excellent piece of code :)
"SQL Menace" wrote:

> Like this...
> SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE())+0, 0)
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||Juz one nagging doubt. Why yuu need that +0
wouldn't this work good enuf'
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)|||Perfect, thanks so much!
"SQL Menace" wrote:

> Like this...
> SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE())+0, 0)
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||Thanks
Denis the SQL Menace
http://sqlservercode.blogspot.com/

QA returning wrong data

SQL 7.0
In QA:
"Select name from <table> where date = '20041010'
I get a list of 75,000 names. The name "Jones" is not in
the list.
However, if in QA:
"Select * from <table> where name = 'Jones' and ddate
= '20041010'
The name "Jones" is returned!
I can't figure this out for the life of me. It's a table
with 95 million records and a clustered index based on
name,date.
Any help appreciated.
thx,
Don"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
Just to make absolutely sure (nothing personal ), how are you
verifying that the name 'Jones' is not in the result set?
Assuming that Jones really isn't there, I would A) Make sure you're
upgraded to the latest service pack for SQL Server 7.0 (SP4, IIRC), B) Run
DBCC CHECKDB to verify that there's no data corruption, and C) Make sure you
look in the messages pane -- sometimes you'll get warnings indicating why
rows are not being returned.|||Are these the exact queries you are running (copy and paste from QA)?
In one you are looking for date and in the other you are looking for ddate?
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> SQL 7.0
> In QA:
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
> However, if in QA:
> "Select * from <table> where name = 'Jones' and ddate
> = '20041010'
> The name "Jones" is returned!
> I can't figure this out for the life of me. It's a table
> with 95 million records and a clustered index based on
> name,date.
> Any help appreciated.
> thx,
> Don
>|||Ok, here are the exact queries:
select name from maindata where ddate = '20041011'
select * from maindata where name = 'vpipx' and ddate
= '20041011'

>--Original Message--
>Are these the exact queries you are running (copy and
paste from QA)?
>In one you are looking for date and in the other you are
looking for ddate?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
in[vbcol=seagreen]
table[vbcol=seagreen]
>
>.
>|||In QA, I can do an Edit/Find, or I can copy the results
and paste into NotePad and search there, or I can scroll
down and see where the name should be alphabetically.
It's already running SP4.
Ran DBCC CHECKDB and no errors were returned.
There were no messages or messages pane.
Thx,
Don

>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
in[vbcol=seagreen]
> Just to make absolutely sure (nothing personal ),
how are you
>verifying that the name 'Jones' is not in the result set?
> Assuming that Jones really isn't there, I would A)
Make sure you're
>upgraded to the latest service pack for SQL Server 7.0
(SP4, IIRC), B) Run
>DBCC CHECKDB to verify that there's no data corruption,
and C) Make sure you
>look in the messages pane -- sometimes you'll get
warnings indicating why
>rows are not being returned.
>
>.
>|||Can you post the CREATE TABLE DDL for maindata as well? Also please include
the results of SELECT @.@.VERSION.
Anith|||>--Original Message--
>Can you post the CREATE TABLE DDL for maindata as well?
Also please include
>the results of SELECT @.@.VERSION.
>--
>Anith
>
Here you go..
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)
(1 row(s) affected)
CREATE TABLE [dbo].[MAINDATA] (
[Name] [varchar] (32) NOT NULL ,
[DDate] [smalldatetime] NOT NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[DV] [int] NOT NULL ,
[DOI] [int] NOT NULL
) ON [PRIMARY]
GO|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9a01c4b536$557551f0$a401280a@.phx.gbl...
> Ok, here are the exact queries:
> select name from maindata where ddate = '20041011'
> select * from maindata where name = 'vpipx' and ddate
> = '20041011'
What happens when you try:
select name from maindata where name = 'vpipx' and ddate
= '20041011'
Also, can you check the execution plans of the two queries and see if
they're using different indexes? If so, can you try an index hint on the
first query to force use of the other index and see if that corrects the
issue?|||> What happens when you try:
>select name from maindata where name = 'vpipx' and ddate
> = '20041011'
data returned: 'VPIPX'

> Also, can you check the execution plans of the two
queries and see if
>they're using different indexes? If so, can you try an
index hint on the
>first query to force use of the other index and see if
that corrects the
>issue?
looking at the execution plan for:
select name from maindata where ddate = '20041011'
Warning: Statistics missing for this table.
Choose 'Create Missing Statistics' from the context menu.
thx,
don

>.
>

QA returning wrong data

SQL 7.0
In QA:
"Select name from <table> where date = '20041010'
I get a list of 75,000 names. The name "Jones" is not in
the list.
However, if in QA:
"Select * from <table> where name = 'Jones' and ddate
= '20041010'
The name "Jones" is returned!
I can't figure this out for the life of me. It's a table
with 95 million records and a clustered index based on
name,date.
Any help appreciated.
thx,
Don"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
Just to make absolutely sure (nothing personal :) ), how are you
verifying that the name 'Jones' is not in the result set?
Assuming that Jones really isn't there, I would A) Make sure you're
upgraded to the latest service pack for SQL Server 7.0 (SP4, IIRC), B) Run
DBCC CHECKDB to verify that there's no data corruption, and C) Make sure you
look in the messages pane -- sometimes you'll get warnings indicating why
rows are not being returned.|||Are these the exact queries you are running (copy and paste from QA)?
In one you are looking for date and in the other you are looking for ddate?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> SQL 7.0
> In QA:
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
> However, if in QA:
> "Select * from <table> where name = 'Jones' and ddate
> = '20041010'
> The name "Jones" is returned!
> I can't figure this out for the life of me. It's a table
> with 95 million records and a clustered index based on
> name,date.
> Any help appreciated.
> thx,
> Don
>|||Ok, here are the exact queries:
select name from maindata where ddate = '20041011'
select * from maindata where name = 'vpipx' and ddate
= '20041011'
>--Original Message--
>Are these the exact queries you are running (copy and
paste from QA)?
>In one you are looking for date and in the other you are
looking for ddate?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
>> SQL 7.0
>> In QA:
>> "Select name from <table> where date = '20041010'
>> I get a list of 75,000 names. The name "Jones" is not
in
>> the list.
>> However, if in QA:
>> "Select * from <table> where name = 'Jones' and ddate
>> = '20041010'
>> The name "Jones" is returned!
>> I can't figure this out for the life of me. It's a
table
>> with 95 million records and a clustered index based on
>> name,date.
>> Any help appreciated.
>> thx,
>> Don
>
>.
>|||In QA, I can do an Edit/Find, or I can copy the results
and paste into NotePad and search there, or I can scroll
down and see where the name should be alphabetically.
It's already running SP4.
Ran DBCC CHECKDB and no errors were returned.
There were no messages or messages pane.
Thx,
Don
>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
>> "Select name from <table> where date = '20041010'
>> I get a list of 75,000 names. The name "Jones" is not
in
>> the list.
> Just to make absolutely sure (nothing personal :) ),
how are you
>verifying that the name 'Jones' is not in the result set?
> Assuming that Jones really isn't there, I would A)
Make sure you're
>upgraded to the latest service pack for SQL Server 7.0
(SP4, IIRC), B) Run
>DBCC CHECKDB to verify that there's no data corruption,
and C) Make sure you
>look in the messages pane -- sometimes you'll get
warnings indicating why
>rows are not being returned.
>
>.
>|||Can you post the CREATE TABLE DDL for maindata as well? Also please include
the results of SELECT @.@.VERSION.
--
Anith|||>--Original Message--
>Can you post the CREATE TABLE DDL for maindata as well?
Also please include
>the results of SELECT @.@.VERSION.
>--
>Anith
>
Here you go..
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)
(1 row(s) affected)
CREATE TABLE [dbo].[MAINDATA] (
[Name] [varchar] (32) NOT NULL ,
[DDate] [smalldatetime] NOT NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[DV] [int] NOT NULL ,
[DOI] [int] NOT NULL
) ON [PRIMARY]
GO|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9a01c4b536$557551f0$a401280a@.phx.gbl...
> Ok, here are the exact queries:
> select name from maindata where ddate = '20041011'
> select * from maindata where name = 'vpipx' and ddate
> = '20041011'
What happens when you try:
select name from maindata where name = 'vpipx' and ddate
= '20041011'
Also, can you check the execution plans of the two queries and see if
they're using different indexes? If so, can you try an index hint on the
first query to force use of the other index and see if that corrects the
issue?|||> What happens when you try:
>select name from maindata where name = 'vpipx' and ddate
> = '20041011'
data returned: 'VPIPX'
> Also, can you check the execution plans of the two
queries and see if
>they're using different indexes? If so, can you try an
index hint on the
>first query to force use of the other index and see if
that corrects the
>issue?
looking at the execution plan for:
select name from maindata where ddate = '20041011'
Warning: Statistics missing for this table.
Choose 'Create Missing Statistics' from the context menu.
thx,
don
>.
>

QA returning wrong data

SQL 7.0
In QA:
"Select name from <table> where date = '20041010'
I get a list of 75,000 names. The name "Jones" is not in
the list.
However, if in QA:
"Select * from <table> where name = 'Jones' and ddate
= '20041010'
The name "Jones" is returned!
I can't figure this out for the life of me. It's a table
with 95 million records and a clustered index based on
name,date.
Any help appreciated.
thx,
Don
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
Just to make absolutely sure (nothing personal ), how are you
verifying that the name 'Jones' is not in the result set?
Assuming that Jones really isn't there, I would A) Make sure you're
upgraded to the latest service pack for SQL Server 7.0 (SP4, IIRC), B) Run
DBCC CHECKDB to verify that there's no data corruption, and C) Make sure you
look in the messages pane -- sometimes you'll get warnings indicating why
rows are not being returned.
|||Are these the exact queries you are running (copy and paste from QA)?
In one you are looking for date and in the other you are looking for ddate?
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> SQL 7.0
> In QA:
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
> However, if in QA:
> "Select * from <table> where name = 'Jones' and ddate
> = '20041010'
> The name "Jones" is returned!
> I can't figure this out for the life of me. It's a table
> with 95 million records and a clustered index based on
> name,date.
> Any help appreciated.
> thx,
> Don
>
|||Ok, here are the exact queries:
select name from maindata where ddate = '20041011'
select * from maindata where name = 'vpipx' and ddate
= '20041011'

>--Original Message--
>Are these the exact queries you are running (copy and
paste from QA)?
>In one you are looking for date and in the other you are
looking for ddate?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
in[vbcol=seagreen]
table
>
>.
>
|||In QA, I can do an Edit/Find, or I can copy the results
and paste into NotePad and search there, or I can scroll
down and see where the name should be alphabetically.
It's already running SP4.
Ran DBCC CHECKDB and no errors were returned.
There were no messages or messages pane.
Thx,
Don

>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
in
> Just to make absolutely sure (nothing personal ),
how are you
>verifying that the name 'Jones' is not in the result set?
> Assuming that Jones really isn't there, I would A)
Make sure you're
>upgraded to the latest service pack for SQL Server 7.0
(SP4, IIRC), B) Run
>DBCC CHECKDB to verify that there's no data corruption,
and C) Make sure you
>look in the messages pane -- sometimes you'll get
warnings indicating why
>rows are not being returned.
>
>.
>
|||Can you post the CREATE TABLE DDL for maindata as well? Also please include
the results of SELECT @.@.VERSION.
Anith
|||>--Original Message--
>Can you post the CREATE TABLE DDL for maindata as well?
Also please include
>the results of SELECT @.@.VERSION.
>--
>Anith
>
Here you go..
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)
(1 row(s) affected)
CREATE TABLE [dbo].[MAINDATA] (
[Name] [varchar] (32) NOT NULL ,
[DDate] [smalldatetime] NOT NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[DV] [int] NOT NULL ,
[DOI] [int] NOT NULL
) ON [PRIMARY]
GO
|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9a01c4b536$557551f0$a401280a@.phx.gbl...
> Ok, here are the exact queries:
> select name from maindata where ddate = '20041011'
> select * from maindata where name = 'vpipx' and ddate
> = '20041011'
What happens when you try:
select name from maindata where name = 'vpipx' and ddate
= '20041011'
Also, can you check the execution plans of the two queries and see if
they're using different indexes? If so, can you try an index hint on the
first query to force use of the other index and see if that corrects the
issue?
|||> What happens when you try:
>select name from maindata where name = 'vpipx' and ddate
> = '20041011'
data returned: 'VPIPX'

> Also, can you check the execution plans of the two
queries and see if
>they're using different indexes? If so, can you try an
index hint on the
>first query to force use of the other index and see if
that corrects the
>issue?
looking at the execution plan for:
select name from maindata where ddate = '20041011'
Warning: Statistics missing for this table.
Choose 'Create Missing Statistics' from the context menu.
thx,
don

>.
>

q; start and end date of previous month

I need start and end date of previous month, such as StartDate=07/01/2006 an
d
EndDate=07/31/2006 for currentdate 08/156/2006
How can I do this?The start date of the previous month is easy, use DATEADD to subtract one
month, then set the day to 1.
For the last date of the previous month, subtract the day plus 1 (e.g. 15 +
1) from the current date. That will take you to the last day of the previous
month.
RLF
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:52D53ED0-F5D6-40A0-9D25-F691A537F004@.microsoft.com...
>I need start and end date of previous month, such as StartDate=07/01/2006
>and
> EndDate=07/31/2006 for currentdate 08/156/2006
> How can I do this?
>|||select LastdayofPerviousMonth = convert(char(10),getdate() -
day(getdate()),101)
select FirstdayofPerviousMonth = convert(char(10),getdate() -
day(getdate()) - day(getdate() - day(getdate())) +1,101)
regards,
Saravanan
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:52D53ED0-F5D6-40A0-9D25-F691A537F004@.microsoft.com...
>I need start and end date of previous month, such as StartDate=07/01/2006
>and
> EndDate=07/31/2006 for currentdate 08/156/2006
> How can I do this?
>|||JIM.H. wrote:
> I need start and end date of previous month, such as StartDate=07/01/2006
and
> EndDate=07/31/2006 for currentdate 08/156/2006
> How can I do this?
>
DECLARE @.CurrentDate DATETIME
SELECT @.CurrentDate = GETDATE()
PRINT 'First day of previous month:'
PRINT DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(d, -1 * (DAY(DATEADD(d, -1 *
DAY(@.CurrentDate), @.CurrentDate)) - 1), DATEADD(d, -1 *
DAY(@.CurrentDate), @.CurrentDate))), 0)
PRINT 'Last day of previous month:'
PRINT DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(d, -1 * DAY(@.CurrentDate),
@.CurrentDate)), 0)
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Keep in mind that if any date fields contain the time, that the last day of
the month (without time) will exclude all rows entered on the last day of
the month.
For an entire months entries, it may be 'safer' to evaluate datetime fields
[>=] the first of the month (midnight) and [<] the first of the foll
owing
month (midnight).
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:52D53ED0-F5D6-40A0-9D25-F691A537F004@.microsoft.com...
>I need start and end date of previous month, such as StartDate=07/01/2006
>and
> EndDate=07/31/2006 for currentdate 08/156/2006
> How can I do this?
>

q; start and end date of previous month

I need start and end date of previous month, such as StartDate=07/01/2006 and
EndDate=07/31/2006 for currentdate 08/156/2006
How can I do this?The start date of the previous month is easy, use DATEADD to subtract one
month, then set the day to 1.
For the last date of the previous month, subtract the day plus 1 (e.g. 15 +
1) from the current date. That will take you to the last day of the previous
month.
RLF
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:52D53ED0-F5D6-40A0-9D25-F691A537F004@.microsoft.com...
>I need start and end date of previous month, such as StartDate=07/01/2006
>and
> EndDate=07/31/2006 for currentdate 08/156/2006
> How can I do this?
>|||select LastdayofPerviousMonth = convert(char(10),getdate() -
day(getdate()),101)
select FirstdayofPerviousMonth = convert(char(10),getdate() -
day(getdate()) - day(getdate() - day(getdate())) +1,101)
regards,
Saravanan
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:52D53ED0-F5D6-40A0-9D25-F691A537F004@.microsoft.com...
>I need start and end date of previous month, such as StartDate=07/01/2006
>and
> EndDate=07/31/2006 for currentdate 08/156/2006
> How can I do this?
>|||JIM.H. wrote:
> I need start and end date of previous month, such as StartDate=07/01/2006 and
> EndDate=07/31/2006 for currentdate 08/156/2006
> How can I do this?
>
DECLARE @.CurrentDate DATETIME
SELECT @.CurrentDate = GETDATE()
PRINT 'First day of previous month:'
PRINT DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(d, -1 * (DAY(DATEADD(d, -1 *
DAY(@.CurrentDate), @.CurrentDate)) - 1), DATEADD(d, -1 *
DAY(@.CurrentDate), @.CurrentDate))), 0)
PRINT 'Last day of previous month:'
PRINT DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(d, -1 * DAY(@.CurrentDate),
@.CurrentDate)), 0)
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Keep in mind that if any date fields contain the time, that the last day of
the month (without time) will exclude all rows entered on the last day of
the month.
For an entire months entries, it may be 'safer' to evaluate datetime fields
[>=] the first of the month (midnight) and [<] the first of the following
month (midnight).
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:52D53ED0-F5D6-40A0-9D25-F691A537F004@.microsoft.com...
>I need start and end date of previous month, such as StartDate=07/01/2006
>and
> EndDate=07/31/2006 for currentdate 08/156/2006
> How can I do this?
>

q; past and future date

How should I find the dates for 3 days past and 5 days future. Such as
TodayDate-3 and TodayDate+5 base in the date only, discarding the time?One method:
SELECT SomeData
FROM dbo.MyTable
WHERE
MyDate >= DATEADD(dd, -3, DATEDIFF(dd, 0, GETDATE())) AND
MyDate < DATEADD(dd, 6, DATEDIFF(dd, 0, GETDATE()))
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:0A94FA08-3A91-4F0C-9117-0721F782E7E3@.microsoft.com...
> How should I find the dates for 3 days past and 5 days future. Such as
> TodayDate-3 and TodayDate+5 base in the date only, discarding the time?

q; past and future date

How should I find the dates for 3 days past and 5 days future. Such as
TodayDate-3 and TodayDate+5 base in the date only, discarding the time?One method:
SELECT SomeData
FROM dbo.MyTable
WHERE
MyDate >= DATEADD(dd, -3, DATEDIFF(dd, 0, GETDATE())) AND
MyDate < DATEADD(dd, 6, DATEDIFF(dd, 0, GETDATE()))
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:0A94FA08-3A91-4F0C-9117-0721F782E7E3@.microsoft.com...
> How should I find the dates for 3 days past and 5 days future. Such as
> TodayDate-3 and TodayDate+5 base in the date only, discarding the time?

q; How to write this function

How can I write a function that accepts a data as a parameter and if the date
is
1. Last Monday in May or
2. First Monday in September or
3. 4th Tuesday in November
returns true otherwise returns false.The best answer, if these are particular dates known ahead of time, is
a calendar table. But brute force works too.
declare @.d datetime
set @.d = '28 Nov 2006'
SELECT CASE WHEN DATENAME(weekday, @.d) = 'Monday'
AND DATEPART(month,@.d) = 5
AND DATEPART(day,@.d) between 25 and 31
THEN 'True'
WHEN DATENAME(weekday, @.d) = 'Monday'
AND DATEPART(month,@.d) = 9
AND DATEPART(day,@.d) between 1 and 7
THEN 'True'
WHEN DATENAME(weekday, @.d) = 'Tuesday'
AND DATEPART(month,@.d) = 11
AND DATEPART(day,@.d) between 22 and 28
THEN 'True'
ELSE 'False'
END as Answer
Roy Harvey
Beacon Falls, CT
On Wed, 27 Dec 2006 04:43:00 -0800, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>How can I write a function that accepts a data as a parameter and if the date
>is
>1. Last Monday in May or
>2. First Monday in September or
>3. 4th Tuesday in November
>returns true otherwise returns false.|||JIM
Take a look at Steve Kass's function
create function dbo.NthWeekDay(
@.first datetime, -- First of the month of interest (no time part)
@.nth tinyint, -- Which of them - 1st, 2nd, etc.
@.dow tinyint -- Day of week we want
) returns datetime as begin
-- Note: Returns a date in a later month if @.nth is too large
declare @.result datetime
set @.result = @.first + 7*(@.nth-1)
return @.result + (7 + @.dow - datepart(weekday,@.result))%7
end
go
-- Find the 5th Thursday of August, 2002
select dbo.NthWeekDay('2002/08/01',5,5) as D
select datename(weekday,D) + space(1) + cast(D as varchar(20))
from (
select dbo.NthWeekDay('2002/08/01',5,5) as D
) X
go
drop function NthWeekDay
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:FED1D4FF-5F9E-414B-B115-2118EE32F4EC@.microsoft.com...
> How can I write a function that accepts a data as a parameter and if the
> date
> is
> 1. Last Monday in May or
> 2. First Monday in September or
> 3. 4th Tuesday in November
> returns true otherwise returns false.
>|||On Wed, 27 Dec 2006 07:18:00 -0800, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>Thank you very much for your help, last one should be 4th Thursday (sorry my
>mistake), how would this change the code?
Well the fourth Thursday would be a lot like the fourth Tuesday,
wouldn't it? Just copy the day range from that one.
Roy|||Wow, I've been seeing homework assignments getting posted for 20 years now.
"JIM.H." wrote:
> How can I write a function that accepts a data as a parameter and if the date
> is
> 1. Last Monday in May or
> 2. First Monday in September or
> 3. 4th Tuesday in November
> returns true otherwise returns false.
>