COMSysApp DCOM got error

How can I resolve the error listed below.
Thank You,
DCOM got error "The service cannot be started, either because it is disabled
or because it has no enabled devices associated with it. " attempting to
start the service COMSysApp with arguments "" in order to run the server:Joe,
This is a SQL Server group - you are more likely to get an answer if you
post in a DCOM related group.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> How can I resolve the error listed below.
> Thank You,
>
> DCOM got error "The service cannot be started, either because it is disabl
ed
> or because it has no enabled devices associated with it. " attempting to
> start the service COMSysApp with arguments "" in order to run the server:

COMSysApp DCOM got error

How can I resolve the error listed below.
Thank You,
DCOM got error "The service cannot be started, either because it is disabled
or because it has no enabled devices associated with it. " attempting to
start the service COMSysApp with arguments "" in order to run the server:
Joe,
This is a SQL Server group - you are more likely to get an answer if you
post in a DCOM related group.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> How can I resolve the error listed below.
> Thank You,
>
> DCOM got error "The service cannot be started, either because it is disabled
> or because it has no enabled devices associated with it. " attempting to
> start the service COMSysApp with arguments "" in order to run the server:

COMSysApp DCOM got error

How can I resolve the error listed below.
Thank You,
DCOM got error "The service cannot be started, either because it is disabled
or because it has no enabled devices associated with it. " attempting to
start the service COMSysApp with arguments "" in order to run the server:Joe,
This is a SQL Server group - you are more likely to get an answer if you
post in a DCOM related group.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Joe K. wrote:
> How can I resolve the error listed below.
> Thank You,
>
> DCOM got error "The service cannot be started, either because it is disabled
> or because it has no enabled devices associated with it. " attempting to
> start the service COMSysApp with arguments "" in order to run the server:sqlsql

Complicated Query with Subs for Grand Total

I'm trying to get the syntax right below. Know that what I'm trying to do is correct (I don't want to explain why or the entire thing but just need help with correcting the syntax issues on the join and stuff:

Errors:

Msg 156, Level 15, State 1, Procedure IT_Get_ProjFee_GT, Line 205

Incorrect syntax near the keyword 'INNER'.

Msg 156, Level 15, State 1, Procedure IT_Get_ProjFee_GT, Line 213

Incorrect syntax near the keyword 'as'.

Msg 170, Level 15, State 1, Procedure IT_Get_ProjFee_GT, Line 219

Line 219: Incorrect syntax near 'branch'.

Msg 170, Level 15, State 1, Procedure IT_Get_ProjFee_GT, Line 219

Line 219: Incorrect syntax near ')'.

Msg 170, Level 15, State 1, Procedure IT_Get_ProjFee_GT, Line 221

Line 221: Incorrect syntax near ')'.

Select @.InHouse2 = SUM(InHouse2)

FROM

(Select SUM (CCsNewTable.SumOldCCs) * CASE WHEN FeeSchedule = 0 THEN

1

WHEN FeeSchedule = 1 THEN

FeeSchedule / 100

ELSE

FeeSchedule / 100

END AS InHouse2

FROM DC

INNER JOIN

( Select Company, FeeSchedule, SUM(OldCCs) SumOldCCs

INNER JOIN

(Select CC AS OldCCs from DC d

WHERE d.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today)) AND d.EnteredDate IS NOT NULL

AND Type = 'CC'

AND ((branch = '00002' and customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1) )

OR (customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0) ))

AND pdc_OnHold IS NULL

GROUP BY CompanyNumber

) as CCsNewTable ON CompanyNumber = CCsNewTable.CompanyNumber

) as InHouse2Table

WHERE branch='00002'

AND Type = 'In-House'

AND ((branch = '00002' and customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

)

Also tried adding aliases to distinguish the difference between DC and DC with d and dd

Select @.InHouse2 = SUM(InHouse2)

FROM

(Select SUM (CCsNewTable.SumOldCCs) * CASE WHEN FeeSchedule = 0 THEN

1

WHEN FeeSchedule = 1 THEN

FeeSchedule / 100

ELSE

FeeSchedule / 100

END AS InHouse2

FROM DCR d

INNER JOIN

( Select Company, FeeSchedule, SUM(OldCCs) SumOldCCs

INNER JOIN

(Select CC AS OldCCs from DCR dd

WHERE dd.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today)) AND dd.EnteredDate IS NOT NULL

AND dd.Type = 'CC'

AND ((dd.branch = '00002' and dd.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1) )

OR (dd.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0) ))

AND dd.pdc_OnHold IS NULL

GROUP BY dd.CompanyNumber

) as CCsNewTable ON d.CompanyNumber = CCsNewTable.CompanyNumber

) as InHouse2Table

WHERE d.branch='00002'

AND d.Type = 'In-House'

AND ((d.branch = '00002' and d.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

)

|||You need to specify the JOIN conditions wiht your ON statement after the end of the subquery you are joining to, as I did in the part of the sub query:

as CCsNewTable ON CompanyNumber = CCsNewTable.CompanyNumber

HTH
|||

My aliases and other stuff are still screwed up...need some help on this one...I can't see it:

DECLARE @.today DATETIME,

@.datefirst datetime,

@.Posted money,

@.GrossGoal money,

@.OldPDCs money,

@.NewPDCs money,

@.OldCCs money,

@.NewCCs money,

@.InHouse1 money,

@.InHouse2 money,

@.Var1 money

SET @.today = CONVERT(CHAR(8), GETDATE(), 112) --'Jun 26 2005'

SET @.datefirst = @.@.DATEFIRST

Select @.InHouse2 = SUM(inhouse1table.InHouse2)

FROM

(Select SUM (InHouse2Table.InHouse2) * CASE WHEN FeeSchedule = 0 THEN

1

WHEN FeeSchedule = 1 THEN

FeeSchedule / 100

ELSE

FeeSchedule / 100

END AS InHouse2

FROM DC d

INNER JOIN

( Select branch, Type, CustomerNumber, CustomerName, SUM(OldCCs) * FeeSchedule as InHouse2

FROM DC ddd

INNER JOIN

(Select CustomerNumber, CC AS OldCCs from DC dd

WHERE dd.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today)) AND dd.EnteredDate IS NOT NULL

AND dd.Type = 'CC'

AND ((dd.branch = '00002' and dd.CustomerNumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1) )

OR (dd.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

AND dd.pdc_OnHold IS NULL

GROUP BY dd.CustomerNumber

)as CCsNewTable ON CompanyNumber = CCsNewTable.CompanyNumber

) as InHouse2Table on d.CustomerNumber = inhouse2table.CustomerNumber

WHERE d.branch='00002'

AND d.Type = 'In-House'

AND ((d.branch = '00002' and d.CustomerNumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

) as inhouse1table

|||

Please check out another post here which explains what I'm doing which was one step before this post:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21753502.html

|||Is this a college assignment or something? Wh are you asking the same questions as dba123? If its for school, I cannot help you.|||are you out of your mind? does this look like a college assignement? something this complicated? What do you mean same questions...this is not the same. Is this too complicated for you to help me with? This is Grand Total that I have to mimic from a crazy excel spreadsheet from our finance dept....|||

this is a complete different problem than you saw from dba123...if you read you'll realize this. Just check this out, OK then come back with some useful replies please:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21753502.html

|||

that's ok pal, I fixed it...

DECLARE @.today DATETIME,
@.datefirst datetime,
@.Posted money,
@.GrossGoal money,
@.OldPDCs money,
@.NewPDCs money,
@.OldCCs money,
@.NewCCs money,
@.InHouse1 money,
@.InHouse2 money,
@.Var1 money

SET @.today = CONVERT(CHAR(8), GETDATE(), 112) --'Jun 26 2005'
SET @.datefirst = @.@.DATEFIRST

Select @.InHouse2 = SUM(InHouse2)
FROM
(Select CCsOldTable.SumOldCCs * CASE WHEN d1.FeeSchedule = 0 THEN
1
WHEN d1.FeeSchedule = 1 THEN
(d1.FeeSchedule / 100)
ELSE
(FeeSchedule / 100)
END AS InHouse2
FROM DCR d1
INNER JOIN
(Select d2.CustomerNumber, SUM(CCsQuery.OldCCs) AS SumOldCCs
FROM DCR d2
INNER JOIN
-- NewCCs Query
(Select CustomerNumber, CC AS OldCCs
FROM DCR d3
WHERE d3.EnteredDate NOT BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))
AND d3.Type = 'CC'
AND ((d3.branch = '00002' and d3.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))
OR (d3.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))
AND d3.dc_OnHoldDate IS NULL
GROUP BY d3.CustomerNumber, d3.CC)as CCsQuery on CCsQuery.CustomerNumber = d2.CustomerNumber
GROUP BY d2.CustomerNumber
) as CCsOldTable ON d1.CustomerNumber = CCsOldTable.CustomerNumber

GROUP BY d1.CustomerNumber, CCsOldTable.SumOldCCs, d1.FeeSchedule
) as z

Select @.InHouse2

|||

Sorry, did not mean to offend you, I was just wondering why you are using the same tables and queries. Hey, some college courses can get complicated now

Hope I was some help. Btw, you would probably be better (and quicker) to use a single join with an OR here instead of using IN, which is slow:

AND ((d3.branch = '00002' and d3.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))
OR (d3.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

|||

thanks, sorry, yea, I was a bit irritated but that's cool.

Can you help me with your explanation ion using the single join with OR instead of IN ?

|||I figured it out....|||Sorry, that was the last thing I posted before logging off.

Complicated query - select based on value

SQL2K on W2Kserver

I need some help revamping a rather complicated query. I've given the
table and existing query information below. (FYI, changing the
database structure is right out.)

The current query lists addresses with two particular types
('MN30D843J2', 'SC93JDL39D'). I need to change this to (1) check each
contact for address type 'AM39DK3KD9' and then (2) if the contact has
type 'AM39DK3KD9' select types ('AM39DK3KD9', 'ASKD943KDI') OR if the
contact does not have that type then select types ('MN30D843J2',
'SC93JDL39D'). (Context - the current query selects two standard
address types "Main" and "Secondary"; we've added new data and now have
types "Alternate Main" and "Alternate Secondary". If the Contact has
Alternate addresses, I need to select those; if not, I need to select
the standard addresses. There are other address types in use, so I
must specify which types to select.)

Can anyone point me in the right direction?

Thanks very much! jamileh

CREATE TABLE [CONTACTS] (
[CONTACT_X] [char] (10),
[LONGNAME] [char] (75),
[ACTIVE] [bit])

CREATE TABLE [CONTACTADDRESSES] (
[CONTACT_X] [char] (10),
[ADDRESS_X] [char] (10),
[ADDRESSTYPE_REFX] [char] (10),
[ACTIVE] [bit])

CREATE TABLE [ADDRESSES] (
[ADDRESS_X] [char] (10),
[ADDRESSLINE1] [char] (60),
[ADDRESSLINE2] [char] (60),
[CITY] [char] (20),
[STATE] [char] (2),
[ZIPCODE] [char] (11),
[PHONE] [char] (10))

CREATE TABLE [REFERENCETABLE] (
[REFERENCETABLE_X] [char] (10),
[ADDRESS_X] [char] (10),
[DESCRIPTION] [char] (60))

CREATE TABLE [MASTERTABLE] (
[CONTACT_X] [char] (10),
[RECORDTYPE] [char] (1),
[ACTIVE] [bit])

CREATE VIEW vw_CONTACTInfo_ListLoc
AS
SELECT CONTACTS.CONTACT_X, CONTACTS.LONGNAME,
CONTACTADDRESSES.ADDRESSTYPE_REFX,
Type_REFERENCETABLE.DESCRIPTION AS Type_DESCRIPTION,
CONTACTADDRESSES.ADDRESS_X, ADDRESSES.ADDRESSLINE1,
ADDRESSES.ADDRESSLINE2, ADDRESSES.CITY, ADDRESSES.STATE,
ADDRESSES.ZIPCODE, ADDRESSES.PHONE
FROM CONTACTS INNER JOIN CONTACTADDRESSES ON
CONTACTS.CONTACT_X = CONTACTADDRESSES.CONTACT_X INNER JOIN
ADDRESSES ON CONTACTADDRESSES.ADDRESS_X =
ADDRESSES.ADDRESS_X
INNER JOIN REFERENCETABLE Type_REFERENCETABLE ON
CONTACTADDRESSES.ADDRESSTYPE_REFX =
Type_REFERENCETABLE.REFERENCETABLE_X
WHERE (CONTACTS.ACTIVE = 1) AND (CONTACTADDRESSES.ADDRESSTYPE_REFX
IN
('MN30D843J2', 'SC93JDL39D') AND (CONTACTADDRESSES.ACTIVE =
1)) AND
(CONTACTS.CONTACT_X IN
(SELECT CONTACT_X FROM MASTERTABLE WHERE
ACTIVE = 1 AND RECORDTYPE = 'E'))"jqq" <jqq@.myrealbox.com> wrote in message
news:1120839746.620891.96250@.f14g2000cwb.googlegro ups.com...
> SQL2K on W2Kserver
> I need some help revamping a rather complicated query. I've given the
> table and existing query information below. (FYI, changing the
> database structure is right out.)
> The current query lists addresses with two particular types
> ('MN30D843J2', 'SC93JDL39D'). I need to change this to (1) check each
> contact for address type 'AM39DK3KD9' and then (2) if the contact has
> type 'AM39DK3KD9' select types ('AM39DK3KD9', 'ASKD943KDI') OR if the
> contact does not have that type then select types ('MN30D843J2',
> 'SC93JDL39D'). (Context - the current query selects two standard
> address types "Main" and "Secondary"; we've added new data and now have
> types "Alternate Main" and "Alternate Secondary". If the Contact has
> Alternate addresses, I need to select those; if not, I need to select
> the standard addresses. There are other address types in use, so I
> must specify which types to select.)
> Can anyone point me in the right direction?
> Thanks very much! jamileh

<snip
The short answer is probably to see CASE in Books Online. If you need more
information, I suggest you provide some INSERT statements for sample data,
and also the output you expect - it's not very clear (to me) exactly what
your query should return.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon|||If the Contact has (INNER JOIN)
Alternate addresses, I need to select those;

UNION ALL
if not, NOT EXISTS(...)

I need to select
the standard addresses.|||Without better specs, this is hard. The tables had no keys; the names
of the data elements are awful, you even put physical storage and usage
into the names! You are using bit flags in SQL. There does not seem to
be any consistent design here. Clraning it up a bit, I got this:

CREATE TABLE Contacts
(contact_id CHAR(10) NOT NULL PRIMARY KEY,
long_name CHAR(75) NOT NULL);

CREATE TABLE Addresses
(address_id CHAR(10) NOT NULL PRIMARY KEY,
address_line1 CHAR(35) NOT NULL, -- usps lengths
address_line2 CHAR(35) NOT NULL,
city_name CHAR(20) NOT NULL,
state_code CHAR(2) NOT NULL,
zip_code CHAR(9) NOT NULL,
phone_nbr CHAR(10) NOT NULL));

Your codes belong to the relationship, and not in their own tables,
something more like this

