Nested query

Hey guys,
I have this query i can't seem to get to work just write. Can anyone
help me out here?
SELECT Table1.*
FROM Table1 INNER JOIN (SELECT * FROM Table2)
ON (Table1.ProvID = Table2.ProvID) AND
(Table1.VerifDate = Table2.VerifDate)
WHERE Table1.Type Like 'A%'
-Scott
When you use a query as a dynamic table like this, you have to provide an
alias for it:
SELECT Table1.*
FROM Table1
INNER JOIN (SELECT * FROM Table2) As Table2 ON (Table1.ProvID =
Table2.ProvID) AND (Table1.VerifDate = Table2.VerifDate)
WHERE Table1.Type Like 'A%'
However, I suggest just doing a straight inner join on the tables:
SELECT Table1.*
FROM Table1
INNER JOIN Table2 ON Table1.ProvID = Table2.ProvID AND Table1.VerifDate =
Table2.VerifDate
WHERE Table1.Type Like 'A%'
"Scott Elgram" wrote:

> Hey guys,
> I have this query i can't seem to get to work just write. Can anyone
> help me out here?
> SELECT Table1.*
> FROM Table1 INNER JOIN (SELECT * FROM Table2)
> ON (Table1.ProvID = Table2.ProvID) AND
> (Table1.VerifDate = Table2.VerifDate)
> WHERE Table1.Type Like 'A%'
> --
> -Scott
>
>

Nested query

Hey guys,
I have this query i can't seem to get to work just write. Can anyone
help me out here?
SELECT Table1.*
FROM Table1 INNER JOIN (SELECT * FROM Table2)
ON (Table1.ProvID = Table2.ProvID) AND
(Table1.VerifDate = Table2.VerifDate)
WHERE Table1.Type Like 'A%'
-ScottWhen you use a query as a dynamic table like this, you have to provide an
alias for it:
SELECT Table1.*
FROM Table1
INNER JOIN (SELECT * FROM Table2) As Table2 ON (Table1.ProvID =
Table2.ProvID) AND (Table1.VerifDate = Table2.VerifDate)
WHERE Table1.Type Like 'A%'
However, I suggest just doing a straight inner join on the tables:
SELECT Table1.*
FROM Table1
INNER JOIN Table2 ON Table1.ProvID = Table2.ProvID AND Table1.VerifDate =
Table2.VerifDate
WHERE Table1.Type Like 'A%'
"Scott Elgram" wrote:

> Hey guys,
> I have this query i can't seem to get to work just write. Can anyone
> help me out here?
> SELECT Table1.*
> FROM Table1 INNER JOIN (SELECT * FROM Table2)
> ON (Table1.ProvID = Table2.ProvID) AND
> (Table1.VerifDate = Table2.VerifDate)
> WHERE Table1.Type Like 'A%'
> --
> -Scott
>
>

Nested query

Hey guys,
I have this query i can't seem to get to work just write. Can anyone
help me out here?
SELECT Table1.*
FROM Table1 INNER JOIN (SELECT * FROM Table2)
ON (Table1.ProvID = Table2.ProvID) AND
(Table1.VerifDate = Table2.VerifDate)
WHERE Table1.Type Like 'A%'
--
-ScottWhen you use a query as a dynamic table like this, you have to provide an
alias for it:
SELECT Table1.*
FROM Table1
INNER JOIN (SELECT * FROM Table2) As Table2 ON (Table1.ProvID =Table2.ProvID) AND (Table1.VerifDate = Table2.VerifDate)
WHERE Table1.Type Like 'A%'
However, I suggest just doing a straight inner join on the tables:
SELECT Table1.*
FROM Table1
INNER JOIN Table2 ON Table1.ProvID = Table2.ProvID AND Table1.VerifDate =Table2.VerifDate
WHERE Table1.Type Like 'A%'
"Scott Elgram" wrote:
> Hey guys,
> I have this query i can't seem to get to work just write. Can anyone
> help me out here?
> SELECT Table1.*
> FROM Table1 INNER JOIN (SELECT * FROM Table2)
> ON (Table1.ProvID = Table2.ProvID) AND
> (Table1.VerifDate = Table2.VerifDate)
> WHERE Table1.Type Like 'A%'
> --
> -Scott
>
>

nested loops join

