Help for a newbie with an SQL Query

Hi,
Not sure if this is where I should post this question or not but couldn't
find anywhere else to post. Please bear with me.
I am trying to structure a simple SQL query that will return all records
whose date is less than the current system date. I've read a number of basic
tutorials, but can't seem to find how to do this. Iv'e tried using the
following statement, but get errors:
SELECT ServiceTicketEntry.* FROM ServiceTicketEntry WHERE
sysdate()>ServiceTicketEntry.Call_Date
Can anyone give me a quick answer or point me to a good resource? Thanks in
advance.
William
You are close. You can use the ANSI standard CURRENT_TIMESTAMP or the T-SQL
specific getdate() functions to get the current date and time. That would
make your query:
SELECT ServiceTicketEntry.*
FROM ServiceTicketEntry
WHERE getdate() > ServiceTicketEntry.Call_Date
You might want to look at the DATEADD function to manipulate dates.
Remember to adjust the current-date derived functions rather than the
column-based dates. In other words, calculate a date and compare a column
to it rather than calculate each column and compar it to today's date.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"William" <William@.discussions.microsoft.com> wrote in message
news:DD4BF13D-1485-4930-B461-FBA72900EE26@.microsoft.com...
> Hi,
> Not sure if this is where I should post this question or not but couldn't
> find anywhere else to post. Please bear with me.
> I am trying to structure a simple SQL query that will return all records
> whose date is less than the current system date. I've read a number of
basic
> tutorials, but can't seem to find how to do this. Iv'e tried using the
> following statement, but get errors:
> SELECT ServiceTicketEntry.* FROM ServiceTicketEntry WHERE
> sysdate()>ServiceTicketEntry.Call_Date
> Can anyone give me a quick answer or point me to a good resource? Thanks
in
> advance.
> William
sql

Help for a newbie with an SQL Query

Hi,
Not sure if this is where I should post this question or not but couldn't
find anywhere else to post. Please bear with me.
I am trying to structure a simple SQL query that will return all records
whose date is less than the current system date. I've read a number of basic
tutorials, but can't seem to find how to do this. Iv'e tried using the
following statement, but get errors:
SELECT ServiceTicketEntry.* FROM ServiceTicketEntry WHERE
sysdate()>ServiceTicketEntry.Call_Date
Can anyone give me a quick answer or point me to a good resource? Thanks in
advance.
WilliamYou are close. You can use the ANSI standard CURRENT_TIMESTAMP or the T-SQL
specific getdate() functions to get the current date and time. That would
make your query:
SELECT ServiceTicketEntry.*
FROM ServiceTicketEntry
WHERE getdate() > ServiceTicketEntry.Call_Date
You might want to look at the DATEADD function to manipulate dates.
Remember to adjust the current-date derived functions rather than the
column-based dates. In other words, calculate a date and compare a column
to it rather than calculate each column and compar it to today's date.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"William" <William@.discussions.microsoft.com> wrote in message
news:DD4BF13D-1485-4930-B461-FBA72900EE26@.microsoft.com...
> Hi,
> Not sure if this is where I should post this question or not but couldn't
> find anywhere else to post. Please bear with me.
> I am trying to structure a simple SQL query that will return all records
> whose date is less than the current system date. I've read a number of
basic
> tutorials, but can't seem to find how to do this. Iv'e tried using the
> following statement, but get errors:
> SELECT ServiceTicketEntry.* FROM ServiceTicketEntry WHERE
> sysdate()>ServiceTicketEntry.Call_Date
> Can anyone give me a quick answer or point me to a good resource? Thanks
in
> advance.
> William

Help for a newbie with an SQL Query

Hi,
Not sure if this is where I should post this question or not but couldn't
find anywhere else to post. Please bear with me.
I am trying to structure a simple SQL query that will return all records
whose date is less than the current system date. I've read a number of basi
c
tutorials, but can't seem to find how to do this. Iv'e tried using the
following statement, but get errors:
SELECT ServiceTicketEntry.* FROM ServiceTicketEntry WHERE
sysdate()>ServiceTicketEntry.Call_Date
Can anyone give me a quick answer or point me to a good resource? Thanks in
advance.
WilliamYou are close. You can use the ANSI standard CURRENT_TIMESTAMP or the T-SQL
specific getdate() functions to get the current date and time. That would
make your query:
SELECT ServiceTicketEntry.*
FROM ServiceTicketEntry
WHERE getdate() > ServiceTicketEntry.Call_Date
You might want to look at the DATEADD function to manipulate dates.
Remember to adjust the current-date derived functions rather than the
column-based dates. In other words, calculate a date and compare a column
to it rather than calculate each column and compar it to today's date.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"William" <William@.discussions.microsoft.com> wrote in message
news:DD4BF13D-1485-4930-B461-FBA72900EE26@.microsoft.com...
> Hi,
> Not sure if this is where I should post this question or not but couldn't
> find anywhere else to post. Please bear with me.
> I am trying to structure a simple SQL query that will return all records
> whose date is less than the current system date. I've read a number of
basic
> tutorials, but can't seem to find how to do this. Iv'e tried using the
> following statement, but get errors:
> SELECT ServiceTicketEntry.* FROM ServiceTicketEntry WHERE
> sysdate()>ServiceTicketEntry.Call_Date
> Can anyone give me a quick answer or point me to a good resource? Thanks
in
> advance.
> William

HELP With THIS MESSAGE