CREATE TABLE ContactAddresses
(contact_id CHAR(10) NOT NULL
REFERENCES Contacts (contact_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
address_id CHAR(10) NOT NULL
REFERENCES Addresses (address_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
address_type INTEGER NOT NULL, -- see suggestion below
PRIMARY KEY (contact_id, address_id, address_type)
contact_status CHAR(3) DEFAULT 'act' NOT NULL
CHECK (contact_status IN ('act', 'old', ..));

Views should be kept simple so they can be used in many place. And
unless they dela wiyth a Volkswagen, you do not prefix them with "vw_"
:)

CREATE VIEW ContactInfo (contact_id, long_name,
address_type, address_id,
address_line1, address_line2,
city_name, state_code, zip_code,
phone_nbr)
AS
SELECT C.contact_id, C.long_name,
CA.address_type, CA.address_id,
A.address_line1, A.address_line2,
A.city_name, A.state_code, A.zip_code,
A.phone_nbr
FROM Contacts AS C,
ContactAddresses AS CA,
Addresses AS A
WHERE CA.contact_id = C.contact_id
AND CA.address_id = A.address_id
AND CA.address_type BETWEEN 100 AND 299;

>> the current query selects two standard address types "Main" and "Secondary"; we've added new data and now have types "Alternate Main" and "Alternate Secondary". If the Contact has Alternate addresses, I need to select those; if not, I need to select the standard addresses. <<

You need a better encoding scheme than those awful ten-letter
nightmares. I read your narrative as meaning one contact can have only
one address of each type. Here is a hierarchical encoding suggestion,
with some room for growth.

100-199 = Main Address
110-119 = Alternative Main Address
200-299 = Secondary Address
210-219 = Alternative Secondary Address

The query would be something like this:

SELECT DISTINCT I1.*
FROM ContactInfo AS I1
WHERE address_type IN (200, 210) -- has secondary address
OR (address_type IN (100, 110) -- has main address
AND NOT EXISTS
(SELECT *
FROM ContactInfo AS I2
WHERE address_type IN (200, 210) -- no secondary address
AND I1.contact_id = I2.contact_id
AND I1.address_id = I2.address_id));|||My apologies for leaving out the data, I didn't want to get too long in
my original post if it wasn't needed. Please see below (including one
fix on a table).

So, the current query will pull John Smith's "standard" addresses on
Main, Second, and Third streets, plus Frank Doe's "standard" addresses
on Main, Second and Third streets.

The results I need would give John Smith's "standard" addresses on
Main, Second, and Third streets, plus Frank Doe's "alternate" addresses
on Fifth and Sixth streets.

I've used CASE, but not to select multiple records based on one field.
I'm not sure how to make that work and I can't find anything in BOL to
explain it.

Thanks.

ALTER TABLE [REFERENCETABLE] DROP COLUMN [ADDRESS_X]

INSERT [CONTACTS] VALUES ('A1','John Smith',1)
INSERT [CONTACTS] VALUES ('B2','Frank Doe',1)
INSERT [CONTACTS] VALUES ('C3','Jane Jones',1)
INSERT [CONTACTS] VALUES ('D4','Susan Roe',0)
INSERT [CONTACTS] VALUES ('E5','George Brown',1)

INSERT [CONTACTADDRESSES] VALUES ('A1','F1','MN30D843J2',1)
INSERT [CONTACTADDRESSES] VALUES ('A1','G2','SC93JDL39D',1)
INSERT [CONTACTADDRESSES] VALUES ('A1','H3','SC93JDL39D',1)
INSERT [CONTACTADDRESSES] VALUES ('A1','I4','BL2309DD3L',1)
INSERT [CONTACTADDRESSES] VALUES ('A1','J5','AM39DK3KD9',0)
INSERT [CONTACTADDRESSES] VALUES ('B2','K6','MN30D843J2',1)
INSERT [CONTACTADDRESSES] VALUES ('B2','L7','SC93JDL39D',1)
INSERT [CONTACTADDRESSES] VALUES ('B2','M8','SC93JDL39D',1)
INSERT [CONTACTADDRESSES] VALUES ('B2','N9','BL2309DD3L',1)
INSERT [CONTACTADDRESSES] VALUES ('B2','O0','AM39DK3KD9',1)
INSERT [CONTACTADDRESSES] VALUES ('B2','P1','ASKD943KDI',1)
INSERT [CONTACTADDRESSES] VALUES ('C3','Q2','AM39DK3KD9',1)
INSERT [CONTACTADDRESSES] VALUES ('D4','R3','AM39DK3KD9',1)
INSERT [CONTACTADDRESSES] VALUES ('E5','S4','AM39DK3KD9',1)

INSERT [ADDRESSES] VALUES ('F1','123 Main
St','','Anytown','PA','12345','5074951548')
INSERT [ADDRESSES] VALUES ('G2','456 Second St','Apt
9','Anytown','PA','45678','5074328548')
INSERT [ADDRESSES] VALUES ('H3','789 Third
St','','Anytown','PA','45678','5074321111')
INSERT [ADDRESSES] VALUES ('I4','987 Fourth
St','','Anytown','PA','12345','5074959999')
INSERT [ADDRESSES] VALUES ('J5','654 Fifth
St','','Anytown','PA','12345','5074955555')
INSERT [ADDRESSES] VALUES ('K6','1 Main
St','','Somewhere','UT','87654','2426831234')
INSERT [ADDRESSES] VALUES ('L7','2 Second St','Suite
600','Somewhere','UT','87654','2426835678')
INSERT [ADDRESSES] VALUES ('M8','3 Third
St','','Somewhere','UT','87654','2426839876')
INSERT [ADDRESSES] VALUES ('N9','4 Fourth
St','','Somewhere','UT','87654','2426835432')
INSERT [ADDRESSES] VALUES ('O0','5 Fifth
St','','Somewhere','UT','87654','2426831111')
INSERT [ADDRESSES] VALUES ('P1','6 Sixth
St','','Somewhere','UT','87654','2426839999')
INSERT [ADDRESSES] VALUES ('Q2','123 NoGood
St','','Nowhere','AK','98765','9051875135')
INSERT [ADDRESSES] VALUES ('R3','456 NotMe
St','','Nonesuch','CA','43210','7631584625')
INSERT [ADDRESSES] VALUES ('S4','789 UhOh
St','','Noway','GA','36847','6427892462')

INSERT [REFERENCETABLE] VALUES ('MN30D843J2','Standard Main')
INSERT [REFERENCETABLE] VALUES ('SC93JDL39D','Standard Secondary')
INSERT [REFERENCETABLE] VALUES ('AM39DK3KD9','Alternate Main')
INSERT [REFERENCETABLE] VALUES ('ASKD943KDI','Alternate Secondary')
INSERT [REFERENCETABLE] VALUES ('BL2309DD3L','Billing Only')

INSERT [MASTERTABLE] VALUES ('A1','E',1)
INSERT [MASTERTABLE] VALUES ('B2','E',1)
INSERT [MASTERTABLE] VALUES ('C3','N',1)
INSERT [MASTERTABLE] VALUES ('D4','E',1)
INSERT [MASTERTABLE] VALUES ('E5','E',0)|||As I said, changing the database structure is right out. I didn't
build the beastie - it's the backend for a proprietary application.
The only thing I can do is pull data.

These views are for the exact purpose of selecting very specific sets
of data to provide to some websites. This one's not half bad, you
should see some of the others!

I did leave out PKs, etc. - sorry. The tables are actually much
bigger & I was trying to just pull the needed info for simplicity. If
there's something specific that would help, please let me know & I'll
post an update. In general, you're right and the "tablename_K" columns
are PKs and FKs.

As you can see in my last post (with data), one contact can have
multiple addresses of each type and I need to pull all addresses of the
correct types.

Any rate, thanks for the advice! j

p.s. What's wrong with bit flags?|||I read this several times without any clue what you meant, but I think
it's beginning to permeate into my poor, bleeding braincells. I'll see
what I can come up with. Thanks!|||jqq (jqq@.myrealbox.com) writes:
> p.s. What's wrong with bit flags?

Nothing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||jqq (jqq@.myrealbox.com) writes:
> My apologies for leaving out the data, I didn't want to get too long in
> my original post if it wasn't needed. Please see below (including one
> fix on a table).
> So, the current query will pull John Smith's "standard" addresses on
> Main, Second, and Third streets, plus Frank Doe's "standard" addresses
> on Main, Second and Third streets.
> The results I need would give John Smith's "standard" addresses on
> Main, Second, and Third streets, plus Frank Doe's "alternate" addresses
> on Fifth and Sixth streets.

This query gives the result described above, but does not match your
description in the first post. But maybe you simply messed up on all
these terrible 10-letter codes when you composed the sample data.

SELECT C.CONTACT_X, C.LONGNAME, CA.ADDRESSTYPE_REFX,
R.DESCRIPTION AS Type_DESCRIPTION, CA.ADDRESS_X,
A.ADDRESSLINE1, A.ADDRESSLINE2, A.CITY, A.STATE, A.ZIPCODE,
A.PHONE
FROM CONTACTS C
JOIN CONTACTADDRESSES CA ON C.CONTACT_X = CA.CONTACT_X
JOIN ADDRESSES A ON CA.ADDRESS_X = A.ADDRESS_X
JOIN REFERENCETABLE R ON CA.ADDRESSTYPE_REFX = R.REFERENCETABLE_X
WHERE C.ACTIVE = 1
AND CA.ADDRESSTYPE_REFX IN ('MN30D843J2', 'SC93JDL39D')
AND CA.ACTIVE = 1
AND C.CONTACT_X IN (SELECT M.CONTACT_X
FROM MASTERTABLE M
WHERE M.ACTIVE = 1
AND M.RECORDTYPE = 'E')
AND NOT EXISTS (SELECT *
FROM CONTACTADDRESSES CA1
WHERE C.CONTACT_X = CA1.CONTACT_X
AND CA1.ADDRESSTYPE_REFX IN ('ASKD943KDI'))
UNION ALL
SELECT C.CONTACT_X, C.LONGNAME, CA.ADDRESSTYPE_REFX,
R.DESCRIPTION AS Type_DESCRIPTION, CA.ADDRESS_X,
A.ADDRESSLINE1, A.ADDRESSLINE2, A.CITY, A.STATE, A.ZIPCODE,
A.PHONE
FROM CONTACTS C
JOIN CONTACTADDRESSES CA ON C.CONTACT_X = CA.CONTACT_X
JOIN ADDRESSES A ON CA.ADDRESS_X = A.ADDRESS_X
JOIN REFERENCETABLE R ON CA.ADDRESSTYPE_REFX = R.REFERENCETABLE_X
WHERE C.ACTIVE = 1
AND CA.ADDRESSTYPE_REFX IN ('AM39DK3KD9', 'ASKD943KDI')
AND CA.ACTIVE = 1
AND C.CONTACT_X IN (SELECT M.CONTACT_X
FROM MASTERTABLE M
WHERE M.ACTIVE = 1
AND M.RECORDTYPE = 'E')

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||They are a proprietary, low level data type. The design is the way we
handled things with punch cards in the old days. Flags are usually
computed columns. It is usually better to invent a status code which
can be extended, or to capture the date of an event, etc.|||Wow, I went off to read up on EXISTS and came back to find the query
all done!

That's exactly what I needed *and* I learned a new trick.

Thanks very much!!

Complexx AutoIncrment colum

Hi,

I have a situation where I have to maintain auto incremental values based on values of other colum. Sample table is given below.

colA colB colC 1 1 1 1 1 2 1 1 3 1 1 4 1 2 1 1 2 2 1 2 3 2 1 1 2 1 2 2 1 3

Now in above table I want to autoincrement value of colC on the basis of Values of colA+colB. Is there any way I can set this fuctionalty directky on table schema.

Nilkkanth Desai

There is no way to create this kind of functionality on the table schema.

You could use a TRIGGER.

|||

You can use computed column for ColC.

Thanks

Naras.

|||

Hello Narasimhan Jayachandran ,

Thanks for your reply. I tried to findout samples on computed columns but I did not find any. Can u give me sample on this issue. or give me more details link if any on this topic. Your sample code will be more helpful for me to solve this issue.

Thank you,

Nilkanth Desai

|||

It is not good idea to use the computed columns here.. (in other words you can't achive the result using the computed columns, since your computed colum is non-deterministic).

The best approach may be on the trigger or you can use new feature OUTPUT.

dafafadfa

Using OUTPUT:

Create table ABCTable
(C1 int, C2 Int, C3 INT)

Go

Begin Tran;
Begin Try

Declare @.Inserted Table(C1 int, C2 int);

Insert Into ABCTable(c1,C2)
OUTPUT Inserted.C1,Inserted.C2 Into @.Inserted
values (1,1)

Update ABCTable
Set
C3=Isnull(NewC3,1)
From
(
Select
Max(C3)+ 1 NewC3,
A.C1,
A.C2
From
ABCTable A
Join @.Inserted B On A.C1= B.C1 and A.c2=B.C2
Group By
A.C1, A.C2
)as NewC3
Where
ABCTable.C1 = NewC3.C1
And ABCTable.C2 = NewC3.C2
And C3 is NULL

IF @.@.ERROR = 0
Commit Tran
Else
Rollback Tran

End Try

Begin Catch
Rollback
End Catch

|||

Hi Nilkanth,

Computed column :

Example : create table tbcc ( c1 int, c2 int, c3 as (c1+c2))

insert tbcc (c1,c2) values (1,2)

select * from tbcc

c1 c2 c3
-- -- --
1 2 3

More details, refer Books on line under Create table

--
CREATE TABLE
[ database_name.[ owner ] . | owner. ] table_name
( { < column_definition >
| column_name AS computed_column_expression
| < table_constraint > ::= [ CONSTRAINT constraint_name ] }

| [ { PRIMARY KEY | UNIQUE } [ ,...n ]
) ..............

--

Thanks

Naras.

|||

Hi Narah,

Thanks for am alternative with sample code. It works.

Nilkanth

|||

Another question:

What is done if a row is deleted; are the rows supposed to auto-decrement?

|||

Hi,

It seems for update also it require one more trigger. Overall Triggers are costly in terms of resource utilisation. As this is my main trnsaction trble which handles 98% of traffic of the system I think triggers will be more heavy solution. Microsoft shall provide some mechenism in future release products as this kind of situation is mostlikely faced by most of the database developers. If we can find any schema based solution it will be light & easy to deploy.

Anyway bu this time this is the only solution I can use. If anyone finds more better solution then this please update this blog.

Nilkanth Desai

complex sql server 2005 query

Hi,
A sql server table is populated with records every 2 minutes. See below sample table
In the table, the Import_Date is a datetime field.

create table tblData
(
ID int identity(1, 1),
SourceID int,
SourceCode varchar(255)
Security varchar(255),
Bprice decimal(12, 8),
Aprice decimal(12, 8),
ImportDate datetime
)

Here is a populated table.
I have left gaps for better visual checks for you.

ID SourceID SourceCode Security Bprice BpriceSize Aprice ApriceSize ImportDate

1 1 sourceA SecA 100.2 2 99.12 1 2007-11-07 16:24:31.297
2 2 sourceW SecH 95.7 89.43 2007-11-07 16:24:31.297
3 3 SourceX SecS 50.56 1 76.44 4 2007-11-07 16:24:31.297
4 4 SourceQ SecZ 87.98 2007-11-07 16:24:31.297
5 5 SourceJ SecH 100.2 99.12 2 2007-11-07 16:24:31.297
6 6 SourceK SecU 2007-11-07 16:24:31.297
7 7 SourceT SecA 50.56 3 87.11 2007-11-07 16:24:31.297

8 1 sourceA SecA 100.2 6 99.12 2 2007-11-07 16:26:15.123
9 2 sourceW SecH 99.54 4 89.43 2007-11-07 16:26:15.123
10 3 SourceX SecS 50.56 2 19.33 2007-11-07 16:26:15.123
11 4 SourceQ SecZ 16.98 87.98 2007-11-07 16:26:15.123
12 5 SourceJ SecH 100.2 1 99.12 2 2007-11-07 16:26:15.123
13 6 SourceK SecU 2007-11-07 16:26:15.123
14 7 SourceT SecA 50.56 2 87.11 1 2007-11-07 16:26:15.123

15 1 sourceA SecA 100.2 1 87.11 1 2007-11-07 16:26:15.123
16 2 sourceW SecH 99.66 89.43 2 2007-11-07 16:26:15.123
17 3 SourceX SecS 50.56 2 19.33 2007-11-07 16:26:15.123
18 4 SourceQ SecZ 16.98 3 87.98 3 2007-11-07 16:26:15.123
19 5 SourceJ SecH 100.2 3 99.12 3 2007-11-07 16:26:15.123
20 6 SourceK SecU 2007-11-07 16:26:15.123
21 7 SourceT SecA 101.32 5 87.11 3 2007-11-07 16:26:15.123
...

I am trying to build a sql query to show which source is offering the max(Bprice) and who is offering the min(Aprice).
In addition if more than one sources are offering the same prices then they should be shown as shown below in the first record i.e. (SourceA, SourceT) --> 3 + 1 = 4
This is what I would like to see:

Security Max_Bprice Bprice_Size Bprice_SourceCode Min_Aprice Aprice_Size Aprice_SourceCode

SecA 101.32 5 SourceT 87.11 4 SourceA, SourceT
SecH 100.2 3 SourceJ 89.43 2 SourceW
SecS 50.56 2 SourceX 19.33 SourceX
SecZ 16.98 3 SourceQ 87.98 3 SourceQ

What is the sql query to do this please?

This is what I have started with but it is not correct...

select
Security,
max(Bprice) as 'Max_Bprice',
SourceCode as 'Bprice_SourceCode',
min(Aprice) as 'Min_Aprice',
SourceCode as 'Aprice_SourceCode'
from
tblData
group by
Security,
SourceCodeHi

You are almost there but not quite. Could you provide your sample data as point 3 here please:
http://www.dbforums.com/showthread.php?t=1196943

Also, your DDL does not match the data you have supplied.

Not really related, but there looks to be a third normal form issue here.

Cheers|||http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92298|||Here it is.
Thanks

DECLARE @.Sample TABLE (ID INT, SourceID INT, SourceCode VARCHAR(20), Security VARCHAR(20), Bprice MONEY, BpriceSize INT, Aprice MONEY, ApriceSize INT, ImportDate DATETIME)

INSERT @.Sample
SELECT 1, 1, 'sourceA', 'SecA', 100.2 , 2, 99.12, 1, '2007-11-07 16:24:31.297' UNION ALL
SELECT 2, 2, 'sourceW', 'SecH', 95.7 , NULL, 89.43, NULL, '2007-11-07 16:24:31.297' UNION ALL
SELECT 3, 3, 'SourceX', 'SecS', 50.56, 1, 76.44, 4, '2007-11-07 16:24:31.297' UNION ALL
SELECT 4, 4, 'SourceQ', 'SecZ', 87.98, NULL, NULL, NULL, '2007-11-07 16:24:31.297' UNION ALL
SELECT 5, 5, 'SourceJ', 'SecH', 100.2 , NULL, 99.12, 2, '2007-11-07 16:24:31.297' UNION ALL
SELECT 6, 6, 'SourceK', 'SecU', NULL, NULL, NULL, NULL, '2007-11-07 16:24:31.297' UNION ALL
SELECT 7, 7, 'SourceT', 'SecA', 50.56, 3, 87.11, NULL, '2007-11-07 16:24:31.297' UNION ALL
SELECT 8, 1, 'sourceA', 'SecA', 100.2 , 6, 99.12, 2, '2007-11-07 16:26:15.123' UNION ALL
SELECT 9, 2, 'sourceW', 'SecH', 99.54, 4, 89.43, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 10, 3, 'SourceX', 'SecS', 50.56, 2, 19.33, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 11, 4, 'SourceQ', 'SecZ', 16.98, NULL, 87.98, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 12, 5, 'SourceJ', 'SecH', 100.2 , 1, 99.12, 2, '2007-11-07 16:26:15.123' UNION ALL
SELECT 13, 6, 'SourceK', 'SecU', NULL, NULL, NULL, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 14, 7, 'SourceT', 'SecA', 50.56, 2, 87.11, 1, '2007-11-07 16:26:15.123' UNION ALL
SELECT 15, 1, 'sourceA', 'SecA', 100.2 , 1, 87.11, 1, '2007-11-07 16:26:15.123' UNION ALL
SELECT 16, 2, 'sourceW', 'SecH', 99.66, NULL, 89.43, 2, '2007-11-07 16:26:15.123' UNION ALL
SELECT 17, 3, 'SourceX', 'SecS', 50.56, 2, 19.33, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 18, 4, 'SourceQ', 'SecZ', 16.98, 3, 87.98, 3, '2007-11-07 16:26:15.123' UNION ALL
SELECT 19, 5, 'SourceJ', 'SecH', 100.2 , 3, 99.12, 3, '2007-11-07 16:26:15.123' UNION ALL
SELECT 20, 6, 'SourceK', 'SecU', NULL , NULL, NULL, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 21, 7, 'SourceT', 'SecA', 101.32, 5, 87.11, 3, '2007-11-07 16:26:15.123'|||I'll leave it to Peso. He'll get it soon enough I would imagine.|||Thanks anyway|||I suggest that you break it up

Do one, then the other, then combine them

I think you can do a union or a join of 2 derived table.

Since each derived table is going to be a single row, you wont have to worry about a cartesian product

Complex Select statement advise needed please

Hey there. I was wondering if someone could advise me whether or not i've
writted an effective/efficent stored procedure.
Basically the below procedure will be run from a .net application and the
values passed the the parameters will be 1 or null. It will allow the user
to select as many or as little options with like from a checkbox list and
based on what is entered a 1 or null value will be passed in and a results
set passed back.
I was wondering if this is the best way to do such a query or am i on the
wrong track below works fine but I don't know if its the best way to go abou
t
things.
I'd also like to try and order the results somehow but i'm not sure how to
do this as I've know way of knowing how many of the results are part of each
group. The table i'm querying looks like this.
u_forname u_surname b_publications b_consultation b_freedom b_policyt
etc
Stephen Cairns 0 1
0 0
Steve Jones 1 0
1 0
Laura McCall 1 0
0 0
Andrea Jones 0 0
0 1
etc.................
Basically when users can search the table for results equal to 1 from the
fields which they select in the checkbox list.
I hope someone is able to advise me. Thanks for your help
Here is the stored procedure
CREATE PROCEDURE [RegisteredUsers_SpecificSubscribers]
@.publications int,
@.consultation int,
@.freedom int,
@.judgments int,
@.legislation int,
@.policy int,
@.press int,
@.questions int,
@.strategies int,
@.targets int,
@.using int,
@.judgment int ,
@.sentence int,
@.practice int,
@.family int
AS
SET NOCOUNT ON
SELECT u_logon_name, u_firstname, u_surname, u_account_name
FROM UserObject
WHERE
[b_publications] = @.publications OR
([b_consultation] = @.consultation) OR
([b_freedom] = @.freedom) OR
([b_judgments] = @.judgments) OR
([b_legislation] = @.legislation) OR
([b_policy] = @.policy) OR
([b_press] = @.press) OR
([b_questions] = @.questions) OR
([b_strategies] = @.strategies) OR
([b_targets] = @.targets) OR
([b_using] = @.using) OR
([b_judgment] = @.judgment) OR
([b_sentence] = @.sentence) OR
([b_practice] = @.practice) OR
([b_family] = @.family)
GOStephen
Read up this article
http://www.sommarskog.se/dyn-search.html
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:E693AF7A-FA77-4E90-8F5B-4F944E0E3C91@.microsoft.com...
> Hey there. I was wondering if someone could advise me whether or not i've
> writted an effective/efficent stored procedure.
> Basically the below procedure will be run from a .net application and the
> values passed the the parameters will be 1 or null. It will allow the
user
> to select as many or as little options with like from a checkbox list and
> based on what is entered a 1 or null value will be passed in and a results
> set passed back.
> I was wondering if this is the best way to do such a query or am i on the
> wrong track below works fine but I don't know if its the best way to go
about
> things.
> I'd also like to try and order the results somehow but i'm not sure how to
> do this as I've know way of knowing how many of the results are part of
each
> group. The table i'm querying looks like this.
> u_forname u_surname b_publications b_consultation b_freedom b_policyt
> etc
> Stephen Cairns 0 1
> 0 0
> Steve Jones 1 0
> 1 0
> Laura McCall 1 0
> 0 0
> Andrea Jones 0 0
> 0 1
> etc.................
> Basically when users can search the table for results equal to 1 from the
> fields which they select in the checkbox list.
> I hope someone is able to advise me. Thanks for your help
> Here is the stored procedure
> CREATE PROCEDURE [RegisteredUsers_SpecificSubscribers]
> @.publications int,
> @.consultation int,
> @.freedom int,
> @.judgments int,
> @.legislation int,
> @.policy int,
> @.press int,
> @.questions int,
> @.strategies int,
> @.targets int,
> @.using int,
> @.judgment int ,
> @.sentence int,
> @.practice int,
> @.family int
> AS
> SET NOCOUNT ON
> SELECT u_logon_name, u_firstname, u_surname, u_account_name
> FROM UserObject
> WHERE
> [b_publications] = @.publications OR
> ([b_consultation] = @.consultation) OR
> ([b_freedom] = @.freedom) OR
> ([b_judgments] = @.judgments) OR
> ([b_legislation] = @.legislation) OR
> ([b_policy] = @.policy) OR
> ([b_press] = @.press) OR
> ([b_questions] = @.questions) OR
> ([b_strategies] = @.strategies) OR
> ([b_targets] = @.targets) OR
> ([b_using] = @.using) OR
> ([b_judgment] = @.judgment) OR
> ([b_sentence] = @.sentence) OR
> ([b_practice] = @.practice) OR
> ([b_family] = @.family)
> GO

complex query!

Hi,
I have Below query with me. but it's showing me error i.e "[Microsoft]
[ODBC SQL Server Driver]Syntax error or access violation"
I am not able to understand what to do exactly ' Please help
Query:
----
SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
incident.inc_resolve_act,
incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
incident.date_logged,
incident.time_to_resolve, inc_data.total_service_time,
incident.inc_resolve_sla, inc_cat.inc_cat_sc
FROM (((incident INNER JOIN inc_data ON
incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
AND
usr_group.usr_group_sc='" & SERVICE DESK & "' AND
((incident.date_logged>={27/12/2006)
AND incident.date_logged<{29/12/2006}) OR
(incident.inc_close_date>={27/12/2006}
AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status = 'n')or(incident.inc_status = 'c'))
ORDER BY sla.sla_sc"
---Instead of braces, enclose your date literals in single quotes. Braces are
used as the ODBC escape sequence.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1176547837.456693.297880@.y80g2000hsf.googlegroups.com...
> Hi,
> I have Below query with me. but it's showing me error i.e "[Microsoft]
> [ODBC SQL Server Driver]Syntax error or access violation"
> I am not able to understand what to do exactly ' Please help
> Query:
> ----
> SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
> incident.inc_resolve_act,
> incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
> incident.date_logged,
> incident.time_to_resolve, inc_data.total_service_time,
> incident.inc_resolve_sla, inc_cat.inc_cat_sc
> FROM (((incident INNER JOIN inc_data ON
> incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
> ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
> incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
> incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
> assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
> AND
> usr_group.usr_group_sc='" & SERVICE DESK & "' AND
> ((incident.date_logged>={27/12/2006)
> AND incident.date_logged<{29/12/2006}) OR
> (incident.inc_close_date>={27/12/2006}
> AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status => 'n')or(incident.inc_status = 'c'))
> ORDER BY sla.sla_sc"
> ---
>|||Try this:-
Some of the braces and { braces are not required:-
SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
incident.inc_resolve_act,
incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
incident.date_logged,
incident.time_to_resolve, inc_data.total_service_time,
incident.inc_resolve_sla, inc_cat.inc_cat_sc
FROM incident INNER JOIN inc_data ON
incident.incident_id=inc_data.incident_id INNER JOIN assyst_usr
ON incident.assyst_usr_id=assyst_usr.assyst_usr_id INNER JOIN sla ON
incident.sla_id=sla.sla_id INNER JOIN inc_cat ON
incident.inc_cat_id=inc_cat.inc_cat_id INNER JOIN usr_group ON
assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
AND
usr_group.usr_group_sc='" & SERVICE DESK & "' AND
((incident.date_logged>='27/12/2006')
AND incident.date_logged<'29/12/2006') OR
(incident.inc_close_date>='27/12/2006'
AND incident.inc_close_date<'27/12/2006')OR(incident.inc_status ='n')
or(incident.inc_status = 'c')
ORDER BY sla.sla_sc
Thanks
Hari
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1176547837.456693.297880@.y80g2000hsf.googlegroups.com...
> Hi,
> I have Below query with me. but it's showing me error i.e "[Microsoft]
> [ODBC SQL Server Driver]Syntax error or access violation"
> I am not able to understand what to do exactly ' Please help
> Query:
> ----
> SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
> incident.inc_resolve_act,
> incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
> incident.date_logged,
> incident.time_to_resolve, inc_data.total_service_time,
> incident.inc_resolve_sla, inc_cat.inc_cat_sc
> FROM (((incident INNER JOIN inc_data ON
> incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
> ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
> incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
> incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
> assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
> AND
> usr_group.usr_group_sc='" & SERVICE DESK & "' AND
> ((incident.date_logged>={27/12/2006)
> AND incident.date_logged<{29/12/2006}) OR
> (incident.inc_close_date>={27/12/2006}
> AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status => 'n')or(incident.inc_status = 'c'))
> ORDER BY sla.sla_sc"
> ---
>

complex query!

Hi,
I have Below query with me. but it's showing me error i.e "[Microsoft]
[ODBC SQL Server Driver]Syntax error or access violation"
I am not able to understand what to do exactly ? Please help
Query:
SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
incident.inc_resolve_act,
incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
incident.date_logged,
incident.time_to_resolve, inc_data.total_service_time,
incident.inc_resolve_sla, inc_cat.inc_cat_sc
FROM (((incident INNER JOIN inc_data ON
incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
AND
usr_group.usr_group_sc='" & SERVICE DESK & "' AND
((incident.date_logged>={27/12/2006)
AND incident.date_logged<{29/12/2006}) OR
(incident.inc_close_date>={27/12/2006}
AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status =
'n')or(incident.inc_status = 'c'))
ORDER BY sla.sla_sc"
Instead of braces, enclose your date literals in single quotes. Braces are
used as the ODBC escape sequence.
Hope this helps.
Dan Guzman
SQL Server MVP
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1176547837.456693.297880@.y80g2000hsf.googlegr oups.com...
> Hi,
> I have Below query with me. but it's showing me error i.e "[Microsoft]
> [ODBC SQL Server Driver]Syntax error or access violation"
> I am not able to understand what to do exactly ? Please help
> Query:
> ----
> SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
> incident.inc_resolve_act,
> incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
> incident.date_logged,
> incident.time_to_resolve, inc_data.total_service_time,
> incident.inc_resolve_sla, inc_cat.inc_cat_sc
> FROM (((incident INNER JOIN inc_data ON
> incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
> ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
> incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
> incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
> assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
> AND
> usr_group.usr_group_sc='" & SERVICE DESK & "' AND
> ((incident.date_logged>={27/12/2006)
> AND incident.date_logged<{29/12/2006}) OR
> (incident.inc_close_date>={27/12/2006}
> AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status =
> 'n')or(incident.inc_status = 'c'))
> ORDER BY sla.sla_sc"
>
|||Try this:-
Some of the braces and { braces are not required:-
SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
incident.inc_resolve_act,
incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
incident.date_logged,
incident.time_to_resolve, inc_data.total_service_time,
incident.inc_resolve_sla, inc_cat.inc_cat_sc
FROM incident INNER JOIN inc_data ON
incident.incident_id=inc_data.incident_id INNER JOIN assyst_usr
ON incident.assyst_usr_id=assyst_usr.assyst_usr_id INNER JOIN sla ON
incident.sla_id=sla.sla_id INNER JOIN inc_cat ON
incident.inc_cat_id=inc_cat.inc_cat_id INNER JOIN usr_group ON
assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
AND
usr_group.usr_group_sc='" & SERVICE DESK & "' AND
((incident.date_logged>='27/12/2006')
AND incident.date_logged<'29/12/2006') OR
(incident.inc_close_date>='27/12/2006'
AND incident.inc_close_date<'27/12/2006')OR(incident.inc_status ='n')
or(incident.inc_status = 'c')
ORDER BY sla.sla_sc
Thanks
Hari
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1176547837.456693.297880@.y80g2000hsf.googlegr oups.com...
> Hi,
> I have Below query with me. but it's showing me error i.e "[Microsoft]
> [ODBC SQL Server Driver]Syntax error or access violation"
> I am not able to understand what to do exactly ? Please help
> Query:
> ----
> SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
> incident.inc_resolve_act,
> incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
> incident.date_logged,
> incident.time_to_resolve, inc_data.total_service_time,
> incident.inc_resolve_sla, inc_cat.inc_cat_sc
> FROM (((incident INNER JOIN inc_data ON
> incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
> ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
> incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
> incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
> assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
> AND
> usr_group.usr_group_sc='" & SERVICE DESK & "' AND
> ((incident.date_logged>={27/12/2006)
> AND incident.date_logged<{29/12/2006}) OR
> (incident.inc_close_date>={27/12/2006}
> AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status =
> 'n')or(incident.inc_status = 'c'))
> ORDER BY sla.sla_sc"
>

complex query!

Hi,
I have Below query with me. but it's showing me error i.e "[Microsoft]
[ODBC SQL Server Driver]Syntax error or access violation"
I am not able to understand what to do exactly ' Please help
Query:
----
SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
incident.inc_resolve_act,
incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
incident.date_logged,
incident.time_to_resolve, inc_data.total_service_time,
incident.inc_resolve_sla, inc_cat.inc_cat_sc
FROM (((incident INNER JOIN inc_data ON
incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
AND
usr_group.usr_group_sc='" & SERVICE DESK & "' AND
((incident.date_logged>={27/12/2006)
AND incident.date_logged<{29/12/2006}) OR
(incident.inc_close_date>={27/12/2006}
AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status =
'n')or(incident.inc_status = 'c'))
ORDER BY sla.sla_sc"
---Instead of braces, enclose your date literals in single quotes. Braces are
used as the ODBC escape sequence.
Hope this helps.
Dan Guzman
SQL Server MVP
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1176547837.456693.297880@.y80g2000hsf.googlegroups.com...
> Hi,
> I have Below query with me. but it's showing me error i.e "[Microsoft]
> [ODBC SQL Server Driver]Syntax error or access violation"
> I am not able to understand what to do exactly ' Please help
> Query:
> ----
> SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
> incident.inc_resolve_act,
> incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
> incident.date_logged,
> incident.time_to_resolve, inc_data.total_service_time,
> incident.inc_resolve_sla, inc_cat.inc_cat_sc
> FROM (((incident INNER JOIN inc_data ON
> incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
> ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
> incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
> incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
> assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
> AND
> usr_group.usr_group_sc='" & SERVICE DESK & "' AND
> ((incident.date_logged>={27/12/2006)
> AND incident.date_logged<{29/12/2006}) OR
> (incident.inc_close_date>={27/12/2006}
> AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status =
> 'n')or(incident.inc_status = 'c'))
> ORDER BY sla.sla_sc"
> ---
>|||Try this:-
Some of the braces and { braces are not required:-
SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
incident.inc_resolve_act,
incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
incident.date_logged,
incident.time_to_resolve, inc_data.total_service_time,
incident.inc_resolve_sla, inc_cat.inc_cat_sc
FROM incident INNER JOIN inc_data ON
incident.incident_id=inc_data.incident_id INNER JOIN assyst_usr
ON incident.assyst_usr_id=assyst_usr.assyst_usr_id INNER JOIN sla ON
incident.sla_id=sla.sla_id INNER JOIN inc_cat ON
incident.inc_cat_id=inc_cat.inc_cat_id INNER JOIN usr_group ON
assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
AND
usr_group.usr_group_sc='" & SERVICE DESK & "' AND
((incident.date_logged>='27/12/2006')
AND incident.date_logged<'29/12/2006') OR
(incident.inc_close_date>='27/12/2006'
AND incident.inc_close_date<'27/12/2006')OR(incident.inc_status ='n')
or(incident.inc_status = 'c')
ORDER BY sla.sla_sc
Thanks
Hari
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1176547837.456693.297880@.y80g2000hsf.googlegroups.com...
> Hi,
> I have Below query with me. but it's showing me error i.e "[Microsoft]
> [ODBC SQL Server Driver]Syntax error or access violation"
> I am not able to understand what to do exactly ' Please help
> Query:
> ----
> SELECT sla.sla_sc, incident.incident_id, incident.inc_resolve_due,
> incident.inc_resolve_act,
> incident.inc_close_date, incident.inc_status, usr_group.usr_group_sc,
> incident.date_logged,
> incident.time_to_resolve, inc_data.total_service_time,
> incident.inc_resolve_sla, inc_cat.inc_cat_sc
> FROM (((incident INNER JOIN inc_data ON
> incident.incident_id=inc_data.incident_id) INNER JOIN assyst_usr
> ON incident.assyst_usr_id=assyst_usr.assyst_usr_id) INNER JOIN sla ON
> incident.sla_id=sla.sla_id) INNER JOIN inc_cat ON
> incident.inc_cat_id=inc_cat.inc_cat_id) INNER JOIN usr_group ON
> assyst_usr.usr_group_id=usr_group.usr_group_id WHERE sla.sla_sc<>''
> AND
> usr_group.usr_group_sc='" & SERVICE DESK & "' AND
> ((incident.date_logged>={27/12/2006)
> AND incident.date_logged<{29/12/2006}) OR
> (incident.inc_close_date>={27/12/2006}
> AND incident.inc_close_date<{27/12/2006})OR(incident.inc_status =
> 'n')or(incident.inc_status = 'c'))
> ORDER BY sla.sla_sc"
> ---
>

Complex Query question. Using Sum and CAST operation.

Hi

Can onayone tell me what is wrong with the below code. Thank you,

SELECT P.FirstName, P.LastName, P.PortfolioID, P.PortfolioDescription, SC.Description, SC.SecurityID, SC.SecurityTypeID, SUM(T.Quantity) AS TQuantity,
CASE SC.SecurityTypeID WHEN 11 THEN CAST(SUM(T .Quantity)) WHEN 2 THEN CAST(SUM(T .Quantity *
(SELECT Price
FROM dbo.SecurityPrices AS SP WITH (Nolock)
WHERE T .SymbolID = SP.SecurityID AND PriceDate = '1/24/2007'))) / 100 ELSE CAST(SUM(T .Quantity *
(SELECT Price
FROM dbo.SecurityPrices AS SP WITH (Nolock)
WHERE T .SymbolID = SP.SecurityID AND PriceDate = '1/24/2007'))) END AS Amount, P.AccountNumber, P.AccountTypeDescription,
SC.CodeDescription, SC.CodeDescription2, SC.Symbol
FROM dbo.Transactions AS T WITH (Nolock) INNER JOIN
dbo.Portfolios AS P WITH (Nolock) ON P.PortfolioID = T.PortfolioID INNER JOIN
dbo.SFGSecuritiesAndCodes AS SC WITH (Nolock) ON T.SymbolID = SC.SecurityID INNER JOIN
dbo.Groups AS G WITH (Nolock) ON P.PortfolioID = G.PortfolioID
WHERE (T.StatusTypeID = 100) AND (G.OwningPortfolioID = 270) AND (T.TradeDate <= '1/24/2007')
GROUP BY T.SymbolID, P.FirstName, P.LastName, P.PortfolioID, SC.Description, SC.SecurityID, SC.SecurityTypeID, P.PortfolioDescription, P.AccountNumber,
P.AccountTypeDescription, SC.CodeDescription, SC.CodeDescription2, SC.Symbol

The correct syntax for CAST is CAST( <expr> as <datatype>). So fix your query and try again. Also, it is good to post the actual error message that you are getting so that we don't have to guess what might be wrong.|||

The following query will work for you..

Note: You Cannot perform an aggregate function on an expression containing an aggregate or a subquery; So you have to use INNER JOIN Instead of Subquery. It is good practice to use join rather than the subquery.

SELECT
P.FirstName,
P.LastName,
P.PortfolioID,
P.PortfolioDescription,
SC.Description,
SC.SecurityID,
SC.SecurityTypeID,
SUM(T.Quantity) AS TQuantity,
CASE SC.SecurityTypeID
WHEN 11 THEN
CAST(SUM(T .Quantity) As Int)
WHEN 2 THEN
CAST(SUM(T .Quantity * SP.Price) / 100 as INT)
ELSE
CAST(SUM(T .Quantity * SP.Price) as INT)
END AS Amount,
P.AccountNumber,
P.AccountTypeDescription,
SC.CodeDescription,
SC.CodeDescription2,
SC.Symbol
FROM
dbo.Transactions AS T WITH (Nolock)
INNER JOIN dbo.Portfolios AS P WITH (Nolock) ON P.PortfolioID = T.PortfolioID
INNER JOIN dbo.SFGSecuritiesAndCodes AS SC WITH (Nolock) ON T.SymbolID = SC.SecurityID
INNER JOIN dbo.Groups AS G WITH (Nolock) ON P.PortfolioID = G.PortfolioID
INNER JOIN dbo.SecurityPrices As SP WITH (Nolock) ON T.SymbolID = SP.SecurityID AND SP.PriceDate = '1/24/2007'
WHERE
(T.StatusTypeID = 100)
AND (G.OwningPortfolioID = 270)
AND (T.TradeDate <= '1/24/2007')
GROUP BY
T.SymbolID,
P.FirstName,
P.LastName,
P.PortfolioID,
SC.Description,
SC.SecurityID,
SC.SecurityTypeID,
P.PortfolioDescription,
P.AccountNumber,
P.AccountTypeDescription,
SC.CodeDescription,
SC.CodeDescription2,
SC.Symbol

complex query help - count

Hi,
I am trying to add an aggregate function to the query below but I am not
able to get the intended results. I want to get the count to return total
downloads by each user but my query returns the total downloads by all users
.
Current Output
1,ttt,rrr,6/1/3005,30
2,ddd,jjj,5/31/2005,30
3,ppp,yyy,5/20/2005,30
Desired Output
1,ttt,rrr,6/1/3005,15
2,ddd,jjj,5/31/2005,5
3,ppp,yyy,5/20/2005,10
QUERY:
select distinct spl. [main_id],fname,lname,subscription_ends,
count(download_id)
from
main m,
subscribers spl,
downloads
where
m.main_id = spl.main_id
and
spl.confnum like 'T12%'
and
subscription_ends > = Getdate()
group by
spl.[main_id],
fname,lname,subscription_ends
order by
subscription_ends DESC
CREATE TABLE [dbo].[Main] (
[main_id] [int] IDENTITY (1, 1) NOT NULL ,
[fname] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lname] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
GO
CREATE TABLE [dbo].[subscribers] (
[main_id] [numeric](18, 0) NOT NULL ,
[confnum] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[subscription_starts] [datetime] NOT NULL
)
GO
CREATE TABLE [dbo].[Downloads] (
[main_id] [numeric](18, 0) NOT NULL ,
[download_id] [numeric](18, 0) NOT NULL
)
GOMike,
Try this,
select distinct spl. [main_id],fname,lname,subscription_ends,
count(main_id)
from
main m,
subscribers spl,
downloads
where
m.main_id = spl.main_id
and
spl.confnum like 'T12%'
and
subscription_ends > = Getdate()
and downloads.main_id = m.main_id
group by
spl.[main_id],
fname,lname,subscription_ends
order by
subscription_ends DESC
Thanks
"Mike" wrote:

> Hi,
> I am trying to add an aggregate function to the query below but I am not
> able to get the intended results. I want to get the count to return total
> downloads by each user but my query returns the total downloads by all use
rs.
> Current Output
> 1,ttt,rrr,6/1/3005,30
> 2,ddd,jjj,5/31/2005,30
> 3,ppp,yyy,5/20/2005,30
>
> Desired Output
> 1,ttt,rrr,6/1/3005,15
> 2,ddd,jjj,5/31/2005,5
> 3,ppp,yyy,5/20/2005,10
> QUERY:
> select distinct spl. [main_id],fname,lname,subscription_ends,
> count(download_id)
> from
> main m,
> subscribers spl,
> downloads
> where
> m.main_id = spl.main_id
> and
> spl.confnum like 'T12%'
> and
> subscription_ends > = Getdate()
> group by
> spl.[main_id],
> fname,lname,subscription_ends
> order by
> subscription_ends DESC
> CREATE TABLE [dbo].[Main] (
> [main_id] [int] IDENTITY (1, 1) NOT NULL ,
> [fname] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [lname] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> GO
>
> CREATE TABLE [dbo].[subscribers] (
> [main_id] [numeric](18, 0) NOT NULL ,
> [confnum] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [subscription_starts] [datetime] NOT NULL
> )
> GO
> CREATE TABLE [dbo].[Downloads] (
> [main_id] [numeric](18, 0) NOT NULL ,
> [download_id] [numeric](18, 0) NOT NULL
> )
> GO
>

Complex Query

Hello,
I am having trouble in getting the expected results. I have two tables as below. I need Idno,transdt,cd,cycdt,amt from joining two tables. The criteria is that if the transdt greater than same month of cycdt then we need get the next month cycdt and corresponding amount for that, if it is less than or equal to same months cycdt then get the same months cycdt and amt. Cd is dummy field which can be anything. I am using sqlserver 8.0

table1
idno,trandt,cd
12345,04/15/2005,cd1
12345,04/15/2005,cd2
12345,04/22/2005,cd3
12345,07/03/2005,cd4
12345,09/10/2005,cd5
3421,03/05/2005,cd6
3421,05/06/2005,cd7
3421,07/04/2005,cd8
3421,07/15/2005,cd9
3421,09/15/2005,cd10

idno,cycdt,amt
12345,02/10/2005,15.43
12345,03/13/2005,40.84
12345,04/18/2005,10.10
12345,05/24/2005,13.00
12345,06/16/2005,20.89
12345,07/18/2005,12.12
12345,08/17/2005,10.89
12345,09/17/2005,12.87
12345,10/16/2005,13.89
3421,05/10/2005,15.00
3421,06/11/2005,20.00
3421,07/11/2005,14.15
3421,08/12/2005,15.54

Expected result.
12345,04/15/2005,cd1,04/18/2005,10.10
12345,04/15/2005,cd2,04/18/2005,10.10
12345,04/22/2005,cd3,05/24/2005,13.00
12345,07/03/2005,cd4,07/18/2005,12.12
12345,09/10/2005,cd5,09/17/2005,12.87
3421,05/06/2005,cd7,05/10/2005,15.00
3421,07/04/2005,cd8,07/11/2005,14.15
3421,07/15/2005,cd9,08/12/2005,15.54

I really appreciate if someone can give solution for this using a query (SQL server,Access,Foxpro) is fine.

I am able to do this oracle using count(trandt) over(partition trandt order by 1) as cnt.

and cnt=1.

If u want I can post that too for getting any idea.

I need more information. I would assume that idno is the primary key on both tables, except that it is not unique for each row. If the key is the idno and the date column on each table, then you will not be able to perform this query as you won't be able to join the two tables together. I will attempt the query, but I am not sure it is what you want without knowing more information. Please reply to let me know whether this answere your question, or whether you need more help. As you either want the current month's cycdt or the next months, I suggest join to two copies of the second table, one joining on the same month, one joining on the subsequent month:

select

case t1.transdt > t2a.cycdt

then t2b.cycdt -- get next month's cycdt

else t2a.cycdt -- get this month's cycdt

end as cycdt,

case t1.transdt > t2a.cycdt

then t2b.amt -- get next month's amt

else t2a.amt -- get next month's amt

end as amt

from table1 as t1

join table2 as t2a -- current month join

on t2a.idno = t1.idno and month(t2a.cycdt) = month(t1.transdt)

join table2 as t2b -- joins to next month.

on t2b.idno = t1.idno and month(t2b.cycdt) = month(t1.transdt) + 1

-- The second join may need to be an outer join, as will restrict result set to

-- include only dates upto the month before the latest month. I don't know if

-- this is going to be a problem for you

For more T-SQL tips and advice, visit my blog:

|||

Whoops, forgot the first three columns.

select

t1.Idno,

t1.transdt,

t1.cd,

case

when t1.transdt > t2a.cycdt then t2b.cycdt -- get next month's cycdt

else t2a.cycdt -- get this month's cycdt

end as cycdt,

case when t1.transdt > t2a.cycdt then t2b.amt -- get next month's amt

else t2a.amt -- get next month's amt

end as amt

from table1 as t1

join table2 as t2a -- current month join

on t2a.idno = t1.idno and month(t2a.cycdt) = month(t1.transdt)

join table2 as t2b -- joins to next month.

on t2b.idno = t1.idno and month(t2b.cycdt) = month(t1.transdt) + 1

|||

First, I really appreciate you in taking time to write the query.

Basically, its not a formal table but just set of two result set and need to produce report out of it as one time. so, I am not making it as any primary key or something. But Idno is the main link between the tables.

Also, this is test tables as my original tables have 3million in table1 and 500k in table2.

The solution you had given works fine as long as there are no missing cycdt in between. But if one cycdt misses the query is not pulling the proper records.

(E.g) try removing the record 12345,05/24/05 from table2.

Let me know if you need more info.

I have this in Oracle but not able to use "Over - Partition" thing since I am using SQL server 8.0

Here it is in Oracle.

SELECT IDNO,TRANDT,IDNO1,CYCDT,AMT FROM
(SELECT IDNO,TRANDT,IDNO1,CYCDT,AMT,COUNT(TRANDT) OVER(PARTITION BY TRANDT ORDER BY 1) CNT
FROM
( SELECT T1.IDNO IDNO,T1.TRANDT,T2.IDNO IDNO1,T2.CYCDT,T2.AMT
FROM tab1 T1,tab2 T2 WHERE T1.IDNO=T2.IDNO )
WHERE (TO_CHAR(TRANDT,'MM') = TO_CHAR(CYCDT,'MM') AND
TO_CHAR(TRANDT,'DD') < TO_CHAR(CYCDT,'DD')) OR
(TO_NUMBER(TO_CHAR(TRANDT,'MM'))+1 = TO_CHAR(CYCDT,'MM'))
ORDER BY 1,2,4
)
WHERE TO_CHAR(TRANDT,'MM') = TO_CHAR(CYCDT,'MM') OR CNT=1ORDER BY 1 DESC,2,4

|||Use LEFT OUTER JOINS on both the JOINS instead, in which case when cycdt is null, then the columns from the second table will be null. You can then test for this using ISNULL, and specify the value to return in this case.

Complex Query

Need to create a SELECT statement which is above my skills. There are two
tables involved, both are shown below. Table 1 stores messages to be
delivered to a user on a web page. Messages can be sent to a specific set o
f
users by setting BlockList to false and adding each user to Table 2 or to al
l
users by setting BlockList to true and adding no users to Table 2. Messages
can be active until acknowledged (EndDate is null) or to a specific EndDate.
The query must return all active messages for a specific user name (the only
parameter). A message should be returned if BlockList is false and the user
is listed, or BlockList is true and the user is not listed. In addition, if
there is an end date it must not be passed and if BlockList is false (meanin
g
the user list was established as a list of recipients) then the Recieved dat
e
on Table 2 must be null indicating the user has not yet received the message
.
I am open to table architecture changes which might allow the same
information to be conveyed in a simpler manner if those more experienced
think that is better than writing a query to extract this information.
Table 1: IntranetMessages
_Column Name_ _Type_ _Allow Null_
MessageID (PK) uniqueidentifier No
Text nvarchar(MAX) No
URL nvarchar(MAX) No
Tooltip nvarchar(MAX) No
BlockList bit No
IssueDate DateTime No
EndDate DateTime Yes
Table 2: IntranetMessageRecipients
_Column Name_ _Type_ _Allow Null_
RecID (PK) uniqueidentifier No
MessageID (FK) uniqueidentifier No
Recipient nvarchar(MAX) No
Received datetime Yesyou'll need a reference from the IntranetMessageRecipients to the
IntranetMessages to achieve this, aka foreign key.
is this possible?

> _Column Name_ _Type_ _Allow Null_
RecID int No
> MessageID (PK) uniqueidentifier No
> Text nvarchar(MAX) No
> URL nvarchar(MAX) No
> Tooltip nvarchar(MAX) No
> BlockList bit No
> IssueDate DateTime No
> EndDate DateTime Yes
you could insert a default value for those messages that do not have a
recipient.

> Table 2: IntranetMessageRecipients

> _Column Name_ _Type_ _Allow Null_
> RecID (PK) uniqueidentifier No
> MessageID (FK) uniqueidentifier No
> Recipient nvarchar(MAX) No
> Received datetime Yes|||Sorry, I marked the MessageID field in the IntranetMessageRecipients table a
s
a Foreign Key but didn't explicitely say that it corresponds to the MessageI
D
(PK) in the IntranetMessages table. That is how it is setup, however, if
that is what you mean.
"Gerard" wrote:

> you'll need a reference from the IntranetMessageRecipients to the
> IntranetMessages to achieve this, aka foreign key.
> is this possible?
>
> RecID int No
> you could insert a default value for those messages that do not have a
> recipient.
>
>
>|||SELECT im.MessageID, im.Text, im.URL, im.Tooltip, @.passedInUser as Recipient
FROM IntranetMessages im
LEFT OUTER JOIN
IntranetMessageRecipients imr
ON im.MessageID = imr.MessageID
AND imr.Received IS NULL
WHERE CASE WHEN im.BlockList = 1 THEN Recipient ELSE @.passedInUser END =
@.passedInUser
AND im.EndDate IS NULL
A couple of thoughts on your schema. I would be careful with the use of
uniqueidentifiers as your primary keys. For several reasons:
1) I hope that you have the primary key as a clustered index. It is a good
rule of thumb to always have a clustered index
2) Uniqueidentifiers are typically bad choices as clustered indexes since
they are not ever increasing. This causes your table to become fragmented
quickly. If you use uniqueidentifiers as your clustered primary key, you
should make sure that you are rebuilding the index regularly.
Beyond that, your schema is not normalized since you could have the same
username in your intranetMessage table many times. It would make this a bit
cleaner if you had a table with all users that is referenced by the
Recipients table.
But, I think that my query should do the trick for you.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"MKing" wrote:

> Need to create a SELECT statement which is above my skills. There are two
> tables involved, both are shown below. Table 1 stores messages to be
> delivered to a user on a web page. Messages can be sent to a specific set
of
> users by setting BlockList to false and adding each user to Table 2 or to
all
> users by setting BlockList to true and adding no users to Table 2. Messag
es
> can be active until acknowledged (EndDate is null) or to a specific EndDat
e.
> The query must return all active messages for a specific user name (the on
ly
> parameter). A message should be returned if BlockList is false and the us
er
> is listed, or BlockList is true and the user is not listed. In addition,
if
> there is an end date it must not be passed and if BlockList is false (mean
ing
> the user list was established as a list of recipients) then the Recieved d
ate
> on Table 2 must be null indicating the user has not yet received the messa
ge.
> I am open to table architecture changes which might allow the same
> information to be conveyed in a simpler manner if those more experienced
> think that is better than writing a query to extract this information.
> Table 1: IntranetMessages
> _Column Name_ _Type_ _Allow Null_
> MessageID (PK) uniqueidentifier No
> Text nvarchar(MAX) No
> URL nvarchar(MAX) No
> Tooltip nvarchar(MAX) No
> BlockList bit No
> IssueDate DateTime No
> EndDate DateTime Yes
> Table 2: IntranetMessageRecipients
> _Column Name_ _Type_ _Allow Null_
> RecID (PK) uniqueidentifier No
> MessageID (FK) uniqueidentifier No
> Recipient nvarchar(MAX) No
> Received datetime Yes
>
>|||sorry misunderstanding on my part
maybe something like this will work (not tested)
select * from IntranetMessages
inner join IntranetMessageRecipients on
IntranetMessageRecipients.MessageID = IntranetMessages .MessageID
where
(BlockList = 0 and IntranetMessageRecipients.RecID = @.recp) or
(BlockList = 1 and IntranetMessageRecipients.RecID <> @.recp)
regards
Gerard|||Thanks Ryan, I will try this. Thanks for the tip on uniqueidentifiers... I
don't know what a clustered index is, but that's for me to look up and your
point about usernames is well taken.
"Ryan Powers" wrote:
> SELECT im.MessageID, im.Text, im.URL, im.Tooltip, @.passedInUser as Recipie
nt
> FROM IntranetMessages im
> LEFT OUTER JOIN
> IntranetMessageRecipients imr
> ON im.MessageID = imr.MessageID
> AND imr.Received IS NULL
> WHERE CASE WHEN im.BlockList = 1 THEN Recipient ELSE @.passedInUser END =
> @.passedInUser
> AND im.EndDate IS NULL
> A couple of thoughts on your schema. I would be careful with the use of
> uniqueidentifiers as your primary keys. For several reasons:
> 1) I hope that you have the primary key as a clustered index. It is a goo
d
> rule of thumb to always have a clustered index
> 2) Uniqueidentifiers are typically bad choices as clustered indexes since
> they are not ever increasing. This causes your table to become fragmented
> quickly. If you use uniqueidentifiers as your clustered primary key, you
> should make sure that you are rebuilding the index regularly.
> Beyond that, your schema is not normalized since you could have the same
> username in your intranetMessage table many times. It would make this a b
it
> cleaner if you had a table with all users that is referenced by the
> Recipients table.
> But, I think that my query should do the trick for you.
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "MKing" wrote:
>|||Let me know if it works.
Also, if you need me to point you to a couple of sites about Clustered
indexes and tuning, let me know.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"MKing" wrote:
> Thanks Ryan, I will try this. Thanks for the tip on uniqueidentifiers...
I
> don't know what a clustered index is, but that's for me to look up and you
r
> point about usernames is well taken.
> "Ryan Powers" wrote:
>|||If you don't mind a few references are always helpful. I am just getting my
feet wet in SQL Server so I have a lot to learn.
Your query got me 90% of the way there and once I read up on CASE and LEFT
OUTER JOIN statements to see what was going on I got it to do what I needed.
The end result follows:
SELECT im.MessageID, im.BlockList, imr.Recipient
FROM dbo.IntranetMessages AS im LEFT OUTER JOIN
dbo.IntranetMessageRecipients AS imr ON im.MessageID =
imr.MessageID AND imr.Received IS NULL
WHERE (im.EndDate IS NULL OR
im.EndDate > GETDATE()) AND (CASE WHEN im.BlockList =
0 THEN (CASE WHEN Recipient = 'odehengineers\kingm' THEN 1 ELSE 0 END)
ELSE (CASE WHEN (Recipient IS NULL OR
Recipient <> 'odehengineers\kingm') THEN 1 ELSE 0 END)
END = 1)
"Ryan Powers" wrote:
> Let me know if it works.
> Also, if you need me to point you to a couple of sites about Clustered
> indexes and tuning, let me know.
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "MKing" wrote:
>|||Sorry, I had replaced the parameters for testing. The real version is:
SELECT im.MessageID
FROM dbo.IntranetMessages AS im LEFT OUTER JOIN
dbo.IntranetMessageRecipients AS imr ON
im.MessageID = imr.MessageID AND imr.Received IS NULL
WHERE (im.EndDate IS NULL OR
im.EndDate > GETDATE()) AND (CASE WHEN
im.BlockList = 0 THEN (CASE WHEN Recipient = @.USER THEN 1 ELSE 0 END)
ELSE (CASE WHEN (Recipient IS NULL OR
Recipient <> @.USER) THEN 1 ELSE 0 END) END = 1)
"MKing" wrote:
> If you don't mind a few references are always helpful. I am just getting
my
> feet wet in SQL Server so I have a lot to learn.
> Your query got me 90% of the way there and once I read up on CASE and LEFT
> OUTER JOIN statements to see what was going on I got it to do what I neede
d.
> The end result follows:
> SELECT im.MessageID, im.BlockList, imr.Recipient
> FROM dbo.IntranetMessages AS im LEFT OUTER JOIN
> dbo.IntranetMessageRecipients AS imr ON im.MessageID
=
> imr.MessageID AND imr.Received IS NULL
> WHERE (im.EndDate IS NULL OR
> im.EndDate > GETDATE()) AND (CASE WHEN im.BlockList
=
> 0 THEN (CASE WHEN Recipient = 'odehengineers\kingm' THEN 1 ELSE 0 END)
> ELSE (CASE WHEN (Recipient IS NULL OR
> Recipient <> 'odehengineers\kingm') THEN 1 ELSE 0 EN
D)
> END = 1)
> "Ryan Powers" wrote:
>|||Glad you got it to work.
I was trying to figure out why what I gave you did not work right and I see
two things. Because I think you can do something a bit cleaner than your
final solution.
1) It looks like you are saying match the recipient when blocklist is 0
instead of 1. Can you explain that (just for my curiousity)? Is it true tha
t
you want to look the recipient up when BlockList = 0? Is it also true that
the message will have no rows in the Recipient table when BlockList = 1?
2) I failed to include rows with end date later than today.
As far as references go, I prefer watching presentations/ webcasts. There
is a great one by Kimberly Tripp on index best practices that would give you
a great background on clustered indexes. It is focused on SQL 2005, but the
concepts transcend the releases. You can find it here.
http://www.microsoft.com/uk/technet...aspx?videoid=29
Beyond that, here are a couple good articles.
http://www.sql-server-performance.c..._structures.asp
http://www.sql-server-performance.c...red_indexes.asp
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"MKing" wrote:
> Sorry, I had replaced the parameters for testing. The real version is:
> SELECT im.MessageID
> FROM dbo.IntranetMessages AS im LEFT OUTER JOIN
> dbo.IntranetMessageRecipients AS imr ON
> im.MessageID = imr.MessageID AND imr.Received IS NULL
> WHERE (im.EndDate IS NULL OR
> im.EndDate > GETDATE()) AND (CASE WHEN
> im.BlockList = 0 THEN (CASE WHEN Recipient = @.USER THEN 1 ELSE 0 END)
> ELSE (CASE WHEN (Recipient IS NULL OR
> Recipient <> @.USER) THEN 1 ELSE 0 END) END = 1
)
> "MKing" wrote:
>