Server Reconnection Logic
Mar 30, 2004
Hi,
I am a bit of a novice to SQL Server programming. So, forgive me if my question is too silly or easy.
I have an client-server application where the server connects to an SQL Server and does simple DB operations. In a particular test case, after my SQL Server is shut down and restarted when I try to execute an SQL Statement, the SQLExecute function fails. The problem is that the connection handle used to allocate and prepare the statement is invalid. So, I need to reconnect to the server.
Now my question is, how do I determine if the connection handle is valid or not ? That is, is there any API/function which when used can detect whether the connection handle is invalid or not.
I have tried calls to SQLGetConnectAttr and SQLGetInfo passing the connection handle, but these API's still return the correct value even if the handle is invalid.
Thanks in advance,
Ganesh
View 3 Replies
ADVERTISEMENT
Apr 14, 2008
Hi, sorry for another newbie question but I was wondering if the following is possible:
I have to take some information from a table which I have already created a query for. This information then has to be inserted into a new table but needs another column (not the promary key) with another unique custom identifer for each record in the format EX01 which is incremented by 1 for each record. I was wondering how is it possible to do this?
My approach was to create a view and then insert the values form the view into the new table but I still have no idea how to do the unique identifer. Was the first part of my approach correct or have been wrong from the start?
Thanks for any help.
View 1 Replies
View Related
Jan 14, 2006
I am using C# and ASP.NET 2.0, with SQL Server 2000. In my database I have a table that is similar to the following:
WebpageId WebpageAddress Handler 1 /company/about ~/about.aspx 2 /blog ~/blog.aspx
As you can guess, one of my queries will be a SELECT command where WebpageAddress = @address.
The hiccup I have is with a friendly URL such as the following:
/blog/2005/10/6
The friendly URLs have no extension, so I cannot immediately "pick out" the extension. (If the address were /blog.aspx/2005/10/6, then things would be simpler.)
What I am doing at present is using a loop in C# where I first perform a query with that full address. If no match is found, I trim the address back to the last slash (/blog/2005/10) and perform another query. If no match is found, I trim the address (/blog/2005) and perform another query. Again, no match is found, so I trim the address again (/blog) and perform yet another query. This time, a match is found, in which case the URL rewriting looks vaguely like this:
~/blog.aspx?parameters=2005/10/6
While this works fine, these friendly URLs require hitting the database up to four or five times. My question is, can this looping logic be moved to SQL Server?
My SQL knowledge is extremely basic, so I am just looking for someone to point me in the right direction. If it is not possible, I'd love to know now rather than wasting hours trying. If it is possible, I've love to know the keyword or technique involved, so that I can Google for the full answer.
View 1 Replies
View Related
Feb 24, 2015
I am not able to understand why just appending blank string in below code removes '<item>' from xml result.Is it converting to varchar datatype ?
This question is just for my understanding
CREATE TABLE #tbl
(id INT IDENTITY(1,1),
item varchar(100))
INSERT #tbl
SELECT 'This'
UNION ALL
[Code] .....
View 4 Replies
View Related
May 25, 2008
sub = 1
DETAILED-VOUCHER
VOUCHER-A OCCURS 13 TIMES.
ACC-NUM PIC X(6).
ADB-V OCCURS 12 TIMES.
CNT-V PIC 9999 COMP-3.
AMT-V PIC S9(9)V99 COMP-3.
It is a initialization of the two dimensional array DETAILED-VOUCHER(Temporary Variable) which consists of the account number for that account number different group of people say as (teachers, state police etc) have different 12 kinds of amounts such as (DEATH-AMT, PAYEE-AMTetc ) for every account number. SUB (Temporary Variable) is used as subscript whose value will be incremented by 1 after initializing current position by zero in order to initialize all 12 positions in array.
LOP-ZERO-2.
MOVE ZEROS TO CNT-V (1 SUB).
MOVE ZEROS TO AMT-V (1 SUB).
ADD 1 TO SUB.
IF SUB NOT GREATER THAN 12
GO TO LOP-ZERO-2.
MOVE 2 TO SUB.
LOP-ZERO-3.
MOVE CNTS (1) TO CNTS (sub).
ADD 1 TO SUB.
IF SUB NOT > 13 GO TO LOP-ZERO-3.
MOVE '401.21' TO ACC-NO-V (1). ---------- This are the hotcode values
MOVE '410.01' TO ACC-NO-V (2).
MOVE '410.02' TO ACC-NO-V (3).
MOVE '410.03' TO ACC-NO-V (4).
MOVE '410.04' TO ACC-NO-V (5).
MOVE '410.06' TO ACC-NO-V (6).
MOVE '410.05' TO ACC-NO-V (7).
MOVE '411.01' TO ACC-NO-V (8).
MOVE '411.02' TO ACC-NO-V (9).
MOVE '331.01' TO ACC-NO-V (10).
MOVE '212.19' TO ACC-NO-V (11).
MOVE '212.22' TO ACC-NO-V (12).
MOVE ZEROS TO XYZ-S (1).
MOVE ZEROS TO MNO-B (1).
MOVE ALL '9' TO A-KEY
MOVE 01 TO D-X.
MOVE 1 TO SYS-Y
Please give suggest me how to write logic in the sql server. I want to how to write two dimensional array loop in the sql server.
View 1 Replies
View Related
Feb 20, 2015
I would like to create a function that take a value and run some logic and output the value
I have a table like this
Table A
value
*
001
004.00
3.0
1.22
Logic I want to run is
The value that you are passing is numeric and numeric with only decimal 0 value, and then convert it to integer otherwise leave as it is
So if I run a query something like this
Select value, fn_convertointerger(value) as converted_value from TableA
I will get
Value converted_value
* *
001 1
004.00 4
3.0 3
1.22 1.22
2.02 2.02
4.000 4
Jkil& Jkil&
How can I create a function like this to convert specific numeric value?
View 9 Replies
View Related
May 25, 2015
I have a below query which have a date filter like "EST_PICK_DATE between '2015-02-01' and '2015-06-01'", where the logic is EST_PICK_DATE should be 3 months from the current month and 1st date of next month. Ex for current month MAY, EST_PICK_DATE shoulc be between '2015-02-01' and '2015-06-01'. I need to write below query dynamically. In below query i have hardcoded the value ("EST_PICK_DATE between '2015-02-01' and '2015-06-01'"), but it should take dynamically. How to achieve this?
I am using this query in SSIS package, So Shall i do in SQL level or we should implement this logic in package? If yes, How?
INSERT INTO STG_Open_Orders (Div_Code, net_price, gross_price) SELECT ord.DIV_CODE AS Div_Code, ord_l.NET_PRICE AS net_price, ord_l.gross_price AS gross_price, FROM ORD ord inner join ORD_L ord_l ONord.ORD_ID=ord_l.ORD_ID WHERE ord_l.EST_PICK_DATE BETWEEN '2015-02-01' AND'2015-06-01'
View 1 Replies
View Related
Sep 22, 2015
I am trying to split the annual cost into monthly numbers based on the contract Period.Since the contract period varies from company to company not sure how to implement the logic.
create table #Invoice
(
Company Varchar(50),
Startdate2015 DateTime,
EndDate2015 DateTime,
ContractPeriod2015 Int,
ContractAmount2015 Float,
[code]..
View 3 Replies
View Related
Dec 17, 2007
Hi All,
This is the code for calculating a formula field in Crystal Reports.
I want to implement the same in Sql Server Reporting Services..
But it doesn't have the feature of For Loop....
The strings started with @ are formula fields....
Can anyone tell me, how can the below code be implemented in Sql Server Reporting Services
numbervar YR;
for YR := 1 to {@CalcFiscalAge} step 1 do
(
IF YR = {@CalcLifeCode} +1 then
locFactor1 := 1;
exit for;
IF (YR = 1 OR YR = {@CalcFiscalAge}) THEN
HALF_YEAR := 2
ELSE
HALF_YEAR := 1 ;
LINEAR := ROUND(REM/ ({@CalcLifeCode}-YR+1.5)/HALF_YEAR,4);
MACR := ROUND(REM / {@CalcLifeCode}*{@CalcSRate}/HALF_YEAR,4);
IF MACR >= LINEAR then
DEP := MACR
ELSE
DEP := LINEAR;
locFactor1 := locFactor1 + DEP ;
REM := 1 - locFactor1;
locFactor := locFactor1;
);
Thanks in advance
Regards,
View 3 Replies
View Related
Aug 9, 2015
I have a data model with 7 tables and I'm trying to write a stored procedure for each table that allows four actions. Each stored procedure should have 4 parameters to allow a user to insert, select, update and delete a record from the table.
I want to have a stored procedure that can accept those 4 parameters so I only need to have one stored procedure per table instead of having 28 stored procedures for those 4 actions for 7 tables. I haven't found a good example online yet of conditional logic used in a stored procedure.
Is there a way to add a conditional logic IF statement to a stored procedure so if the parameter was INSERT, go run this statement, if it was UPDATE, go run this statement, etc?
I have attached my data model for reference.
View 9 Replies
View Related
Jul 23, 2007
Pocket PC 2003, SQL Compact Edition, SQL2005, IIS6.0
I implemented a business logic handler to deal with conflicts. When I deploy it on the SQL server which is also the web replication server, the logic handler seems working fine. However, if I deploy this handler to another web server. The logic handler failed to be loaded.
My enviroment settings are desribed as below.
Machine A, distributor, with database and publication. The business logic handler is deployed at C:Program FilesMicrosoft SQL Server90COMBusinessLogicHandler.dll. It's registered by using sp_registercustomresolver. The @assembly is specified as @assembly=C:Program FilesMicrosoft SQL Server90COMBusinessLogicHandler.dll';
Machine B, IIS server. The same business logic handler is deployed at C:Program FilesMicrosoft SQL Server90COMBusinessLogicHandler.dll on the Machine B itself.
When I ran the web replication, the Merge Agent reported the error as below.
Error loading custom assembly "C:Program FilesMicrosoft SQL Server90COMBusinessLogicHandler.dll", Error: "Could not load file or assembly 'C:\Program Files\Microsoft SQL Server\90\COM\BusinessLogicHandler.dll' or one of its dependencies. The given assembly name or codebase was invalid.
It seemed that the Merge Agent had trouble to find my logic handler because the path reported in the error log has two backward slashes. I have no idea where did that came from. I am not sure if that's the cause of the error. Without business logic handler. I had successfully finished web replication of Machine B to sync with Machine A. If I setup web replication directly on Machine A with business logic handler, I can successfully sync as well.
Does anyone has any idea about how to correctly deploy business logic handler on a web server?
Thanks,
Nigel
View 1 Replies
View Related
Nov 13, 2014
I could deploy across my environment, which is a mix of 2008R2/2012 servers, to give some information on log files. Running into a silly issue right off the bat. The table that DBCC LogInfo() conjures out of magic is different between the two. In 2012 it gained the RecoveryUnitID column. So I'm trying to write some logic to create a temp table based on which version is running. I would like to avoid a global temp table if possible. Here's what I've tried:
sp_executesql creates a table outside of the scope of my session:
DECLARE @PrVers NVARCHAR(128)
, @PrVersNum DECIMAL(10,2)
, @StageTable NVARCHAR(1024) = N''
[code]....
View 9 Replies
View Related
May 4, 2015
I'm trying to convert the query immediately below into a function with the conditional logic to return a VARCHAR value with the gender: male, female or unknown.
SELECT empid, firstname, lastname, titleofcourtesy,
CASE
WHEN titleofcourtesy IN('Ms.', 'Mrs.') THEN 'Female'
WHEN titleofcourtesy = 'Mr.' THEN 'Male'
ELSE 'Unknown'
END AS gender
FROM HR.Employees;
GO
Below is the conditional logic function I'm trying to create to replicate the logic above.
CREATE FUNCTION dbo.Gender
(
@male AS VARCHAR(10),
@female AS VARCHAR(10),
@unknown AS VARCHAR(10)
)
RETURNS VARCHAR(10)
[Code] .....
View 6 Replies
View Related
Oct 20, 2014
I have a stored proc that contains an update which utilizes a case statement to populate values in a particular column in a table, based on values found in other columns within the same table. The existing update looks like this (object names and values have been changed to protect the innocent):
UPDATE dbo.target_table
set target_column =
case
when source_column_1= 'ABC'then 'XYZ'
when source_column_2= '123'then 'PDQ'
[Code] ....
The powers that be would like to replace this case statement with some sort of table-driven structure, so that the mapping rules defined above can be maintained in the database by the business owner, rather than having it embedded in code and thus requiring developer intervention to perform changes/additions to the rules.
The rules defined in the case statement are in a pre-defined sequence which reflects the order of precedence in which the rules are to be applied (in other words, if a matching value in source_column_1 is found, this trumps a conflicting matching value in source_column_2, etc). A case statement handles this nicely, of course, because the case statement will stop when it finds the first "hit" amongst the WHEN clauses, testing each in the order in which they are coded in the proc logic.
What I'm struggling with is how to replicate this using a lookup table of some sort and joins from the target table to the lookup to replace the above case statement. I'm thinking that I would need a lookup table that has column name/value pairings, with a sequence number on each row that designates the row's placement in the precedence hierarchy. I'd then join to the lookup table somehow based on column names and values and return the match with the lowest sequence number, or something to that effect.
View 9 Replies
View Related
May 10, 2008
Can anyone see where my logic may have gone a stray?
A user would be attempting to update a gridview row, I'm storing the amount of an item and the qty they purchased. If the order was for $150 and the grid has two item 1 x $50 and 2 x $50 the order already totals $150, so if the user tries to change the first item to a qty of 2 x $50 the total would be $200 and this would be more than the total payment or $150.
I want to then tell them they cannot do this.
@AP_ID Int,@AI_ID int,@PurchaseAmount money,@PurchaseQTY int,@LastUpdate datetime,@LastUpdateBy nvarchar(50),@Receipt_ID intASdeclare @current_item_total decimal(18,2)declare @new_item_total decimal(18,2)declare @total_payments decimal(18,2)declare @current_purchase table(receipt_id int,purchase_amt money)INSERT INTO @current_purchase SELECT tblPurchase.Receipt_ID,(tblPurchase.PurchaseAmount * tblPurchase.PurchaseQty) as purchase_amtFROM tblPurchase INNER JOIN tblReceipts ON tblPurchase.Receipt_ID = tblReceipts.Receipt_IDWHERE tblReceipts.Receipt_ID=@receipt_id--Get total already saved to recordselect @current_item_total = sum(purchase_amt) from @current_purchase--Get total amount of payment saved to recordselect @total_payments = sum(tblReceipts.AmountPaid) from tblReceipts where tblReceipts.receipt_id=@receipt_id--Get total amount user is trying to save to recordset @new_item_total = (@PurchaseAmount * @PurchaseQTY)--If current total plus new total is greater than total payment, tell user they cannot do this.IF ((@current_item_total + @new_item_total) > @total_payments)BEGINSELECT 'You are attempting to add more than your total payment.' AS MESSAGEENDELSEBEGIN--UPDATE ROWUPDATE tblPurchase SET [AI_ID]=@AI_ID,[PurchaseAmount]=@PurchaseAmount,PurchaseQTY=@PurchaseQTY,LastUpdate=@LastUpdate,LastUpdateBy=@LastUpdateBy WHERE [AP_ID]=@AP_ID SELECT 'Item was updated.' AS MESSAGEEND
View 2 Replies
View Related
Jan 6, 2004
Trying to bind some data to a datalist for a report.
User selects an Industry from a dropdown list and then I dump all records for that industry. However in order to parse some of the record field values into names (I.E. from a 1 to the actual company name) for some records I have to read TABLE_ONE and for other records I might have to read TABLE_TWO depending on the value of FIELD_ONE.
If FIELD_ONE = "A" then I get the NAME from TABLE_ONE.
If FIELD_ONE = "B" then I get the NAME from TABLE_TWO.
If FIELD_ONE = "C" then I get the NAME from TABLE_THREE.
I'm lost at how to get started on this. I thought about adding IF statements to my query but these won't work because I'm not passing in the value of FIELD_ONE ahead of time - it's part of the query. So I thought maybe I could do a pre-read and store all FIELD_ONE values in an ArrayList and pass these in as parameters, but the stored proc is only being called once - so that won't work.
Any thoughts on how I can do this?
View 2 Replies
View Related
Apr 27, 2006
tbl_one hv 8mil rows, tbl_2 have 8k rows...
if
select count(*) from tbl_one
where sub_col1 = 2
return 3mil rows
and
select count(*) from tbl_2
where ad_col1 = '000009'
return 4k rows
HOW COME..
select count(*) from tbl_one,tbl_2
where (sub_col1 = 2 and ad_col1 = '1234')
return more than 12 billion rowss?? helpp..
View 7 Replies
View Related
May 3, 2006
I have to write trigger to relate two table.
If I have made changes like insert, update and modify in one table1 automatically have to change the table2 and vice versa.
How this can be done, do we need to point the common key fields in both table while inserting
View 8 Replies
View Related
Jun 12, 2007
What i'm trying to do is if this column dt.IN_DIV_NO is populated take that value first if it's null than rr.AIQ_R_DIVISION_NO and if that column is null than rr.F_DIVISION_NO.
This is what i came up with, will it evaluate the way i need it to?
case When dt.IN_DIV_NO is not null then dt.IN_DIV_NO else case when rr.AIQ_R_DIVISION_NO is null then rr.F_DIVISION_NO end end as Current_DIV
Thank-you
View 4 Replies
View Related
Mar 13, 2008
I have a logic problem,
I am selecting from a table
Select * from TAB_A
where state not in (1,4)
and create_date < '2008-01-01'
and
(
(x != 2 and y != 1 and z != 4) or
(x != 6 and y != 3 and z != 1) or
(x != 8 and y != 0 and z != 9)
)
then for example i am getting results where x,y and z is equal to one
or more of above combinations ?
Now i vaguely remember that using or with a != messes up the logic ?
if so can i use an NOR ? does it exist ?
View 5 Replies
View Related
Mar 25, 2008
I am looking for the best approach to update a table's column based on the results of two other different tables. My tables structure is as follows.
Table 1 has columns A and B (tblemployee has location and employeenumber )
Table 2 has columns C and A (tblocation has locationID (identity) and location)
Table 3 has coumns C and B (tblcountry locationID (foreign key) and employeenumber.)
I want to update Table 3 (tblcountry) with the new locationID if and employee changes location or gets miscoded using an SSIS package.
Thanks in advance.
View 7 Replies
View Related
Nov 14, 2007
I am having a little problem with my logic.
i have a table simplehoursassignment that has a field named br.
i need to get the value from simplehoursassigment & say if @mybit AND br > 0
set each day.
--CREATE PROCEDURE rpt_siteMealList(
-- (@cmb1 AS VARCHAR(100)) WITH ENCRYPTION
--)
--
--AS
DECLARE @dtm1 AS DATETIME
DECLARE @mybit AS INTEGER
SET @mybit=10
SELECT @mybit = CASE datepart(dw,@dtm1)
WHEN 1 THEN 1 -- 'Sunday'
WHEN 2 THEN 2 -- 'Monday'
WHEN 3 THEN 4 -- 'Tuesday'
WHEN 4 THEN 8 -- 'Wednesday'
WHEN 5 THEN 16 -- 'Thursday'
WHEN 6 THEN 32 -- 'Friday'
WHEN 7 THEN 64 -- 'Saturday'
END
SELECT br FROM simplehoursassignment
IF @mybit AND br > 0
BEGIN
CASE WHEN @mybit = 1 THEN 'Sunday'
WHEN @mybit = 2 THEN 'Monday'
WHEN @mybit = 3 THEN 'Tuesday'
WHEN @mybit = 4 THEN 'Wednesday'
WHEN @mybit = 5 THEN 'Thursday'
WHEN @mybit = 6 THEN 'Friday'
WHEN @mybit = 7 THEN 'Saturday'
END
END
View 3 Replies
View Related
Apr 30, 2008
I have this query that is returning the same result twice and i cannot find why. I only have one record in Subquote.
Does anyone know what the problem is?
Code Snippet
SELECT s.*
FROM Quote q
INNER JOIN SubQuote s
ON q.id = s.quoteID
INNER JOIN TakeOffSheetItem t
ON t.quoteID_takeoffitem = q.id AND t.subQuoteID_takeoffitem = s.subquoteid
INNER JOIN PipeGroup p
ON p.quoteID_PipeGroup = q.id AND p.subQuoteID_PipeGroup = s.subQuoteID
WHERE (q.id = 1
AND q.deleted = 0
AND s.deleted = 0
AND t.deleted_takeoffitem = 0
AND p.deleted_PipeGroup = 0)
ORDER BY s.subquoteid
Thanks
K
View 4 Replies
View Related
Jul 31, 2006
I am trying to figure out how to set up this database.
Basically, there are products with their associated fields. Each product can belong to multiple categories, and each category also has subcategories.So far I have the following, but not sure if this is the best way to set it up...TABLE Products:product_id (int) (1-many relationship to product_id in Table Product_Category)sku (int)descriptionpriceTABLE Categorycategory_id (int) (1-many relationship to category_id in Table Product_Category)nameTABLE SubCategorysubcategory_id (int) (1-many relationship to subcategory_id in Table Product_Category)category_id (int)nameTABLE Product_Categoryprodcat_id (int) product_id (int) (many-1 relationship to product_id in Table Products)category_id (int) (many-1 relationship to category_id in Table Category)subcategory_id (int) (many-1 relationship to subcategory_id in Table SubCategory)
Thanks,Mick
View 1 Replies
View Related
Mar 20, 2007
Suppose in the table below the left column is called intNumber and right column is called strItem.I want to select the rows where the intNumber number appears for the first time(the ones marked with arrows), how do I do it?
View 4 Replies
View Related
May 21, 2007
I'm trying to figure out how to track what messages have been read by what users in a message board program we wrote. The users are identified by an ID number and each message has a unique number.
Let's say there are 10000 users and 200,000 messages. What would be the database setup/logic to track every user as to what messages they had read or not read? Maybe I'm missing something but it does not seem to be a trivial task.
View 2 Replies
View Related
Feb 27, 2006
Hi there!
Hope somebody got some better insight into this problem than i have. I'm struggling with some db logic.
Overview:
I have a client who is a publisher. They would like to post all their advert-size specs for each magazine to their web site. My first thought was that ill be able to build one table and populate it with the information for each magazine. I was wrong. Each magazine comes in a different size. Thus each magazine will have different advert-sizes as well.
Layout for a magazine will look something like this:
Size Trim Type Bleed
Full Page 280x440 270x430 290x450
Half Page 140x220 130x210 150x230
etc...
Some mags will not have values for Half pages since they dont print half pages and others will not have specs for Bleed.
Because of this - as an easy way out I created a table per magazine. It works but i dont think its very smart.
Break it down!
Ok so what im trying as a solution is to have three tables. The 1st table will hold the magID and Size values (e.g Half Page). The 2nd table will also have the magID and the Trim, Type, Bleed info. The 3rd table holds magID, sizeID, specID and the actual value (140x220).
I thought that this would be better because within these three tables i can store the information for each magazine regardless of their differences. Brilliant!
The Problem.
The problem comes when i have to put it all back together again. I need to represent this data in a table so i can bind it to a datagrid. I have NO idea how to do this. What i THINK i need to have is some temp table created on the fly. The row names for this temp table will come from the 1st table. The column names will come from the 2nd table and the values for each field will come from the 3rd table bound by foreign keys.
I've somewhat managed to do this with INNER JOINS. But it doesn't give the desired result. I need to set row and column NAMES using tables 1 & 2 then populate the columns with table 3, then bind to a datagrid.
Any ideas on how i could manage this?
If you made it tis far through my question then thanks anyways! I hope you can help me out!
Ta
View 4 Replies
View Related
Oct 26, 1999
I need to create a view joining 2 tables(1st with data, 2nd is a lookup) but I need to have some
logic(IF/ ELSE,ELSE and an UPDATE).
What is the best way?
-aw
View 1 Replies
View Related
Feb 15, 2007
okay;
i have a table called tblSlots
tblSlots is a list of Start datetimes and End datetimes.
every day has the same list of 10 slots.
tblSlots:
PKSlotINDEX
datStartTime
datEndTime
then i have a table called tblPersons
tblPersons:
PKPersonINDEX
txtLast
txtFirst
then i have a table called tblSchedule
tblSchedule:
PKScheduleINDEX
fkSlotINDEX
fkPersonINDEX
i want to write a query that takes any one specific person's schedule for an entire specific day, adds an arbitrary number of days to datStartTime, and finally inserts the PKSlotINDEX corresponding to the calcultated StartTime and fkPersonINDEX.
wow complicated isnt it...
the goal is to take the schedule of one day for one person and copy it to another day; i can scrap my current layout if necessary.
thanks!
View 1 Replies
View Related
Jul 28, 2005
I have 2 tables:
First table: empID,PlanID,groupID
Second: PlanID,groupID,EffectiveDate,TerminationDate,DeadlineDate
I need to show only employee with in spesific group who is not
enroll for the current month until deadline passed.
Example:
empID PlanIDgroupID
11012
PlanIDGroupIDEffectiveDate TerminationdateDeadlineDate
101208/01/200508/31/200508/15/2005
111209/01/200509/31/200509/15/2005
91208/01/200508/31/200508/15/2005
If I run it today I should not get any results back. If I run in
on 8/15/2005 I should get back data with palnID11.
View 1 Replies
View Related
Jul 27, 2004
Arrrgggg, nothing makes any sence any more. I need to write a select statement that will display results based on one of two things...
Zip Code OR City State
By the way this is a Table of addresses with zip codes, I also have a Lat/Long table associated to all the different zip codes that need to be joined.
The Psuedo code is somethinglike this...
SHOW All People WITH Appropreate Longditude and Latitude
Who Live
In @This ZipCode
OR
(In @State AND @City)
-------------------
Table Dealers
-------------------
dlrID - varchar
dlrName - varchar
dlrAddress - varchar
dlrCity - varchar
dlrState - varchar
dlrZip - varchar
dlrCountry - varchar
-------------------
-------------------
Table ZipCodes
-------------------
zipCode
zipCity
zipState
zipCountrty
zipLat
zipLong
...
-------------------
I have achieved this with Joins but the query takes 2+ minutes to execute
What am I doing wrong??? I am sure there is a better approach to this!
Thanks,
--Lito
View 13 Replies
View Related
Oct 9, 2006
Hi,
This might just be my brain not working after the weekend, but I'm having problems working out just how to do this.
The table:
CREATE TABLE [dbo].[tblQuiz] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[q1] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q2] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q3] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q4] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q5] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q6] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q7] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q8] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[quizdate] [datetime] NULL ,
[ipaddress] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sessionid] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[score] [int] NULL
)
The field [q5] has one of three values in it: "Happy", "Unhappy" or "Neither".
I have a list of about 50 session ID's. If a record in [tblQuiz] has a [sessionid] that matches one in this list, then:
if [q5]= 'Happy' then change it to 'Unhappy'
if [q5]= 'Unhappy' then change it to 'Happy'
The only way I can think of is to change all of one matching record type to some other value, then change all the other type, then change all the original type back to the other type. Which doesn't make much sense even when I've written it down, much less when I'm doing it and have to remember where I'm at. Is there a better way?
View 3 Replies
View Related
Jan 4, 2007
Hi pundits,
I need a logic for solving a realtime issue.
I have series 2^0,2^1,2^2,.......2^n ( i.e 1,2,4,8,16.....2^n)
I want to write a function(algorithm) which returns all the terms used(added) to form the number
e.g fun(7) :-1,2,4
fun(5) :-1,4
fun(257):-1,256
fun(6):-2,4
Ur help is highly appreciated.
Thanks in advance.
View 1 Replies
View Related
