Hi All,
Please suggest me is there any performance/other differences between the below two queries.
----query1
select T1.name,T1.Number, T2.Dept, T2.Desig
From T1 Inner Join T2 on T1.EID = T2.EID
----query2
select T1.name,T1.Number, T2.Dept, T2.Desig
From T1 Inner Join (Select Dept, Desig From T2) As T2 on T1.EID = T2.EID
Thanks
Senthil
This is what I have:
Code:
Select
PR.WBS1,
PR.WBS2,
PR.WBS3,
PR.LongName,
PR.Name,
CL.Name as CLIENTNAME,
CFGMain.FirmName,
CFGMain.Address1,
CFGMain.Address2,
CFGMain.Address3,
CFGMain.Address4,
Contacts.FirstName + ' ' + Contacts.LastName as CONTACT,
isnull(LB.AmtBud, '0.00') as AmtBud,
Sum(LD.BillExt) as BillExt
From PR
left join Contacts ON PR.ContactID = Contacts.ContactID
left join CL ON CL.ClientID = PR.ClientID
left join LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3
left join LD ON LD.WBS1 = PR.WBS1 AND PR.WBS2 = LD.WBS2 AND LD.WBS3 = PR.WBS3 And LD.BilledPeriod = '200408',
CFGMain
Where (PR.WBS3 <> 'ZZZ') and (PR.WBS2 <> 'ZZZ')
Group By PR.WBS1, PR.WBS2, PR.WBS3, PR.LongName, PR.Name, CL.Name, CFGMain.FirmName,
CFGMain.Address1, CFGMain.Address2, CFGMain.Address3,
CFGMain.Address4, Contacts.FirstName, Contacts.LastName,
LB.AmtBud
Right now I have a column that takes the sum of LD.BilledPeriod when equaled to '200408'. The problem is that I need to include another column that also calculates the sum for BillExt where LD.BilledPeriod <= '200408'. Ordinarily I would have done a subquery which I have tested and it does work. But, I have to find another way of doing this because when I add subqueries other functionality in the software I using does not work. So, I am hoping that someone might know another route for me to take. I was thinking about using a UNION but this will not create a new column that I need.
Thank You,
Laura
This is what I have:
Select
PR.WBS1,
PR.WBS2,
PR.WBS3,
PR.LongName,
PR.Name,
CL.Name as CLIENTNAME,
CFGMain.FirmName,
CFGMain.Address1,
CFGMain.Address2,
CFGMain.Address3,
CFGMain.Address4,
Contacts.FirstName + ' ' + Contacts.LastName as CONTACT,
isnull(LB.AmtBud, '0.00') as AmtBud,
Sum(LD.BillExt) as BillExt
From PR
left join Contacts ON PR.ContactID = Contacts.ContactID
left join CL ON CL.ClientID = PR.ClientID
left join LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3
left join LD ON LD.WBS1 = PR.WBS1 AND PR.WBS2 = LD.WBS2 AND LD.WBS3 = PR.WBS3 And LD.BilledPeriod = '200408',
CFGMain
Where (PR.WBS3 <> 'ZZZ') and (PR.WBS2 <> 'ZZZ')
Group By PR.WBS1, PR.WBS2, PR.WBS3, PR.LongName, PR.Name, CL.Name, CFGMain.FirmName,
CFGMain.Address1, CFGMain.Address2, CFGMain.Address3,
CFGMain.Address4, Contacts.FirstName, Contacts.LastName,
LB.AmtBud
Right now I have a column that takes the sum of LD.BilledPeriod when equaled to '200408'. The problem is that I need to include another column that also calculates the sum for BillExt where LD.BilledPeriod <= '200408'. Ordinarily I would have done a subquery which I have tested and it does work. But, I have to find another way of doing this because when I add subqueries other functionality in the software I using does not work. So, I am hoping that someone might know another route for me to take. I was thinking about using a UNION but this will not create a new column that I need.
Thank You,
Laura
SELECT WBS2, (SUM(CASE WHEN wbs2 = '9001' THEN amount * - 1 END)) AS reimb
FROM LedgerAR
WHERE (WBS1 = '001-298') AND (WBS2 <> 'zzz') AND (WBS3 <> 'zzz') AND transtype = 'in'
GROUP BY wbs2
this is what the output looks like:
wbs2 amount
01410
12170
12180
12190
12220
12230
90013271.02
Id there a way to specify where I want this exact amount to display without having to a subquery. I would ideally like to take the 3271.02 and put it where wbs2 = '1217'
Any suggestions?
Thank You.
Hi,
I need an advice from some SQL Guru. I have a Cursor (SQL Server 2005) which calculates the monthly employee wages. The outer query selects all the employees and the inner query computes the monthly salary day by day in a loop. For each day in a month, i insert a record in a temporary table. So for 2 employees, there can be 2 x 31 records (31 are the maximum days in a month). Later i rotate the temporary table to convert the 31 rows for each employee into one record so at the end there are only 2 employee records but with 31 columns. The problem is that the rotation is taking a huge performance hit and almost takes 5 minutes to convert rows to columns. I have even tried the PIVOT statement but that too almost takes the same time.
Could anyone suggest me how can i nail down a performance efficient query for a monthly report. As far as i know, even on Mainframes, monthly/annual reports takes a long time to compile. So if any GURU has a better idea, i would really appreciate it.
I am only asking about how would any Guru/Expert handle monthly/annual reports with huge amount of data in the tables efficiently.
Regards.........
Hi all,Just after some help with a query (Stored Procedure) I've managed to get wrapped round my head.The DB is as such:
COMPANY
Company_id
Company_name
COMPANY_GROUP
Group_id
Group_name
USER
User_id
User_name
Bridging tables
COMPANY_GROUP_BRIDGE
company_id
group_id
USER_COMPANY_BRIDGE
user_id
company_id Basically, the only parameter I have for the query is a User_id.I need to get the Group linked to the User and return all the companies within that group. I've
tried reading up on all the join types again but have just got this
completely wrapped round my neck. I keep thinking along the lines of
SELECT all the companies linked to all the groups linked to all the
companies linked to the User_id :s I must be able to do
this without using two Company tables...?
Any help much appreciated,Pete
I have
Categories table with CategoryID, Title, CategoryParentID columns
Questions table with QuestionID, Text columns
CategoriesQuestions table with CategoryID, QuestionID columns
Now I want to make the query which takes QuestionID and return all Categories of that provided QuestionID.
I already have made that query in 2 different styles.
First I make an Inner Join to retrieve the results
Select Categories.* from Categories Inner Join CategoriesQuestiosn on Categories.CategoryID = CategoriesQuestions.CategoryID where CategoriesQuestions.QuestionID = 1
Second I make a sub query to retrive the results.
Select * from Categories where CategoryID In (Select CategoryID From CategoriesQuestions where QuestionID = 1)
Now I am asking what is the better one in respect to performance.
And please provide any other query if you have.
Iwrote a query right, and need to get further information out of another table, so did a Join, but then also need to get further information out of another table..So, I need to add this query:
SELECT PERSONNUM as EmployeeCode
,[FIRSTNM] as Firstname
,[LASTNM] as LastName
,[HOMELABORLEVELDSC1] as Site
,[HOMELABORLEVELNM1] as SiteID
FROM [tkcsdb].[dbo].[VP_ALLPERSONV42]where USERACCOUNTSTATUS = 'active'
and homelaborlevelnm1 in ('11', '13', '32')
order by SiteID, LastName
To be included in this query:
SELECTa.PERSONNUM,
a.PERSONFULLNAME,
HOMELABORLEVELDSC1,
[SERVER]
[code]...
I have the following:
select
ad.bank,ad.account_number,ad.account_short_name,
p.security_id,isnull(b.NAME,isnull(tw.name,ms.name)) as SecurityDescription,
p.security_price,mc.classcode,
sum(case
when p.security_price = 9999999.99900 then 0
when mc.classcode between '000'and'299' then (p.quantity * p.security_price) / 100
when mc.classcode between '950'and'999' then (p.security_price * p.quantity) * -1
when mc.classcode between '300' and '324' or mc.classcode between '330' and '399'then p.acm_ctf_mtf_price * p.quantity
when p.security_price = '0' then p.cost
else
(p.security_price * p.quantity)
end) as MV,p.position_date,p.quantity,p.cost,p.acm_ctf_mtf_price
from positions p
left join BloombergEquityData b on p.security_id = b.ID_CUSIP
left join account_detail ad on p.account_id = ad.account_id
left join metavanteclasscodes mc on p.security_id = mc.cusip
left join MacgregorSecurityMaster ms on p.security_id = ms.cusip
left join TobaSecurityWarehouse tw on p.security_id = tw.cusip
where p.position_date = '03/05/2008' and
((ad.investment_authority_id in ('2','3') and ad.bank in ('98','7R')) or ad.bank = '9W')-- and ad.account_number = '41G006012'
group byad.account_number,ad.account_short_name,p.security_id,
ms.name,b.NAME,p.security_price,p.position_date,ad.bank,mc.classcode,p.quantity,p.cost,p.acm_ctf_mtf_price,isnull(b.NAME,isnull(tw.name,ms.name))
order by ad.account_number,p.security_id
For a given day, in this example 3/5 if I were to just select all the records I would get back around 550,000. If I put the filter on where I tell it to just show me one account its snappy. If I remove that filter and have it show me all the accounts it take forever. Here is the layout of my positions table where i am pulling the majority of the information:
CREATE TABLE [dbo].[positions](
[account_id] [int] NOT NULL,
[security_id] [varchar](50) NOT NULL,
[position_date] [smalldatetime] NOT NULL,
[quantity] [decimal](18, 5) NOT NULL,
[cost] [decimal](18, 5) NULL,
[security_price] [decimal](18, 5) NULL,
[acm_ctf_mtf_price] [decimal](18, 9) NULL,
CONSTRAINT [PK_positions] PRIMARY KEY CLUSTERED
(
[account_id] ASC,
[position_date] ASC,
[security_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
My questions is if you can identify any reason why the query might take more than 10 minutes (actually still running after 10 minutes) to complete when I have it bring me back all the records, not sure if its a lack of indexes or what, but was hoping for some suggestions on what I might be missing. I apologize if I havent included enough information, still learning how to troubleshoot the performance side of things.
Is there a better way to write this query?
SELECT t1.title,t1.record_id,
(SELECT SUM(t2.amount1) FROM table2 t2 WHERE t2.parent_id = t1.record_id)AS amt1,
(SELECT SUM(t2.amount2) FROM table2 t2 WHERE t2.parent_id = t1.record_id)AS amt2,
(SELECT SUM(t2.amount3) FROM table2 t2 WHERE t2.parent_id = t1.record_id)AS amt3
FROM table1 t1
Hi all..
i have a question about TSQL,
how it is possible to write this querry correctly:
Code SnippetSELECT dtEvent.EventNo as "Event ID:", FROM LIKE '%dtEvent%' AS dtEvent,WHERE dtMachine.id = dtEvent.agentmachineid
in the "FROM" part is a problem....LIKE '%%' isn't correct, but i don't know how can i write this query correctly...
by the way: for example just "dtEvent" is known ...but not the fullname like "dtEvent_XXXXXXXX"
Thanks
Not sure if this is right place to ask but here is our problem. We are getting records from more than one table. Table1 and Table2 for example Both Tables haveid, value table11, 201, 30 Table 21, 501, 40 here is the queryselect table1.id, table1.value, table2.value from Table1inner join Table2on Table1.id = table2.idproblem is this returns 4 rows like this1, 20 , 501, 30 , 501, 20 , 401, 30 , 40 When we need it to return1, 20 , null1, 30, null1, null, 401, null , 50 any help on this would be appreciated.Thank You in advance.
View 2 Replies View RelatedHi all.I want to use the following query in a sp to enable paging using ObjectDataSource.The problem (being EXTREMELEY slow) arises when I add these joins and where statements.
SELECT r.RID AS ReqID, r.Name AS ReqName, r.Family AS ReqFamily,t3.Name AS DistName, t4.Name AS RurName,t5.Name AS VilName, n.Name+' '+n.Family AS NazerName ,ROW_NUMBER() over (order by r.Family) AS RowRankFROM Requests rLEFT OUTER JOIN Nazeran n ON r.nazerID = n.ID LEFT OUTER JOIN t1States t1 ON t1.ID = r.StateID LEFT OUTER JOIN t2Provinces t2 ON t1.ID = t2.StateID AND r.ProvID = t2.ID LEFT OUTER JOIN t3Districts t3 ON t2.ID = t3.provID AND t1.ID = t3.stateID AND r.DistID = t3.ID LEFT OUTER JOIN t4RuralDistricts t4 ON t3.ID = t4.distID AND t2.ID = t4.provID AND t1.ID = t4.stateID AND r.RurID = t4.ID LEFT OUTER JOIN t5Villages t5 ON t4.ID = t5.rurID AND t3.ID = t5.distID AND t2.ID = t5.provID AND t1.ID = t5.stateID AND r.VilID = t5.IDWHERE r.stateid=(case when @StateID is null or @StateID='' then r.stateid else @StateID end) and r.provid=(case when @provID is null or @provID='' then r.provid else @provID end) and r.rID=(case when @ReqID is null or @ReqID='' then r.rID else @ReqID end) and isnull(r.nazerID,'')=(case when @nazerID is null or @nazerID='' then isnull(r.nazerID,'') else @nazerID end) and r.name+' '+r.family like (case when @ReqName is null or @ReqName='' then r.name+' '+r.family else '%'+@ReqName+'%' end)
**there are 1million rows in [Requests] table ,200000 rows in [t5villages], and about total 5000 rows in other tables.As you can see, this is for a GridView showing list of people requesting a loan allowing users to make alternative searches based on Name, Familyname ,ID ,...
would you please help me optimize and make fast this query.Many thanks..
I wrote a SQL query that firstly do an outer-join for two table and after I add another
table with outer-join as well.
I'm getting the following message:
Server: Msg 301, Level 16, State 1, Line 1
Query contains an outer-join request that is not permitted.
May be someone knows what is it?
If I remove the second join- it work....
Ok I have a complex query that works great, but now I need to join yet another table but the problem is, the value that links it to the rest of the query is a value that is returned from a case statement earlier in the query...
For Example...
SELECT var1, var2, var3,
CASE T1.Number WHEN 0 THEN T2.Result ELSE T3.Result END as var4
FROM TABLE1 T1
LEFT OUTER JOIN TABLE2 T2 on T2.Value=T1.Value
LEFT OUTER JOIN TABLE3 T3 on T3.Value=T1.Value2
Above is a generalized, much smaller example of my working Query.
Now what I need to do is join TABLE4 but the value I need to connect it to the rest of the query is var4, which can come from 2 different places depending on the value of T1.Number
Basically the logic I need to make it work is to either have a case statement in my JOIN. Like, 'LEFT OUTER JOIN TABLE4 on T4.Value=(CASE T1.Number WHEN 0 THEN T2.Result ELSE T3.Result END)'
Is this even possible and if so, how? I need to keep the logic in the main query to preserve performance...
Thank you in advance,
Casey
My tables and data as follows
AB_Corporate_Project
Fields are : ab_crp_id , cust_name , owner_rep_id
Data in table AB_Corporate_Project is as follows
ab_crp_id cust_name owner_rep_id
1 harry 3
2 msas 2
AB_Plant_Project
Fields are : ab_plant_id , ab_name , owner_rep_id
Data in Table AB_Plant_Project
ab_plant_id ab_name owner_rep_id
1 abc 1
2 def 2
Other_Project
Fields are : other_proj_id,ot_name, owner_rep_id
Data in table Other_Project
other_proj_id ot_name owner_rep_id
1 xyz 2
2 cdf 3
Owner_Rep
Fields are : owner_rep_id,owner_name
Data in Table Owner_Rep
owner_rep_id owner_name
1 henry
2 hologa
3 tmw
Daily_Time_Entry
Fields are: dl_id,dt_id,project_type,project_id,time_st
Data in table Daily_Time_Entry
dl_id dt_id project_type project_id time_st
1 03/23/08 AB Corporate 1 1.20
2 03/23/08 AB Corporate 2 3.25
3 03/21/08 AB Corporate 1 2.25
4 03/23/08 AB Plant 1 4.35
5 03/23/08 AB Plant 2 4.50
6 03/23/08 Other Project 1 3.24
7 03/23/08 Other Project 2 4.35
I want to show records as per date from Daily_Time_Entry table and detail data to be displayed as follows
dl_id dt_id project_type project_id time_st exp1 exp2 exp3 owner_name
1 03/23/08 AB Corporate 1 1.20 3 -- -- tmw
2 03/23/08 AB Corporate 2 3.25 2 -- -- hologa
3 03/21/08 AB Corporate 1 2.25 3 -- -- tmw
4 03/23/08 AB Plant 1 4.35 -- 1 -- henry
5 03/23/08 AB Plant 2 4.50 -- 2 -- hologa
6 03/23/08 Other Project 1 3.24 -- -- 2 hologa
7 03/23/08 Other Project 2 4.35 -- -- 3 tmw
Also in project_type AB Corporate for AB_Corporate_Project, AB Plant for AB_Plant_Project , Other Project for Other_Project
I write query for this is as follows
Select dl. dl_id,dl.dt_id,dl.project_type,dl. project_id,dl.time_st,ac.owner_rep_id as exp1,ab. owner_rep_id as exp2,op. owner_rep_id as exp3,ow. owner_name
From Daily_Time_Entry dl left outer join
AB_Corporate_Project ac on dl. project_id = ac. ab_crp_id and dl. project_type=’ AB Corporate’ left outer join AB_Plant_Project ab on dl. project_id =ab. ab_plant_id and
dl. project_type=’ AB Plant’ left outer join Other_Project op on dl. project_id = op. other_proj_id and dl. project_type=’ Other Project’ inner join Owner_Rep ow on
(ow. owner_rep_id = ac. owner_rep_id) or(ow. owner_rep_id = ab. owner_rep_id)or
(ow. owner_rep_id = op.owner_rep_id)
So how can I write query to show output as follows or any other way or how to create Sql cursors for to show output
Plz help to solve this query to show output as specified.
uday
Hi all,I have the following tables:A1==HostID Name RunID------ ---- -----1 host1 NULL2 host2 13 host3 NULLA2==RunID SessionID----- ---------1 42 2A3==SessionID Name--------- ----4 Session12 Session3I want to show every record from A1 with SessionName from A3, unlessthe field RunID in A1 is NULL and then I want to see NULL, like this:HostID Name RunID Name------ ---- ----- ----1 host1 NULL NULL2 host2 1 Session13 host3 NULL NULLwhen I try the following query:select A1.*, A3.Namefrom A1, A2, A3where A1.RunId *= A2.RunId and A2.SessionID = A3.SessionIDI get the following error:The table 'A2' is an inner member of an outer-join clause. This is notallowed if the table also participates in a regular join clause.How can I overcome this problem. Please help. (I use this syntax isteadof joins since I have to supprt also Oracle DB and this syntax issimpler to translate).Thanks in advance,Yaron
View 1 Replies View RelatedHi,I have a view(A) and I am trying to do a join on another table (B) toinclude only rows where date values in view A is greater than in tableB. I also want the view to pick up rows in viewA based on date values.Here is what I have so far:SELECT *FROM viewA vwleft JOIN tableB tb ONvw.id = tb.id and(vw.date1 > tb.date1 orvw.date2 > tb.date2 orvw.date3 > tb.date3)WHERE vw.date4 > getdate()-1Not matter what kind of join I use I can get both the rows from theview where dateA > getdate()-1 AND where date1-3 are greate than intableB. Dates 1 - 4 seperate date fields. Could someone please tellme what I am doing wrong.Thanks.
View 3 Replies View RelatedHelloLet me explain the problem I am having:I have two tables, data_t and a_data_ta_data_t is the archive table of data_tThe two tables are exactly the same.In the table values are stored:Value (A numeric value)Code (A text code to identify a report with data)Line (The line number)Col (The Col Number)EDate (The date of entry)Grp (A number of a group the data belongs to)I want to get the value from data_t minus the value from a_data_t withthe same Code, Line and Col but with a different EDate (To view thevariance).Here is my statement:select d1.line, d1.col, (IsNull(d1.value,0) - IsNull(d2.value,0)) asvalue from data_t d1full outer join a_data_t d2 on d1.Code = d2.Code and d2.line = d2.lineand d1.col = d2.colwhered1.Code = 'XC001' and d1.line between 1 and 20 and d1.grp = 26and d1.EDate = '2006/06' and d2.grp = 26 and d2.EDate = '2006/05'order by d1.line, d1.colIt works fine EXCEPT when there is a value in either of the tables thatisn't in the other one, then a value is not given.Example:data_t doens't have a value for line=1 and col=2 and grp=26 and Code ='XC001' and EDate = '2006/06'a_data_t has the value of 50000 for the same details (Except Edate of'2006/5')Instead of returning -50000 it doesn't return anything.I hope I could explain it correctly.Any help will be greatly appreciated.Thanks.
View 1 Replies View RelatedMy tables and data as follows
AB_Corporate_Project
Fields are : ab_crp_id , cust_name , owner_rep_id
Data in table AB_Corporate_Project is as follows
ab_crp_id cust_name owner_rep_id
1 harry 3
2 msas 2
AB_Plant_Project
Fields are : ab_plant_id , ab_name , owner_rep_id
Data in Table AB_Plant_Project
ab_plant_id ab_name owner_rep_id
1 abc 1
2 def 2
Other_Project
Fields are : other_proj_id,ot_name, owner_rep_id
Data in table Other_Project
other_proj_id ot_name owner_rep_id
1 xyz 2
2 cdf 3
Owner_Rep
Fields are : owner_rep_id,owner_name
Data in Table Owner_Rep
owner_rep_id owner_name
1 henry
2 hologa
3 tmw
Daily_Time_Entry
Fields are: dl_id,dt_id,project_type,project_id,time_st
Data in table Daily_Time_Entry
dl_id dt_id project_type project_id time_st
1 03/23/08 AB Corporate 1 1.20
2 03/23/08 AB Corporate 2 3.25
3 03/21/08 AB Corporate 1 2.25
4 03/23/08 AB Plant 1 4.35
5 03/23/08 AB Plant 2 4.50
6 03/23/08 Other Project 1 3.24
7 03/23/08 Other Project 2 4.35
I want to show records as per date from Daily_Time_Entry table and detail data to be displayed as follows
dl_id dt_id project_type project_id time_st exp1 exp2 exp3 owner_name
1 03/23/08 AB Corporate 1 1.20 3 -- -- tmw
2 03/23/08 AB Corporate 2 3.25 2 -- -- hologa
3 03/21/08 AB Corporate 1 2.25 3 -- -- tmw
4 03/23/08 AB Plant 1 4.35 -- 1 -- henry
5 03/23/08 AB Plant 2 4.50 -- 2 -- hologa
6 03/23/08 Other Project 1 3.24 -- -- 2 hologa
7 03/23/08 Other Project 2 4.35 -- -- 3 tmw
Also in project_type AB Corporate for AB_Corporate_Project, AB Plant for AB_Plant_Project , Other Project for Other_Project
I write query for this is as follows
Select dl. dl_id,dl.dt_id,dl.project_type,dl. project_id,dl.time_st,ac.owner_rep_id as exp1,ab. owner_rep_id as exp2,op. owner_rep_id as exp3,ow. owner_name
From Daily_Time_Entry dl left outer join
AB_Corporate_Project ac on dl. project_id = ac. ab_crp_id and dl. project_type=€™ AB Corporate€™ left outer join AB_Plant_Project ab on dl. project_id =ab. ab_plant_id and
dl. project_type=€™ AB Plant€™ left outer join Other_Project op on dl. project_id = op. other_proj_id and dl. project_type=€™ Other Project€™ inner join Owner_Rep ow on
(ow. owner_rep_id = ac. owner_rep_id) or(ow. owner_rep_id = ab. owner_rep_id)or
(ow. owner_rep_id = op.owner_rep_id)
So how can I write query to show output as follows or any other way or how to create Sql cursors for to show output
Plz help to solve this query to show output as specified.
<!--[endif]-->
I have a table with a DateTime column called 'cutoff'. Is it possible to grab the TOP 1 record ordered by the cutoff column based on the current date?SELECT TOP 1 * FROM mytable WHERE cutoff >= GetDate() ORDER BY cutoff...works only if there is a cutoff < the current date. If GetDate() is greater then latest cutoff date it returns nothing. In this case, is it possible to just return the latest record? I'd like to keep this to 1 query if possible? :-)
View 1 Replies View RelatedI was hoping to get a little input on a problem I'm having.
In the DataAccessLayer of my application, I have a "search" function written that takes a bunch of parameters (in the form of a class object) and depending on what each of the paramaters are set to (to include search type parameters) it builds an appropriate select statement. The issue is that my company has recently decided to require all DataAccessLayer functions to use TableAdapters. TableAdapters can use StoredProceedures, and StoredProceedures can make external calls (it all seems a bit backward to me, but there does seem to be a bit of logic in that TableAdapters contain a connection string which is set in the app.Config file)... Anyway, here's an example of how I am doing it currently, and I was hoping someone could suggest a way I could do it with either TSQL, or otherwise. If CLR is the way to go, how does that work?
internal static dsStrongTypeDataSet GetAll(clsMyClass inData)
{
bool first = true;
dsStrongTypeDataSet data = new dsStrongTypeDataSet ();
string selectStatement = "Select * from tblMyTable where ";
//There is one of these if statements for each parameter
if ((inData.Paramater1 != null))
{
if (!first)//not as important in this section of code,
//but there are areas where there are up to 30 parameters
selectStatement += " and ";
if (inData.SrchParameter1 == SearchType.Fuzzy)
selectStatement += " Column1 LIKE ('%" + inData.Parameter1 + "%') ";
else if (inData.SrchParameter1 == SearchType.Literal)
selectStatement += "Column1 = '" + inData.Parameter1 + "'";
first = false;
}
//More if statements like above for EVERY parameter possible
SqlCommand selectCommand =
new SqlCommand(selectStatement, CorrectSqlConnection());
SqlDataAdapter dataAdapter = new SqlDataAdapter(selectCommand);
dataAdapter.Fill(data, "tblMyTable");
return data;
}
Hi,
I hope that someone can help me understand why my query is not returning what I expect. When I run this query:
SELECT DISTINCT(TransactionKey)
FROM Transactions_Fact
WHERE DateKey = 14550
AND TransactionKey BETWEEN 1 AND 90000000
AND TransactionKey NOT IN (SELECT DISTINCT(TransactionKey)
FROM tmpTransactions
WHERE TranDate = 14550
AND TransactionKey BETWEEN 1 AND 90000000)
I get 150 rows back, which is what I expect. However, if I leave out the 'AND TransactionKey BETWEEN 1 AND 90000000', then I don't get back anything?!?
SELECT DISTINCT(TransactionKey)
FROM Transactions_Fact
WHERE DateKey = 14550
AND TransactionKey NOT IN (SELECT DISTINCT(TransactionKey)
FROM tmpTransactions
WHERE TranDate = 14550)
Any ideas as to what I'm missing here? It seems like it should at least return the same 150 rows. Thanks for any help.
Gary Hines
Hello....
i have 4 tables in a databse(tab1,tab2,tab3,tab4). id is the attribute of TAB1 and id also foreign key of all the tables.....That mean id attribute exists on all the tables........so when i want to query to more than 1 table , i have to go for Joins...basically i am using innerjoin.........
Can u tell me is there any other way to find the same output as the innerjoin......without using the keyword INNER JOIN
for example ......SELECT * FROM TAB1 INNER JOIN (TAB2 INNERJOIN TAB3 ON TAB2.ID=TAB3.ID) ON TAB1.ID=TAB2.ID
another query........
SELECT *FROM TAB1,TAB2,TAB3 WHERE (TAB1.ID=TAB2.ID) AND (TAB2.ID=TAB3.ID) AND (TAB3.ID=TAB1.ID)
above queries are same or not??????????
Hi There !!
To finetune performance for some of our queries,
I have come across suggestions to use
- JOINS instead of WHERE clause wherever possible
- and avoid using Aliases
Although Avoiding aliases looks reasonable I am yet to be convinced about JOINS replacing the WHERE CLAUSE . What is the experts take on this one ????
Also,
I checked the estimated plan in SQL server by running the following 2 queries into my Query Designer
tables : dba ( empid, empname )
project ( project_empid references dba.empid, project_name )
USING A WHERE CLAUSE and Alias
-------------------------
select a.emp_name from dbo.dba a, dbo.project b
where
a.empid =b.project_emp
and b.project_name is not null
USING A JOIN
-----------------
select emp_name from dbo.dba
as
a inner JOIN dbo.project
ON empid = dbo.project.project_emp
AND dbo.project.project_name is not NULL
******
I find from the Estimated plan that both the queries give the same amount of cost ( I/O, CPU, et all ) :shocked:
Any comments/ suggestions.
Thanks,
Have a great time
-Ranjit.
-------------------------------------
It pays to be honest to your DBA
I have a single table named PROCESS which contain following three fields
ProcessID
ParentID,
info
* Every process have a unique ProcessID and ave single parent process which is identified by ParentID.
* If a process does not have a Parent then its ParentID value is -1.
*Only single level of Parent-child hierarchy is maintained.
Can anyone please tell me the Query that uses Recursive JOINS to retrieve the ProcessID s based in following Conditions.
1. if the Parent's 'info' field contains given value then retrieve all the process under it.
2. Retrive all the process whose 'info' contains given value and excluding the Processes resulted from 1st conditiion
Thanks in advance
Bharath Booshan L
I have a couple of tables that look like this (not excactly but closeenough):[Contact]id intfname varchar(50)lname varchar(50)[ContactPhoneNumber]id intnumber varchar(15)ext varchar(6)contact_id intpriority int (indicates primary, secondary... numbers)type int (indicates type of number: fax, cell, land line)I'm looking for a more optimized method of displaying this informationin this format:fname, primary business phoneUsing a derived column like this works, but seems to be slow with manyrecords, despite tuning indexes:SELECT c.fname AS [First Name],( SELECT TOP 1numberFROM ContactPhoneNumber cpnWHERE cpn.type = 1AND cpn.contact_id = c.idORDER BY cpn.priority) AS NumberFROM Contact cI can get the same results using a join, and it's a lot faster. But I'mnot sure how to select only the primary phone number this way...basically the first phone number whose priority is either NULL or 1.Any suggestions?*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
View 5 Replies View RelatedHello,
I have a query with 11 left joins. Some hits against tables with small amounts of reference data, whereas others are not so small. Should I rewrite this in another way, as performance is a requirement on this one? Or, should I do it another way?
How would I rewrite left joins? Any examples?
Thanks.
How to find maximum value from two tables have the same field name?
For example:
Table -1 has field calcuated_price and its max value is 3500 and then Table -2 has
same field name calcuated_price has max value is 3000.
Nishith
I am trying to return an ouput parameter from my query. I have tested the stored proceedure extensivly within the SQL Management Studio, and I know it works fine, so that means the error is somewhere within my code, but for the life of my I cant figure where.
Here is my stored proc:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:Name
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[tblSiteVisits_FindOfficerName]
@VisitID int,
@OfficerName varchar(100) output
AS
BEGIN
-- Variables
Declare @OfficerID int
--Declare @OfficerName varchar(100)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Select @OfficerID = (Select top 1
OfficerID from tblSiteVisitOfficers Where VisitID = @VisitID)
IF (@OfficerID Is Null)
BEGIN -- Get the None Registered Officer
Select @OfficerName = (Select top 1 OfficerOther from dbo.tblSiteVisitOfficers Where VisitID = @VisitID)
print 'Got unregistered Officer ' + @OfficerName
END
ELSE
BEGIN -- Get the Registered Officer
Select @OfficerName = (Select OfficerFName + ' ' + OfficerLname from dbo.tblOfficers Where OfficerID = @OfficerID)
print 'Got Registered Officer ' + @OfficerName
END
END
And here is the code I am using to access this proceedure:1 Dim blah As String
2 Dim conn2 As New SqlConnection()
3 Dim cmd2 As New SqlCommand()
4 conn2.ConnectionString = _ConnString
5 cmd2.Connection = conn2
6 cmd2.CommandType = CommandType.StoredProcedure
7 cmd2.CommandText = "dbo.tblSiteVisits_FindOfficerName"
8
9 cmd.Parameters.AddWithValue("@VisitID", Convert.ToInt32(row("VisitID")))
10 cmd.Parameters.Add("@OfficerName", SqlDbType.VarChar, 100)
11 cmd.Parameters("@OfficerName").Direction = ParameterDirection.Output
12 Try
13 conn.Open()
14 cmd.ExecuteNonQuery()
15 blah = cmd.Parameters("@OfficerName").Value.ToString()
16
17 Catch ex As Exception
18 Throw ex
19 Finally
20 conn.Close()
21 End Try
22
However there I never recieve the output value, and because of the way my database is structures, there is no possible way, that there is no output value.If anyone can help, that would be great, kind regards.
VIEW -ITMISSUES
Fields
issno-issdate-item-trxqty
1 - 010904-xyz - 2
2 - 020904-xyz - 5
3 - 080804-xyz - 6
4 - 020804-xyz - 9
VIEW-USR_VIEW_RPTS
Fields
receiptno- rptdate - item- rctqty
1 - 010804 - xyz - 10
2 - 010904 - xyz - 20
3 - 150804 - xyz - 25
4 - 150904 - xyz - 15
I have 2 views (ITMISSUES,USR_VIEW_RPTS) I want to retreive (Count of Issues Alis name)-noofissues,total issqty,
total (sum of Total Receipt Qty Alis name)- rctqty between a range of items and between a range of dates.How can i do it.
Result:
noofissues - totalIssqty - rctqty
2 - 15 - 35
i have tried with this Query
SELECT COUNT(DOCNUMBR)AS NOOFISSUES,ABS(SUM(TRXQTY))AS TOTISSQTY,
MAX(ITMISSUES.DOCDATE)AS LASTISSDATE,SUM(QTY) AS RCQTY
from ITMISSUES
INNER JOIN USR_VIEW_RPTS ON ITMISSUES.ITEMNMBR=USR_VIEW_RPTS.ITEMNMBR
WHERE ITMISSUES.ITEMNMBR BETWEEN 'xyz' AND 'xyz'
AND ITMISSUES.DOCDATE BETWEEN '2004-08-01' AND '2004-08-31'
Hi,I am using a .dqy file to import some rows from a sql server 2003database to microsoft excel. Unfortunately, I have am importing morerows than excel can handle. Is there a way around this?It appears that there is no equivalent of the LIMIT(offset, number ofrows to return) function of mysql in tsql. Had there been anequivalent, I would have created multiple .dqy files, which would querythe database for pages of results as appropriate. There is an upperlimit to the number of records that there can be in the database, sothis would have been a feasible solution.Also, I must use .dqy files (or something equivalen) because thequeries are invoked from a website, and it is necessary to download theresults, sort/filter, etc. (in excel).Thanks for any suggestions.
View 4 Replies View RelatedHello.
Is there any possibility in SQL server 2005 to find out the source of some sql-query result? I need some kind of meta information about result I got. For example:
[if query is]
select
t1.id as id1,
t2.id as id2,
67 as col67
from t1, t2
[result will be]
| id1 | id2 | col67 | .......................
and at the end of all I need something like :
id1-from t1.id; id2-from t2.id; col67-unknown;
thanks for help