Query/View: The 2 newest periods for each indicator

Hi,

I'm working on a simple performance-program, where I need to extract
information from the 2 newest periods for every performance-indicator
- And from there calculate a trend between these results.

The problem is, that I can't find a simple way to extract the 2 latest
results.

The Table (Table1) looks like this:
kpiIDperiodIDActual
Acceleration23
Acceleration54
Speed1100
Speed4200
Speed7220
Speed9180
Weight122
Weight332
Weight721
Weight1033

If I want to extract the newest I use something like this (made it in
MS Access, so the syntax might differ slightly from SQLServer):

SELECT table1.kpiID, table1.periodID, table1.Actual
FROM table1 WHERE table1.periodID = (SELECT max(t.periodID) from
table1 as t WHERE t.kpiID=table1.kpiID);

BUT - how how do I get the second-newest period as well?

Preferably I would like the final result to be a View with the
following fields:
kpiID, periodID_newest, Actual_newest, periodID_sec_newest,
Actual_sec_newest

Alternatively a View with 2 posts for each performace-indicator.

Thanks in advance
RyanOn Fri, 24 Mar 2006 23:08:18 +0100, Ryan Dahl wrote:

>Hi,
>I'm working on a simple performance-program, where I need to extract
>information from the 2 newest periods for every performance-indicator
>- And from there calculate a trend between these results.
>The problem is, that I can't find a simple way to extract the 2 latest
>results.
>The Table (Table1) looks like this:
>kpiIDperiodIDActual
>Acceleration23
>Acceleration54
>Speed1100
>Speed4200
>Speed7220
>Speed9180
>Weight122
>Weight332
>Weight721
>Weight1033
>If I want to extract the newest I use something like this (made it in
>MS Access, so the syntax might differ slightly from SQLServer):
>SELECT table1.kpiID, table1.periodID, table1.Actual
>FROM table1 WHERE table1.periodID = (SELECT max(t.periodID) from
>table1 as t WHERE t.kpiID=table1.kpiID);
>BUT - how how do I get the second-newest period as well?

Hi Ryan,

SELECT a.kpiID, a.periodID, a.Actual
FROM table1 AS a
WHERE (SELECT COUNT(*)
FROM table1 AS b
WHERE b.kpiID = a.kpiID
AND b.periodID >= a.periodID) <= 2

>Preferably I would like the final result to be a View with the
>following fields:
>kpiID, periodID_newest, Actual_newest, periodID_sec_newest,
>Actual_sec_newest

In that case, try this instead:

SELECT a.kpiID, a.periodID, a.Actual, b.periodID, b.Actual
FROM table1 AS a
LEFT JOIN table1 AS b
ON b.kpiID = a.kpiID
AND b.periodID = (SELECT MAX(c.periodID)
FROM table1 AS c
WHERE c.kpiID = a.kpiID
AND c.periodID < a.periodID)
WHERE a.periodID = (SELECT MAX(t.periodID)
FROM table1 AS t
WHERE t.kpiID = a.kpiID)

(Both queries above are untested - see www.aspfaq.com/5006 if you prefer
a tested reply).

--
Hugo Kornelis, SQL Server MVP|||Hi Hugo,

Thanks a lot. I got them both working without any hassle.

>SELECT a.kpiID, a.periodID, a.Actual
>FROM table1 AS a
>WHERE (SELECT COUNT(*)
> FROM table1 AS b
> WHERE b.kpiID = a.kpiID
> AND b.periodID >= a.periodID) <= 2

I find this to be quite clever - had to look at it some time to figure
out how it works.

>>
>>Preferably I would like the final result to be a View with the
>>following fields:
>>kpiID, periodID_newest, Actual_newest, periodID_sec_newest,
>>Actual_sec_newest
>In that case, try this instead:
>SELECT a.kpiID, a.periodID, a.Actual, b.periodID, b.Actual
>FROM table1 AS a
>LEFT JOIN table1 AS b
> ON b.kpiID = a.kpiID
> AND b.periodID = (SELECT MAX(c.periodID)
> FROM table1 AS c
> WHERE c.kpiID = a.kpiID
> AND c.periodID < a.periodID)
>WHERE a.periodID = (SELECT MAX(t.periodID)
> FROM table1 AS t
> WHERE t.kpiID = a.kpiID)
Works as well - minor adjustment needed: Move lines 5-8 to the end.

Regards
Ryan|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

I am a little confused on this. Aren't "acceleration", "speed", and
"weight" attributes and not values? Surely you are not mixing
meteadata and data.|||celko, have you actually ever HELPED anyone on this list???

i'd be curious to review a link where your original SQL code, written
in the past 10 years, is deomonstrated.

thx,
doug|||On Sat, 25 Mar 2006 08:21:38 +0100, Ryan Dahl wrote:

>>SELECT a.kpiID, a.periodID, a.Actual, b.periodID, b.Actual
>>FROM table1 AS a
>>LEFT JOIN table1 AS b
>> ON b.kpiID = a.kpiID
>> AND b.periodID = (SELECT MAX(c.periodID)
>> FROM table1 AS c
>> WHERE c.kpiID = a.kpiID
>> AND c.periodID < a.periodID)
>>WHERE a.periodID = (SELECT MAX(t.periodID)
>> FROM table1 AS t
>> WHERE t.kpiID = a.kpiID)
>>
>Works as well - minor adjustment needed: Move lines 5-8 to the end.

Hi Ryan,

That changes the meaning of the query - the place where you put those
lines dictates what will happen for a kpiID that has only one row.

This one row is by definition the latest - but there's no second latest.
If you use the query I suggested, you'll get this kpiID in your result,
with it's only row as last measurement and NULLs as it's second latest
measurement.

Your version (after moving those rows) will exclude any kpiID with only
one row. Only kpiIDs with two or more measurements will be displayed. If
that is indeed your requirement, then you can safely move these lines.
And you can change the LEFT JOIN in an INNER JOIN as well, to get some
performance gain.

--
Hugo Kornelis, SQL Server MVP|||On Mon, 27 Mar 2006 23:48:50 +0200, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALID> wrote:

>On Sat, 25 Mar 2006 08:21:38 +0100, Ryan Dahl wrote:
>>>SELECT a.kpiID, a.periodID, a.Actual, b.periodID, b.Actual
>>>FROM table1 AS a
>>>LEFT JOIN table1 AS b
>>> ON b.kpiID = a.kpiID
>>> AND b.periodID = (SELECT MAX(c.periodID)
>>> FROM table1 AS c
>>> WHERE c.kpiID = a.kpiID
>>> AND c.periodID < a.periodID)
>>>WHERE a.periodID = (SELECT MAX(t.periodID)
>>> FROM table1 AS t
>>> WHERE t.kpiID = a.kpiID)
>>>
>>Works as well - minor adjustment needed: Move lines 5-8 to the end.
>Hi Ryan,
>That changes the meaning of the query - the place where you put those
>lines dictates what will happen for a kpiID that has only one row.
>This one row is by definition the latest - but there's no second latest.
>If you use the query I suggested, you'll get this kpiID in your result,
>with it's only row as last measurement and NULLs as it's second latest
>measurement.
>Your version (after moving those rows) will exclude any kpiID with only
>one row. Only kpiIDs with two or more measurements will be displayed. If
>that is indeed your requirement, then you can safely move these lines.
>And you can change the LEFT JOIN in an INNER JOIN as well, to get some
>performance gain.

Hi Hugo,

thanks for pointing this out. SQLServer accepted without any problems.
As mentioned earlier I tested on MS Access, and it seems that it
doesn't support this join-type (no error-description of any kind) so I
made the mistake of assuming there was a small error in the
sql-string.

Thanks again.
Ryan|||I've read 2 books written by joe celko, and and both have really helped
me. I frequently profile my code to see what it is doing. So he's
helped me.|||Go to the CMP archives for DBMS, DATABASE PROGRAMMING & DESIGN, and
INTELLIGENT ENTERPRISE magazines to go back over ten years. I have
written over 750 columns in the computer trade and academic press,
mostly dealing with data and databases. I currently write for BMC's
DBAzine.com e-magazine.

My six books: SQL FOR SMARTIES (Morgan-Kaufmann, 1995, second edition
1999, third edition 2005), SQL PUZZLES & ANSWERS (Morgan-Kaufmann,
1997), DATA & DATABASES (Morgan-Kaufmann, 1999) and TREES & HIERARCHIES
IN SQL (Morgan-Kaufmann, 2004) and SQL PROGRAMMING STYLE
Morgan-Kaufmann, 2005).
Past magazine columns include: "SQL Explorer" in DBMS (Miller-Freeman);
"Celko on SQL" in DATABASE PROGRAMMING & DESIGN (Miller-Freeman);
"WATCOM SQL Corner" in POWERBUILDER DEVELOPERS' JOURNAL (SysCon); "SQL
Puzzle" in BOXES & ARROWS (Frank Sweet Publishing); "DBMS/Report" in
SYSTEMS INTEGRATION (Cahner-Ziff); "Data Desk" in TECH SPECIALIST
(R&D); "Data Points" in PC TECHNIQUES (Coriolis Group); "Celko on
Software" in COMPUTING (VNC Publications, UK), "SELECT * FROM Austin"
(Array Publications, The Netherlands), and he was editor for the
"Puzzles & Problems" section of ABACUS (Springer-Verlag) and I ran the
CASEFORUM section 18, "Celko on SQL", on CompuServe.

So, what have you done?|||if it is SQL Server 2005, use row_number() *untested*:

select * from(
SELECT table1.kpiID, table1.periodID, table1.Actual,
row_number() over(partition by kpId order by periodId desc) rn
FROM table1
) t
where rn<3|||That's Joe Celko, the demi-god of SQL for pete's sake.sql

Query written in CODE part is not working

Hi
I have a report in which the Dataset is filled using MDX query and for every row in the DataSet the Function in the CODE is called, which in turn Connects to the DB and Selects a particular value. The data set is getting filled but the CODE (which contains the SQL Select query) is not getting executed.
I have references System.data, System.data.SQLClient, System.data.Xml dlls for a RDL and have written a connection string with UID and Pwd and the userid has sufficient permissions.
When i Preview it(using visual studio), the value(O/P, the expected value) is shown in the report but i dont see any query executed in the SQL Profiler. Also when i preview this report through the report viewer control from an Window Application, i dont get the output. I dont seem to figure out what the problem is. Please HELP!!

Thanking you in advance.
Regards
Sai
Not sure if I undestand your scenario well. Do you have a VB.NET function embedded in the report? If so, most likely the function is erroring out or you are facing a security issue. What I'd suggest is moving the code to an external .NET assembly. Then, set your report as a startup item on the project properties. Put a breakpoint in the custom function and hit F5 to load the report in the Report Host. When the report is run, the breakpoint should be hit from the first dataset row and you should be able to troubleshoot what's wrong.sql

Query written in CODE part is not working

Hi
I have a report in which the Dataset is filled using MDX query and for every row in the DataSet the Function in the CODE is called, which in turn Connects to the DB and Selects a particular value. The data set is getting filled but the CODE (which contains the SQL Select query) is not getting executed.

I have references System.data, System.data.SQLClient, System.data.Xml

dlls for a RDL and have written a connection string with UID and Pwd

and the userid has sufficient permissions.
When i Preview it(using visual studio), the value(O/P, the expected value) is shown in the report but i dont see any query executed in the SQL Profiler. Also when i preview this report through the report viewer control from an Window Application, i dont get the output. I dont seem to figure out what the problem is. Please HELP!!

Thanking you in advance.
Regards
SaiNot sure if I undestand your scenario well. Do you have a VB.NET function embedded in the report? If so, most likely the function is erroring out or you are facing a security issue. What I'd suggest is moving the code to an external .NET assembly. Then, set your report as a startup item on the project properties. Put a breakpoint in the custom function and hit F5 to load the report in the Report Host. When the report is run, the breakpoint should be hit from the first dataset row and you should be able to troubleshoot what's wrong.

Query work in sql 200 fails in sql 2005

Hello all,
The query below, has been succesfully working in sql 2000 for months.
While I recongnize that the isnumeric attribute is reference twice
(this has since been corrected).
My concern is why did this generate an error in sql 2005 and not sql
2000.
Error = (Duplicate column names are not allowed in result sets obtained
through OPENQUERY and OPENROWSET.)
I am concerned that I've missed a server setting.
If this is just a case where it should of failed in 2000 I would feel
much better.
Any insight into this would be greatly appreciated.
Thanks,
Henry Lovera
--
insert into @.deal_properties (deal_property_id,
parent_deal_property_id, is_numeric, property_name, property_format,
display_order, string_value, numeric_value)
select
deal_property_id
,parent_deal_property_id
,is_numeric
,property_name
,property_format
,display_order
,string_value = case when is_numeric = 0 then value
end
,numeric_value = case when is_numeric = 1 then convert(float,
value) end
from
openxml(@.xml_doc, '//deal_property', 1)
with( deal_property_id int
,parent_deal_property_id int
,is_numeric int
,property_name varchar(32)
,property_format varchar(16)
,display_order int
,is_numeric bit
,value varchar(100))Henry
I was not able to test it because the script throws lots of errors.
Please post proper DDL+ sample data.
<hanklvr@.yahoo.com> wrote in message
news:1143684314.846726.121030@.t31g2000cwb.googlegroups.com...
> Hello all,
> The query below, has been succesfully working in sql 2000 for months.
> While I recongnize that the isnumeric attribute is reference twice
> (this has since been corrected).
> My concern is why did this generate an error in sql 2005 and not sql
> 2000.
> Error = (Duplicate column names are not allowed in result sets obtained
> through OPENQUERY and OPENROWSET.)
> I am concerned that I've missed a server setting.
> If this is just a case where it should of failed in 2000 I would feel
> much better.
> Any insight into this would be greatly appreciated.
> Thanks,
> Henry Lovera
> --
> insert into @.deal_properties (deal_property_id,
> parent_deal_property_id, is_numeric, property_name, property_format,
> display_order, string_value, numeric_value)
> select
> deal_property_id
> ,parent_deal_property_id
> ,is_numeric
> ,property_name
> ,property_format
> ,display_order
> ,string_value = case when is_numeric = 0 then value
> end
> ,numeric_value = case when is_numeric = 1 then convert(float,
> value) end
> from
> openxml(@.xml_doc, '//deal_property', 1)
> with( deal_property_id int
> ,parent_deal_property_id int
> ,is_numeric int
> ,property_name varchar(32)
> ,property_format varchar(16)
> ,display_order int
> ,is_numeric bit
> ,value varchar(100))
>

Query with a left outer join is not working as expected

