Query Help

Hi All,

I want to display the data in vertical format though the data is stored horizontally in the datatable.Suppose i have table with five columns-id.a,b,c,d.If i use a select statement that will give me the data in the format-ID A B C D

but now i want to display the data as

ID A

ID B

ID C

ID D

Any help on this pls??I m thinking to use the self join but would it take a long time??

Thanks

select id,a

union

select id,b

union

select id,c

union

select id,d

|||It sounds to me more like you require a pivot query rather than a union. If you have Sql Server 2005 you can do pivot queries but, unfortunately, not in the older versions. There a good article you can readhere that explains how to do pivot queries.|||

You can do pivot tables in Sql Server 2000. Just search for "pivot tables" in the Books Online. The example there was greatly helpful to me with the same issue.

Hope this helps

Query Help

Hoping the community can help me with this problem. I have a stored
procedure that returns a few alias fields.
Here is the select clause to give you an idea what I am talking about (FROM
and WHERE omitted to shorten the length):
SELECT DriverTable.DriverLastName, DriverTable.DriverFirstName,
DriverTable.DriverMiddleInitial,[Company Table].CompanyName, [Locations
Table].LocationName,
CASE WHEN DriverLicenseRenewDate <= GETDATE()
THEN 'Drivers Licanse has Expired'
WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
DATEADD(dd, 60, GETDATE())
THEN 'Drivers Licanse expires within the next 60 days' END AS DLRenewDate,
CASE WHEN [Driver'sLicensePhoto] = 0 THEN 'Send Copy of Driver''s License'
END AS DLPhoto,
CASE WHEN DriverEmploymentDateHired >= '8/17/2001' AND
[DriverPre-employmentDrugTestDate]IS NULL THEN 'Send Copy of Current Driver
Pre-employment Drug Test' END AS [DPre-Employ], CASE WHEN
[DriverEmploymentDateHired] >= '8/17/2001' AND
[DriverPre-employment4025(j)DrugQuestion] = 0 THEN 'Send Copy of Driver
Pre-employment Form (§40.25(j)' END AS DPre4025DrugTest
This query has 4 alias's to be returned; DLRenewDate,DLPhoto,[DPre-Employ],
and DPre4025DrugTest. My problem is that if all 4 are returned NULL I do
not want the record to be returned with the query. If I could have a WHERE
like this:
WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
NOT NULL AND DPre4025DrugTest IS NOT NULL
then it would not be a problem, but you cannot test alias's in this manner
(as far as I can tell). What are my options for solving this problem? If I
was in Access I could run a second query on the first and test my
conditions. Can I use another Stored procedure in this manner?
For Example a second stored procedure that works like this:
Select * From StoredProcedure1
WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
NOT NULL AND DPre4025DrugTest IS NOT NULLHi
To reduce the amount of typing you have to do, you can use a table alias for
the table. see Books Online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\acd
ata.chm::/ac_8_qd_05_4ofn.htm
Instead of using the column alias in the WHERE clause, use the actual
expression. As these are CASE statements returning non null values when they
are satisfied they will only return null if non of the clauses are returned,
so these can be tested in the WHERE clause
e.g
For:
CASE WHEN DriverLicenseRenewDate <= GETDATE()
THEN 'Drivers Licanse has Expired'
WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
DATEADD(dd, 60, GETDATE())
THEN 'Drivers Licanse expires within the next 60 days'
END AS DLRenewDate
This will only be null if
NOT ( DriverLicenseRenewDate <= GETDATE()
OR ( DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
DATEADD(dd, 60, GETDATE()) ) )
Which is the same as
DriverLicenseRenewDate > GETDATE()
AND DriverLicenseRenewDate >= DATEADD(dd, 60, GETDATE()) )
HTH
John
"REB" <russ@.bjorgaard.com> wrote in message
news:%23HAwGAZ8DHA.488@.TK2MSFTNGP12.phx.gbl...
> Hoping the community can help me with this problem. I have a stored
> procedure that returns a few alias fields.
> Here is the select clause to give you an idea what I am talking about
(FROM
> and WHERE omitted to shorten the length):
> SELECT DriverTable.DriverLastName, DriverTable.DriverFirstName,
> DriverTable.DriverMiddleInitial,[Company Table].CompanyName, [Locations
> Table].LocationName,
> CASE WHEN DriverLicenseRenewDate <= GETDATE()
> THEN 'Drivers Licanse has Expired'
> WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
> DATEADD(dd, 60, GETDATE())
> THEN 'Drivers Licanse expires within the next 60 days' END AS DLRenewDate,
> CASE WHEN [Driver'sLicensePhoto] = 0 THEN 'Send Copy of Driver''s License'
> END AS DLPhoto,
> CASE WHEN DriverEmploymentDateHired >= '8/17/2001' AND
> [DriverPre-employmentDrugTestDate]IS NULL THEN 'Send Copy of Current
Driver
> Pre-employment Drug Test' END AS [DPre-Employ], CASE WHEN
> [DriverEmploymentDateHired] >= '8/17/2001' AND
> [DriverPre-employment4025(j)DrugQuestion] = 0 THEN 'Send Copy of Driver
> Pre-employment Form (§40.25(j)' END AS DPre4025DrugTest
> This query has 4 alias's to be returned;
DLRenewDate,DLPhoto,[DPre-Employ],
> and DPre4025DrugTest. My problem is that if all 4 are returned NULL I do
> not want the record to be returned with the query. If I could have a
WHERE
> like this:
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
> then it would not be a problem, but you cannot test alias's in this manner
> (as far as I can tell). What are my options for solving this problem? If
I
> was in Access I could run a second query on the first and test my
> conditions. Can I use another Stored procedure in this manner?
> For Example a second stored procedure that works like this:
> Select * From StoredProcedure1
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
>|||That makes sense, thank you.
"REB" <russ@.bjorgaard.com> wrote in message
news:%23HAwGAZ8DHA.488@.TK2MSFTNGP12.phx.gbl...
> Hoping the community can help me with this problem. I have a stored
> procedure that returns a few alias fields.
> Here is the select clause to give you an idea what I am talking about
(FROM
> and WHERE omitted to shorten the length):
> SELECT DriverTable.DriverLastName, DriverTable.DriverFirstName,
> DriverTable.DriverMiddleInitial,[Company Table].CompanyName, [Locations
> Table].LocationName,
> CASE WHEN DriverLicenseRenewDate <= GETDATE()
> THEN 'Drivers Licanse has Expired'
> WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
> DATEADD(dd, 60, GETDATE())
> THEN 'Drivers Licanse expires within the next 60 days' END AS DLRenewDate,
> CASE WHEN [Driver'sLicensePhoto] = 0 THEN 'Send Copy of Driver''s License'
> END AS DLPhoto,
> CASE WHEN DriverEmploymentDateHired >= '8/17/2001' AND
> [DriverPre-employmentDrugTestDate]IS NULL THEN 'Send Copy of Current
Driver
> Pre-employment Drug Test' END AS [DPre-Employ], CASE WHEN
> [DriverEmploymentDateHired] >= '8/17/2001' AND
> [DriverPre-employment4025(j)DrugQuestion] = 0 THEN 'Send Copy of Driver
> Pre-employment Form (§40.25(j)' END AS DPre4025DrugTest
> This query has 4 alias's to be returned;
DLRenewDate,DLPhoto,[DPre-Employ],
> and DPre4025DrugTest. My problem is that if all 4 are returned NULL I do
> not want the record to be returned with the query. If I could have a
WHERE
> like this:
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
> then it would not be a problem, but you cannot test alias's in this manner
> (as far as I can tell). What are my options for solving this problem? If
I
> was in Access I could run a second query on the first and test my
> conditions. Can I use another Stored procedure in this manner?
> For Example a second stored procedure that works like this:
> Select * From StoredProcedure1
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
>sql

query function in stored proc... how to do it?

how can i view all the records in my stored procedure?

how is the query function done?

for example i had my datagrid view... and of course a view button that will trigger a view action in able to view the entire records that i input....

i am not familiar with such things..

pls help me to figure this out..

thanks..

im just a begginer when it comes to this..

pls help me..

thanks..

Are you asking about how to do this from your application code or from toosl like SSMS ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

query for stored procedures?

I'm working with SQL Server and I'd like to run a query to find out a
list of the user stored procedures in a given database, and what the
parameters for each stored procedure are.http://www.aspfaq.com/2123
http://www.aspfaq.com/search.asp?q=schema%3A
<apandapion@.gmail.com> wrote in message
news:1129665163.315912.19510@.g14g2000cwa.googlegroups.com...
> I'm working with SQL Server and I'd like to run a query to find out a
> list of the user stored procedures in a given database, and what the
> parameters for each stored procedure are.
>

query engine statitistics

Hi, Is there an esay way to gather information about how many times each
stored procedures has been called , the average execution time , etc ..
I know profiling can give you punctual info about this .. however i need
aggregated info over a determined period of time
thank in advance
best regards
--
Enrico Sabbadin
MTS/COM+/VBCOM/.NET FAQ: http://www.sabbasoft.com
BLOG: http://www.sabbasoft.com/myblogCheck out sys.dm_exec_query_stats if you are on 2005.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<x> wrote in message news:OvENi8jcHHA.4820@.TK2MSFTNGP06.phx.gbl...
> Hi, Is there an esay way to gather information about how many times each stored procedures has
> been called , the average execution time , etc ..
> I know profiling can give you punctual info about this .. however i need aggregated info over a
> determined period of time
> thank in advance
> best regards
> --
> Enrico Sabbadin
> MTS/COM+/VBCOM/.NET FAQ: http://www.sabbasoft.com
> BLOG: http://www.sabbasoft.com/myblog
>

Query Date Stored as Text

I am using Windows "Performance Logs and Alerts" to log performance statistics to a SQL database. In this case I am using a Counter Log, which creates the necessary tables upon startup of the log. I can see my data pouring into the database and now I am writing some T-SQL statements to query the data and display it on an aspx page. I have one particular problem that is presenting me with a bit of a challenge. I want to be able to query my captured performance counters based on date ranges entered on the aspx page. Unfortunately I am having trouble doing that because the "CounterDateTime" field where the date for each counter sample is stored is a char(24) data type instead of a datetime data type. I'm not sure why MS chose to store dates in a char field but I am not sure how to query that field for date ranges. So my question is, how would one query for a date range when the dates that I'm querying are in char format? Thanks in advance.

Could you post some sample of how the format of the datetime value looks like in the table? If it is some format that retain order in character format for datetime values then you can simply use string comparisons. Best is to convert the column to datetime and then do your manipulations. Of course, performance will suffer if your primary search condition is the datetime column only. For the conversion part, I think you may have to strip some time part. If I remember correctly the value stored by perfmon are higher resolution than what SQL Server currently supports. This is probably one reason why they can't store it in datetime column for example.|||

Here is an example of what the data looks like in the datetime field: 2006-08-15 11:37:17.121

Unfortunately, I doubt that changing the datatype in the table is an option since the table and datatypes are created automatically by the SysMon counter log. My guess is that if I change the structure of the field, the counter log will error out when it tries to log data since the format in the table will not jive with the type of data that the counter log is trying to insert. I don't know if there is a good solution to this problem. Is the CounterDateTime field basically just useless data that one can't query? On my ASP.Net web page, I allow the user to select a start date and an end date. Therefore, in my SQL query I want to select everything that falls in between the two dates. Since the date fields are text, perhaps that is not going to be possible.

Thanks.

|||

It is the ODBC canonical format so you can just use it directly without converting the datetime. This format will preserve order of the values. See examples below:

select * from Northwind.dbo.Orders
where convert(varchar, OrderDate, 121) >= '1996-07-10 00:00:00.000' and
convert(varchar, OrderDate, 121) < '1996-08-01 06:40:00.000'

select * from Northwind.dbo.Orders
where OrderDate >= '1996-07-10 00:00:00.000' and
OrderDate < '1996-08-01 06:40:00.000'

I didn't imply that you should convert the column in the table. Instead you can do a convert at run-time in your query. In any case, it is not really required. You can do your query like:

select *

from perfmon_table

where CounterDateTime >= convert(varchar, @.datetime_start, 121)

and CounterDateTime < convert(varchar, @.datetime_end, 121)

-- or if there is no index on the CounterDateTime column then just do:

select *

from perfmon_table

where cast(CounterDateTime as datetime) >= @.datetime_start

and cast(CounterDateTime as datetime) < @.datetime_end

|||Thanks for your help! That works like a champ!

Query by year group by total students sort by total for each county

I haven't a clue how to accomplish this.
All the data is in one table. The data is stored by registration date
and includes county and number of students brokne out by grade.

Any help appreciated!

RobSomething like this, maybe:

SELECT county, YEAR(registration_date), SUM(num_students) AS tot_students
FROM Students
GROUP BY county, YEAR(registration_date)
ORDER BY county, tot_students

If you need more help please post DDL (CREATE TABLE statement) for the
table, including keys and constraints.

--
David Portas
----
Please reply only to the newsgroup
--|||the_ainbinders@.yahoo.com (Rob) wrote in message news:<14be79e4.0312010657.8573393@.posting.google.com>...
> I haven't a clue how to accomplish this.
> All the data is in one table. The data is stored by registration date
> and includes county and number of students brokne out by grade.
> Any help appreciated!
> Rob

Here's the CREATE TABLE

CREATE TABLE [dbo].[EdSales] (
[FName] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LName] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrgName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Addr1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Addr2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[County] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[extension] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [nvarchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DOV] [datetime] NULL ,
[grade1] [numeric](10, 0) NULL ,
[grade2] [numeric](10, 0) NULL ,
[grade3] [numeric](10, 0) NULL ,
[grade4] [numeric](10, 0) NULL ,
[grade5] [numeric](10, 0) NULL ,
[grade6] [numeric](10, 0) NULL ,
[grade7] [numeric](10, 0) NULL ,
[grade8] [numeric](10, 0) NULL ,
[grade9] [numeric](10, 0) NULL ,
[grade10] [numeric](10, 0) NULL ,
[grade11] [numeric](10, 0) NULL ,
[grade12] [numeric](10, 0) NULL ,
[grade13] [numeric](10, 0) NULL ,
[grade14] [numeric](10, 0) NULL ,
[grade15] [numeric](10, 0) NULL ,
[grade16] [numeric](10, 0) NULL ,
[grade17] [numeric](10, 0) NULL ,
[comments] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dateEntered] [datetime] NULL ,
[result] [numeric](18, 0) NULL ,
[ConfirmNum] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[visible] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[EdSales] WITH NOCHECK ADD
CONSTRAINT [PK_EdSales_1] PRIMARY KEY CLUSTERED
(
[ConfirmNum]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[EdSales] WITH NOCHECK ADD
CONSTRAINT [DF_EdSales_visible] DEFAULT ('y') FOR [visible]
GO|||Your table design has some problems: Non-normalised repeating group of
Grades; No natural primary key; Too many NULLable columns (if every column
can be NULL then why would you have a row in the table anyway!). This query
would be a lot simpler if you fixed these things.

SELECT county, YEAR(dateentered) AS year_entered,
SUM(
COALESCE(grade1,0)+
COALESCE(grade2,0)+
COALESCE(grade3,0)+
COALESCE(grade4,0)+
COALESCE(grade5,0)+
COALESCE(grade6,0)+
COALESCE(grade7,0)+
COALESCE(grade8,0)+
COALESCE(grade9,0)+
COALESCE(grade10,0)+
COALESCE(grade11,0)+
COALESCE(grade12,0)+
COALESCE(grade13,0)+
COALESCE(grade14,0)+
COALESCE(grade15,0)+
COALESCE(grade16,0)+
COALESCE(grade17,0))
AS tot_students
FROM EdSales
GROUP BY county, YEAR(dateentered)

--
David Portas
----
Please reply only to the newsgroup
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<_dGdnR8i6ZJR9VGiRVn-jg@.giganews.com>...
> Your table design has some problems: Non-normalised repeating group of
> Grades; No natural primary key; Too many NULLable columns (if every column
> can be NULL then why would you have a row in the table anyway!). This query
> would be a lot simpler if you fixed these things.
> SELECT county, YEAR(dateentered) AS year_entered,
> SUM(
> COALESCE(grade1,0)+
> COALESCE(grade2,0)+
> COALESCE(grade3,0)+
> COALESCE(grade4,0)+
> COALESCE(grade5,0)+
> COALESCE(grade6,0)+
> COALESCE(grade7,0)+
> COALESCE(grade8,0)+
> COALESCE(grade9,0)+
> COALESCE(grade10,0)+
> COALESCE(grade11,0)+
> COALESCE(grade12,0)+
> COALESCE(grade13,0)+
> COALESCE(grade14,0)+
> COALESCE(grade15,0)+
> COALESCE(grade16,0)+
> COALESCE(grade17,0))
> AS tot_students
> FROM EdSales
> GROUP BY county, YEAR(dateentered)

Well, I am a newbie at this sort of a thing. So, any pointers to
useful info on the diffiencies you mention are appreciated.

Query batch completed with errors...

Hi,
I am executin a stored procedure which contains a sequence of Stored
procedures. One of these stored procedures is a cursor-stored procedure
(basically a cursor).
At the end of the execution of this Stp(cursor), it gives me "query batch
completed with errors". I tried to capture the error through Printing
@.@.error, but to no luck. The problem is that this stp is part of a sequence
of stored procedures. Because of this error the subsequent stps dont get
executed.
Could someone give some leads how to resolve this?
Thanx,
S.How are you executing the proc? Did you try running it in isolation in Query
Analyzer? That should at least show you the error message in the results
window. Debug the proc to find the statement that throws the error (In
Query Analyzer's Object Browser right-click the proc name and select Debug).
If you're still stuck once you've narrowed it down a bit then post some code
here to reproduce the error.
David Portas
SQL Server MVP
--|||David,
Yes I was executing it in the Analyser..that how i found out it was this stp
which was givin the prob...
Here is the code for the problem stp..
---
CREATE PROCEDURE CALC_MyDealValue_As_DealPartner
AS
DECLARE
@.pCustomerCivilId nvarchar(15),
@.pInvoiceNo nvarchar(50),
@.pLiability char(1),
@.pAppId char(3),
@.pGrossBalance decimal(15,3),
@.pShareHolderCivilID nvarchar(15),
@.pPartnerShare decimal(15,3)
--Declare the copy cursor
DECLARE DealPartner_Cursor CURSOR FOR
SELECT CustomerCivilId,InvoiceNumber,Liability,
AppId,ShareHolderCivilID,
Convert(Decimal(15,3),SharePercentage)/100 as share
FROM dbo.Shareholder
--where Left(CustomerCivilId,2) ='JA'
where (InvoiceNumber<>'') and (Liability <> '') and (AppId <>'') --For
Deal Partner This is not empty in Shareholder Table
--Open the cursor
BEGIN
OPEN DealPartner_Cursor
FETCH NEXT FROM DealPartner_Cursor
INTO
@.pCustomerCivilId,@.pInvoiceNo,@.pLiabilit
y,@.pAppId,@.pShareHolderCivilID,@.pPar
tnerShare
IF @.@.Error <>0
Begin
Print 'Fetch 1' + convert(nvarchar(50),@.@.Error)
End
WHILE @.@.FETCH_STATUS = 0
BEGIN
--Print 'Parnter- ' + convert(nvarchar(50),@.pShareHolderCivilI
D)
--Print 'Share- ' + convert(nvarchar(50),@.pPartnerShare)
SET @.pGrossBalance = (SELECT isnull(GrossBalance,0) FROM vwMyDealAmounts
WHERE ( --dbo.vwMyDealAmounts.CivilID = @.pCustomerCivilId and
dbo.vwMyDealAmounts.InvoiceNumber = @.pInvoiceNo and
dbo.vwMyDealAmounts.Liability = @.pLiability and
dbo.vwMyDealAmounts.AppId = @.pAppId))
IF @.@.Error <>0
Begin
Print 'Fetch GB ' + convert(nvarchar(50),@.@.Error)
End
--Print 'GrossBal - ' + convert(nvarchar(50),@.pGrossBalance)
--Print 'DealCivilId - ' + convert(nvarchar(50),@.pDealCivilID)
Set @.pGrossBalance = isnull(@.pGrossBalance,0)
if (@.pGrossBalance <> 0.000)
Begin
if Left(@.pCustomerCivilId,2) ='JA'
Begin
Update TmpMyDealings
Set ValAsDealPartner= ValAsDealPartner +(@.pGrossBalance*@.pPartnerShare)
where TmpMyDealings.CivilId=@.pCustomerCivilId -- update TO JA
End
IF @.@.Error <>0
Begin
Print 'UP JA ' + convert(nvarchar(50),@.@.Error)
End
Update TmpMyDealings
Set ValAsDealPartner= ValAsDealPartner +(@.pGrossBalance*@.pPartnerShare)
where TmpMyDealings.CivilId=@.pShareHolderCivilID
IF @.@.Error <>0
Begin
Print 'UP NON-JA ' + convert(nvarchar(50),@.@.Error)
End
--Print 'Done for ' + convert(nvarchar(50),@.pCustomerCivilId) + ' - ' +
convert(nvarchar(50),@.pShareHolderCivilI
D)
--Print 'Done for Count ' + convert(nvarchar(50),@.iCount)
End
FETCH NEXT FROM DealPartner_Cursor
INTO
@.pCustomerCivilId,@.pInvoiceNo,@.pLiabilit
y,@.pAppId,@.pShareHolderCivilID,@.pPar
tnerShare
IF @.@.Error <>0
Begin
Print 'Fetch 2 ' + convert(nvarchar(50),@.@.Error)
End
END
CLOSE DealPartner_Cursor
DEALLOCATE DealPartner_Cursor
END
GO
---
"David Portas" wrote:

> How are you executing the proc? Did you try running it in isolation in Que
ry
> Analyzer? That should at least show you the error message in the results
> window. Debug the proc to find the statement that throws the error (In
> Query Analyzer's Object Browser right-click the proc name and select Debug
).
> If you're still stuck once you've narrowed it down a bit then post some co
de
> here to reproduce the error.
> --
> David Portas
> SQL Server MVP
> --
>
>|||You still didn't tell us the error message. Did you run it in Debug?
Honestly I wouldn't bother though. You should always try to avoid cursors
and 90% of this code is redundant. The effect seems to be the same as that
of a single UPDATE statement.
The following is my best guess of what you need to do. It's untested and as
it's done without a spec you'll probably have to make some mods. In
particular you may want to add WHERE EXISTS depending on requirements.
CREATE PROCEDURE calc_mydealvalue_as_dealpartner
AS
UPDATE TmpMyDealings
SET valasdealpartner = valasdealpartner +
(SELECT ISNULL(SUM(D.grossbalance*
CONVERT(DECIMAL(15,3),S.sharepercentage)/100),0)
FROM vwMyDealAmounts AS D
JOIN Shareholder AS S
ON D.invoicenumber = S.invoicenumber
AND D.liability = S.liability
AND D.appid = S.appid
AND D.grossbalance <>0
AND tmpmydealings.civilid = S.customercivilid
AND S.customercivilid LIKE 'JA%')
RETURN
GO
If you need more help please post DDL, a few rows of sample data (INSERT
statements are the best way to post sample data) and show us what result you
require from the sample. See:
http://www.aspfaq.com/etiquette.asp?id=5006
Hope this helps.
David Portas
SQL Server MVP
--

query assistance

I have a field in a table that the datatype is varchar
the data stored in the field is a date in the format of 7/19/2007
I need to run a query that will give me all teh records that is of a
certain date or newer.
teh query I am running is
Select * from table1 where {do rec] >= '07/19/2007'
what I get is pretty much everything. items dated 7/31/2006 etc...
how can I run this query correctly?
The reason the field is a varchar is becasue I don't want the 'time' in the
field along with the date.
thanks in advance.
The obvious question is why you stored the values as varchar instead of datetime. I suggest you fix
the design before it causes more trouble, bad performance and grief.
Having said that, you can convert the value to datetime (in the WHERE clause) before you do the
conversion. Since you use a date format which isn't language neutral (assuming the values are stored
in the format m/d/yyyy), you need to use CONVERT (with a proper formatting code) instead of CAST,
and because the conversion of the column value to datetime, you need to be prepared for the query to
be slow. Something like:
WHERE CONVERT(datetime, dtcol, 101) > '20070719'
I suggest you give below some time and thoughts:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Johnfli" <john@.ivhs.us> wrote in message news:uqVwl3S1HHA.3760@.TK2MSFTNGP03.phx.gbl...
>I have a field in a table that the datatype is varchar
> the data stored in the field is a date in the format of 7/19/2007
> I need to run a query that will give me all teh records that is of a certain date or newer.
> teh query I am running is
> Select * from table1 where {do rec] >= '07/19/2007'
> what I get is pretty much everything. items dated 7/31/2006 etc...
> how can I run this query correctly?
> The reason the field is a varchar is becasue I don't want the 'time' in the field along with the
> date.
>
> thanks in advance.
>
>
|||I tried going to your link, but the page cannot be found
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:A0346BCE-A87B-40A3-9C98-247B6A6B273D@.microsoft.com...
> The obvious question is why you stored the values as varchar instead of
> datetime. I suggest you fix the design before it causes more trouble, bad
> performance and grief.
> Having said that, you can convert the value to datetime (in the WHERE
> clause) before you do the conversion. Since you use a date format which
> isn't language neutral (assuming the values are stored in the format
> m/d/yyyy), you need to use CONVERT (with a proper formatting code) instead
> of CAST, and because the conversion of the column value to datetime, you
> need to be prepared for the query to be slow. Something like:
> WHERE CONVERT(datetime, dtcol, 101) > '20070719'
> I suggest you give below some time and thoughts:
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Johnfli" <john@.ivhs.us> wrote in message
> news:uqVwl3S1HHA.3760@.TK2MSFTNGP03.phx.gbl...
>
|||On Aug 2, 12:53 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I have a field in a table that the datatype is varchar
> the data stored in the field is a date in the format of 7/19/2007
> I need to run a query that will give me all teh records that is of a
> certain date or newer.
> teh query I am running is
> Select * from table1 where {do rec] >= '07/19/2007'
> what I get is pretty much everything. items dated 7/31/2006 etc...
> how can I run this query correctly?
> The reason the field is a varchar is becasue I don't want the 'time' in the
> field along with the date.
> thanks in advance.
I do agree that you really should change your database design to use a
proper data type. This will make your life easier and not require
some of the conversions. Storing the time in the database shouldn't
cause you any grief and you shouldn't have any issues with the query
you specified
Of course your workaround will be to utilize the convert function in
your query.
|||Perhaps there was a temporary IP hiccup somewhere. I just re-tried my link and it work fine for me:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Johnfli" <john@.ivhs.us> wrote in message news:etTzacV1HHA.4344@.TK2MSFTNGP03.phx.gbl...
>I tried going to your link, but the page cannot be found
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:A0346BCE-A87B-40A3-9C98-247B6A6B273D@.microsoft.com...
>
|||I am trying to change the field type to DateTime, but I can not figure out
how.
"acorcoran" <acorcoran@.gmail.com> wrote in message
news:1186105967.168834.62160@.19g2000hsx.googlegrou ps.com...
> On Aug 2, 12:53 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I do agree that you really should change your database design to use a
> proper data type. This will make your life easier and not require
> some of the conversions. Storing the time in the database shouldn't
> cause you any grief and you shouldn't have any issues with the query
> you specified
> Of course your workaround will be to utilize the convert function in
> your query.
>
|||Here's how such a ALTER TABLE statement could look like:
ALTER TABLE tbl
ALTER COLUMN col datetime
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Johnfli" <john@.ivhs.us> wrote in message news:%23meH$Jf1HHA.3788@.TK2MSFTNGP02.phx.gbl...
>I am trying to change the field type to DateTime, but I can not figure out
> how.
>
>
> "acorcoran" <acorcoran@.gmail.com> wrote in message
> news:1186105967.168834.62160@.19g2000hsx.googlegrou ps.com...
>

query assistance

I have a field in a table that the datatype is varchar
the data stored in the field is a date in the format of 7/19/2007
I need to run a query that will give me all teh records that is of a
certain date or newer.
teh query I am running is
Select * from table1 where {do rec] >= '07/19/2007'
what I get is pretty much everything. items dated 7/31/2006 etc...
how can I run this query correctly?
The reason the field is a varchar is becasue I don't want the 'time' in the
field along with the date.
thanks in advance.The obvious question is why you stored the values as varchar instead of date
time. I suggest you fix
the design before it causes more trouble, bad performance and grief.
Having said that, you can convert the value to datetime (in the WHERE clause
) before you do the
conversion. Since you use a date format which isn't language neutral (assumi
ng the values are stored
in the format m/d/yyyy), you need to use CONVERT (with a proper formatting c
ode) instead of CAST,
and because the conversion of the column value to datetime, you need to be p
repared for the query to
be slow. Something like:
WHERE CONVERT(datetime, dtcol, 101) > '20070719'
I suggest you give below some time and thoughts:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Johnfli" <john@.ivhs.us> wrote in message news:uqVwl3S1HHA.3760@.TK2MSFTNGP03.phx.gbl...[vbco
l=seagreen]
>I have a field in a table that the datatype is varchar
> the data stored in the field is a date in the format of 7/19/2007
> I need to run a query that will give me all teh records that is of a certa
in date or newer.
> teh query I am running is
> Select * from table1 where {do rec] >= '07/19/2007'
> what I get is pretty much everything. items dated 7/31/2006 etc...
> how can I run this query correctly?
> The reason the field is a varchar is becasue I don't want the 'time' in th
e field along with the
> date.
>
> thanks in advance.
>
>[/vbcol]|||I tried going to your link, but the page cannot be found
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:A0346BCE-A87B-40A3-9C98-247B6A6B273D@.microsoft.com...
> The obvious question is why you stored the values as varchar instead of
> datetime. I suggest you fix the design before it causes more trouble, bad
> performance and grief.
> Having said that, you can convert the value to datetime (in the WHERE
> clause) before you do the conversion. Since you use a date format which
> isn't language neutral (assuming the values are stored in the format
> m/d/yyyy), you need to use CONVERT (with a proper formatting code) instead
> of CAST, and because the conversion of the column value to datetime, you
> need to be prepared for the query to be slow. Something like:
> WHERE CONVERT(datetime, dtcol, 101) > '20070719'
> I suggest you give below some time and thoughts:
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Johnfli" <john@.ivhs.us> wrote in message
> news:uqVwl3S1HHA.3760@.TK2MSFTNGP03.phx.gbl...
>|||On Aug 2, 12:53 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I have a field in a table that the datatype is varchar
> the data stored in the field is a date in the format of 7/19/2007
> I need to run a query that will give me all teh records that is of a
> certain date or newer.
> teh query I am running is
> Select * from table1 where {do rec] >= '07/19/2007'
> what I get is pretty much everything. items dated 7/31/2006 etc...
> how can I run this query correctly?
> The reason the field is a varchar is becasue I don't want the 'time' in th
e
> field along with the date.
> thanks in advance.
I do agree that you really should change your database design to use a
proper data type. This will make your life easier and not require
some of the conversions. Storing the time in the database shouldn't
cause you any grief and you shouldn't have any issues with the query
you specified
Of course your workaround will be to utilize the convert function in
your query.|||Perhaps there was a temporary IP hiccup somewhere. I just re-tried my link a
nd it work fine for me:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Johnfli" <john@.ivhs.us> wrote in message news:etTzacV1HHA.4344@.TK2MSFTNGP03.phx.gbl...[vbco
l=seagreen]
>I tried going to your link, but the page cannot be found
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:A0346BCE-A87B-40A3-9C98-247B6A6B273D@.microsoft.com...
>[/vbcol]|||I am trying to change the field type to DateTime, but I can not figure out
how.
"acorcoran" <acorcoran@.gmail.com> wrote in message
news:1186105967.168834.62160@.19g2000hsx.googlegroups.com...
> On Aug 2, 12:53 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I do agree that you really should change your database design to use a
> proper data type. This will make your life easier and not require
> some of the conversions. Storing the time in the database shouldn't
> cause you any grief and you shouldn't have any issues with the query
> you specified
> Of course your workaround will be to utilize the convert function in
> your query.
>|||Here's how such a ALTER TABLE statement could look like:
ALTER TABLE tbl
ALTER COLUMN col datetime
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Johnfli" <john@.ivhs.us> wrote in message news:%23meH$Jf1HHA.3788@.TK2MSFTNGP02.phx.gbl...[vb
col=seagreen]
>I am trying to change the field type to DateTime, but I can not figure out
> how.
>
>
> "acorcoran" <acorcoran@.gmail.com> wrote in message
> news:1186105967.168834.62160@.19g2000hsx.googlegroups.com...
>[/vbcol]

