November 25, 2015 at 6:46 am
Hi,
i've a table with lots of rows and i want only the rows from last quarter and current year.
i run this script and i have problem with the results that show me only the last row.
but when i run this query i return rows ok. what is the problem with my query?
--return rows
select * from [msys_monitor].[dbo].[TBL_HTML_DBA_MAINTENANCE_REPORT] where datepart(qq,RUN_TIME) = datepart(qq, getdate()) and datepart(yy,RUN_TIME) = datepart(yy, getdate()) ORDER BY RUN_TIME DESC
--return row
declare @dba_maintenance nvarchar (max)
select @dba_maintenance =
N'<p class="title">18.DBA activity (last 15 activities)' + N'</p>'
+N'<table border="1" bordercolor="#767474" cellspacing="0" cellpadding="0" width="900">
<TR>
<TH><p class="header">Date</p></TH>
<TH><p class="header">Operator</p></TH>
<TH><p class="header">Description</p></TH>
</TR>'
select @dba_maintenance = @dba_maintenance
+N'<tr class="DataPass">'
+N'<TD>' + ISNULL( + '<p class="datarows">' + CONVERT(NVARCHAR(MAX),[RUN_TIME],103),'<p class="datarowserror">Not Avail') +N'</p></TD>'
+N'<TD>' + ISNULL( + '<p class="datarows">' + CONVERT(NVARCHAR(MAX),[OPERATOR]),'<p class="datarowserror">Not Avail') +N'</p></TD>'
+N'<TD>' + ISNULL( + '<p class="datarows">' + CONVERT(NVARCHAR(MAX),[DESCRIPTION]),'<p class="datarowserror">Not Avail') ++N'</p></TD>'
+N'</TR>'
from [msys_monitor].[dbo].[TBL_HTML_DBA_MAINTENANCE_REPORT] where datepart(qq,RUN_TIME) = datepart(qq, getdate()) and datepart(yy,RUN_TIME) = datepart(yy, getdate()) ORDER BY RUN_TIME DESC
November 25, 2015 at 7:37 am
Mad-Dog (11/25/2015)
Hi,i've a table with lots of rows and i want only the rows from last quarter and current year.
i run this script and i have problem with the results that show me only the last row.
but when i run this query i return rows ok. what is the problem with my query?
--return rows
select * from [msys_monitor].[dbo].[TBL_HTML_DBA_MAINTENANCE_REPORT] where datepart(qq,RUN_TIME) = datepart(qq, getdate()) and datepart(yy,RUN_TIME) = datepart(yy, getdate()) ORDER BY RUN_TIME DESC
--return row
declare @dba_maintenance nvarchar (max)
select @dba_maintenance =
N'<p class="title">18.DBA activity (last 15 activities)' + N'</p>'
+N'<table border="1" bordercolor="#767474" cellspacing="0" cellpadding="0" width="900">
<TR>
<TH><p class="header">Date</p></TH>
<TH><p class="header">Operator</p></TH>
<TH><p class="header">Description</p></TH>
</TR>'
select @dba_maintenance = @dba_maintenance
+N'<tr class="DataPass">'
+N'<TD>' + ISNULL( + '<p class="datarows">' + CONVERT(NVARCHAR(MAX),[RUN_TIME],103),'<p class="datarowserror">Not Avail') +N'</p></TD>'
+N'<TD>' + ISNULL( + '<p class="datarows">' + CONVERT(NVARCHAR(MAX),[OPERATOR]),'<p class="datarowserror">Not Avail') +N'</p></TD>'
+N'<TD>' + ISNULL( + '<p class="datarows">' + CONVERT(NVARCHAR(MAX),[DESCRIPTION]),'<p class="datarowserror">Not Avail') ++N'</p></TD>'
+N'</TR>'
from [msys_monitor].[dbo].[TBL_HTML_DBA_MAINTENANCE_REPORT] where datepart(qq,RUN_TIME) = datepart(qq, getdate()) and datepart(yy,RUN_TIME) = datepart(yy, getdate()) ORDER BY RUN_TIME DESC
How could we know what is wrong with query? We don't know what this is supposed to do OR what it does or doesn't do correctly. Please take a few minutes and read the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 25, 2015 at 7:41 am
In the script, you're selecting into a variable. No matter how many rows there are, the variable only gets set once.
November 25, 2015 at 7:44 am
lnardozi 61862 (11/25/2015)
In the script, you're selecting into a variable. No matter how many rows there are, the variable only gets set once.
Not quite, the OP is using a concatenation trick that will keep injection each row into the variable. I use that technique myself and it works just fine.
Here is a very simplified example of what the OP is doing.
declare @TableNames varchar(max) = ''
select @TableNames = @TableNames + name + ','
from sys.tables
select @TableNames
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 25, 2015 at 9:27 am
Sean Lange (11/25/2015)
lnardozi 61862 (11/25/2015)
In the script, you're selecting into a variable. No matter how many rows there are, the variable only gets set once.Not quite, the OP is using a concatenation trick that will keep injection each row into the variable. I use that technique myself and it works just fine.
Here is a very simplified example of what the OP is doing.
declare @TableNames varchar(max) = ''
select @TableNames = @TableNames + name + ','
from sys.tables
select @TableNames
It works fine until you find out that the set is not ordered. It suffers from the same problems as the quirky update. That's why I prefer the FOR XML PATH('')[/url] option.
November 25, 2015 at 9:36 am
Luis Cazares (11/25/2015)
Sean Lange (11/25/2015)
lnardozi 61862 (11/25/2015)
In the script, you're selecting into a variable. No matter how many rows there are, the variable only gets set once.Not quite, the OP is using a concatenation trick that will keep injection each row into the variable. I use that technique myself and it works just fine.
Here is a very simplified example of what the OP is doing.
declare @TableNames varchar(max) = ''
select @TableNames = @TableNames + name + ','
from sys.tables
select @TableNames
It works fine until you find out that the set is not ordered. It suffers from the same problems as the quirky update. That's why I prefer the FOR XML PATH('')[/url] option.
Agreed the XML option is more robust but if the order in the variable doesn't matter who cares? For example if it being used as an IN list or used to find other values the order doesn't matter. I have heard about the order getting messed up in this but have never experienced it. That is probably though because I tend to only use that technique when the order makes no real difference.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 25, 2015 at 9:46 am
And by the way, you might want to change your WHERE clause to something like this to make it SARGable:
where RUN_TIME >= DATEADD( qq, DATEDIFF( qq, 0, GETDATE()), 0)
and RUN_TIME < DATEADD( qq, DATEDIFF( qq, 0, GETDATE()) + 1, 0)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply