Query help

DECLARE @.Test TABLE (AccountNo INT, Invoicedate datetime, dex_row_id INT)

INSERT @.Test
SELECT 1180, '05/05/2006', 1 UNION ALL
SELECT 1180, '06/05/2006',2 UNION ALL
SELECT 1180, '04/05/2006',3 UNION ALL
SELECT 1180, '07/05/2006',4 UNION ALL
SELECT 1181, '09/05/2006',1 UNION ALL
SELECT 1181, '10/05/2006',2 UNION ALL
SELECT 1181, '05/05/2006',3 UNION ALL
SELECT 1182, '06/05/2006',1

-- I want a delete first month row for each accounts. If account has more then one row for same accountno and invoice date then i want a select any one and delete.
-- I wrote this but did not work because for min dex_row_id and min invoicedate combination.

--delete the firest month data for each accountno
DELETE FROM @.test WHERE LTRIM(RTRIM(CONVERT(VARCHAR,AccountNo)))+'@.'+CONVERT(VARCHAR,Invoicedate,101)+'@.'+CONVERT(VARCHAR,DEX_ROW_ID) IN
(SELECT LTRIM(RTRIM(CONVERT(VARCHAR,AccountNo)))+'@.'+CONVERT(VARCHAR,MIN(Invoicedate),101)+'@.'+CONVERT(VARCHAR,MIN(DEX_ROW_ID)) FROM @.test
GROUP BY AccountNo)

-- select statment
select * from @.test where
CONVERT(VARCHAR,AccountNo)+'@.'+CONVERT(VARCHAR,Invoicedate,101)+'@.'+CONVERT(VARCHAR,dex_row_id)in (
select CONVERT(VARCHAR,AccountNo)+'@.'+CONVERT(VARCHAR,min(Invoicedate),101)+'@.'+CONVERT(VARCHAR,min(dex_row_id))
from @.test group by AccountNo)

--selecting all record
select * from @.test

need helpDo you want to delete all except the last date in each account or only delete the earliest date in each account?|||

Dhaval:

I looked through what you requested. See if below is what you mean

Dave

DECLARE @.Test TABLE (AccountNo INT, Invoicedate datetime, dex_row_id INT)

INSERT @.Test
SELECT 1180, '05/05/2006', 1 UNION ALL
SELECT 1180, '06/05/2006',2 UNION ALL
SELECT 1180, '04/05/2006',3 UNION ALL
SELECT 1180, '07/05/2006',4 UNION ALL
SELECT 1180, '07/05/2006',5 UNION ALL
SELECT 1181, '09/05/2006',1 UNION ALL
SELECT 1181, '10/05/2006',2 UNION ALL
SELECT 1181, '05/05/2006',3 UNION ALL
SELECT 1182, '06/05/2006',1

-- I want a delete first month row for each accounts. If account has more then one row for same accountno and invoice date then i want a select any one and delete.
-- I wrote this but did not work because for min dex_row_id and min invoicedate combination.

/*
--delete the firest month data for each accountno
DELETE FROM @.test WHERE LTRIM(RTRIM(CONVERT(VARCHAR,AccountNo)))+'@.'+CONVERT(VARCHAR,Invoicedate,101)+'@.'+CONVERT(VARCHAR,DEX_ROW_ID) IN
(SELECT LTRIM(RTRIM(CONVERT(VARCHAR,AccountNo)))+'@.'+CONVERT(VARCHAR,MIN(Invoicedate),101)+'@.'+CONVERT(VARCHAR,MIN(DEX_ROW_ID)) FROM @.test
GROUP BY AccountNo)
*/

print '-- '
print '-- Records before deletions: -- '
print '--'
select * from @.test order by accountNo, invoiceDate

delete from @.test
from ( select accountNo,
min (invoiceDate) as min_invoiceDate
from @.test
group by accountNo
) x
inner join @.test a
on x.accountNo = a.accountNo
and x.min_invoiceDate = a.invoiceDate

delete from @.test
from (
select accountNo,
invoiceDate,
min (dex_row_id) min_dex_row_id,
count(*) as recCt
from @.test
group by accountNo,
invoiceDate
having count(*) > 1
) x
inner join @.test a
on x.accountNo = a.accountNo
and x.invoiceDate = a.invoiceDate
and x.min_dex_row_id <> a.dex_row_id

-- select statment
/*
select * from @.test where
CONVERT(VARCHAR,AccountNo)+'@.'+CONVERT(VARCHAR,Invoicedate,101)+'@.'+CONVERT(VARCHAR,dex_row_id)in (
select CONVERT(VARCHAR,AccountNo)+'@.'+CONVERT(VARCHAR,min(Invoicedate),101)+'@.'+CONVERT(VARCHAR,min(dex_row_id))
from @.test group by AccountNo)
*/

--selecting all record
print ' '
print ' '
print '-- '
print '-- Records after deletions: -- '
print '--'
select * from @.test

--need help

-- --
-- -- Records before deletions: --
-- --
-- AccountNo Invoicedate dex_row_id
-- -- -- --
-- 1180 2006-04-05 00:00:00.000 3
-- 1180 2006-05-05 00:00:00.000 1
-- 1180 2006-06-05 00:00:00.000 2
-- 1180 2006-07-05 00:00:00.000 4
-- 1180 2006-07-05 00:00:00.000 5
-- 1181 2006-05-05 00:00:00.000 3
-- 1181 2006-09-05 00:00:00.000 1
-- 1181 2006-10-05 00:00:00.000 2
-- 1182 2006-06-05 00:00:00.000 1

-- --
-- -- Records after deletions: --
-- --
-- AccountNo Invoicedate dex_row_id
-- -- -- --
-- 1180 2006-05-05 00:00:00.000 1
-- 1180 2006-06-05 00:00:00.000 2
-- 1180 2006-07-05 00:00:00.000 4
-- 1181 2006-09-05 00:00:00.000 1
-- 1181 2006-10-05 00:00:00.000 2

Query help

Hello Everyone,

I have a query that looks something like this:

DEFINE @.VAR_A VARCHAR(6)

DECLARE trsite_cursor CURSOR FOR
SELECT DISTINCT AppField
FROM TABLE_1
ORDER BY 1

OPEN trsite_cursor

FETCH NEXT FROM trsite_cursor INTO @.VAR_A

WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.SELCMD ='SELECT * FROM TABLE_2
WHERE Field1 =' + @.VAR_A +
' ORDER BY 1,2,3;'
END
...
...
...
EXEC xp_sendmail @.query = @.SELCMD,
...
...

and the rest of the query.....

The column "AppField" in TABLE_1 has been defined as varchar. Let's
assume it contains the value: ABCD. When I run it, the query fails at
the SET @.SELCMD statement, saying that the column name ABCD is
invalid. It assumes that ABCD is a column name & not a value. However,
if AppField contains a numeric value, ex: 123, I don't get any errors
& the query outputs the desired results.

So, I guess, the question is: how do I make the SET @.SELCMD treat the
value in AppField as "ABCD" or 'ABCD' and not just ABCD?

Thanks,
Suhassurround the variable in quotes in the string

SET @.SELCMD ='SELECT * FROM TABLE_2
WHERE Field1 = '' ' + @.VAR_A +
'' ' ORDER BY 1,2,3;'

also you need another fetch before the END in the while loop or you
loop infinitly|||"Suhas" <sgtembe@.hotmail.com> wrote in message
news:5c440ee2.0311201306.4e7f5323@.posting.google.c om...
> SET @.SELCMD ='SELECT * FROM TABLE_2
> WHERE Field1 =' + @.VAR_A +
> ' ORDER BY 1,2,3;'
> assume it contains the value: ABCD. When I run it, the query fails at
> the SET @.SELCMD statement, saying that the column name ABCD is
> invalid. It assumes that ABCD is a column name & not a value. However,

--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

You need to enclose the value contained in @.VAR_A in single quotes when you
build the @.SELCMD string. In other words, you need to create a string that
contains single quotes. To do that, you use a *pair* of single quotes. So,
the SET command should look like this:

SET @.SELCMD ='SELECT * FROM TABLE_2
WHERE Field1 = ''' + @.VAR_A +
''' ORDER BY 1,2,3;'
END

If @.VAR_A = ABCD then this set command sets @.SELCMD to:
SELECT * FROM TABLE_2 WHERE Field1 = 'ABCD' ORDER BY 1,2,3;

--BEGIN PGP SIGNATURE--
Version: GnuPG v1.0.6 (MingW32)
Comment: For info see http://www.gnupg.org

iEYEARECAAYFAj+9Pw0ACgkQFt8ABY6ZYSJ0bQCdE0eEaDLP8l 0QhwQVjnMe6DME
BY8Anjp68HxMiMxCqs9zLHWEHgTkQtGw
=agcy
--END PGP SIGNATURE--

Query Hangs on Values that start with a number