The query below is suppose to join customer information with sales and vendo
r
information. An outer join and a union is used to insure I get all customer
s
and vendors within the selection criteria even though either entity may not
have sales. The query is:
SELECT
cs.customer_ident,
ss.Total_Cases,
ss.Total_Pounds,
ss.Total_Sales,
ss.Total_Actual,
ss.Total_Rep,
ss.Total_Profit,
ss.Total_Items,
cs.customer_num,
cs.customer_name,
cs.ship_to_address1,
cs.ship_to_county,
cs.ship_to_state,
cs.customer_phone1,
op.opco_desc,
ss.opco_vendor_num,
ss.opco_vendor_desc,
rp.salesrep_num,
rp.salesrep_name
FROM
dim_customer cs
LEFT OUTER JOIN
(SELECT
sh.customer_ident,
vn.opco_vendor_num,
vn.opco_vendor_desc,
vn.vendor_ident,
sum(sd.extended_cases) AS Total_Cases,
sum(sd.extended_sales) AS Total_Sales,
sum(sd.extended_cost) AS Total_Actual,
sum(sd.extended_sales_rep_cost) AS Total_Rep,
sum(sd.extended_pounds) AS Total_Pounds,
Count(DISTINCT sd.item_ident) As Total_Items,
sum(sd.extended_sales - sd.extended_Cost) As Total_Profit
FROM
fact_sales_header sh
INNER JOIN
fact_sales_detail sd
ON
sh.header_ident = sd.header_ident
INNER JOIN
dim_item it
ON
sd.item_ident = it.item_ident
INNER JOIN
dim_time tm
ON
sh.time_ident = tm.time_ident
INNER JOIN
dim_vendor vn
ON
it.vendor_ident = vn.vendor_ident
INNER JOIN
dim_customer cs1
ON
cs1.customer_ident = sh.customer_ident
INNER JOIN
dim_salesRep rp1
ON
cs1.sales_rep_ident = rp1.salesRep_ident
WHERE
(((tm.gl_year = 2004 AND tm.gl_period >= 4) OR tm.gl_year > 2004) and
((tm.gl_year =2005 AND tm.gl_period <=3) OR tm.gl_year < 2005)) AND
sh.opco_num = 125
and it.subCategory_num in (3932)
and cs1.abc_rating = 'A'
GROUP BY
vn.vendor_ident,
vn.opco_vendor_num,
sh.customer_ident,
vn.opco_vendor_desc
) ss
ON
cs.customer_ident = ss.customer_ident
INNER JOIN
dim_opco op
ON
cs.opco_num = op.opco_num
INNER JOIN
dim_salesRep rp
ON
cs.sales_rep_ident = rp.salesRep_ident
WHERE
cs.opco_num = 125 AND NOT exists
(SELECT
sh1.customer_ident,
sum(sd1.extended_cases) AS Cases_Test
FROM
fact_sales_header sh1
INNER JOIN
fact_sales_detail sd1
ON
sh1.header_ident = sd1.header_ident
INNER JOIN
dim_item it1
ON
sd1.item_ident = it1.item_ident
INNER JOIN
dim_time tm1
ON
sh1.time_ident = tm1.time_ident WHERE
sh1.customer_ident = cs.customer_ident
AND (((tm1.gl_year = 2004 AND tm1.gl_period >= 4) OR tm1.gl_year > 2004)
and ((tm1.gl_year =2005 AND tm1.gl_period <=3) OR tm1.gl_year < 2005)) AND
sh1.opco_num = 125
and it1.subCategory_num in (3932)
GROUP BY
sh1.customer_ident
HAVING
sum(sd1.extended_cases) > 10000)
and cs.abc_rating = 'A'
UNION ALL
SELECT Distinct
NULL as customer_ident,
NULL as Total_Cases,
NULL as Total_Pounds,
NULL as Total_Sales,
NULL as Total_Actual,
NULL as Total_Rep,
NULL as Total_Profit,
NULL as Total_Items,
NULL as customer_num,
NULL as customer_name,
NULL as ship_to_address1,
NULL as ship_to_county,
NULL as ship_to_state,
NULL as customer_phone1,
op.opco_desc,
vn.opco_vendor_num,
vn.opco_vendor_desc,
NULL as salesrep_num,
NULL as salesrep_name
FROM
dim_item it
INNER JOIN
dim_vendor vn
ON
it.vendor_ident = vn.vendor_ident
INNER JOIN
dim_opco op
ON
it.opco_num = op.opco_num
WHERE
it.opco_num = 125
and it.subCategory_num in (3932)
ORDER BY
cs.customer_ident,
opco_vendor_desc,
salesRep_name,
salesRep_num,
customer_num,
customer_name
Even though the result set from the left outer join query produces rows with
customer idents that match customer idents from the main select statement,
the sales data is not joined in (It looks like the query from the LEFT OUTER
JOIN returned no matching rows).
The interesting thing is if I comment out or remove the UNION ALL portion of
the query, the LEFT OUTER JOIN seems to work as I would expect.
I also change the LEFT OUTER JOIN to a FULL OUTER JOIN but did not make any
difference.
Does anyone have any idea what is happening here? Below are the DDL
statements for the tables involved:
CREATE TABLE [fact_sales_header] (
[header_ident] [int] IDENTITY (1, 1) NOT NULL ,
[opco_num] [int] NOT NULL ,
[time_ident] [int] NOT NULL ,
[customer_ident] [int] NOT NULL ,
[order_source] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[credit_invoice_flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[invoice_count] [int] NOT NULL ,
[invoice_line_count] [int] NOT NULL ,
[routed_orders] [int] NOT NULL ,
[published_ident] [int] NOT NULL ,
PRIMARY KEY CLUSTERED
(
[header_ident]
) ON [PRIMARY] ,
CONSTRAINT [un_fact_sales_header_01] UNIQUE NONCLUSTERED
(
[opco_num],
[time_ident],
[customer_ident],
[order_source],
[credit_invoice_flag]
) ON [PRIMARY] ,
FOREIGN KEY
(
[customer_ident]
) REFERENCES [dim_customer] (
[customer_ident]
),
FOREIGN KEY
(
[opco_num]
) REFERENCES [dim_opco] (
[opco_num]
),
FOREIGN KEY
(
[published_ident]
) REFERENCES [meta_published] (
[published_ident]
),
FOREIGN KEY
(
[time_ident]
) REFERENCES [dim_time] (
[time_ident]
)
) ON [PRIMARY]
GO
CREATE TABLE [fact_sales_detail] (
[detail_ident] [int] IDENTITY (1, 1) NOT NULL ,
[header_ident] [int] NOT NULL ,
[item_ident] [int] NOT NULL ,
[original_item_ident] [int] NULL ,
[special_handling_flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[credit_ident] [int] NULL ,
[extended_sales] [numeric](15, 2) NULL ,
[extended_cost] [numeric](15, 2) NULL ,
[extended_sales_rep_cost] [numeric](15, 2) NULL ,
[extended_cases] [numeric](19, 5) NULL ,
[extended_pounds] [numeric](15, 2) NULL ,
[market_cost] [numeric](15, 4) NULL ,
[invoice_line_count] [int] NOT NULL ,
CONSTRAINT [pk_fact_sales_detail] PRIMARY KEY NONCLUSTERED
(
[detail_ident]
) ON [PRIMARY] ,
FOREIGN KEY
(
[credit_ident]
) REFERENCES [dim_creditCode] (
[credit_ident]
),
FOREIGN KEY
(
[header_ident]
) REFERENCES [fact_sales_header] (
[header_ident]
),
FOREIGN KEY
(
[item_ident]
) REFERENCES [dim_item] (
[item_ident]
),
FOREIGN KEY
(
[original_item_ident]
) REFERENCES [dim_item] (
[item_ident]
)
) ON [PRIMARY]
GO
CREATE TABLE [dim_item] (
[item_ident] [int] IDENTITY (1, 1) NOT NULL ,
[opco_num] [int] NOT NULL ,
[opco_item_num] [int] NOT NULL ,
[item_desc] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF__dim_item__item_d__15A53433] DEFAULT ('Unknown'),
[item_desc2] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_item__item_d__1699586C] DEFAULT ('Unknown'),
[category_num] [numeric](4, 0) NOT NULL CONSTRAINT
[DF__dim_item__catego__178D7CA5] DEFAULT (0),
[category_desc] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_item__catego__1881A0DE] DEFAULT ('Unknown'),
[subCategory_num] [numeric](4, 0) NOT NULL CONSTRAINT
[DF__dim_item__subCat__1975C517] DEFAULT (0),
[subCategory_desc] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_item__subCat__1A69E950] DEFAULT ('Unknown'),
[brand_num] [numeric](5, 0) NOT NULL CONSTRAINT
[DF__dim_item__brand___1B5E0D89] DEFAULT (0),
[brand_key_word] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_item__brand___1C5231C2] DEFAULT ('Unknown'),
[brand_desc] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_item__brand___1D4655FB] DEFAULT ('Unknown'),
[brand_type_desc] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_item__brand___1E3A7A34] DEFAULT ('Unknown'),
[brand_type_num] [numeric](2, 0) NOT NULL CONSTRAINT
[DF__dim_item__brand___1F2E9E6D] DEFAULT (0),
[vendor_ident] [int] NULL CONSTRAINT [DF__dim_item__vendor__2116E6DF]
DEFAULT (0),
[pack] [numeric](4, 0) NULL ,
[size] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[common_item_num] [numeric](6, 0) NOT NULL CONSTRAINT
[DF__dim_item__common__220B0B18] DEFAULT (0),
[non_inv_item_code_flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL CONSTRAINT [DF__dim_item__non_in__22FF2F51] DEFAULT (''),
[common_item_desc] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF__dim_item__common__3AD6B8E2] DEFAULT ('Unknown'),
PRIMARY KEY CLUSTERED
(
[item_ident]
) ON [PRIMARY] ,
FOREIGN KEY
(
[opco_num]
) REFERENCES [dim_opco] (
[opco_num]
),
FOREIGN KEY
(
[vendor_ident]
) REFERENCES [dim_vendor] (
[vendor_ident]
)
) ON [PRIMARY]
GO
CREATE TABLE [dim_time] (
[time_ident] [int] IDENTITY (1, 1) NOT NULL ,
[opco_num] [int] NULL ,
[gl_w] [int] NOT NULL ,
[gl_period] [int] NOT NULL ,
[gl_quarter] [int] NOT NULL ,
[gl_year] [int] NOT NULL ,
[begin_w_date] [datetime] NULL ,
PRIMARY KEY CLUSTERED
(
[time_ident]
) ON [PRIMARY] ,
CONSTRAINT [un_dim_time_01] UNIQUE NONCLUSTERED
(
[opco_num],
[gl_w],
[gl_year]
) ON [PRIMARY] ,
FOREIGN KEY
(
[opco_num]
) REFERENCES [dim_opco] (
[opco_num]
)
) ON [PRIMARY]
GO
CREATE TABLE [dim_vendor] (
[vendor_ident] [int] IDENTITY (1, 1) NOT NULL ,
[opco_num] [int] NOT NULL ,
[opco_vendor_num] [int] NOT NULL ,
[opco_vendor_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_vendo__opco___11D4A34F] DEFAULT ('Unknown'),
PRIMARY KEY CLUSTERED
(
[vendor_ident]
) ON [PRIMARY] ,
CONSTRAINT [un_dim_vendor_01] UNIQUE NONCLUSTERED
(
[opco_num],
[opco_vendor_num]
) ON [PRIMARY] ,
FOREIGN KEY
(
[opco_num]
) REFERENCES [dim_opco] (
[opco_num]
)
) ON [PRIMARY]
GO
CREATE TABLE [dim_customer] (
[customer_ident] [int] IDENTITY (1, 1) NOT NULL ,
[opco_num] [int] NOT NULL CONSTRAINT [DF__dim_custo__opco___73501C2F]
DEFAULT (0),
[customer_num] [int] NOT NULL ,
[customer_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_custo__custo__74444068] DEFAULT ('Unknown'),
[sales_rep_ident] [int] NOT NULL CONSTRAINT
[DF__dim_custo__sales__762C88DA] DEFAULT (0),
[abc_rating] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_custo__abc_r__7720AD13] DEFAULT ('0'),
[type_num] [int] NOT NULL CONSTRAINT [DF__dim_custo__type___7814D14C]
DEFAULT (0),
[type_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_custo__type___7908F585] DEFAULT ('Unknown'),
[major_type_num] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_custo__major__79FD19BE] DEFAULT (0),
[major_type_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_custo__major__7AF13DF7] DEFAULT ('Unknown'),
[chain_code] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_custo__chain__7BE56230] DEFAULT ('INDEP'),
[chain_code_desc] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_custo__chain__7CD98669] DEFAULT ('Not a Chain'),
[chain_code_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_custo__chain__7DCDAAA2] DEFAULT ('U'),
[ship_to_county] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_custo__ship___7EC1CEDB] DEFAULT ('Unknown'),
[ship_to_state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_custo__ship___7FB5F314] DEFAULT ('Un'),
[ship_to_address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_custo__ship___00AA174D] DEFAULT ('Unknown'),
[customer_phone1] [numeric](10, 0) NOT NULL CONSTRAINT
[DF__dim_custo__custo__019E3B86] DEFAULT (0),
PRIMARY KEY CLUSTERED
(
[customer_ident]
) ON [PRIMARY] ,
CONSTRAINT [un_dim_customer_01] UNIQUE NONCLUSTERED
(
[opco_num],
[customer_num]
) ON [PRIMARY] ,
FOREIGN KEY
(
[opco_num]
) REFERENCES [dim_opco] (
[opco_num]
),
FOREIGN KEY
(
[sales_rep_ident]
) REFERENCES [dim_salesRep] (
[salesRep_ident]
)
) ON [PRIMARY]
GO
CREATE TABLE [dim_salesRep] (
[salesRep_ident] [int] IDENTITY (1, 1) NOT NULL ,
[opco_num] [int] NOT NULL ,
[salesRep_num] [int] NOT NULL ,
[salesRep_name] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__dim_sales__sales__6ABAD62E] DEFAULT ('Unknown'),
[salesManager_code] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_sales__sales__6BAEFA67] DEFAULT ('Unk'),
[salesManager_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_sales__sales__6CA31EA0] DEFAULT ('Unknown'),
[salesTerritory] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL CONSTRAINT [DF__dim_sales__sales__6D9742D9] DEFAULT ('Unk'),
[salesTerritory_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL CONSTRAINT [DF__dim_sales__sales__6E8B6712] DEFAULT ('Unknown'),
[Rep_Net_Name] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Mgr_Net_Name] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[salesRep_ident]
) ON [PRIMARY] ,
FOREIGN KEY
(
[opco_num]
) REFERENCES [dim_opco] (
[opco_num]
)
) ON [PRIMARY]
GO
CREATE TABLE [dim_opco] (
[opco_num] [int] NOT NULL ,
[opco_desc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[region_num] [int] NOT NULL ,
[region_desc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_Dim_OPCO] PRIMARY KEY CLUSTERED
(
[opco_num]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GOHere is a suggestion that may solve your problem: move the two INNER
JOIN-s (with dim_opco and with dim_salesRep) BEFORE the LEFT JOIN.
Razvan|||Thanks for the suggestion. However, moving the INNER JOINS up made no
effect on the result set.
"Razvan Socol" wrote:

> Here is a suggestion that may solve your problem: move the two INNER
> JOIN-s (with dim_opco and with dim_salesRep) BEFORE the LEFT JOIN.
> Razvan
>sql

Query where feild name is reserved

Using query analyzer, how can I return a column (desc in this case)
that is also a reserved word?
I'm working with a commercial product so I can't change the name of
the column.
I've tried things like:
Select desc from foo
Select 'desc' from foo
select id,desc from foo
etc...
each one gives me: Error near reserved word 'desc'
Thanks.
When encountering any object name that is a reserved word, enclose it in
either double quotes, or square brackets.
"desc" or [desc]
And when you use multiple part names, such as:
MyDatabase.dbo.Table
enclose on the reserved word part of the name in delimiters:
MyDatabase.dbo.[Table]
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<Scamp@.nospam.com> wrote in message
news:i187l2tkup4aols7dv9kvbr4gfjd51626t@.4ax.com...
> Using query analyzer, how can I return a column (desc in this case)
> that is also a reserved word?
> I'm working with a commercial product so I can't change the name of
> the column.
> I've tried things like:
> Select desc from foo
> Select 'desc' from foo
> select id,desc from foo
> etc...
> each one gives me: Error near reserved word 'desc'
>
> Thanks.
>

Query where feild name is reserved

Using query analyzer, how can I return a column (desc in this case)
that is also a reserved word?
I'm working with a commercial product so I can't change the name of
the column.
I've tried things like:
Select desc from foo
Select 'desc' from foo
select id,desc from foo
etc...
each one gives me: Error near reserved word 'desc'
Thanks.ANSI SQL compliant:
SELECT "desc" FROM foo
SQL Server specific:
SELECT [desc] FROM foo
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<Scamp@.nospam.com> wrote in message news:i187l2tkup4aols7dv9kvbr4gfjd51626t@.4ax.com...
> Using query analyzer, how can I return a column (desc in this case)
> that is also a reserved word?
> I'm working with a commercial product so I can't change the name of
> the column.
> I've tried things like:
> Select desc from foo
> Select 'desc' from foo
> select id,desc from foo
> etc...
> each one gives me: Error near reserved word 'desc'
>
> Thanks.
>|||When encountering any object name that is a reserved word, enclose it in
either double quotes, or square brackets.
"desc" or [desc]
And when you use multiple part names, such as:
MyDatabase.dbo.Table
enclose on the reserved word part of the name in delimiters:
MyDatabase.dbo.[Table]
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<Scamp@.nospam.com> wrote in message
news:i187l2tkup4aols7dv9kvbr4gfjd51626t@.4ax.com...
> Using query analyzer, how can I return a column (desc in this case)
> that is also a reserved word?
> I'm working with a commercial product so I can't change the name of
> the column.
> I've tried things like:
> Select desc from foo
> Select 'desc' from foo
> select id,desc from foo
> etc...
> each one gives me: Error near reserved word 'desc'
>
> Thanks.
>sql

Query Where clause not working quite correct

My query below should only do an insert if these 2 evaluate to true:

a) The customer number is not currently in the DR table

b) The customer number is in the list check (where customer in)

a has to be true in order for b to be checked. The insert cannot happen for a customer that's already in the DCR whose number is in the IN clause

So far, it is close however I'm finding a few numbers being inserted that are already in the DR table.

I would also like to insert one record for the customer. It will find multiple entries per customer number since this is really a transactiosn table but I need to insert like the Top 1 o something since these inserts are going to be customers who have no transactions...thus is why you see blanks or zeros for all the values. These inserts are part of a larger picture but are needed...so there is no n eed to explain why I'd want to insert nothing ('' and 0 values) for those customers...just leave it at that

SELECT top 1 m.customer,

c.name,

c.customer,

'',

0,

m.Branch,

0,

'',

'',

'',

0,

'',

'',

0,

0,

0,

0,

'UI' AS Type,

1 AS Active,

m.number,

0,

0,

0,

0,

0,

0,

'',

0,

0,

'',

'',

(SELECT TotalPostingDays from TotalPostingDays),

(SELECT CurrentPostingDAy from CurrentPostingDay)

FROM dbo.Master m (NOLOCK)

INNER JOIN dbo.Customer c ON c.Customer = m.Customer

AND c.customer IN ( '0000093',

'0000066',

'0000050',

'0000114',

'0000112',

'0000124',

'0000113'

'0000094',

'0000104',

'0000122',

'0000123',

'0000127',

'0000057',

'0000132',

'0000138',

'0000128',

'0000142',

'0000149',

'0000147',

'0000144',

'0000148',

'0000145',

'0000103',

'0000105',

'0000109',

'0000135',

'0000155',

'0000156',

'0000157',

'0000159',

'0000160',

'0000161',

'0000118',

'0000143',

'0000146',

'0000153',

'0000152',

'0000108',

'0000158',

'0000133')

AND c.customer NOT IN (select customernumber FROM DR)

I think your problem lies in the fact that you are making your customer not in the DR table comparison as part or the join criteria. Try moving it to a WHERE clause instead or actually join the DR table using left join, see below

1st, instead of "AND c.customer NOT IN (select customernumber FROM DR)" Replace with WHERE c.customer NOT IN (SELECT customernumber FROM DR) Also move your check for customers in the provided list, into a where clause instead of the join criteria.

2nd, use the join as below with a where clause

Code Snippet

SELECT top 1 m.customer,

c.name,

c.customer,

'',

0,

m.Branch,

0,

'',

'',

'',

0,

'',

'',

0,

0,

0,

0,

'UI' AS Type,

1 AS Active,

m.number,

0,

0,

0,

0,

0,

0,

'',

0,

0,

'',

'',

(SELECT TotalPostingDays from TotalPostingDays),

(SELECT CurrentPostingDAy from CurrentPostingDay)

FROM dbo.Master m (NOLOCK)

INNER JOIN dbo.Customer c ON c.Customer = m.Customer

LEFT OUTER JOIN DR d ON c.customer = d.customernumber

WHERE d.customernumber is null

AND c.customer IN ( '0000093',

'0000066',

'0000050',

'0000114',

'0000112',

'0000124',

'0000113'

'0000094',

'0000104',

'0000122',

'0000123',

'0000127',

'0000057',

'0000132',

'0000138',

'0000128',

'0000142',

'0000149',

'0000147',

'0000144',

'0000148',

'0000145',

'0000103',

'0000105',

'0000109',

'0000135',

'0000155',

'0000156',

'0000157',

'0000159',

'0000160',

'0000161',

'0000118',

'0000143',

'0000146',

'0000153',

'0000152',

'0000108',

'0000158',

'0000133')

Either method should provide the results you need.

|||

Try re-writing your statement to use NOT EXISTS instead NOT IN to check for existence. If for any reason, there is a row in [DR] where customernumber is NULL, then you will have some trouble.

Example:

Code Snippet

select

*

from

(select 1 as c1 union all select 2) as a

where

c1 not in (1, NULL)

It should looks like:

...

where

c.customer IN (

'0000093',

'0000066',

'0000050',

'0000114',

'0000112',

'0000124',

'0000113',

'0000094',

'0000104',

'0000122',

'0000123',

'0000127',

'0000057',

'0000132',

'0000138',

'0000128',

'0000142',

'0000149',

'0000147',

'0000144',

'0000148',

'0000145',

'0000103',

'0000105',

'0000109',

'0000135',

'0000155',

'0000156',

'0000157',

'0000159',

'0000160',

'0000161',

'0000118',

'0000143',

'0000146',

'0000153',

'0000152',

'0000108',

'0000158',

'0000133'

)

AND NOT exists (

select *

from DR as d

where d.customernumber = c.customer

)

AMB

Query Where clause not working quite correct

My query below should only do an insert if these 2 evaluate to true:

a) The customer number is not currently in the DR table

b) The customer number is in the list check (where customer in)

a has to be true in order for b to be checked. The insert cannot happen for a customer that's already in the DCR whose number is in the IN clause

So far, it is close however I'm finding a few numbers being inserted that are already in the DR table.

I would also like to insert one record for the customer. It will find multiple entries per customer number since this is really a transactiosn table but I need to insert like the Top 1 o something since these inserts are going to be customers who have no transactions...thus is why you see blanks or zeros for all the values. These inserts are part of a larger picture but are needed...so there is no n eed to explain why I'd want to insert nothing ('' and 0 values) for those customers...just leave it at that

SELECT top 1 m.customer,

c.name,

c.customer,

'',

0,

m.Branch,

0,

'',

'',

'',

0,

'',

'',

0,

0,

0,

0,

'UI' AS Type,

1 AS Active,

m.number,

0,

0,

0,

0,

0,

0,

'',

0,

0,

'',

'',

(SELECT TotalPostingDays from TotalPostingDays),

(SELECT CurrentPostingDAy from CurrentPostingDay)

FROM dbo.Master m (NOLOCK)

INNER JOIN dbo.Customer c ON c.Customer = m.Customer

AND c.customer IN ( '0000093',

'0000066',

'0000050',

'0000114',

'0000112',

'0000124',

'0000113'

'0000094',

'0000104',

'0000122',

'0000123',

'0000127',

'0000057',

'0000132',

'0000138',

'0000128',

'0000142',

'0000149',

'0000147',

'0000144',

'0000148',

'0000145',

'0000103',

'0000105',

'0000109',

'0000135',

'0000155',

'0000156',

'0000157',

'0000159',

'0000160',

'0000161',

'0000118',

'0000143',

'0000146',

'0000153',

'0000152',

'0000108',

'0000158',

'0000133')

AND c.customer NOT IN (select customernumber FROM DR)

I think your problem lies in the fact that you are making your customer not in the DR table comparison as part or the join criteria. Try moving it to a WHERE clause instead or actually join the DR table using left join, see below

1st, instead of "AND c.customer NOT IN (select customernumber FROM DR)" Replace with WHERE c.customer NOT IN (SELECT customernumber FROM DR) Also move your check for customers in the provided list, into a where clause instead of the join criteria.

2nd, use the join as below with a where clause

Code Snippet

SELECT top 1 m.customer,

c.name,

c.customer,

'',

0,

m.Branch,

0,

'',

'',

'',

0,

'',

'',

0,

0,

0,

0,

'UI' AS Type,

1 AS Active,

m.number,

0,

0,

0,

0,

0,

0,

'',

0,

0,

'',

'',

(SELECT TotalPostingDays from TotalPostingDays),

(SELECT CurrentPostingDAy from CurrentPostingDay)

FROM dbo.Master m (NOLOCK)

INNER JOIN dbo.Customer c ON c.Customer = m.Customer

LEFT OUTER JOIN DR d ON c.customer = d.customernumber

WHERE d.customernumber is null

AND c.customer IN ( '0000093',

'0000066',

'0000050',

'0000114',

'0000112',

'0000124',

'0000113'

'0000094',

'0000104',

'0000122',

'0000123',

'0000127',

'0000057',

'0000132',

'0000138',

'0000128',

'0000142',

'0000149',

'0000147',

'0000144',

'0000148',

'0000145',

'0000103',

'0000105',

'0000109',

'0000135',

'0000155',

'0000156',

'0000157',

'0000159',

'0000160',

'0000161',

'0000118',

'0000143',

'0000146',

'0000153',

'0000152',

'0000108',

'0000158',

'0000133')

