Strange Query Needed
Apr 25, 2007
Hi Friends,
I need a very strange SQL query.
I have table structure like
cat_id, cat_name, cat_parent_id
Each cat has subcategories and those subcategories can have subcategories means
1, Cat1, 0
2, Cat2, 0
3, Cat3, 2
4, Cat4, 2
5, Cat5, 4
So Cat1 >> Cat2 >> Cat3
Now , I need a list of cat, subcat and subcat's subcat in a single query. e.g.
If I need all subcat of cat 2 then
result should be
2,3,4,5
Is it possible in a single query?
thanks.
View 3 Replies
ADVERTISEMENT
Jul 9, 2005
I have a query as follow:
SELECT @resRate = (SELECT resRate FROM ProjectAssign WHERE proNo = (SELECT projNo FROM Timer_Cust WHERE refNum = @actProjNo AND username= (SELECT username FROM Timer_Emp WHERE refNum = @actEmployee) ) )
the definition of table Timer_cust contains refNum(int), projNo(varchar) and projName(varchar).Timer_cust table doesn't contain column username. So this query shoud generate a runtime error. however it works fine without error.But if I run SELECT projNo FROM Timer_Cust WHERE refNum = @actProjNo AND username= (SELECT username FROM Timer_Emp WHERE refNum = @actEmployee)A runtime error message appears.Why?
View 3 Replies
View Related
Jun 8, 2007
hi there , i'm using sql server 2005 express and i have some problems with the two of my tables :), iwant to query both of my tables but the table entries are not in an equal count of rows for example:
incoming(table) outgoing(table)
money date corp money date corp
15 1,1,2006 ar 17 1,1,2006 ar
25 1,2,2007 ar 21 2,2,2007 es
35 2,2,2007 es
6 3,3,2007 ar
in this example the first table has more rows but the opposite is possible in my tables because this is an (account extract) query and here's my problem: first i used the "select incoming.money as m1,incoming date as d1,incoming.corp as c1,outgoing.money as m2,outgoing.date as d2,outgoing.corp as c2 from incoming left outer join outgoing on incoming.corp=outgoing.corp where incoming.corp='ar'(or don't use the where eliminating)" any way i couldn't get the correct results, then i used the (union all) select , the result was ok but the results form is not what i want here's the form i want and is it possible to query to tables to get this?
in this query i just wanted the elements with the corporation named 'ar' for example:
m1 m2 d1 d2 c1 c2
15 17 1,1,2006 1,1,2006 ar ar
25 null(or (0)) 1,2,2007
6 null(or (0)) 3,3,2007
is it possible to achieve this form(and may be any of the tables may have more rows than the other, it's not exact) , i am in a very difficult position :) and now i need your help , thanks anybody to think of helping me
View 4 Replies
View Related
May 17, 2006
When I try to execute the below query:
select * from jobmaster where status in ('Active') and materialtypecode in (1,2) and unit='IMPERIAL' and superusercode='S051000014' order by controllercode,jobname
through ADO I got the error message:
Invalid column name controllercode
When I execute the same above query in MS SQL Query Analyzer it is working.
Is there any workaround?
View 3 Replies
View Related
Mar 7, 2007
I am having in writing a simple select command........here's My ProblemI am using the ASP SQLDataSources in VS2005.... and my need is that i need to show Products details in a Gridview...... Actually through QueryString a StoreID is being fetched..... and the Products under those StoreID are shown..... if there is nothing in query string then it should show all the results... ........ It means that my querystring should be something like thisselect * from tblProducts where StoreID = <xyz>and <xyz> should be some thing that could show all the rows in that table ........ i mean that it should show all the possible results that can be shown through...select * from tblProducts
View 6 Replies
View Related
Jun 24, 2004
Okay... here's the deal...
I have two pages that Im using the same query out of... one page returns results, the other page returns an error... SOMEBODY HELP!!! I just need a hint if nothing more!!!
The Error:
System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'WHERE'.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at Global.Default_Search(Object Sender, EventArgs e) in C:InetpubwwwrootChampionRealtyINFOprojecttrackingglobal.vb:line 206
The SQL Query:
SELECT PT_JobCat.JobCat, PT_Job.JobID, PT_Job.Created, PT_Status.Status,
Offices.Name, Employees.First + ' ' + Employees.Last AS EmpName
FROM PT_Job
INNER JOIN PT_JobCat ON PT_Job.CatID = PT_JobCat.CatID
INNER JOIN PT_Status ON PT_Job.StatusID = PT_Status.StatusID
INNER JOIN PT_Assign ON PT_Job.JobID = PT_Assign.JobID
INNER JOIN Employees ON PT_Assign.AgentID = Employees.ID
INNER JOIN PT_Office ON PT_Job.JobID = PT_Office.JobID
INNER JOIN Offices ON PT_Office.Office_ID = Offices.ID
WHERE PT_Job.JobID=1
The SQL Query Output By ASP.NET: (When an error is produced on the trouble page)
SELECT PT_JobCat.JobCat, PT_Job.JobID, PT_Job.Created, PT_Status.Status,
Offices.Name AS OfficeName, Employees.First + ' ' + Employees.Last AS EmpName
FROM PT_Job
INNER JOIN PT_JobCat ON PT_Job.CatID = PT_JobCat.CatID
INNER JOIN PT_Status ON PT_Job.StatusID = PT_Status.StatusID
INNER JOIN PT_Assign ON PT_Job.JobID = PT_Assign.JobID
INNER JOIN Employees ON PT_Assign.AgentID = Employees.ID
INNER JOIN PT_Office ON PT_Job.JobID = PT_Office.JobID
INNER JOIN Offices ON PT_Office.Office_ID = Offices.ID
WHERE JobID=1
The Trouble Page:
Public Sub Default_Search(ByVal Sender As Object, ByVal e As EventArgs)
dim NeedAnd as boolean = False
dim strSQL as string = "SELECT PT_JobCat.JobCat, PT_Job.JobID, PT_Job.Created, PT_Status.Status, " & _
"Offices.Name AS OfficeName, Employees.First + ' ' + Employees.Last AS EmpName " & _
"FROM PT_Job " & _
"INNER JOIN PT_JobCat ON PT_Job.CatID = PT_JobCat.CatID " & _
"INNER JOIN PT_Status ON PT_Job.StatusID = PT_Status.StatusID " & _
"INNER JOIN PT_Assign ON PT_Job.JobID = PT_Assign.JobID " & _
"INNER JOIN Employees ON PT_Assign.AgentID = Employees.ID " & _
"INNER JOIN PT_Office ON PT_Job.JobID = PT_Office.JobID " & _
"INNER JOIN Offices ON PT_Office.Office_ID = Offices.ID " & _
"WHERE "
dim C as New SQLCommand(strSQL, Conn)
dim DR as SQLDataReader
If txtJobNum.Text <> Nothing Then
strSQL += "JobID=" & CInt(txtJobNum.Text) & " "
NeedAnd = True
End If
If ddlJobType.SelectedItem.Value <> Nothing Then
If NeedAnd Then strSQL += "AND "
strSQL += "CatID=" & ddlJobType.SelectedItem.Value & " "
NeedAnd = True
End If
If txtCreated.Text <> Nothing Then
If NeedAnd Then strSQL += "AND "
strSQL += "PT_Job.Created LIKE '" & txtCreated.Text & "' "
NeedAnd = True
End If
If ddlStatus.SelectedItem.Value <> Nothing Then
If NeedAnd Then strSQL += "AND "
strSQL += "PT_Job.Status=" & ddlStatus.SelectedItem.Value & " "
NeedAnd = True
End If
If ddlAssign.SelectedItem.Value <> Nothing Then
If NeedAnd Then strSQL += "AND "
strSQL += "Employees.ID=" & ddlAssign.SelectedItem.Value & " "
NeedAnd = True
End If
If ddlOffice.SelectedItem.Value <> Nothing Then
If NeedAnd Then strSQL += "AND "
strSQL += "Offices.ID=" & ddlOffice.SelectedItem.Value & " "
NeedAnd = True
End If
response.write(strSQL)
Conn.Open
Try
DR = C.ExecuteReader
If DR.Read Then
rptResults.Datasource = DR
rptResults.Databind
End If
DR.Close
Catch Exc as Exception
'ErrorAlert(Exc, strSQL)
response.write("<p>" & Exc.ToString.Replace(Environment.NewLine(), "<br />") & "</p>")
End Try
Conn.Close
End Sub
The Page That Works:
Sub LoadResults(ByVal Sender As Object, ByVal e As EventArgs)
' Clean up some things...
txtError.Visible = False
txtError.Text = Nothing
' Create database interaction objects...
dim Conn as Object
dim C as Object
dim DR
' Set database interaction objects...
Select Case CInt(DBType.SelectedItem.Value)
Case 0
Conn = New SQLConnection(txtConnString.Text)
C = New SQLCommand(txtSQL.Text, Conn)
Case 1
Conn = New OleDbConnection(txtConnString.Text)
C = New OleDbCommand(txtSQL.Text, Conn)
Case Else
txtError.Visible = True
txtError.Text = "Whoa... wierd error d00d... o.0"
End Select
' Open the database for reading...
Conn.Open
Try
' Load the datagrid with any information retrieved...
Select Case CInt(CommType.SelectedItem.Value)
Case 0
DR = C.ExecuteReader()
dgResults.Datasource = DR
dgResults.Databind
DR.Close
Case 1
C.ExecuteNonQuery()
Case Else
txtError.Visible = True
txtError.Text = "That's some funky ****..."
End Select
Catch Exc As Exception
' Make the error viewable so we know what went on...
txtError.Visible = True
txtError.Text = Exc.ToString
End Try
' Close the connection to the database...
Conn.Close
End Sub
View 7 Replies
View Related
Aug 12, 2000
Hi...
Not sure what's wrong with the query or table, but I just can't get the result I want.
The column is varchar(35) and contains toll free no, like 18001234567...
But when I queried using the query below, I can't get any results. Same if I use 1*, 18*, 180*...
SELECT ... FROM ...
WHERE CONTAINS(toll_no, '"1800*"')
However, if I used 18001* I do get results.
Can anybody show me how to get result for these cases?
Thanks.
View 2 Replies
View Related
Jan 18, 2006
Good Morning!
First off, let me get my disclaimer out of the way - I'm a relative newbie at this SQL lark, so please ignore the kludgy-ness of this code etc. Thanks ;-)
Firstly, here's my query:
Code:
SET DATEFORMAT DMY
declare @issuecounter int; -- Declare
declare @previssue int; -- Declare
declare @date smalldatetime -- Declare
DECLARE @issues TABLE (issues int, dates smalldatetime)
-- Declare
DECLARE issue Cursor scroll FOR -- Create the cursor
select articleIssue, articleDate from cr_newsletterArticles
where newsletter = @newsletter -- Which Newsletter
order by articleIssue desc ;
open issue -- Open the cursor
SET @previssue = 0 -- Set our comparison variable to 0
FETCH next FROM issue -- Grab the first recordset from the cursor
into @issuecounter, @date -- Stick it into our variable
WHILE @@fetch_status = 0 -- While we're not at EOF
begin -- begin
if @issuecounter != @previssue BEGIN -- begin
insert into @issues(issues,dates)
values(@issuecounter, @date)
SET @previssue = @issuecounter -- set comparison variable to issuecounter
END
fetch next from issue -- Grab the next recordset
into @issuecounter, @date
END -- End
CLOSE issue -- close cursor
deallocate issue
select * from @issues
GO
When I run this query in Query Analyser, it seems to work fine. IE. I get a table returned with the correct values. However, when I try and get this data into ASP via the old objRS.open "EXECUTE stored procedure <value>" command, no data is returned...
I'm not sure if this is an ASP problem, or a SQL problem... So, I altered the query slightly, and instead of a temporary table, I used a static one in the database, and it still didn't work - even though when I open the table in Enterprise manager the content is there...
Also, if I run the query via an ASP objCommand.execute "procedure <value> " and then use objRS.open "SELECT * from table" then it pulls the data in properly then...
If I was dealing with millions of recordsets in my original table, then I can understand that maybe the ASP isn't waiting long enough for SQL to return its final table. But I'm currently only dealing with about 50... I'd rather use the temporary table route rather than static incase of multiple hits at the procedure at the same time...
Any ideas? Please go easy - I refer you back to my disclaimer ;-)
Thanks...
View 2 Replies
View Related
Mar 19, 2008
I have a query like below .. if i add where Served = 1 , the query takes foreever... if i remove it, it takes only 6 sec...
I am not sure why this is hapening?
select distinct a.Episode_Key,
case when ag.Category IN ('ASMI', 'COOC', 'SPCL') then 'SMI'
when ag.Category = 'SEDC' then 'SED'
when ag.Category = 'ACCA' then 'SA'
when ag.Category like 'CGA%' then 'Gam'
end as [Category],
ag.Agreement_Type_Name as [Agreement],
p.ServiceProvider,
s2.Served
from dbo.Assessment a
INNER JOIN (
select distinct Episode_Key, p.ServiceProvider, max(CSDS_Object_Key) as [Sequence]
from dbo.Assessment a
INNER JOIN dbo.CD_Provider_Xref p
ON a.Provider_CD = p.Provider_CD
where Creation_DT >= '07/01/2007'
and Reason_CD = 1
group by Episode_Key, p.ServiceProvider
) as s1
ON a.CSDS_Object_Key = s1.Sequence
INNER JOIN dbo.CD_Provider_XREF p
ON a.Provider_CD = p.Provider_CD
INNER JOIN dbo.CD_Agreement_Type ag
ON ag.Agreement_Type_CD = a.Agreement_Type_CD
LEFT OUTER JOIN (
select distinct Episode_Key, p.ServiceProvider,
1 as [Served]
from dbo.Encounters e
INNER JOIN dbo.CD_Provider_Xref p
ON e.Provider_CD = p.Provider_CD
where Encounter_Begin_DT between '01/01/2008' and '01/31/2008'
and Procedure_CD is not null
and Encounter_Units > 0
) as s2
ON a.Episode_Key = s2.Episode_Key
and p.ServiceProvider = s2.ServiceProvider
????---where Served = 1
group by a.Episode_Key, ag.Agreement_Type_Name, p.ServiceProvider, Served,
case when ag.Category IN ('ASMI', 'COOC', 'SPCL') then 'SMI'
when ag.Category = 'SEDC' then 'SED'
when ag.Category = 'ACCA' then 'SA'
when ag.Category like 'CGA%' then 'Gam'
End
View 2 Replies
View Related
Jul 10, 2006
Hi all,Using SQL Server 2000, SP4.I have a table of street names (Rua) whose ids (cod_rua) are foreignkeys into a consumer table (Consumidor). It turns out that the "Rua"table has many unused records which I'd like to wipe out. For instance,there are some 2800 unused records in the "Rua" table, and only some200 records actually being used by the "Consumidor" table (which,itself, has some 5000 records).Attempting to find the unused records, I issued the following query:a)SELECT COD_RUA FROM RUAWHERE COD_RUA NOT IN (SELECT COD_RUA FROM CONSUMIDOR)To my surprise, the query came out empty. But the following queryshowed the 200 or so records which *are* being used:b)SELECT COD_RUA FROM RUAWHERE COD_RUA IN (SELECT COD_RUA FROM CONSUMIDOR)I've found two solutions for the query to list the records *notexisting* in the Consumidor table:c)SELECT COD_RUA FROM RUAWHERE COD_RUA NOT IN (SELECT COD_RUA FROM CONSUMIDORWHERE COD_RUA IS NOT NULL)d)SELECT COD_RUA FROM RUAWHERE COD_RUA NOT IN (SELECT COD_RUA FROM RUAWHERE COD_RUA IN (SELECT COD_RUA FROM CONSUMIDOR))I know that there are many other possible solutions to the query(including left joins), but what I don't understand is why the query a)failed.Can some of you, oh mighty gurus, enlighten me?For the record, here's how both tables are (partially) declared:CREATE TABLE Rua (Cod_Rua int NOT NULL ,Rua varchar (35) NULL ,-- ...-- other unrelated fields-- ...CONSTRAINT Pk_CodRuaPRIMARY KEY (Cod_Rua))CREATE TABLE Consumidor (Cod_Consumidor int NOT NULL ,Cod_Rua int NULL ,-- ...-- other unrelated fields-- ...CONSTRAINT Pk_CodConsumidorPRIMARY KEY(Cod_Consumidor) ,CONSTRAINT Fk_CodRua_ConsumidorFOREIGN KEY (Cod_Rua)REFERENCES Rua (Cod_Rua))Regards,Branco Medeiros
View 3 Replies
View Related
Jul 20, 2005
Hi everibody,it's the first time i post on this newsgroup. I'm Stefano from Milano,italy.I'm a beginners with Sql2000. My problem is this.I run a View using enterprise manager and after less then 20 second it goesin error time out. I run this view using a VB application and the errorcomes again .When i run it with Query Analyzer after 50 seconds it give methe right result.i've tried to change the value of querytimeout using sp_configure with thesame bad result.i've tried to change the ado command timeout in visul basic but stilldoesn't work.any suggest ?Thanks in advance
View 4 Replies
View Related
Mar 26, 2007
Hi all,
Last week we've made some modifications to our 20 GB database(with 2 huge tables (around 30 million rows)):
1.
we've created 8 filegroups on 8 physical disks with RAID 1+0. In each
of them we have one big table and others small tables unrelated with
the big table. We've also created a filegroup for the nonclustered
indexes from those big tables.
2. The Server is running SQL Server 2000 with SP3a
3. We've reindexed the database, updated statistics on all tables
Now, we have a SP that on the old configuration took about 20 min to complete.
But now, on our new configuration with the modifications listed above it's taking about
3 HOURS.
This is the SP's body: is calling a function:
create proc X
as
begin
Select dbo.myfunc(mytable.field)
from mytable
end
The function used in select statement is querying those 2 huge tables.
If the select statement is runned for only one record it's done instantly, but for 20000 recs it is taking almost 3 hours.
I must specify that also the function is running instantly, but in that select statement something is happening and we don't know what!!!
If you have any ideeas on what to do or what to check
Thanks a lot
Dan
View 5 Replies
View Related
Nov 15, 2006
Hi,
I am seeming strange results with a query. I have two tables, lets call them Table1 and Table2. Table1 has an ID field, Table2 does not have an ID field. To be sure I wasn't blind, the query
'SELECT ID FROM Table2'
returns: Invalid column name 'ID'. OK. Now when I run the query
'SELECT * FROM Table1 WHERE ID IN (SELECT ID FROM Table2)'
it returns all the records from Table1.
What gives? Is this a bug, or am I missing something?
View 8 Replies
View Related
Nov 23, 2006
Jezemine,
No, the number of reads is approximately the same. I can also confirm the disk read speed is the same on the test vs. production server. Update stats is run regularly on the production server - as I test, I ran sp_updatestats and then immediately ran the query a few times but it didn't affect the duration. Apart from the durations in the profiler traces, I can't see any differences. Clearly, something is causing the increased duration on the prod server but I don't know where to look to find it. It's definitely within SQL Server 2000.
Clive
View 8 Replies
View Related
Aug 9, 2004
Hello All,
I have been trying to get this code work, but I could not. Every thing seems going well. However, The result of running the sql query is strange. It shows the field names twice.
Eg:) if you have a table called "newtable" that has two fields[Custnumber, Custname], you will get somthing like this [Custnumber, Custname Custnumber, Custname]. I have tried many times, but I couldn't fix it.
Sub Page_Load(sender As Object, e As EventArgs) handles Mybase.Load
if not page.Ispostback then
try
Sqlconnection = New Sqlconnection (connectionString)
querystring = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNs
WHERE TABLE_NAME = 'Newtable'"
SqlCommand = New SqlCommand(queryString, Sqlconnection)
SqlConnection.Open
dataReader = SqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
while dataReader.Read()
Tablefields_txt.text += dataReader.Getstring(0) & ", "
End while
catch ex as Exception
msgbox("An error has occured: " + ex.Message,0, "Error Message")
finally
SqlConnection.Close()
End try
End if
Any help , please
View 3 Replies
View Related
Mar 20, 2001
We have SQL server 7 installed. And we experiencing the performance problem.
When I tried to solve this problem I have found the interesting thing:
We have the table S_EVT_ACT with the non clustered index
S_EVT_ACT_F4 created on [OWNER_PER_ID], [APPT_REPT_FLG] fields and another clustered one
S_EVT_ACT_M4 created on [ROW_STATUS], [OWNER_PER_ID] fields
I use the next select statement and before run this statement I declare the variable @P1 - @P5 and set the values to them:
declare @P1 as char(1)
declare @P2 as char(1)
declare @P3 as char(1)
declare @P4 as varchar(10)
declare @P5 as char(1)
set @P1 = 'Y'
set @P2 = 'Y'
set @P3 = 'N'
set @P4 = '1-K56'
set @P5 = 'Y'
SELECT
..
...
...
FROM
dbo.S_EVT_ACT T1 --(index = S_EVT_ACT_F4)
LEFT OUTER JOIN dbo.S_CONTACT T2 ON T1.TARGET_PER_ID = T2.ROW_ID
LEFT OUTER JOIN dbo.S_OPTY T3 ON T1.OPTY_ID = T3.ROW_ID
LEFT OUTER JOIN dbo.S_ORG_EXT T4 ON T1.TARGET_OU_ID = T4.ROW_ID
LEFT OUTER JOIN dbo.S_EVT_ACT_X T5 ON T1.ROW_ID = T5.PAR_ROW_ID
WHERE
((T1.ALARM_FLAG = @P1 OR T1.APPT_REPT_REPL_CD IS NOT NULL) AND
(T1.APPT_REPT_FLG = @P2 AND (T1.CAL_DISP_FLG = @P3 OR T1.CAL_DISP_FLG IS NULL)) AND
(T1.OWNER_PER_ID = @P4) AND
(T1.TEMPLATE_FLG != @P5 OR T1.TEMPLATE_FLG IS NULL))
ORDER BY
T1.CREATED
In this case Query Analyzer uses S_EVT_ACT_M4 index and the performance is bad!
But when I try to run the same statement and use hard code 'Y' instead of the variable @P2 the Query Analyzer uses the S_EVT_ACT_F4 index and performance is PERFECT.
Question: What the difference between using variable @P2 and hard code 'Y' in the select statement and how to configure SQL server to use the right index in the situation when I can't change the Select statement and use the hard coding?
Any Idea will be very Appreciated.
Dmitri Denejkine
stssystems
MS SQL Server DBA, MIS
(514) 426-0822 ext. 2676
View 3 Replies
View Related
Feb 11, 2004
declare @l decimal(38,2)
select @l = 24.35
if @l - convert(int,@l) = 0
select floor (@l)
else
select @l
select case
when @l - convert(int, @l) = 0 then floor (@l)
else @l
end
The if statement is giving correct result, but the case statement is not. I am fed up why it is so. Please advise.
View 14 Replies
View Related
Jul 23, 2007
i have marked in bold the query in question ..whch gives me the the runtime error mentioned at the below , i double checked everything all the table names and the field names are correct so whats the problem , please anyone??im really stumped! if (Radio_Btn_Book.Checked == true) { string book_query = "update Issue_Book_Reserve I set I.Issue_Book_Reserve_state = 1 where I.Book_Id = Books.Book_Id and Books.Book_Name = '" + Session["Book_name"].ToString()+"'"; SqlCommand Cmd_book = new SqlCommand(book_query, con); con.Open(); Cmd_book.ExecuteNonQuery(); con.Close(); } ERROR: The column prefix 'Books' does not match with a table name or alias name used in the query.The column prefix 'Books' does not match with a table name or alias name used in the query.
View 3 Replies
View Related
Jan 14, 2008
Hi,
TABLES
(Association CONTAINS COLUMNS AssociationID),
(Group CONTAINS COLUMNS GroupID, GroupName, AssociationID),
(GroupMembers CONTAINS COLUMNS GroupID, GroupMemberID, UserprofileID, DateCreated, DateRemoved),
(UserProfiles CONTAINS COLUMNS UserProfileID, UserID),
(AllUsers CONTAINS COLUMNS UserID, FirstName, LastName)
I am trying to write a query to collect information about from the tables. I need to collect all the Users who are not members of Group A in Association I.
Note that Users can belong to more than one group and have more than one profile.
I would appreciate it if you could help me figure out how to deal with this logic. Thanks in Advance
View 2 Replies
View Related
Mar 20, 2008
Hi
I have a function which returns the periodId from the period table and the function is as follows:
ALTER FUNCTION [dbo].[udf_Quarter] (@Date datetime)
RETURNS int
AS
BEGIN
DECLARE @month int
SET @month = Month(@Date)
RETURN
CASE
WHEN @month BETWEEN 1 AND 3 THEN 5
WHEN @month BETWEEN 4 AND 6 THEN 6
WHEN @month BETWEEN 7 AND 9 THEN 7
WHEN @month BETWEEN 10 AND 12 THEN 8
END
END
Which works fine but i want to do the following with it. We produce quarterly statements.. So the user comes in jan or the begining of Feb to upload and process and order Statements from 10/31 to 12/31 the the 4 quarter data.. So Is there a way i can check if the user orders the statement in between jan and feb it has to reture PeriodId 5.
Any help will be appreciated.
Regards,
Karen
View 5 Replies
View Related
Apr 10, 2008
I have three parameters called @rsLengthofservice, @rsLengthofServicematch and @rsLenghtofservicePS.
How do i check if all the three parameters have the same value..
Any help will be appreciated..
Regards,
Karen
View 5 Replies
View Related
Apr 25, 2008
Hi,
I have this query
SELECT
o.OrderId,
o.OrderDate,
o.CreatedByUserId,
c.LoginId,
o.Quantity,
o.RequiredDeliveryDate,
cp.PlanId,
cp.ClientPlanId
--cp.ClientId
FROM
[Order] o
Inner Join Plan cp on o.PlanId = cp.PlanId and o.CreatedByUserId = cp.UserId
Inner Join User c on o.CreatedByUserId = c.UserId
WHERE
--cp.ClientId = @ClientId
--AND
o.OrderDate BETWEEN @StartDate AND @EndDate
ORDER BY
o.OrderId DESC Which gives the list of order from the start date to the end date..but it doesnt List the orders which are some mins apart. For eg. 16524/24/2008 10:48:05 AM4252840
16534/25/2008 10:15:42 AM378550
16544/25/2008 10:51:24 AM3147110 16614/25/2008 1:35:54 PM425045
16624/25/2008 2:30:43 PM4174387130
16634/25/2008 2:47:14 PM154825
16644/25/2008 2:49:10 PM265085
it listed order no. 1652 and 1654 but not 1653 and in the next 4 it didnt list 1663.. why is this happening.. I am using a reports in the report server. any help will be appreciated. Regards,Karen
So it kist
View 3 Replies
View Related
Nov 17, 2003
Hi everyone,
I consider myself good at SQL when it comes to SELECTS, UPDATES, INSERTS etc - but now I'm actually wanting to delete a record.
How can I do this? Can I just use:
DELETE * FROM Table WHERE RowID = '23'
??
Can anyone help me out?
Cheers
Andrew
View 4 Replies
View Related
Jun 6, 2005
hi,
I have three tables with the following fields:
User:
UserID
Name
Test:
TestID
Name
Grade:
GradeID
TestID
UserID
Score
And I want to display the result something like this:
UserID Name Test1 Test2 Test3
1
Azam
23
34 45
2
Ali
34
45 56
How can this be accomplised thanks,
View 10 Replies
View Related
Aug 7, 2005
my app contains one form (aspx) and it has different controls to be filled by user (textbox,radiobutton ..etc)it has one button which i want to use to pass values entered in these controls to other page and do some queries to sql server there (2nd page)Now the thing is ...my controls can have NULL values ...like user could enter just one parameter and hit button or user can fill 2 parameter and hit enter so on the other hand (2nd page) how should i query the database accordingly ....
View 2 Replies
View Related
Dec 2, 2005
I have some data that looks like this:
field1(pk) field2 field3
563136 199535840A D2119562408 199615461C D2119562806 199748610A D2119547463 199748610A D2368562278 200255598A D2368562286 200255598A D2468
Field2 can have the same value.
What I need is to return all records where field3 is not 'D2468' ever for field2. So with the above data, I'd want the first 4 records returned, but not 5 and 6 because for those field2 is the same value and in 6, field3 is 'D2468'.
So, I can't simply say:
SELECT a.field2FROM table1 a inner join table2 b on a.field2 = b.field2 and a.field5 = b.field5where a.field3 not like 'D2468'
because it will still return record 5.
Can anyone help me with this? Thank you!
View 3 Replies
View Related
Jul 5, 2000
Hi
I need help here. I have two queries one on each server and one does not work the way it should be. Can some help me here in figuring out if both these queries are same or no. If there is any difference, what is causing that difference. Thanks
Query1:
SELECT Shipment.CountryCd, Shipment.City, Shipment.State, Shipment.CountyName,Shipment.Zip, Shipment.WarehouseCd, Phone
FROM Orders INNER JOIN ((NewWarehouse
INNER JOIN Shipment ON NewWarehouse.WarehouseCd = Shipment.WarehouseCd)
INNER JOIN WarehouseCurrency ON NewWarehouse.WarehouseCd = WarehouseCurrency.WarehouseCd)
INNER JOIN Warehouse ON (Shipment.CountryCd = Warehouse.CountryCd) AND (NewWarehouse.WarehouseCd = Warehouse.WarehouseCd)) ON Orders.OrderNum = Shipment.OrderNum
WHERE BE = 5 AND AddressTy = 1 AND WarehouseCurrency.CurrencyCd ='USD' AND Active = -1
GROUP BY Shipment.CountryCd, Shipment.City, Shipment.State, Shipment.CountyName, Shipment.Zip, Shipment.WarehouseCd, Phone
ORDER BY Min(Shipment.OrderNum) DESC)
Query 2: (This is working)
SELECT Shipment.CountryCd, Shipment.City, Shipment.State, Shipment.Zip, Shipment.WarehouseCd, Shipment.Phone, Shipment.CountyName
FROM ((Orders INNER JOIN (NewWarehouse
INNER JOIN Shipment ON NewWarehouse.WarehouseCd = Shipment.WarehouseCd) ON Orders.OrderNum = Shipment.OrderNum)
INNER JOIN WarehouseCurrency ON NewWarehouse.WarehouseCd = WarehouseCurrency.WarehouseCd)
INNER JOIN Warehouse ON (NewWarehouse.WarehouseCd = Warehouse.WarehouseCd) AND (Shipment.CountryCd = Warehouse.CountryCd)
WHERE (((Orders.BE)=5) AND ((Shipment.AddressTy)=1) AND ((WarehouseCurrency.CurrencyCd)='USD') AND ((NewWarehouse.Active)=-1))
GROUP BY Shipment.CountryCd, Shipment.City, Shipment.State, Shipment.Zip, Shipment.WarehouseCd, Shipment.Phone, Shipment.CountyName
ORDER BY Min(Shipment.OrderNum) DESC
I am sorry I am not that good in sql as some of you may be. Thanks.
View 3 Replies
View Related
May 31, 2006
I have 2 tables:
Table1 has a field [Description](varchar) 100
Table2 has a field [IDDescription] int and field [Description] varchar(100)
In table 2 I have IDDescription store but some time also description wich is not store in table2
I need to pull all description (if it is from table2 or table1). How do I do this?
View 2 Replies
View Related
Dec 16, 2005
I have a query that need to do something like this.
About the data:
---------------
I have a rules table which has got all the rules, and a log table that has the logs which says whether the rules are satisfied or not over a point of time.
select rules, sum(decode(status,'pass','1')) as passed, sum(decode(status,'fail',1)) as failed from rulestable,logstable where rulestable.logid=logstable.id
My expected Output
---------------------------------
Rule1 passed= 10 failed=12
Rule2 passed=11 failed=15
But i donot have decode() function in mssql nor can I use IF ELSE here. Can anyone please tell me how this can be implemented here?
Thanks
View 2 Replies
View Related
Jan 14, 2007
Ok guys.
Lets say i have 2 tables. One called 'Leads' and the other called 'Sales_Reps'. When a new lead comes in would like to assign it to the sales rep who has the least number of active leads. If there is a tie, I'll just assign it alphabetically between the sales reps who are tied with the least.
Can someone point me in the right direction here? I would assume I'd need to do some sort of select count, but I'm not sure how to begin.
Thanks!
View 8 Replies
View Related
Nov 20, 2007
Hi pals,
I need small help.
I have a table with data as follows.
select * from test_data
c1 c2 c3
----------------------
111 4 101
122 3 101
133 2 102
144 2 103
155 1 103
Now i need to generate a new column c4 as sequence number in the output
I need to get the output something as follows
c4 c1 c2 c3
-----------------------------
1 111 4 101
2 122 3 101
1 133 2 102
1 144 2 103
2 155 1 103
The newly generated column contains sequence numbers starting from 1 and the sequence should be resetted again to 1 whenever a new c3 value is encountered(for example when the c3 value changes from 101 to 102 the sequence should be resetted to 1).
Can anyone help me out on this regard.
Thanks in advance,
franky
View 3 Replies
View Related
Jan 16, 2008
consider i have these tables:
tbl_requests
id
description
owner
tbl_owners
id
name
tbl_RequestStatus
id
description
am looking for output look like this:
OwnerName New Completed
x 2 5
y 4 3
table requests holds number of requests where each request has an owner, table owners holds the owners names, table status holds values for a request status for example : new, in progress and completed, the query am looking for should show each owner with the number of request he/she is handling grouped by the status, so we can say that owner X has 2 new requests and 5 completed requests.
View 9 Replies
View Related
Apr 26, 2008
hi
i have a table with below structure
id no.of visit no of sales no of delivery.
I want all records to be selected with the following data also
average( no.of visit),average( no of sales ), average( no of delivery) in a single query ?
View 6 Replies
View Related
