Converting MS Query To SQL Server 2000
Jul 20, 2005
I am struggling rewriting my query from MS Access' IIF, Then to SQL
Servers TSQL language. I am hoping some one can give me some
guidance. I believe I have the first portion of the query correct but
do believe this requires a "NESTED" argument. This is where I am
lost.
My Original MS ACCESS Query reads--
SELECT DISTINCTROW REGION_TRAFIC.*,
IIf(Mid([SWITCH CLLI],5,2)=[TERM STATE],
IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTRA_VENDOR4],
IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTRA_VENDOR3],
IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTRA_VENDOR2],
IIf([Carrier]="VENDOR1",[DOMESTIC LD
RATES]![INTRA_VENDOR1])))),
IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],
IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],
IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],
IIf([Carrier]="VENDOR1",[DOMESTIC LD
RATES]![INTER_VENDOR1]))))) AS CPM,
[CPM]*[MOU] AS COST
INTO INTRALATA_LD
FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
ORDER BY REGION_TRAFIC.[TERM LATA] DESC;
I have tried to re-write this in SQL SERVER as --
SELET DISTINCT REGION TRAFIC.*,
CASE
WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
RATES].INTRA_VENDOR4
WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
RATES].INTRA_VENDOR3
WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
RATES].INTRA_VENDOR2
WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
RATES].INTRA_VENDOR1
ELSE
WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
RATES].INTER_VENDOR4
WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
RATES].INTER_VENDOR3
WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
RATES].INTER_VENDOR2
WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
RATES].INTER_VENDOR1
END
AS CPM
CPM*MOU AS COST
INTO INTRALATA_LD
FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
ORDER BY REGION_TRAFIC.[TERM LATA] DESC
My challenge is the Case portion of the query and the nesting! I am
not sure if I have the correct syntax or even chose the correct
argument for my purpose.
Any guidance is appreciated.
View 9 Replies
ADVERTISEMENT
Mar 30, 2006
Hello, I have the following query in Access 2000 that I need to convertto SQL 2000:UPDATE tblShoes, tblBoxesSET tblShoes.Laces1 = NullWHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B"))WITH OWNERACCESS OPTION;The ShoesID in the tblShoes table is an autonumber, however the recordsin the tblBoxes have the ShoesID converted to text.This query runs ok in Access, but when I try to run it in the SQLServer 2000 Query Analizer I get errors because of the comma in the"UPDATE tblShoes, tblBoxes" part. I only need to update the tblShoesfield named Laces1 to NULL for every record matching the ones in thetblBoxes that are marked with an "A" or an "B" in the tblBoxes.Code8field.Any help would be greatly appreciated.JR
View 2 Replies
View Related
Aug 10, 2007
This query from access does not work in sql server 2000. How do I write this in sql to run?
SELECT First(tri_ProcMast.ddesc) AS FirstOfddesc, tri_ProcMast.proccd
FROM tri_ProcMast
GROUP BY tri_ProcMast.proccd;
View 2 Replies
View Related
Oct 18, 2004
Hi,
I worked on a project in ASP.NET using SQL server 2000 as the back end. Its a conversion application that I rewrote in ASP.NET using C#. I need to import the old data in Access db into SQL server 2000 and I have very little knowledge about doing it. The data in not a direct one -one transformation. There are considerable changes to the Database design and data types. Any help and suggestions wud be really helpful. Also, any article links wud be great.
Thanks
View 1 Replies
View Related
Oct 9, 2006
Hi.. I want to convert .dbf files to sql server 2000 tables.. without using any tools. I need to create a different structure for sql server tables other than contains in the .dbf files. May be the dbf files contain only 3 columns. but i need 5 columns and some calculations to determine the values of some fields to insert into sql server table... i need to code this using c# in asp.net.. can u help me? thanks in advance.. Fraijo
View 3 Replies
View Related
Jan 2, 2006
OK, I have some SQL Server 2000 databases that I want to convert to 2005. My question: how do I do that?
I tried importing the data, but it only picked up 3 tables out of 7 user tables in one of my SS2K databases. Plus, it did not pick up any stored procedures or any other kind of objects.
So I tried restoring from a backup, but predictably it could not do that because the original backup was on a different server.
I also tried the upgrade advisor utility that comes with SS2K5, but it could not seem to connect to my SS2K server -- it said it could not find any SQL Server objects on the server, even though I am connected using Enterprise Manager and have been using this server for a long time. Is it looking for 2005 databases? That wouldn't make a lot of sense.
Anyway, I am stumped... can anyone provide help on this?
Thanks!
View 1 Replies
View Related
May 4, 2006
I have a large (huge) database that I want to copy onto my new slq 2005 test server. I'm leaning toward detaching the data on my 2000 box, duplicating it, copying it to my new 2005 machine, and attaching it. Is it possible that it could be that simple? If not, how is it done? Thanks a bunch for any help or pointers to the articles I was totally unable to find on the subject.
View 3 Replies
View Related
Jul 20, 2005
I am trying to upsize a database to SQL server (on which I am a novice). InAccess as part of a much more complex query I had the following (from sqlview)SELECTIIf(InStr([ItemName],"*")>0,Left([ItemName],InStr([ItemName],"*")-1),[ItemName]) AS ShortName FROM corp_infoWhich gives a return value for the whole of ItemName if there is no star init, or the portion up to the star if there is a starI am having a nightmare trying to get an equivalent in SQL server. I'veworked out that Instr is charindex in sql and can adjust for that, but can'twork out how to get a conditional select statement working.It may well be obvious, but any help much appreciated. Thanks.Robin Hammondwww.enhanceddatasystems.com
View 1 Replies
View Related
Oct 16, 2015
I have an inline query that I am trying to convert it into JOIN, results are not coming out the same:
Original query:
SELECT distinct
(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE DAY(LastWorked) = DAY(GETDATE()) and MONTH(LastWorked) = MONTH(GETDATE()) and YEAR(LastWorked) = YEAR(GETDATE()) and PrimStat = '1' and Collector = '3') As DcountMy query:
[code]....
View 8 Replies
View Related
Mar 23, 2015
I am working on a code that will convert the query output into a html query output. That is the output of the query will be along with html tags so that we can save it as a html file.
The stored procedure that i have used is downloaded from symantec website and is working fine. Below is the code of that stored procedure.
/****** Object: StoredProcedure [dbo].[sp_Table2HTML] Script Date: 12/21/2011 09:04:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Table2HTML] (
@TABLENAME NVARCHAR(500),
@OUTPUT NVARCHAR(MAX) OUTPUT,
[Code] ....
The code works fine and i am able to get the output with html tags. The problem occurs when i insert stylesheet in the code. I tried to enforce styles using a stylesheet for the table returned in my sql code, so that it will look good. below is the stylesheet code that i inserted between <head> and </head> tags.
<style type="text/css">table.gridtable { font-family: verdana,arial,sans-serif; font-size:10px; color:#333333;border-width:1px; border-color: #666666; border-collapse: collapse; } table.gridtable td {border-width: 1px; padding: 8px; border-style: solid; border-color: #666666; background-color: #ffffff;}</style>
If I run the procedure without the style sheet code, it works fine. but when i run the procedure with style sheet code i am getting the below errors.
Msg 105, Level 15, State 1, Line 98
Unclosed quotation mark after the character string '</table></body><'.
Msg 102, Level 15, State 1, Line 98
Incorrect syntax near '</table></body><'.
[Code] .....
I checked the code and i am not able to find what is the mistake. I tried changing the quotation mark but it didn't worked.
View 6 Replies
View Related
Jul 27, 2006
Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:
Function Main()
on error resume next
dim cn, i, rs, sSQL
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
set rs = DTSGlobalVariables("SQLstring").value
for i = 1 to rs.RecordCount
sSQL = rs.Fields(0).value
cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution
rs.MoveNext
Next
Main = DTSTaskExecResult_Success
End Function
This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)
Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:
public Sub Main()
...
Dts.TaskResult = Dts.Results.Success
End Class
I get the following error when I attempt to compile this:
Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.
I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.
- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).
Given this statement:
dim cn, i, rs, sSQL
I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!
View 7 Replies
View Related
Feb 19, 2004
how do i go about converting the Mysql Script below to Sql Server Script ????
CREATE TABLE cat (
id int NOT NULL auto_increment,
name char(20) NOT NULL default '',
PRIMARY KEY (id)
);
INSERT INTO cat VALUES (1,'Not Categorised');
ALTER TABLE cat AUTO_INCREMENT = 5;
i want a script that:
creates the table
inserts a row of data and then
sets the increment field to start at 5
i tried using identity(1,1) and
SET IDENTITY_INSERT cat ON;
INSERT INTO cat VALUES (1,'Not Categorised');
but when i enter the data in i get the error below
An explicit value for the identity column in table 'cat' can only be specified when a column list is used and IDENTITY_INSERT is ON.
please can anyone help or advice
thanks
View 4 Replies
View Related
Apr 23, 2007
I have abt 30 reports currrently in SQL 2000. My data still resides in SQL SERVER 2000. However, I need to convert these reports to SSRS 2005.
I want to use the cool features like sorting, multi-value parameters from the RS 2005.
Can you pl advise how I shud go abt converting these reports from RS 2000 TO RS2005 .
Thanks.
View 5 Replies
View Related
Aug 28, 2007
hi All,
I tried following piece of code in SQL 2005 , is working fine.
Select * from Table
FOR XML RAW('RECORDS')ELEMENTS,ROOT('MyTable').
But when i tried the same thing in SQL 2000 , it was not working .
Plz suggest a way in 2000 to get the XML output with custom RootNode Name in 2000 also like in 2005 ?
Thanks in advance.
Mohit
View 3 Replies
View Related
May 22, 2008
i have a stored proc running against the Northwind DB
Code Snippet
CREATE PROCEDURE CustomPaging
@intOrderID varchar(10),
@strCustomerID varchar(10),
@intEmployeeID int,
@dteOrderDate datetime,
@startRowIndex int,
@maximumRows int,
@SortOrder int
AS
DECLARE @TempItems TABLE
(
ID int IDENTITY,
OrderID int
)
DECLARE @maxRow int
SET @maxRow = (@startRowIndex + @maximumRows) - 1
SET ROWCOUNT @maxRow
INSERT INTO @TempItems (OrderID)
SELECT OrderID
FROM Orders o
INNER JOIN Customers c ON c.CustomerID = o.CustomerID
INNER JOIN Employees e ON e.EmployeeID = o.EmployeeID
ORDER BY CASE @SortOrder WHEN 0 THEN OrderID
WHEN 1 THEN c.CompanyName
WHEN 2 THEN e.FirstName
WHEN 3 THEN o.OrderDate
WHEN 4 THEN o.RequiredDate
WHEN 5 THEN o.ShippedDate END
SET ROWCOUNT @maximumRows
SELECT o.OrderID, CompanyName, e.FirstName + ' ' + e.LastName EmployeeName, o.OrderDate, o.RequiredDate, o.ShippedDate
FROM @TempItems t
INNER JOIN Orders o ON o.OrderID = t.OrderID
INNER JOIN Customers c ON c.CustomerID = o.CustomerID
INNER JOIN Employees e ON e.EmployeeID = o.EmployeeID
WHERE
t.[ID] >= @startRowIndex
AND
(o.OrderID LIKE @intOrderID OR @intOrderID = '')
AND
(o.CustomerID = @strCustomerID OR @strCustomerID='')
AND
(o.EmployeeID = @intEmployeeID OR @intEmployeeID = 0)
AND
(o.OrderDate = @dteOrderDate OR @dteOrderDate IS NULL)
SET ROWCOUNT 0
GO
when i execute the sp with @sortOrder = 0, it works fine
exec CustomPaging '','',0,null, 1, 10, 0
but if i try anything else (@SortOrder=1) i get the error message
exec CustomPaging '','',0,null, 1, 10, 1
Syntax error converting datetime from character string.
but im unsure what is causing this problem? and how to sort it?
Any help is greatly appreciated.
Cheers.
Craig
View 13 Replies
View Related
Jul 20, 2005
i have a query which goes like this:-select a.col1,b.col2,c.col2from tab1 a,tab2 b,tab3 cwherea.col1 *= b.col1 anda.col2 *= c.col2 andb.col3 = c.col3how do I write this query in SQL 92?also can we replace *= with =* by interchanging the participating tables as in"a.col1 *= b.col1" is this same as "b.col1 =* a.col1"
View 2 Replies
View Related
Nov 29, 2007
if it is possible to run a distributed query against 2000 from 2005, what would the OPENDATASOURCE parameters look like? I'd like to be able to pivot without copying my older 2000 db to 2005 or using linked servers..
For reference, here's an example of a distrib query that reads excel...
ie SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C: estxltest.xls;Extended Properties=Excel 8.0')...[Customers$]
View 1 Replies
View Related
Jan 16, 2008
Hello
SQL 2000: 8.00.2187 x86, 8 way 700mhz, 6GB Ram
SQL 2005: 9.00.3042 IA64 2 Way Dual-Core 1.66Mhz 16 GB ram
Symptoms
Querys to the SQL 2005 box from SQL 2000 work but when the query is parameterised with non-literals (@variables) then the query run on the SQL 2005 box excludes any where clause causing the entire table to be returned to the SQL 200 box. When the query is parameterised using literal values the query is executed on SQL 2005 including the where clause.
At first I thought that the "Collation Compatible" setting was the culprit but setting this to 1 made no difference. Other SQL 2000 boxes work as expected and any queries from these using literal and non-literal parameters.
Please, any ideas?
Working
SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = 'value'
Not Working (correctly anyway!)
DECALRE @Value tinyint
SET @Value = 22
SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = @value
View 5 Replies
View Related
Oct 4, 2007
Hi,
I am using SQL SERVER 2000.
I need a procedure/ method where i need to display the output value of a particular query.
As I wrote the following procedure
CREATE proc proc1as beginDeclare @str nvarchar(100)set @str = 'select top 5 * from CT_TM_EmployeeMaster'print @strend
the output is giving "select top 5 * from CT_TM_EmployeeMaster"
I need the output of the above query. What should i do?
Thanks & Regards,
JaiShankar
View 4 Replies
View Related
Nov 11, 2005
Dear all,I have a database with 3 tables, one is named Enrollment, and the other Attendance. What I am trying to accomplish here is to get all the Student ID from the Enrollment table which matches a given class ID and insert all of them into the Attendance table for a given day.I did this with the SQL query below :-
INSERT INTO Attendance (ClassID, Date, StudentID, Status) VALUES (1, '10-4-2005', (Select t.StudentID FROM Student t WHERE t.ClassID=1), 1)Trouble here is I get the error :
Subqueries are not allowed in this context. Only scalar expressions are allowed.I understand waht is going on here, that it returns more than 1 result. So my question is, are there any other ways to accomplish what I am trying to do? Hope you all can help me with this.Thanks all in advance,-Victor-
View 4 Replies
View Related
Feb 1, 2007
This query executes (instantly) in SQL Server 2000 without SP4. With SP 4, it hangs - goes over 5 mins and does nothing. If we make the following change to the query: (replace the variables with actual values)
...
where Links.Matter_ID = 5872
AND Links.Entity_ID = 1
Order By Contacts.ContactID
...
It executes almost instantly in SP4 as well.
Looks like a SP4 issue, any ideas on how we could fix this?
t-sql follows:
Declare @MatterID bigint
Declare @EntityID int
Declare @ReturnDetails int
Declare @RETVAL VarChar(8000)
Declare @EntityDetails VarChar(1000)
Set @MatterID = 5875
Set @EntityID = 1
Set @ReturnDetails = 0
DECLARE Entity_Cursor CURSOR FOR
Select CASE WHEN Contacts.Job_Title IS Null Then '' Else Contacts.Job_Title + ' ' End + CASE WHEN Contacts.First_Name IS Null Then '' Else Contacts.First_Name + ' ' End + CASE WHEN Contacts.Middle_Name IS Null Then '' Else Contacts.Middle_Name + ' ' End
+ CASE WHEN Contacts.Last_Name IS Null Then '' Else Contacts.Last_Name End EntityDetails
From Links
INNER JOIN Contacts
On Links.Contact_ID = Contacts.ContactID
where Links.Matter_ID = @MatterID
AND Links.Entity_ID = @EntityID
Order By Contacts.ContactID
OPEN Entity_Cursor
FETCH NEXT FROM Entity_Cursor INTO @EntityDetails
Select @RETVAL = ''
WHILE @@FETCH_STATUS = 0
BEGIN
Print 'test'
/* If @ReturnDetails = 1
Select @RETVAL = @RETVAL + @EntityDetails + '
'
Else
Select @RETVAL = @RETVAL + @EntityDetails + ', '
FETCH NEXT FROM Entity_Cursor INTO @EntityDetails */
END
CLOSE Entity_Cursor
DEALLOCATE Entity_Cursor
View 5 Replies
View Related
Nov 21, 2007
Hi All,
How to implement a hierarchical query in SQL Server 2000.
Example ; I have an Oracle Query as below
SELECT LEVEL,employee_id, manager_id, first_name, last_name
FROM employee
START WITH employee_id = 1
CONNECT BY prior employee_id = manager_id;
I need to get the equivalent of this query in SQL Server 2000 .
In SQL Server 2005 i can achieve this using COMMON TABLE EXPRESSION .
Is there any way to implement this in SQL Server 2000
View 6 Replies
View Related
Sep 15, 2006
I have two tables in an inner join. I'm detailing the tables with some of their fields below. These tables are in a database I'm creating to manage backup tapes. Most importantly, this database will inform me when backup tapes which have already been used can be recycled (e.g. after all the jobs on the tape are over 28 days old). I want to write something which will look at each tape in turn and, if all related backup jobs on that tape are aged, the tape status will be changed from Active to Spare.
Tapes
--TapeNo
--Status (Spare / Assigned)
Jobs
--JobNo
--Name
--Description
--TapeNo
--AgedJob (BIT field indicating whether or not the job has aged)
Each tape can have 0, 1 or many jobs and each job can be on more than one tape.
Anyway, I have the tables and relationsips set up and they're ok. Again, what I'm struggling with is how I take each tape and look at all its jobs and, if all have aged, change the Status for the tape to Spare. I'm using SQL Server 2000 (Access 2003 as front end) and am pretty new to SQL. I was thinking this could be done by using some kind of update query and subquery, but I'm stumped. Could someone please help ?
--
Paul Anderson
View 3 Replies
View Related
Jan 5, 2006
Hello.After searching Microsoft's website for hours and confusing myself morethan I was before I started...Can anyone explain to me what the current state of affairs is for SQL2000 licensing?In particular with a server that has multiple processors and multipleinstances of SQL Server 2000 running - for both Standard and EnterpriseEditions of SQL Server 2000.Please help!Regards,Taz
View 1 Replies
View Related
Sep 12, 2006
When you are inserting/altering a table and you expect values to be added, should you see the number of rows affected at the bottom of the query screen as the time goes by or not?
thx,
Kat
View 1 Replies
View Related
Apr 26, 2006
The execution time for this query on DB2 v8.0 DBMS one second but I execute it on SQL SERVER 2000 is around 55 second
so how i can incease the performance for SQL server
SELECT ACC_KEY1,ACC_STATUS_LAST FROM PSSIG.CLNT_ACCOUNTS INNER JOIN PSSIG.CLNT_CUSTOMERS ON
PSSIG.CLNT_ACCOUNTS.CSTMR_OID = PSSIG.CLNT_CUSTOMERS.CSTMR_OID
WHERE (PSSIG.CLNT_CUSTOMERS.CSTMR_START_DT >= '1900-1-1 12:00:00') AND
(PSSIG.CLNT_CUSTOMERS.CSTMR_END_DT <= '2106-12-31 12:00:00') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 >= '0000000000000') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 <= '9999999999999') AND
(PSSIG.CLNT_ACCOUNTS.ACC_STATUS_LAST = 5 ) AND
ACC_KEY1 > '0' ORDER BY ACC_KEY1
Note 1: value 5 exist in most of rows about ( 999999/1000000 ) from the table rows count
Note 2: the number of rows in each table around 15000000
Note 3: I used the same index structure for both DB2 and SQL server 2000
Note 4: I used some other feature in DB2 that increase the performance but I did not
found the alternative for it in SQL server 2000 :
a- cardinality varies at run time feature
b- include column in index instead of use compound index for
( ACC_KEY1 ,ACC_STATUS_LAST ) columns
Note 5 : Enable reverse scan for index
View 3 Replies
View Related
Sep 28, 2006
I'm having trouble coming up with a query in SQL Server 2000. Trying my best to sum it up in one sentence; I need a result set that for every EventID/StartDate that exists in tblEvents, I need all StartDates within x days to have a minimum sequential, unique number starting at 1. Now I'll go into some more detail and possibly confuse everyone.
What I have is a table of events that to simplify this question include only an EventID and an StartDate field. What I need to do is for each event, find the other events that occur within x days after the event in question. I then need to number the events starting at 1 so that each eventB that occurs within x days of EventA has the minimum number different from any events from the past x days. I know I just sucked at putting it in words so I'll give an example.
Here is what my idea of the end result table should like if x=2. The source table is basically the same thing minus the Num column.
tblEvents
------------
EventID Date Num
1 10/1/06 1
3 10/2/06 2
7 10/1/06 3
4 10/4/06 1
6 10/10/06 1
2 10/20/06 1
5 10/21/06 2
Here's a quick diagram I made (I know it's ugly and not to scale) which hopefully helps you understand what I need better. Basically, I will be drawing a timeline using vml and each item is going to have text next to it so I need items to be staggered if they are on the same day or close to each other so that things don't overlap. I also need to minimize the vertical space it takes up which is why num needs to get back to 1 as soon as it can instead of just putting each event on its own line. The "num" column will tell me what row to put the item in.
http://i10.tinypic.com/43pxycj.gif
Thanks for any help!
Tom
View 6 Replies
View Related
Dec 6, 2007
Hi,I would like some help converting an access query to a SQL Server query.The access query is made up of the following and then repeated for each field:SELECT Sum(IIf([gender]='Female',1,0)) AS Female, Sum(IIf([gender]='Male',1,0)) AS Male...FROM dbo.applicants I have tried using the following to test out an alternative, but it brings back the incorrect figure:SELECT COUNT(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS FemaleFROM dbo.applicants I've looked at the table and should get back 350, but only get back 193.But using the following query I get the correct figure:SELECT COUNT(gender) AS FemaleFROM applicantsGROUP BY genderHAVING (gender = 'Female') Although I can't use the above query because I want to also count how many 'Male' applicants there are.How can I do this?Thanks
View 5 Replies
View Related
Jan 23, 2015
I getting an error when trying to convert the following into a view. I can't take the credit for the code as I copied it from with a program.
Create view vwMAW_KnowledgeDB AS
Select * from don0001
left join (select donclass.donor from donclass where classification = 9 and code in ('KB')) inc0 on inc0.donor = don0001.donor where inc0.donor is not null
quote:Error; Msg 4506, Level 16, State 1, Procedure vwMAW_KnowledgeDB, Line 2
Column names in each view or function must be unique. Column name 'donor' in view or function 'vwMAW_KnowledgeDB' is specified more than once.
View 1 Replies
View Related
Oct 15, 2007
SELECT distinct whse_zone_id, bay_id
From prod_geneology WHERE whse_zone_id in ('SH','CU','SG')
START WITH unit_id = 'BL7B230811'
CONNECT BY PRIOR parent_id = unit_id
And in this case, 'BL7B230811' could be any 10 character string
Any help would be grateful.
Thanks
View 1 Replies
View Related
Aug 14, 2006
Hi everybody,
I have a query slower in SQL Server 2005 than in SQL Server 2000. I have a database in SQL 2000, I put it on the same server, but with SQL 2005 and the query take 5 seconds instead of 0 seconds. The DB compatibility is SQL Server 2000 (I tried with 2005 and result is the same). Execution plan seems right and I tried to change some DB options without results. It is weird, when I remove left join on MandatsEx, it take 2 seconds. The view currentEmployeeLevelHistoric returns 45 000 rows and mandatsex has 0 rows.
Here is the sample:
Select ls.EmployeNiveau.pk_EmployeNiveauID as NoNiveau,
IsNull(nullif(ls.Traduction.Description, ''), ls.TraductionDefaut.Description) + ' (' +
ls.currentLevelHIstoric.NoNiveau + ')' As Nom,
ls.currentEmployeeLevelHistoric.DebAssign As DateAssignationDebut,
ls.EmployeNiveau.assignmentReason As AssignmentReason,
ls.currentEmployeeLevelHistoric.FinAssign As DateAssignationFin,
ls.MandatsEx.noDossier, ls.MandatsEx.pk_MandatID,
'' As Period, ls.currentEmployeeLevelHistoric.NiveauPrincipal,
ls.currentEmployeeLevelHistoric.pk_emplNiveauHisto_Id,
ls.EmployeNiveau.fk_NiveauID_Niveaux, ls.EmployeNiveau.No_Ent_leg,
IsNull(nullif(TradPere.Description, ''), TradDefautPere.Description) + ' (' + NiveauPere.NoNiveau
+ ')' As NomSup,
ls.EmployeNiveau.No_Ent_leg + ls.EmployeNiveau.no_divisio + ls.EmployeNiveau.no_sec_eco +
ls.EmployeNiveau.no_etabli + ls.EmployeNiveau.no_mat as employeeScope,
case when ls.mandatExternalisation.fk_mandatID_MandatsEx is null then 2 else 1 end as
ContractCategory
From ls.currentEmployeeLevelHistoric
Inner Join ls.EmployeNiveau on
ls.currentEmployeeLevelHistoric.pk_EmployeNiveauID = ls.EmployeNiveau.pk_EmployeNiveauID
Inner join ls.currentLevelHistoric On
ls.EmployeNiveau.fk_NiveauID_Niveaux = ls.currentLevelHistoric.fk_niveauID_niveaux
Left Outer Join ls.TraductionDefaut On
ls.currentLevelHIstoric.fk_TraductionID_TraductionDefaut = ls.TraductionDefaut.pk_NoTraduction
Left Outer Join ls.Traduction On
ls.TraductionDefaut.pk_NoTraduction = ls.Traduction.No_Traduction and
ls.Traduction.Langue = 1
Left Join ls.MandatsEx on
ls.EmployeNiveau.fk_MandatID_MandatsEx = ls.MandatsEx.pk_MandatID
Left Join ls.mandatExternalisation on
ls.MandatsEx.pk_MandatID = ls.mandatExternalisation.fk_mandatID_MandatsEx
left Join ls.Niveaux as NiveauPere on
NiveauPere.niveauID = ls.currentLevelHIstoric.supId
Left Outer Join ls.TraductionDefaut As TradDefautPere On
NiveauPere.fk_TraductionID_TraductionDefaut = TradDefautPere.pk_NoTraduction
Left Outer Join ls.Traduction As TradPere On
TradDefautPere.pk_NoTraduction = TradPere.No_Traduction and
TradPere.Langue = 1
Where ls.EmployeNiveau.fk_EmploID_Emplos = 345158 and (convert(varchar,
ls.currentEmployeeLevelHistoric.DebAssign, 112) <= '20060802' and
(ls.currentEmployeeLevelHistoric.FinAssign is null or
convert(varchar, ls.currentEmployeeLevelHistoric.FinAssign, 112) >= '20060802'))
Thank you
Steve Gadoury
View 13 Replies
View Related
Dec 19, 2006
I'm trying to debug some procedural functions in SQL Server 2000 using Query Analyzer, and not having much luck.
Any help would be appreciated!
Thanks,
Doug
View 3 Replies
View Related
Mar 29, 2007
The SQL statement below works without the WHERE statement but as soon as i try to use the where it stops working, I think it has something to do with the HAVING statement in the vwMultiAE2 view but I am not sure.It did the same thing when I had it in one view. Anyone know what is going on here?SELECT Ch, Reg, FMM, AE, Acc, Totals FROM vwMultiAE2 WHERE (g1ID = @g1 or @g1 = 'All') AND (g2ID = @g2 or @g2 = 'All') AND (g3ID = @g3 or @g3 = 'All') AND (AEID = @g4 or @g4 = 'All')
vwMultiAE2 >
SELECT TOP 100 PERCENT
Ch,
Reg,
FMM,
AE,
Acc,
Totals,
g1ID,
g2ID,
g3ID,
AEID
FROM
vwMultAE
WHERE
Account_ID IN (SELECT Account_ID FROM vwMultAE GROUP BY g2ID, Account_ID HAVING ( COUNT(Account_ID) > 1 ))
ORDER BY
g2ID, Account_ID
View 5 Replies
View Related