Either method should provide the results you need.

|||

Try re-writing your statement to use NOT EXISTS instead NOT IN to check for existence. If for any reason, there is a row in [DR] where customernumber is NULL, then you will have some trouble.

Example:

Code Snippet

select

*

from

(select 1 as c1 union all select 2) as a

where

c1 not in (1, NULL)

It should looks like:

...

where

c.customer IN (

'0000093',

'0000066',

'0000050',

'0000114',

'0000112',

'0000124',

'0000113',

'0000094',

'0000104',

'0000122',

'0000123',

'0000127',

'0000057',

'0000132',

'0000138',

'0000128',

'0000142',

'0000149',

'0000147',

'0000144',

'0000148',

'0000145',

'0000103',

'0000105',

'0000109',

'0000135',

'0000155',

'0000156',

'0000157',

'0000159',

'0000160',

'0000161',

'0000118',

'0000143',

'0000146',

'0000153',

'0000152',

'0000108',

'0000158',

'0000133'

)

AND NOT exists (

select *

from DR as d

where d.customernumber = c.customer

)

AMB

Query users in a Security Group with LDAP

I have a linked server set up and working correctly. I can create a query to get all the users from active directory with something like this:

SELECT [name], [samaccountname] from OpenQuery( ADSI,
'SELECT name, samaccountname FROM ''LDAP://DC=domain,DC=com'' WHERE objectClass = ''user'' and objectCategory=''Person''')