This is what we are getting on the server but I cannot seem to find anything anywhere and i am not real strong in SQL...I am a server guy that is tasked with finding the issue!

Scott H
Weasel101

Event Type: Error
Event Source: MSSQLServer
Event Category: Server
Event ID: 17052
Date: 3/12/2002
Time: 2:45:06 PM
User: ********** (Changed for privacy...sorry)
Computer: MYCOMPUTER (Changed for privacy...sorry)
Description:
Error: 823, Severity: 24, State: 1
I/O error (bad page ID) detected during read of BUF pointer = 0x1431e6c0, page ptr = 0x498f6000, pageid = (0x1:0x403), dbid = 4, status = 0x801, file = f:\MSSQL7\DATA\msdbdata.mdf.
Data:
0000: 37 03 00 00 18 00 00 00 7......
0008: 08 00 00 00 4e 00 54 00 ...N.T.
0010: 30 00 30 00 33 00 32 00 0.0.3.2.
0018: 4b 00 00 00 05 00 00 00 K......
0020: 6d 00 73 00 64 00 62 00 m.s.d.b.
0028: 00 00 ..Do you know if this happened during a restore ?|||There looks to be a Technet article on this problem:

BUG: Error Message 823 or Retail Assert Encountered During Restore of SQL Server Log Backup (Q281809) (http://support.microsoft.com/default.aspx?scid=kb;EN-US;q281809)|||CAUSE
SQL Server may allow the log to be backed up when non-logged operations have taken place.

WORKAROUND
To workaround this problem either:

Avoid the use of non-logged operations.

-or-

Ensure that any process performing non-logged operations complete and secure a complete database backup

HELP - I NEED EXTRA MATRIX COLUMNS

I need to be able to add an extra column to my matrix - I've searched high and low on the net and I cant seem to find the answer anywhere.

I have grouped data which displays as it should and I can get the SubTotal part for my Dailys to run - I need to add a total for MTD and YTD (which is a part of the SQL Data) after the subtotal - is there a straight forward way of doing this?

This is an example of how I want my report to look (I can already achieve the bits highlighted bold)

Daily Data Commission (GroupData cell 1) Interest (GroupData CELL 2) GroupData etc … SUBTOTAL (DTD) MTD TOTAL YTD TOTAL Book Currency =sum(Fields!DTD.VALUE) ........ =sum(Fields!MTD.VALUE) =sum(Fields!YTD.VALUE)

and this is an example of the data being returned by my Sproc

Book CCY GroupedData DTD MTD YTD ABC GBP Commission ￡0.01 ￡0.09 -￡0.10 ABC GBP Interest ￡0.02 ￡0.29 ￡0.11 ABC GBP Brokerage ￡0.12 ￡0.06 ￡0.20

When I use the "Add Column" functionality - I get a repeat of the MTD and YTD under each of the groupData cells - where as i only need it as a summary after the subtotal.

Daily Data Grouped Data CELL 1 Grouped Data CELL 2 Book Name Currency DTD TOTAL MTD TOTAL YTD TOTAL DTD TOTAL MTD TOTAL YTD TOTAL =sum(Fields!DTD.VALUE)

Does anyone out there know how to do it?

This SUCKS.... I cant believe no one knows this and there are no Microsoft developers monitoring this thread to answer the questions.

|||

Hi there,

Did you try doing subtotals and totals within SQL Server instead of in the matrix? This should get the extra columns you would need.

Alternatively you could create a matrix next to the matrix that may work.

Or see this posting.

http://www.sqlskills.com/blogs/liz/2006/07/21/ReportingServicesGettingTheMatrixToDisplayTwoSubtotalsForTheSameGroup.aspx

cheers,

Andrew

|||

Thanks Andrew -

For anyone who is reading this thread that isn't sure where the Inscope expression goes (like me) it is in the

"Data" bit of the matrix (where you have your =Sum(field!...) bit)

The only thing now is - how do I sort the positioning out. My design layout looks like below - I have a DTD BreakDown and Total (I have used the <position> element in the XML code behind to add "DTD Total" position to the front) - I can get the MTD to be on the same level as the DTD Parent Cell as needed - however The YTD subtotal is one level higher (I tried messing about with alignment - which sorts the problem temp - but on expanding the DTD breakdown the alignment doesnt work)

How can I make the YTD (Text) appear on the same level as DTD and MTD?

YTD DTD MTD Fields!DTDBreakDown DTD Total Field!Book.Value Field!CCY.Value iif(InScope("YTD"),
iif(InScope("MTD"),
Sum(Fields!AmountCCY_DTD.Value),
sum(Fields!AmountCCY_MTD.Value)),
sum(Fields!AmountCCY_YTD.Value))

e.g of how my report looks - I need the YTD to be on the same level as DTD and MTD

YTD (+)DTD MTD DTD Total Book 1 CCY 0 0 0

|||This is not possible in the Matrix report. You need to return you column from the SP or TSQL that you are using and instead of using Matrix create this report from using table report.|||

Are you sure? According to the example given in the URL on Andrew's Post(http://www.sqlskills.com/blogs/liz/2006/07/21/ReportingServicesGettingTheMatrixToDisplayTwoSubtotalsForTheSameGroup.aspx) says you can. However, its just doesn't explain how.

I need to use a matrix as the DTD breakdown is dynamic - and I'm almost there, the problem I have is cosmetic.

|||If you want to create the report as given in above URL then you can do it. Are you want to do like that ? and steps are clearly added there. Any other problem in that then let me know.