Hi,
I have a select statement that gets data from only one table.
When I write OPTION(LOOP JOIN) after this query and run it, the
execution time is 2-3 times faster than without OPTION(LOOP JOIN).
If I use OPTION(FAST 1) the execution time is as fast as with OPTION(LOOP
JOIN)
Does anyone know why its faster with nested loops join even though I don't
join any tables?
Thanks!
//MalinDid you look at the actual execution plan to see what it is doing in both
cases?
Andrew J. Kelly SQL MVP
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:uwxC2gaRFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a select statement that gets data from only one table.
> When I write OPTION(LOOP JOIN) after this query and run it, the
> execution time is 2-3 times faster than without OPTION(LOOP JOIN).
> If I use OPTION(FAST 1) the execution time is as fast as with OPTION(LOOP
> JOIN)
> Does anyone know why its faster with nested loops join even though I don't
> join any tables?
> Thanks!
> //Malin
>|||The graphical execution plans are identical. (select <-- Clustered index
s)
If I have set showplan_text on there is a difference.
select col1, col2, col3
from table1
where col1=1234
option(loop join)
|--Clustered Index S(OBJECT:([DB].[dbo].[table1].[PK_tIndex]),
SEEK:([table1].[col1]=Convert([@.1])) ORDERED FORWARD)
select col1, col2, col3
from table1
where col1=1234
|--Clustered Index S(OBJECT:([DB].[dbo].[table1].[PK_tIndex]),
SEEK:([table1].[col1]=1234) ORDERED FORWARD)
Does "Convert([@.1])" have something to do with the execution time of the
query?
Thanks for helping.
// Malin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eSqTusaRFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Did you look at the actual execution plan to see what it is doing in both
> cases?
> --
> Andrew J. Kelly SQL MVP
>
> "Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
> news:uwxC2gaRFHA.1500@.TK2MSFTNGP09.phx.gbl...
>|||for instance, ...perhaps you have a couple of search arguments ANDed and SQL
Server can join these
by two indexes (aka index intersection) and this is the join which is influe
nced by your hint.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eSqTusaRFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Did you look at the actual execution plan to see what it is doing in both
cases?
> --
> Andrew J. Kelly SQL MVP
>
> "Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
> news:uwxC2gaRFHA.1500@.TK2MSFTNGP09.phx.gbl...
>|||Hej :-),
I only have one argument in the where statement.
My query looks like "select col1, col2, col3 from table1 where col1=1234"
(as you probably already have seen in my previous message)
That's why I wonder where the "join" is?
// Malin
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23LGPO7aRFHA.3928@.TK2MSFTNGP09.phx.gbl...
> for instance, ...perhaps you have a couple of search arguments ANDed and
> SQL Server can join these by two indexes (aka index intersection) and this
> is the join which is influenced by your hint.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eSqTusaRFHA.2604@.TK2MSFTNGP10.phx.gbl...
>|||Hej. :-)
Strange... Did you look at the execution plan?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:%23pJpeDbRFHA.3444@.tk2msftngp13.phx.gbl...
> Hej :-),
> I only have one argument in the where statement.
> My query looks like "select col1, col2, col3 from table1 where col1=1234"
(as you probably
> already have seen in my previous message)
> That's why I wonder where the "join" is?
> // Malin
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23LGPO7aRFHA.3928@.TK2MSFTNGP09.phx.gbl...
>|||The graphical execution plans are identical. (select <-- Clustered index
s)
If I have set showplan_text on there is a difference.
select col1, col2, col3
from table1
where col1=1234
option(loop join)
|--Clustered Index S(OBJECT:([DB].[dbo].[table1].[PK_tIndex]),
SEEK:([table1].[col1]=Convert([@.1])) ORDERED FORWARD)
select col1, col2, col3
from table1
where col1=1234
|--Clustered Index S(OBJECT:([DB].[dbo].[table1].[PK_tIndex]),
SEEK:([table1].[col1]=1234) ORDERED FORWARD)
Does "Convert([@.1])" have something to do with the execution time of the
query?
Anything more I can do to find out what this can depend on?
// Malin
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eVHTpHbRFHA.3076@.tk2msftngp13.phx.gbl...
> Hej. :-)
> Strange... Did you look at the execution plan?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
> news:%23pJpeDbRFHA.3444@.tk2msftngp13.phx.gbl...
>|||In this example you have the value for Col1 as an integer. In the real
table is the datatype for Col1 an Integer?
Andrew J. Kelly SQL MVP
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:e0XLO5aRFHA.1172@.TK2MSFTNGP12.phx.gbl...
> The graphical execution plans are identical. (select <-- Clustered index
> s)
> If I have set showplan_text on there is a difference.
> select col1, col2, col3
> from table1
> where col1=1234
> option(loop join)
> |--Clustered Index S(OBJECT:([DB].[dbo].[table1].[PK_tIndex]),
> SEEK:([table1].[col1]=Convert([@.1])) ORDERED FORWARD)
>
> select col1, col2, col3
> from table1
> where col1=1234
> |--Clustered Index S(OBJECT:([DB].[dbo].[table1].[PK_tIndex]),
> SEEK:([table1].[col1]=1234) ORDERED FORWARD)
> Does "Convert([@.1])" have something to do with the execution time of the
> query?
> Thanks for helping.
> // Malin
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eSqTusaRFHA.2604@.TK2MSFTNGP10.phx.gbl...
>|||yes "col1" is an integer in the real table, the query looks exactly as I
have written except the names :-)
//Malin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LZ9yTbRFHA.904@.tk2msftngp13.phx.gbl...
> In this example you have the value for Col1 as an integer. In the real
> table is the datatype for Col1 an Integer?
> --
> Andrew J. Kelly SQL MVP
>
> "Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
> news:e0XLO5aRFHA.1172@.TK2MSFTNGP12.phx.gbl...
>|||I will post to the internal group and see if anyone has seen this before.
Andrew J. Kelly SQL MVP
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:uvQUHabRFHA.3076@.TK2MSFTNGP14.phx.gbl...
> yes "col1" is an integer in the real table, the query looks exactly as I
> have written except the names :-)
> //Malin
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23LZ9yTbRFHA.904@.tk2msftngp13.phx.gbl...
>