Having a strange thing happen:
Have the following query:
SET NOCOUNT ON
DECLARE @.StartDate DateTime
DECLARE @.EndDate DateTime
SET @.StartDate = DateAdd(dd,-5,GetDate())
SET @.EndDate = GetDate()
SET NOCOUNT OFF
SET ROWCOUNT 0
SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value = sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
FROM v_SummaryData sd
INNER JOIN Tag ON Tag.TagName = sd.TagName
WHERE SummaryDate >= @.StartDate
AND SummaryDate <= @.EndDate
AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
Duration= '86400' ORDER BY SummaryDate, TagName
SET ROWCOUNT 0
If running this query, it hangs for about 9 minutes before returning a
value. If we comment out the INNER JOIN statement, works in 1 second.
Now, here's the really big twist. If we add in a tagname of 'TI74' as
a third tag, query runs right away!
It appears that if there is more then one tagname that starts with a
number, it will hang. As soon as a tagname is present that starts with
a letter, it works great.
We are at a loss as to what could be causing the issue. Have tried re-
indexing the Tag table, but still no resolve.
Running SQL Server 2000 SP3On Jun 12, 2:54 am, Mini67 <wor...@.gmail.com> wrote:
> Having a strange thing happen:
> Have the following query:
> SET NOCOUNT ON
> DECLARE @.StartDate DateTime
> DECLARE @.EndDate DateTime
> SET @.StartDate = DateAdd(dd,-5,GetDate())
> SET @.EndDate = GetDate()
> SET NOCOUNT OFF
> SET ROWCOUNT 0
> SELECT SummaryDate = sd.SummaryDate, TagName = sd.TagName, Value => sd.Value, Duration = sd.Duration, EventTag = sd.EventTag
> FROM v_SummaryData sd
> INNER JOIN Tag ON Tag.TagName = sd.TagName
> WHERE SummaryDate >= @.StartDate
> AND SummaryDate <= @.EndDate
> AND sd.TagName in ('20SWD','22SWD') AND CalcType = 'AVG' AND
> Duration= '86400' ORDER BY SummaryDate, TagName
> SET ROWCOUNT 0
> If running this query, it hangs for about 9 minutes before returning a
> value. If we comment out the INNER JOIN statement, works in 1 second.
> Now, here's the really big twist. If we add in a tagname of 'TI74' as
> a third tag, query runs right away!
> It appears that if there is more then one tagname that starts with a
> number, it will hang. As soon as a tagname is present that starts with
> a letter, it works great.
> We are at a loss as to what could be causing the issue. Have tried re-
> indexing the Tag table, but still no resolve.
> Running SQL Server 2000 SP3
Comments:
1. Why you are doing SET NOCOUNT OFF , SET ROWCOUNT 0
2. Is V_SummaryData View ? If so , table involved is index on
tagname ?
3. Try changing sd.TagName in ('20SWD','22SWD') to
Tag.TagName in ('20SWD','22SWD') and check
4. In the Where clause introduce the tablename , Instead of Duration='86400'
sd.Duration= '86400'

query analyzer2

hey all,
if i declare a variable and set it is there a way i can get it to display on
the messages window or the grid?
thanks,
rodchar
The grid:
SELECT @.varname
The messages windows
PRINT @.varname
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:12D27260-BADD-4F0F-BBCB-1E1ABC2C17C4@.microsoft.com...
> hey all,
> if i declare a variable and set it is there a way i can get it to display on
> the messages window or the grid?
> thanks,
> rodchar
|||sorry bout that, i figured it out
declare @.myVar datetime
set @.myVar = getdate()
select @.myVar
thanks,
rodchar
"rodchar" wrote:

> hey all,
> if i declare a variable and set it is there a way i can get it to display on
> the messages window or the grid?
> thanks,
> rodchar
|||cool i didn't know about the print function.
"Tibor Karaszi" wrote:

> The grid:
> SELECT @.varname
> The messages windows
> PRINT @.varname
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:12D27260-BADD-4F0F-BBCB-1E1ABC2C17C4@.microsoft.com...
>
|||Describe the procedure that will let the user tell Windows to open any file with an .RPT extension with Notepad.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Start Notepad, File, Open, Files of type: all files, select the file, OK. You can also change the
file extension in QA, Tools, Options.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Glennette Adams" <gadams6822@.vccs.edu> wrote in message
news:eSfm$9X2FHA.2472@.TK2MSFTNGP12.phx.gbl...
> Describe the procedure that will let the user tell Windows to open any file with an .RPT extension
> with Notepad.
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...