Now I am trying to select all the users in a specifed security group, but I am not having much luck. What is the best way to get this?

Thanks much.If that can't be done, is there anyway to check if a user is a member of a group or not through a linked server?sql

Query to get values from datetime column into comma separated text

Hi All

I am working on a query to get all the datetime values in a column in a table into a comma separated text.

eg.

ColumnDate
--------
2005-11-09 00:00:00.000
2005-11-13 00:00:00.000
2005-11-14 00:00:00.000
2005-11-16 00:00:00.000

I wanted to get something like

2005-11-09, 2005-11-13, 2005-11-14, 2005-11-16

Have just started SQL and hence am getting confused in what I think should be a relatively simple query. Any help will be much appreciated. Thanks

DECLARE @.List varchar(8000)
SET @.List = ''
select @.List = @.List + convert(varchar, datefield, 102) + ',' from MyTable

Just be careful, because a varchar can hold only 8000 characters, so if your results are more then they are chopped off. For more info on CONVERT function look in the BOL.

Query to count holidays

Hi,
I'm working on a helpdesk project and I require the calculation of the holidays.
I need to get the time difference of the assigned date and the solved date of the helpdesk tickets considering the week-end holidays and statutory holidays. Is there any possible way to do this. I need something similar to the NetworkDays function in excel.
Thanks.
Madhavi.I did something similar in a previous life. I created a master calendar table with columns for the date plus flag (bit) columns for weekends and holidays (and a third flag as I recall called working day). I then created a user-defined function which would take two dates as an input and return the number of "work" days elapsed between the two.

Perhaps not elegant, but it did work.

The master calendar table was also useful for reporting purposes. In your case, you might want a front-end interface to edit the calendar and identify which days are working versus non-working.

Regards,

hmscott

Hi,
I'm working on a helpdesk project and I require the calculation of the holidays.
I need to get the time difference of the assigned date and the solved date of the helpdesk tickets considering the week-end holidays and statutory holidays. Is there any possible way to do this. I need something similar to the NetworkDays function in excel.
Thanks.
Madhavi.|||Only way since holidays are uniqu to countries...

works well though

Query Timeouts after adding a logging table and SP

Hi everyone,
I hope you can help with some good suggestions. I have a system that was
working fine as far as performance, but after I added the following table and
SP, with a call to this SP from my Insert/Update/Delete queries, I started
getting timeout errors and the system came to a crawl. The first time it
happened I could not get it working until we rebooted the server. After that
it happened again the following day and then I backed out the calls to the
AddTableLog proc. Here is the table def and proc:
TableLogs definition
3 MessageId int 4 0
0 Message varchar 500 1
0 MessageDate datetime 8 1
0 AppLoggedInUser varchar 100 1
0 ComputerName varchar 100 1
0 CompLoggedInUser varchar 100 1
Proc to add to above table
CREATE PROCEDURE [dbo].[AddTableLog]
@.Message varchar(500),
@.AppLoggedInUser varchar(100),
@.ComputerName varchar(100)
AS
Insert into TableLogs( Message, MessageDate, AppLoggedInUser, ComputerName,
CompLoggedInUser)
Values( @.Message, GetDate(), @.AppLoggedInUser, @.ComputerName, Session_User )
GO
Here is an example of how I was using the above log tables:
CREATE PROCEDURE [dbo].[nf_AddAttendance]
@.PatientId varchar(20),
@.AttendDate datetime,
@.ComputerName varchar(100),
@.LoggedInUser varchar(100)
AS
Insert into Attendance (PatientId, AttendDate, CreationDate,
UpdatedBy,ComputerName, LoggedInUser)
values(@.PatientId, @.AttendDate, GetDate(), Session_User,@.ComputerName,
@.LoggedInUser)
Declare @.UserMessage Varchar(500)
select @.UserMessage = 'User ' + @.LoggedInUser + ' has added a attendance
record for PatientId: ' + @.PatientId + ' for attendDate: ' + @.AttendDate
Exec AddTableLog @.UserMessage, @.LoggedInUser, @.ComputerName
GO
Is there a better way to do this. I wanted to be able to log who actually
made the change and what computer they where at. So I pass that info in.
Would using a Trigger be faster at doing this? Or am I on the right track.
Thanks for any suggestions.
Michael LeeI forgot to mention that we are using SQL Server 2000.
Thanks again.
Michael Lee

Query Time Out Expired

I am using a School Management software with MSSql Server 2005 software is working well on the server but it is not accessible on network. we using wareless network....Open [Start> All Programs> Microsoft SQL Server 2005> Configuration Tools> SQL Server Surface Area Configuration], select [MSSQLSERVER> Database Engine> Remote Connections], make sure that "Local and remote connections" option is selected.

query syntax problems

I am working in vb6 and am new to database access. I can't seem to figure out the syntax for the following:

rs1.Open "select * from orders where category = '" & button & " and invoice = " & invoice & "' order by guest", db, adOpenStatic, adLockOptimistic

The above statement doesn't work. What am I doing wrong?

JerrybYour embedded quotes were misplaced. The SQL being run is like:

select * from orders where category = 'xxx and invoice = yyy ' order by guest

But you probably meant this:

select * from orders where category = 'xxx' and invoice = 'yyy' order by guest

Try this:

rs1.Open "select * from orders where category = '" & button & "' and invoice = '" & invoice & "' order by guest", db, adOpenStatic, adLockOptimistic

(This is assuming that both button and invoice are strings rather than numbers - for numbers, you don't need the quotes.)

Hint: it's a good idea to print the SQL that is failing to see if it makes sense - e.g.:

Response.Write "select * from orders where category = '" & button & " and invoice = " & invoice & "' order by guest"