query assistance

I have a field in a table that the datatype is varchar
the data stored in the field is a date in the format of 7/19/2007
I need to run a query that will give me all teh records that is of a
certain date or newer.
teh query I am running is
Select * from table1 where {do rec] >= '07/19/2007'
what I get is pretty much everything. items dated 7/31/2006 etc...
how can I run this query correctly?
The reason the field is a varchar is becasue I don't want the 'time' in the
field along with the date.
thanks in advance.The obvious question is why you stored the values as varchar instead of datetime. I suggest you fix
the design before it causes more trouble, bad performance and grief.
Having said that, you can convert the value to datetime (in the WHERE clause) before you do the
conversion. Since you use a date format which isn't language neutral (assuming the values are stored
in the format m/d/yyyy), you need to use CONVERT (with a proper formatting code) instead of CAST,
and because the conversion of the column value to datetime, you need to be prepared for the query to
be slow. Something like:
WHERE CONVERT(datetime, dtcol, 101) > '20070719'
I suggest you give below some time and thoughts:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Johnfli" <john@.ivhs.us> wrote in message news:uqVwl3S1HHA.3760@.TK2MSFTNGP03.phx.gbl...
>I have a field in a table that the datatype is varchar
> the data stored in the field is a date in the format of 7/19/2007
> I need to run a query that will give me all teh records that is of a certain date or newer.
> teh query I am running is
> Select * from table1 where {do rec] >= '07/19/2007'
> what I get is pretty much everything. items dated 7/31/2006 etc...
> how can I run this query correctly?
> The reason the field is a varchar is becasue I don't want the 'time' in the field along with the
> date.
>
> thanks in advance.
>
>|||I tried going to your link, but the page cannot be found
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:A0346BCE-A87B-40A3-9C98-247B6A6B273D@.microsoft.com...
> The obvious question is why you stored the values as varchar instead of
> datetime. I suggest you fix the design before it causes more trouble, bad
> performance and grief.
> Having said that, you can convert the value to datetime (in the WHERE
> clause) before you do the conversion. Since you use a date format which
> isn't language neutral (assuming the values are stored in the format
> m/d/yyyy), you need to use CONVERT (with a proper formatting code) instead
> of CAST, and because the conversion of the column value to datetime, you
> need to be prepared for the query to be slow. Something like:
> WHERE CONVERT(datetime, dtcol, 101) > '20070719'
> I suggest you give below some time and thoughts:
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Johnfli" <john@.ivhs.us> wrote in message
> news:uqVwl3S1HHA.3760@.TK2MSFTNGP03.phx.gbl...
>>I have a field in a table that the datatype is varchar
>> the data stored in the field is a date in the format of 7/19/2007
>> I need to run a query that will give me all teh records that is of a
>> certain date or newer.
>> teh query I am running is
>> Select * from table1 where {do rec] >= '07/19/2007'
>> what I get is pretty much everything. items dated 7/31/2006 etc...
>> how can I run this query correctly?
>> The reason the field is a varchar is becasue I don't want the 'time' in
>> the field along with the date.
>>
>> thanks in advance.
>>
>|||On Aug 2, 12:53 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I have a field in a table that the datatype is varchar
> the data stored in the field is a date in the format of 7/19/2007
> I need to run a query that will give me all teh records that is of a
> certain date or newer.
> teh query I am running is
> Select * from table1 where {do rec] >= '07/19/2007'
> what I get is pretty much everything. items dated 7/31/2006 etc...
> how can I run this query correctly?
> The reason the field is a varchar is becasue I don't want the 'time' in the
> field along with the date.
> thanks in advance.
I do agree that you really should change your database design to use a
proper data type. This will make your life easier and not require
some of the conversions. Storing the time in the database shouldn't
cause you any grief and you shouldn't have any issues with the query
you specified
Of course your workaround will be to utilize the convert function in
your query.|||Perhaps there was a temporary IP hiccup somewhere. I just re-tried my link and it work fine for me:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Johnfli" <john@.ivhs.us> wrote in message news:etTzacV1HHA.4344@.TK2MSFTNGP03.phx.gbl...
>I tried going to your link, but the page cannot be found
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:A0346BCE-A87B-40A3-9C98-247B6A6B273D@.microsoft.com...
>> The obvious question is why you stored the values as varchar instead of
>> datetime. I suggest you fix the design before it causes more trouble, bad
>> performance and grief.
>> Having said that, you can convert the value to datetime (in the WHERE
>> clause) before you do the conversion. Since you use a date format which
>> isn't language neutral (assuming the values are stored in the format
>> m/d/yyyy), you need to use CONVERT (with a proper formatting code) instead
>> of CAST, and because the conversion of the column value to datetime, you
>> need to be prepared for the query to be slow. Something like:
>> WHERE CONVERT(datetime, dtcol, 101) > '20070719'
>> I suggest you give below some time and thoughts:
>> http://www.karaszi.com/SQLServer/info_datetime.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Johnfli" <john@.ivhs.us> wrote in message
>> news:uqVwl3S1HHA.3760@.TK2MSFTNGP03.phx.gbl...
>>I have a field in a table that the datatype is varchar
>> the data stored in the field is a date in the format of 7/19/2007
>> I need to run a query that will give me all teh records that is of a
>> certain date or newer.
>> teh query I am running is
>> Select * from table1 where {do rec] >= '07/19/2007'
>> what I get is pretty much everything. items dated 7/31/2006 etc...
>> how can I run this query correctly?
>> The reason the field is a varchar is becasue I don't want the 'time' in
>> the field along with the date.
>>
>> thanks in advance.
>>
>>
>|||I am trying to change the field type to DateTime, but I can not figure out
how. :(
"acorcoran" <acorcoran@.gmail.com> wrote in message
news:1186105967.168834.62160@.19g2000hsx.googlegroups.com...
> On Aug 2, 12:53 pm, "Johnfli" <j...@.ivhs.us> wrote:
>> I have a field in a table that the datatype is varchar
>> the data stored in the field is a date in the format of 7/19/2007
>> I need to run a query that will give me all teh records that is of a
>> certain date or newer.
>> teh query I am running is
>> Select * from table1 where {do rec] >= '07/19/2007'
>> what I get is pretty much everything. items dated 7/31/2006 etc...
>> how can I run this query correctly?
>> The reason the field is a varchar is becasue I don't want the 'time' in
>> the
>> field along with the date.
>> thanks in advance.
> I do agree that you really should change your database design to use a
> proper data type. This will make your life easier and not require
> some of the conversions. Storing the time in the database shouldn't
> cause you any grief and you shouldn't have any issues with the query
> you specified
> Of course your workaround will be to utilize the convert function in
> your query.
>|||Here's how such a ALTER TABLE statement could look like:
ALTER TABLE tbl
ALTER COLUMN col datetime
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Johnfli" <john@.ivhs.us> wrote in message news:%23meH$Jf1HHA.3788@.TK2MSFTNGP02.phx.gbl...
>I am trying to change the field type to DateTime, but I can not figure out
> how. :(
>
>
> "acorcoran" <acorcoran@.gmail.com> wrote in message
> news:1186105967.168834.62160@.19g2000hsx.googlegroups.com...
>> On Aug 2, 12:53 pm, "Johnfli" <j...@.ivhs.us> wrote:
>> I have a field in a table that the datatype is varchar
>> the data stored in the field is a date in the format of 7/19/2007
>> I need to run a query that will give me all teh records that is of a
>> certain date or newer.
>> teh query I am running is
>> Select * from table1 where {do rec] >= '07/19/2007'
>> what I get is pretty much everything. items dated 7/31/2006 etc...
>> how can I run this query correctly?
>> The reason the field is a varchar is becasue I don't want the 'time' in
>> the
>> field along with the date.
>> thanks in advance.
>> I do agree that you really should change your database design to use a
>> proper data type. This will make your life easier and not require
>> some of the conversions. Storing the time in the database shouldn't
>> cause you any grief and you shouldn't have any issues with the query
>> you specified
>> Of course your workaround will be to utilize the convert function in
>> your query.
>

Query as stored procedure?

I've read a few articles about this, and trauled the net but I cant quite get one to fit my query.

I need to make this as a stored procedure,

select records_tbl.place_id, who_tbl.person_id, place_tbl.place_name, records_tbl.record_datetime, who_tbl.who_name, thing_tbl.thing_name
from records_tbl, place_tbl, who_tbl, thing_tbl
where records_tbl.place_id=place_tbl.place_id
and thing_tbl.thing_id='TH0001'
and who_tbl.person_id='WH000005'

But where I can simply specify on execute the thing_id and the person_id. ie Execute query1 ('TH0001', WH000005')?

So this would involve removing thos two id's and changing them to parameter names? defined at the top of the query? in theory I understand but in practise I do not.

Can anyone help?Create PROCEDURE MyQuery1
(
@.thing_id VARCHAR(20)
,@.person_id VARCHAR(20)
)
AS

SELECT records_tbl.place_id,
who_tbl.person_id,
place_tbl.place_name,
records_tbl.record_datetime,
who_tbl.who_name,
thing_tbl.thing_name
FROM records_tbl,
place_tbl,
who_tbl,
thing_tbl
WHERE records_tbl.place_id = place_tbl.place_id
AND thing_tbl.thing_id = @.thing_id
AND who_tbl.person_id = @.person_id

GO

and Now call
exec MyQuery1 @.thing_id='TH0001' , @.person_id= 'WH000005'

that what you want??|||thats excellent! thanks a lot! :) :)