negative values...

calculating profit... how do I write an update query that will correct the gross profit calculated column for all negative qty transactions

Basically in the stored procedure that creates the table the query includes:

Profit = CASE Sale WHEN 0 THEN 0 ELSE (Sale - Cost) END,
which is wrong when sale and cost is negative the formula becomes
(-Sale + Cost)... I want it to be -(Sale-Cost) (where sale any cost ignores negative sign....

but i dont know to write this...any ideas?which is wrong when sale and cost is negative the formula becomes
(-Sale + Cost)... I want it to be -(Sale-Cost) (where sale any cost ignores negative sign....

How could 'Cost' come negative..?|||How could 'Cost' come negative..?when the supplier pays you to take his product

next question: how can sale be 0?

answer: when you give your product away|||Sounds like an absolute value to me.

sale - abs(cost)|||calculating profit... how do I write an update query that will correct the gross profit calculated column for all negative qty transactions

Basically in the stored procedure that creates the table the query includes:

Profit = CASE Sale WHEN 0 THEN 0 ELSE (Sale - Cost) END,
which is wrong when sale and cost is negative the formula becomes
(-Sale + Cost)... I want it to be -(Sale-Cost) (where sale any cost ignores negative sign....

but i dont know to write this...any ideas?

Why would it be wrong? Sounds like simple accounting

My wife say I have a lot of negative values|||I worked my way around it... the profit was calculating correct, it was the Profit% that was wrong...soz:

UPDATE SALES
SET [Profit%] = [Profit%] * -1
FROM SALES
WHERE Qty < 0

So that when the profit% is negative when qty is negative... thanks :)|||So that when the profit% is negative when qty is negative... thanks :)you're welcome :)

you sell negative quantities?|||Those are called Returns!|||Those are called Returns!

yup :) :beer:|||Our sales monkeys are good at generating negative GP, too! :D

Needs Another help

hello,
i wanted to know to write bello with out subqueries to increase my
application performance
CREATE TABLE COURSE (
COURSESLNO INT,
COURSENAME VARCHAR)
INSERT INTO COURSE VALUES (1,'JR');
INSERT INTO COURSE VALUES (2,'SR');
INSERT INTO COURSE VALUES (3,'LT');
INSERT INTO COURSE VALUES (4,'ST');
CREATE TABLE BRANCH_COURSE(
BRANCHNAME VARCHAR,
COURSESLNO INT)
INSERT INTO BRANCH_COURSE VALUES ('BR1', 1);
INSERT INTO BRANCH_COURSE VALUES ('BR1', 2);
INSERT INTO BRANCH_COURSE VALUES ('BR1', 3);
INSERT INTO BRANCH_COURSE VALUES ('BR1', 4);
INSERT INTO BRANCH_COURSE VALUES ('BR2', 1);
INSERT INTO BRANCH_COURSE VALUES ('BR2', 3);
INSERT INTO BRANCH_COURSE VALUES ('BR3', 2);
INSERT INTO BRANCH_COURSE VALUES ('BR3', 3);
INSERT INTO BRANCH_COURSE VALUES ('BR4', 4);
and i want
BRANCHNAME COURSESLNO COURSENAME
BR1 1 JR
BR1 2 SR
BR1 3 LT
BR1 4 ST
BR2 1 JR
BR2 NULL NULL
BR2 3 LT
BR2 NULL NULL
BR3 NULL NULL
BR3 2 SR
BR3 3 LT
BR3 NULL NULL
BR4 NULL NULL
BR4 NULL NULL
BR4 NULL NULL
BR4 4 ST
thx a lot
*** Sent via Developersdex http://www.examnotes.net ***I assume that you also have a table for branches?
create table branches(branchname varchar(10) not null primary key);
insert into branches(branchname) values('br1');
insert into branches(branchname) values('br2');
insert into branches(branchname) values('br3');
insert into branches(branchname) values('br4');
If so, use:
select b.branchname, bc.courseslno,
case when bc.courseslno is null then null else c.coursename end as
coursename
from course as c
cross join branches as b
left outer join branch_course as bc
on bc.courseslno = c.courseslno
and bc.branchname = b.branchname
order by b.branchname, c.courseslno;
Otherwise simply cross with a distinct list of branches from branch_course.
BG, SQL Server MVP
www.SolidQualityLearning.com
"kamal hussain" <skkamalh@.yahoo.co.in> wrote in message
news:e2cTLu1lFHA.3144@.TK2MSFTNGP12.phx.gbl...
> hello,
> i wanted to know to write bello with out subqueries to increase my
> application performance
>
> CREATE TABLE COURSE (
> COURSESLNO INT,
> COURSENAME VARCHAR)
>
> INSERT INTO COURSE VALUES (1,'JR');
> INSERT INTO COURSE VALUES (2,'SR');
> INSERT INTO COURSE VALUES (3,'LT');
> INSERT INTO COURSE VALUES (4,'ST');
>
> CREATE TABLE BRANCH_COURSE(
> BRANCHNAME VARCHAR,
> COURSESLNO INT)
>
> INSERT INTO BRANCH_COURSE VALUES ('BR1', 1);
> INSERT INTO BRANCH_COURSE VALUES ('BR1', 2);
> INSERT INTO BRANCH_COURSE VALUES ('BR1', 3);
> INSERT INTO BRANCH_COURSE VALUES ('BR1', 4);
> INSERT INTO BRANCH_COURSE VALUES ('BR2', 1);
> INSERT INTO BRANCH_COURSE VALUES ('BR2', 3);
> INSERT INTO BRANCH_COURSE VALUES ('BR3', 2);
> INSERT INTO BRANCH_COURSE VALUES ('BR3', 3);
> INSERT INTO BRANCH_COURSE VALUES ('BR4', 4);
>
> and i want
> BRANCHNAME COURSESLNO COURSENAME
> BR1 1 JR
> BR1 2 SR
> BR1 3 LT
> BR1 4 ST
> BR2 1 JR
> BR2 NULL NULL
> BR2 3 LT
> BR2 NULL NULL
> BR3 NULL NULL
> BR3 2 SR
> BR3 3 LT
> BR3 NULL NULL
> BR4 NULL NULL
> BR4 NULL NULL
> BR4 NULL NULL
> BR4 4 ST
>
> thx a lot
>
> *** Sent via Developersdex http://www.examnotes.net ***|||try this query
SELECT T.BRANCHNAME, BC.COURSESLNO, CASE WHEN BC.COURSESLNO IS NULL THEN
NULL ELSE C.COURSENAME END FROM
(
SELECT DISTINCT BRANCHNAME
FROM BRANCH_COURSE
) T CROSS JOIN COURSE C
LEFT OUTER JOIN BRANCH_COURSE BC ON C.COURSESLNO = BC.COURSESLNO AND
T.BRANCHNAME = BC.BRANCHNAME
ORDER BY T.BRANCHNAME
ph
"kamal hussain" wrote:

> hello,
> i wanted to know to write bello with out subqueries to increase my
> application performance
>
> CREATE TABLE COURSE (
> COURSESLNO INT,
> COURSENAME VARCHAR)
>
> INSERT INTO COURSE VALUES (1,'JR');
> INSERT INTO COURSE VALUES (2,'SR');
> INSERT INTO COURSE VALUES (3,'LT');
> INSERT INTO COURSE VALUES (4,'ST');
>
> CREATE TABLE BRANCH_COURSE(
> BRANCHNAME VARCHAR,
> COURSESLNO INT)
>
> INSERT INTO BRANCH_COURSE VALUES ('BR1', 1);
> INSERT INTO BRANCH_COURSE VALUES ('BR1', 2);
> INSERT INTO BRANCH_COURSE VALUES ('BR1', 3);
> INSERT INTO BRANCH_COURSE VALUES ('BR1', 4);
> INSERT INTO BRANCH_COURSE VALUES ('BR2', 1);
> INSERT INTO BRANCH_COURSE VALUES ('BR2', 3);
> INSERT INTO BRANCH_COURSE VALUES ('BR3', 2);
> INSERT INTO BRANCH_COURSE VALUES ('BR3', 3);
> INSERT INTO BRANCH_COURSE VALUES ('BR4', 4);
>
> and i want
> BRANCHNAME COURSESLNO COURSENAME
> BR1 1 JR
> BR1 2 SR
> BR1 3 LT
> BR1 4 ST
> BR2 1 JR
> BR2 NULL NULL
> BR2 3 LT
> BR2 NULL NULL
> BR3 NULL NULL
> BR3 2 SR
> BR3 3 LT
> BR3 NULL NULL
> BR4 NULL NULL
> BR4 NULL NULL
> BR4 NULL NULL
> BR4 4 ST
>
> thx a lot
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Hi
here is the query that u can use:
SELECT BRANCHNAME, CASE WHEN result = 0 THEN NULL ELSE COURSESLNO END,
CASE WHEN result = 0 THEN NULL ELSE COURSENAME END
FROM
(
SELECT TOP 100 PERCENT B.BranchName, C.COURSESLNO , C.COURSENAME,
SUM(CASE WHEN C.COURSESLNO=B.COURSESLNO THEN B.COURSESLNO ELSE 0 END) result
FROM BRANCH_COURSE B
CROSS JOIN COURSE C
LEFT OUTER JOIN COURSE C1 ON B.COURSESLNO = C.COURSESLNO
GROUP BY B.BranchName, C.COURSESLNO , C.COURSENAME
ORDER BY B.BranchName
)Der
Please let me know if this worked.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"kamal hussain" wrote:

> hello,
> i wanted to know to write bello with out subqueries to increase my
> application performance
>
> CREATE TABLE COURSE (
> COURSESLNO INT,
> COURSENAME VARCHAR)
>
> INSERT INTO COURSE VALUES (1,'JR');
> INSERT INTO COURSE VALUES (2,'SR');
> INSERT INTO COURSE VALUES (3,'LT');
> INSERT INTO COURSE VALUES (4,'ST');
>
> CREATE TABLE BRANCH_COURSE(
> BRANCHNAME VARCHAR,
> COURSESLNO INT)
>
> INSERT INTO BRANCH_COURSE VALUES ('BR1', 1);
> INSERT INTO BRANCH_COURSE VALUES ('BR1', 2);
> INSERT INTO BRANCH_COURSE VALUES ('BR1', 3);
> INSERT INTO BRANCH_COURSE VALUES ('BR1', 4);
> INSERT INTO BRANCH_COURSE VALUES ('BR2', 1);
> INSERT INTO BRANCH_COURSE VALUES ('BR2', 3);
> INSERT INTO BRANCH_COURSE VALUES ('BR3', 2);
> INSERT INTO BRANCH_COURSE VALUES ('BR3', 3);
> INSERT INTO BRANCH_COURSE VALUES ('BR4', 4);
>
> and i want
> BRANCHNAME COURSESLNO COURSENAME
> BR1 1 JR
> BR1 2 SR
> BR1 3 LT
> BR1 4 ST
> BR2 1 JR
> BR2 NULL NULL
> BR2 3 LT
> BR2 NULL NULL
> BR3 NULL NULL
> BR3 2 SR
> BR3 3 LT
> BR3 NULL NULL
> BR4 NULL NULL
> BR4 NULL NULL
> BR4 NULL NULL
> BR4 4 ST
>
> thx a lot
>
> *** Sent via Developersdex http://www.examnotes.net ***
>

Needed Help

I need to write a query which should calculate the Month and year part of
the Date that is stored in the database. Please note that the query also has
a group by column. Here Date is stored in table c. Please note that the quer
y
works fine and able able to retrieve the Month part. What am interested is
"Jun 2006" when the data is "2006-03-18 00:00:00.000" and not the number 6,
which I am getting now.
Apprecite your help.
Query is given below.
SELECT Month(c.Date) as [Month], a.Col1 AS [ABC], COUNT(*) AS [XYZ]
FROM Table a INNER JOIN
table b ON a.Col3 = b.Col1 INNER JOIN
table c ON b.Col1 = c.Col2
WHERE c.Date BETWEEN '01/01/2005' AND '08/08/2006'
AND (a.Col5 = 'ADM02256')
GROUP BY a.Col1, MONTH(c.Date)Hi Scott
SELECT dataname(month,c.Date) as [Month], a.Col1 AS [ABC], COUNT(*) AS [XYZ]
FROM Table a INNER JOIN
table b ON a.Col3 = b.Col1 INNER JOIN
table c ON b.Col1 = c.Col2
WHERE c.Date BETWEEN '01/01/2005' AND '08/08/2006'
AND (a.Col5 = 'ADM02256')
GROUP BY a.Col1, dataname(month,c.Date)
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"Scott" <scott@.gmail.com> wrote in message
news:6650D53F-2B3C-4C96-8F59-1CDDD1D8BF72@.microsoft.com...
> I need to write a query which should calculate the Month and year part of
> the Date that is stored in the database. Please note that the query also
> has
> a group by column. Here Date is stored in table c. Please note that the
> query
> works fine and able able to retrieve the Month part. What am interested is
> "Jun 2006" when the data is "2006-03-18 00:00:00.000" and not the number
> 6,
> which I am getting now.
> Apprecite your help.
> Query is given below.
> SELECT Month(c.Date) as [Month], a.Col1 AS [ABC], COUNT(*) AS [XYZ]
> FROM Table a INNER JOIN
> table b ON a.Col3 = b.Col1 INNER JOIN
> table c ON b.Col1 = c.Col2
> WHERE c.Date BETWEEN '01/01/2005' AND '08/08/2006'
> AND (a.Col5 = 'ADM02256')
> GROUP BY a.Col1, MONTH(c.Date)|||> GROUP BY a.Col1, dataname(month,c.Date)
oops!
Jan2005 and Jan2006 are same as dataname(month,c.Date) results same january
for both if col1 is same.
You need derived table to group by c.date
Regards
R.D
"GregO" wrote:

> Hi Scott
> SELECT dataname(month,c.Date) as [Month], a.Col1 AS [ABC], COUNT(*) AS [XYZ]
> FROM Table a INNER JOIN
> table b ON a.Col3 = b.Col1 INNER JOIN
> table c ON b.Col1 = c.Col2
> WHERE c.Date BETWEEN '01/01/2005' AND '08/08/2006'
> AND (a.Col5 = 'ADM02256')
> GROUP BY a.Col1, dataname(month,c.Date)
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the first and still the best AGS SQL
> Scribe
> http://www.ag-software.com
> "Scott" <scott@.gmail.com> wrote in message
> news:6650D53F-2B3C-4C96-8F59-1CDDD1D8BF72@.microsoft.com...
>
>|||Hi R.D.
Spot on mate I was a bit quick on that./ He was after Jan 2006 not just Jan
SELECT dataname(month,c.Date) + ' ' + convert(varchar(4),year(c.Date)) as
[MonthYear], a.Col1 AS [ABC], COUNT(*) AS [XYZ]
FROM Table a INNER JOIN
table b ON a.Col3 = b.Col1 INNER JOIN
table c ON b.Col1 = c.Col2
WHERE c.Date BETWEEN '01/01/2005' AND '08/08/2006'
AND (a.Col5 = 'ADM02256')
GROUP BY a.Col1, dataname(month,c.Date) + ' ' +
convert(varchar(4),year(c.Date))
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:8C001DFB-BBA7-42E7-83C0-25809EDB7926@.microsoft.com...
> oops!
> Jan2005 and Jan2006 are same as dataname(month,c.Date) results same
> january
> for both if col1 is same.
> You need derived table to group by c.date
> Regards
> R.D
>
> "GregO" wrote:
>|||GregO
what abt feb to august between 2005 and 2006. I did not mean jan. It was an
instance.
Regards
R.D
"GregO" wrote:

> Hi R.D.
> Spot on mate I was a bit quick on that./ He was after Jan 2006 not just J
an
> SELECT dataname(month,c.Date) + ' ' + convert(varchar(4),year(c.Date)) as
> [MonthYear], a.Col1 AS [ABC], COUNT(*) AS [XYZ]
> FROM Table a INNER JOIN
> table b ON a.Col3 = b.Col1 INNER JOIN
> table c ON b.Col1 = c.Col2
> WHERE c.Date BETWEEN '01/01/2005' AND '08/08/2006'
> AND (a.Col5 = 'ADM02256')
> GROUP BY a.Col1, dataname(month,c.Date) + ' ' +
> convert(varchar(4),year(c.Date))
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the first and still the best AGS SQL
> Scribe
> http://www.ag-software.com
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:8C001DFB-BBA7-42E7-83C0-25809EDB7926@.microsoft.com...
>
>|||Hi R.D.
I must not be understanding you correctly. The query belwo will result in
group by Maonthname + Year so the months will not be the same. Hard to show
without the data but say the .Date col have
2005-01-01
2005-02-02
2006-01-01
2006-02-02
the results would be
January 2005
Febuary 2005
January 2006
Febuary 2006
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:1DE97360-0BD7-46B7-9863-3FB853A0F66E@.microsoft.com...
> GregO
> what abt feb to august between 2005 and 2006. I did not mean jan. It was
> an
> instance.
> Regards
> R.D
> "GregO" wrote:
>|||Good
"GregO" wrote:

> Hi R.D.
> I must not be understanding you correctly. The query belwo will result in
> group by Maonthname + Year so the months will not be the same. Hard to sh
ow
> without the data but say the .Date col have
> 2005-01-01
> 2005-02-02
> 2006-01-01
> 2006-02-02
> the results would be
> January 2005
> Febuary 2005
> January 2006
> Febuary 2006
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the first and still the best AGS SQL
> Scribe
> http://www.ag-software.com
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:1DE97360-0BD7-46B7-9863-3FB853A0F66E@.microsoft.com...
>
>|||Hi Chandra
Yes you can
declare @.myvar nvarchar(10)
declare @.myint bigint
select @.myvar = '9584'
select @.myint = cast(@.myvar as bigint)
print @.myint
--Result
9584
Regards
R.D
"GregO" wrote:

> Hi R.D.
> Spot on mate I was a bit quick on that./ He was after Jan 2006 not just J
an
> SELECT dataname(month,c.Date) + ' ' + convert(varchar(4),year(c.Date)) as
> [MonthYear], a.Col1 AS [ABC], COUNT(*) AS [XYZ]
> FROM Table a INNER JOIN
> table b ON a.Col3 = b.Col1 INNER JOIN
> table c ON b.Col1 = c.Col2
> WHERE c.Date BETWEEN '01/01/2005' AND '08/08/2006'
> AND (a.Col5 = 'ADM02256')
> GROUP BY a.Col1, dataname(month,c.Date) + ' ' +
> convert(varchar(4),year(c.Date))
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the first and still the best AGS SQL
> Scribe
> http://www.ag-software.com
> "R.D" <RD@.discussions.microsoft.com> wrote in message
> news:8C001DFB-BBA7-42E7-83C0-25809EDB7926@.microsoft.com...
>
>

Need to write value of session variable to SQL record

Each user who inserts a new SQL record from the FormView control needs to have their UserID in one of the fields of the record. I have the user ID stored in the Session("UserID") variable. I am having trouble finding the right way to get this done. I have tried using a hidden text box but I can't seem to assign the value. I have tried the Insert Parameters but that will not accept <%# Session("UserID") %> as a DefaultValue. Any ideas would be helpful. Thanks.

Try handling the ItemInserting event of your FormView where you can programmatically set the SqlDataSource.DefaultValue to your Session variable value. For example:

protected void FormView1_ItemInserting(object sender, FormViewInsertEventArgs e)
{
SqlDataSource1.InsertParameters["UserID"].DefaultValue = Session["UserID"].ToString();
}|||Worked great with "(" instead of brackets "[". Thanks!|||I'm glad it worked out. My example was in C#. I guess you needed VB sample instead.|||You could of also created a SessionParameter and done it without any code at all.|||

Motley wrote:

You could of also created a SessionParameter and done it without any code at all.

Brilliant!!!

need to write query...

need to write query...
table1
fname points
-- --
bart -10
homer 0
lisa 20
maggy 5
marge 13
table2
fname points
-- --
bart 5
homer 7
lisa 8
marge -5
need to display report of names ordered by total score descending only
if total is above 0 so result should look like...
fname points
-- --
lisa 28
marge 8
homer 7
maggy 5Try the following:
declare @.table1 table (fname varchar(20), points int)
declare @.table2 table (fname varchar(20), points int)
insert into @.table1
values ('bart',-10)
insert into @.table1
values ('homer',0)
insert into @.table1
values ('lisa',20)
insert into @.table1
values ('maggy',5)
insert into @.table1
values ('marge',13)
insert into @.table2
values ('bart',5)
insert into @.table2
values ('homer',7)
insert into @.table2
values ('lisa',8)
insert into @.table2
values ('marge',-5)
select fname
, SUM(points) points
from (
select t1.fname
, t1.points
from @.table1 t1
union all
select t2.fname
, t2.points
from @.table2 t2
) t
group by fname
having SUM(points) > 0
order by SUM(points) desc
"uspensky@.gmail.com" wrote:
> need to write query...
> table1
> fname points
> -- --
> bart -10
> homer 0
> lisa 20
> maggy 5
> marge 13
> table2
> fname points
> -- --
> bart 5
> homer 7
> lisa 8
> marge -5
> need to display report of names ordered by total score descending only
> if total is above 0 so result should look like...
> fname points
> -- --
> lisa 28
> marge 8
> homer 7
> maggy 5
>|||nevermind
got it
select fname as FirstName, Total=sum(points)
from (select fname, points from t1
UNION ALL
select fname, points from t2) as result
group by fname Having sum(points)>0 order by Total desc

need to write from asp.net to XML type in SQL Server 2005

Hello, I have worked with SQL Server 2000 but now we have a requirement where I need to write values from an asp.net form into an XML type in SQL Server 2005.

I have never used XML as a type in SQL Server 2005. How do we write xml into xml type.

For example the structure of XML is something like:

<application>

<applicationID = "value"></applicationID>

<customerName="value></customerName>

</application>

I have to write this kind of XML into the XML type and later retrieve these XML values and populate the form again.

Kindly suggest. Thanks a lot.

You will need to look into several things to help you achieve this.

Look into the System.Xml.XmlTextWriter to help you construct XML valid strings

Also look into System.IO.StreamWriter to write the stream of data

You can then use methods of the XmlTextWriter to write the elements, attributes based on your XML structure; WriteStartDocument(), WriteStartElement(), WriteElementString();

need to write complex query without cursor

Hi all,
I am trying to do the following and am getting stuck.Your help wil be higly
appreciated.
I am matching two tables.If i get a single matching row then get that single
row
but if i get >1 matching rows i should select only the one with the latest
date.
for example
Table1
--
id value
1 10
2 20
3 30
Table 2
--
id param updateDate
1 10 day1
1 20 day2
1 25 day3
2 20 day2
2 40 day 3
3 30 day 2
(day1 <day2<day3 etc)
So i should get results as
Result
--
1 25 day3
2 40 day3
3 30 day2
how can I do this without a cursor?
Thanks for your help.on SQL 2005,
select id, param, updateDate
from table2
where row_number() over(partition by id order by updateDate desc) = 1
*untested*|||Try this, if you don't have SQL Server 2005:
select Table2.id, Table2.param, Table2.updateDate
from Table2
join Table1 on Table1.id = Table2.id
where updateDate = (
select max(updateDate) from Table2 as T_copy
where T_copy = Table2.id
)
or
select id, param, updateDate
from Table2
where updateDate = (
select max(updateDate) from Table2 as T_copy
where T_copy = Table2.id
) and exists (
select * from Table1
where Table1.id = Table2.id
)
This can yield multiple rows per id if (id, updateDate) is not unique
and there are two or more updates for an id on most recent update date.
For SQL Server 2005, Alexander has the right idea, but windowed
functions cannot appear in the WHERE clause, and you'll have to do this:
select id, param, updateDate
from (
select
id, param, updateDate,
row_number() over (partition by id order by updateDate desc) as rn
from Table2
) as T
where rn = 1
Steve Kass
Drew University
tech77 wrote:

>Hi all,
>I am trying to do the following and am getting stuck.Your help wil be higly
>appreciated.
>I am matching two tables.If i get a single matching row then get that singl
e
>row
> but if i get >1 matching rows i should select only the one with the latest
>date.
>for example
>Table1
>--
>id value
>1 10
>2 20
>3 30
>Table 2
>--
>id param updateDate
>1 10 day1
>1 20 day2
>1 25 day3
>2 20 day2
>2 40 day 3
>3 30 day 2
>(day1 <day2<day3 etc)
>So i should get results as
>Result
>--
>1 25 day3
>2 40 day3
>3 30 day2
>how can I do this without a cursor?
>Thanks for your help.
>|||Here's one method, assuming that each id will only have one row per
day. If you have multiple rows per id per day, you have to have some
rule to determine which row you want returned.
Stu
DECLARE @.Table1 TABLE (id int, value int)
INSERT INTO @.TABLE1
SELECT 1, 10
UNION ALL
SELECT 2, 20
UNION ALL
SELECT 3, 30
DECLARE @.Table2 TABLE (id int, param int, UpdateDate smalldatetime)
INSERT INTO @.TABLE2
SELECT 1, 10, '20060301'
UNION ALL
SELECT 1, 20, '20060302'
UNION ALL
SELECT 1, 25, '20060303'
UNION ALL
SELECT 2, 20, '20060302'
UNION ALL
SELECT 2, 40, '20060303'
UNION ALL
SELECT 3, 30, '20060302'
SELECT t2.id, t2.param, t2.UpdateDate
FROM @.table1 t1 JOIN @.Table2 t2 ON t1.id=t2.id
JOIN (SELECT id, MAX(updateDate) AS upDateDate
FROM @.Table2 t2
GROUP BY id) t2_2 ON t2.ID = t2_2.ID
AND t2.UpDateDate = t2_2.UpdateDate

Need to write a stored procedure

Hi,

I need to write a stored procedure to check if the string = "web_version" exists in another string called FromCode.

The stored procedure accepts 2 parameters like this:

Create proc [dbo].[spGetPeerFromCode]( @.FromCode VARCHAR(50)) (@.WebVersionString VARCHAR(50))as

please assist.

Check Books On Line for theSubstring function.