Subquery Totals Based On Clauses
Aug 1, 2013
I have a Master table with a OrderNbr which is also contained in the Detail table.
It's a 1 to Many relationship, respectively.
I want to update the MASTER.FinalizedDate using a "select top 1 FinalizedDate order by FinalizedDate DESC" from the Detail table but the clause is ALL the Status have to be "F". So OrderNbr 12345 should not get updated because it contains a 'O'. OrderNbr 67899 should get updated in the Master table to 2/26/2013 because all have a 'F' and the last date to post is the official finalized date.
Here is what I came up with.......so far, but not sure how to work in the Status piece on 1 to M.
The rub here is that even if one row has the 'O' status I want to ignore the update. If all have the 'F' then I want the udpate to happen.
Update MASTER
Set FinalizedDate = (select top 1 d.FinazliedDate from Detail d
where m.OrderNbr = d.OrderNbr
and d.Status not in ('O')
Order by FinalizedDate DESC)
From MASTER m
How do I not include all 3 rows for OrderNbr 12345 because one row has the Status "O" in the DETAIL table?
Here are the table looks........
MASTER
OrderNbr Ytotals Ztotals Xtotals Finalized Date
12345$1,500$1,500$1,200
67899$1,200$1,100$900
DETAIL
OrderNbrItemNbr PriceStatusFinalized Date
1234563453453 $1,400F1/2/2013
12345554444 $1,500F1/2/2013
12345545444 $2,200O NULL
67899333334 $899F2/24/2013
678993434344 $659F2/24/2013
67899434676 $499F2/26/2013
6789978888 $599F2/24/2013
View 3 Replies
ADVERTISEMENT
Jul 17, 2007
Hi,
I use the following query in my stats page to find the unique visitors and the pages they visited per day in a given month.
SELECT
CONVERT(CHAR(10),LogDate,103) As Date_,
Count(DISTINCT LogRemote_Addr) As Visitors,
Count(Lid) As Pages
FROM Log
WHERE LogMonth=7
Group by CONVERT(CHAR(10),LogDate,103)
ORDER BY CONVERT(CHAR(10),LogDate,103)
I would like to have the totals (Sum) of the "Visitors" and "Pages" also for the given month.
I think I have to use a subquery to accomplish that but I can't figure it out. I would appreciate your help.
Thanks,
Deni
www.tabletennis.gr
View 9 Replies
View Related
Sep 14, 2013
I have created an SQL report that returns order data from a shop DB.
How can you get the output to total the number of order instances and the total value of those orders, both per user_id? Is this possible?
View 3 Replies
View Related
Jun 13, 2008
I'd like to name an output field based on the result of a subquery look up. This is the idea:
select colname1 as (select name from tableofnames where nameid=1)
..but that doesn't work. What would?
View 4 Replies
View Related
Apr 9, 2008
ID
TestID
IsCorrect
294817
1
1
294817
1
0
294817
1
1
294817
1
0
301391
2
1
301391
2
1
301391
2
1
301391
2
0
304409
1
1
304409
1
0
304409
1
0
304409
1
0
I am trying to get the percent correct for each ID and each testID from the table above. Also, IsCorrect column valuse (1= correct; 0= not correct)
select ID, count(Iscorrect) [total], (select count(Iscorrect) from dbo.StudentTestAnswers
where TestID=1 and Iscorrect=1) as totalCorrect,((select count(Iscorrect) from dbo.StudentTestAnswers
where TestID=1 and Iscorrect=1)/count(Iscorrect)*100) as PctCorrect
from StudentTestAnswers
where testID=1
Group by ID, TestID
The output should look like this after running the T-SQL code above. But I am getting some weird number on totalCorrect and PctCorrect columns. Not sure if my SQL code is even correct.
ID
total
totalCorrect
PctCorrect
294817
4
2
50%
301391
4
3
75%
304409
4
1
25%
View 7 Replies
View Related
May 9, 2015
I have some data grouped in a table by a certain criteria, and for each group it is computed a subtotal for the group. Of the values from each of the group, I want to create a grand total on the report by adding every subtotal from each group.
Example:
...
....
Group1 Value
10
20
Sub Total 1: 30
Group2 Value
15
25
Sub Total 2: 40
Now, I would like to be able to add subtotal 1 (30) to subtotal 2 (40) and my grand total would be 70. Can I accomplish this task in SSRS?
View 5 Replies
View Related
Oct 2, 2006
I have been providing sales data for a few months now from a table that is set up like this:
Date WorkDay GasSales EquipmentSales
9/1/2006 1 100.00 200.00
9/4/2006 2 50.00 45.00
etc.
As can be seen, the data is daily, i.e., on the first workday of September we sold one hundred dollars in gas and two hundred dollars in equipment. On the second workday of September we sold fifty dollars in gas and forty-five dollars in equipment.
Now, however, the data I have to pull from is cumulative. So, using the last table as an example it would look like this:
Date_WorkDay_GasSales_EquipmentSales
9/1/2006 1 100.00 200.00
9/4/2006 2 150.00 245.00
etc.
To make things more complicated, the powers that be wanted this data presented in this fashion:
Total Sales:
1_2_etc.
300.00 95.00 etc.
So, I have been doing a pivot on a CRT to get the data to look like I want. The code is like this:
with SalesCTE (Month, WorkDay, [Total Sales])
as
(
SELECT
datename(month, cag.date),
cag.WorkDay AS [Work Day],
sum(cag.sales_gas + cag.sales_hgs) AS [Total Sales]
FROM CAG INNER JOIN
Branch ON CAG.[Oracle Branch] = Branch.OracleBranch
group by cag.date, cag.WorkDay
)
select * from SalesCTE
pivot
(
sum([Total Sales])
for WorkDay
in ([1],[2],[3],[4],[5],,[7],,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])
) as p
So, my question is:
How do I get the data to give back daily totals instead of the cumulative amounts for each workday? If the query was a simple one, I'd do something like
select [1] as [Day 1], [2]-[1] as [Day 2], [3]-[2] as [Day 3], etc.
but the query is far from normal, with the CRT and the pivot. I can't seem to get it to work how I'd like.
Any advice/answers? Thanks in advance!!!
P.S. I don't know how to get it to quit with the freakin' smileys.... I suppose you can figure out what my code is really supposed to look like above. Needless to say, it doesn't include a devil face and a damn music note...
View 12 Replies
View Related
Apr 26, 2008
hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues] @Uid intASBEGIN SET NOCOUNT ON; select DATEPART(year, c.fy)as fy, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1% JRF' ) as survivorship, (select contribeamount from wh_contribute where and contribename like 'Gross Earnings' and ) as ytdgross, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5% JRP') as totalcontrib, from wh_contribute c where c.uid=@Uid Order by fy Asc .....what is the wrong here?? " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap...
View 1 Replies
View Related
Jul 20, 2005
I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO
View 3 Replies
View Related
Mar 6, 2008
I am getting an error as
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
while running the following query.
SELECT DISTINCT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,
LUP_FIX_DeptDetails.DeptName AS CurrentDepartment,
LUP_FIX_DesigDetails.DesigName AS CurrentDesignation,
LUP_FIX_ProjectDetails.ProjectName AS CurrentProject,
ManagerName=(SELECT E.FirstName+' '+E.LastName
FROM EmployeeDetails E
INNER JOIN LUP_EmpProject
ON E.Empid=LUP_EmpProject.Empid
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid = LUP_FIX_ProjectDetails.Projectid
WHERE LUP_FIX_ProjectDetails.Managerid = E.Empid)
FROM EmployeeDetails
INNER JOIN LUP_EmpDepartment
ON EmployeeDetails.Empid=LUP_EmpDepartment.Empid
INNER JOIN LUP_FIX_DeptDetails
ON LUP_EmpDepartment.Deptid=LUP_FIX_DeptDetails.Deptid
AND LUP_EmpDepartment.Date=(SELECT TOP 1 LUP_EmpDepartment.Date
FROM LUP_EmpDepartment
WHERE EmployeeDetails.Empid=LUP_EmpDepartment.Empid
ORDER BY LUP_EmpDepartment.Date DESC)
INNER JOIN LUP_EmpDesignation
ON EmployeeDetails.Empid=LUP_EmpDesignation.Empid
INNER JOIN LUP_FIX_DesigDetails
ON LUP_EmpDesignation.Desigid=LUP_FIX_DesigDetails.Desigid
AND LUP_EmpDesignation.Date=(SELECT TOP 1 LUP_EmpDesignation.Date
FROM LUP_EmpDesignation
WHERE EmployeeDetails.Empid=LUP_EmpDesignation.Empid
ORDER BY LUP_EmpDesignation.Date DESC)
INNER JOIN LUP_EmpProject
ON EmployeeDetails.Empid=LUP_EmpProject.Empid
AND LUP_EmpProject.StartDate=(SELECT TOP 1 LUP_EmpProject.StartDate
FROM LUP_EmpProject
WHERE EmployeeDetails.Empid=LUP_EmpProject.Empid
ORDER BY LUP_EmpProject.StartDate DESC)
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid=LUP_FIX_ProjectDetails.Projectid
WHERE EmployeeDetails.Empid=1
PLEASE HELP.................
View 1 Replies
View Related
May 14, 2008
Hi,
I've running the below query for months ans suddenly today started getting the following error :"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Any ideas as to why??
SELECT t0.DocNum, t0.Status, t0.ItemCode, t0.Warehouse, t0.OriginNum, t0.U_SOLineNo, ORDR.NumAtCard, ORDR.CardCode, OITM_1.U_Cultivar,
RDR1.U_Variety,
(SELECT OITM.U_Variety
FROM OWOR INNER JOIN
WOR1 ON OWOR.DocEntry = WOR1.DocEntry INNER JOIN
OITM INNER JOIN
OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod ON WOR1.ItemCode = OITM.ItemCode
WHERE (OITB.ItmsGrpNam = 'Basic Fruit') AND (OWOR.DocNum = t0.DocNum)) AS Expr1, OITM_1.U_Organisation, OITM_1.U_Commodity,
OITM_1.U_Pack, OITM_1.U_Grade, RDR1.U_SizeCount, OITM_1.U_InvCode, OITM_1.U_Brand, OITM_1.U_PalleBase, OITM_1.U_Crt_Pallet,
OITM_1.U_LabelType, RDR1.U_DEPOT, OITM_1.U_PLU, RDR1.U_Trgt_Mrkt, RDR1.U_Wrap_Type, ORDR.U_SCCode
FROM OWOR AS t0 INNER JOIN
ORDR ON t0.OriginNum = ORDR.DocNum INNER JOIN
RDR1 ON ORDR.DocEntry = RDR1.DocEntry AND t0.U_SOLineNo - 1 = RDR1.LineNum INNER JOIN
OITM AS OITM_1 ON t0.ItemCode = OITM_1.ItemCode
WHERE (t0.Status <> 'L')
Thanks
Jacquues
View 4 Replies
View Related
Jul 19, 2007
Hi guys,
A have a problem that I need understand.... I have 2 server with the same configuration...
SERVER01:
-----------------------------------------------------
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
********************************
SERVER02:
-----------------------------------------------------
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
OK, the problem is that if a run the below query in server01, i get error 512:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
But, if run the same query in the server02, the query work fine -.
I know that I can use IN, EXISTS, TOP, etc ... but I need understand this behavior.
Any idea WHY?
SELECT dbo.opf_saldo_ctb_opc_flx.dt_saldo,
dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,
dbo.opf_saldo_ctb_opc_flx.cd_classificacao,
dbo.opf_movimento_operacao.ds_tipo_transacao ds_tipo_transacao_movimento ,
dbo.opf_header_operacao.ds_tipo_transacao ds_tipo_transacao_header,
'SD' ds_status_operacao,
dbo.opf_header_operacao.ds_tipo_opcao ,
dbo.opf_header_operacao.id_empresa,
dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente,
0 vl_entrada_compra_ctro ,0 vl_entrada_compra_premio,
0 vl_entrada_venda_ctro , 0 vl_entrada_venda_premio,
0 vl_saida_compra_ctro, 0 vl_saida_compra_premio,
0 vl_saida_venda_ctro, 0 vl_saida_venda_premio,
0 vl_lucro , 0 vl_prejuizo, 0 vl_naoexec_contrato,
0 vl_naoexec_premio,
sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_ganho) vl_aprop_ganho,
sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_perda) vl_aprop_perda,
sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_ganho) vl_rever_ganho,
sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_perda) vl_rever_perda,
sum(dbo.opf_saldo_ctb_opc_flx.vl_irrf) vl_irrf
FROM dbo.opf_saldo_ctb_opc_flx,
dbo.opf_header_operacao ,
dbo.opf_movimento_operacao
WHERE dbo.opf_saldo_ctb_opc_flx.dt_saldo = '6-29-2007 0:0:0.000'
and ( dbo.opf_header_operacao.no_contrato = dbo.opf_saldo_ctb_opc_flx.no_contrato )
and ( dbo.opf_header_operacao.no_contrato = dbo.opf_movimento_operacao.no_contrato )
and ( dbo.opf_movimento_operacao.dt_pregao = (select (o.dt_pregao) from dbo.opf_movimento_operacao o
where o.no_contrato = dbo.opf_movimento_operacao.no_contrato and o.dt_pregao <='6-28-2007 0:0:0.000' ) )
and (dbo.opf_saldo_ctb_opc_flx.ic_tipo_saldo = 'S')
group by dbo.opf_saldo_ctb_opc_flx.dt_saldo,
dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,
dbo.opf_saldo_ctb_opc_flx.cd_classificacao,
dbo.opf_movimento_operacao.ds_tipo_transacao,
dbo.opf_header_operacao.ds_tipo_transacao ,
ds_status_operacao,
dbo.opf_header_operacao.ds_tipo_opcao ,
dbo.opf_header_operacao.id_empresa,
dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente
Thanks
Nilton Pinheiro
View 9 Replies
View Related
Feb 23, 2006
I'm importing a text field that is called advertiserTYPE. It will be one of four options in the parenthesis below:
advertiserTYPE (FSBO,BROKER,DEVELOPER,REALTOR) VarChar(50)
I need to be able to construct a statement that says something like:
If advertiserTYPE = FSBO
Then INSERT INTO [COLUMN1]
If advertiserTYPE = BROKER
Then INSERT INTO [COLUMN2
If advertiserTYPE = DEVELOPER
Then INSERT INTO [COLUMN3]
Am I on the right track??
View 8 Replies
View Related
Feb 21, 2007
Hello helpful people :)
I have this sproc.
ALTER PROCEDURE dbo.cis_UpdateCourseUserWithGrade
@Grade nvarchar,
@UaaStudentId nchar,
@CourseId int
AS
UPDATE cis_CourseUser
SET Grade =@Grade
WHERE UaaStudentId = @UaaStudentId
AND WHERE CourseID = @CourseId
RETURN
the ANE WHERE isn't much liked. How can I update the field with the grade where both of the conditions are true? I'm getting an invalid syntax near @UaaStudentId
Thanks.
View 4 Replies
View Related
Apr 29, 2008
I've seen lots of entries recommending the use of ISNULL in SQL WHERE clauses, e.g. in a search sproc where users can enter some or all parameters to search a table. Previously I would have used something like:SELECT * FROM MyTableWHERE (FName = @fname OR @fname IS NULL) AND(MName = @mname OR @mname IS NULL) AND(LName = @lname OR @lname IS NULL)So using the neat ISNULL syntax it could be updated to:SELECT * FROM MyTableWHERE (FName = ISNULL(@fname, FName)) AND(MName = ISNULL(@mname, MName)) AND(LName = ISNULL(@lname, LName))Having played around with this I stumbled upon a problem. If one of the fields, e.g. MName, is NULL then that clause will return false since MName = NULL isn't true and you have to use MName IS NULL. Did I miss all the caveats with using ISNULL in this way on fields that can contain NULL or have I missed something else?
View 4 Replies
View Related
May 19, 2008
So how do you do dynamic WHERE clauses with asp.net?
I have a QueryString parameter with a value that will look like this. |1| or |1||2| or |1||2||3| or etc. For each number in the value I want to add an EXISTS function to the WHERE clause. I have accomplished this in T-SQL. But I can't figure out how to bind asp.net control to it. It gives me 'Invalid syntax near the keyword 'ORDER'. I know this has something to do with .net not being able to exaluate the schema properly.
So I tried Linq to Sql. Well, so happens that my WHERE clause is pointing to the existance of records in another table which the default install of Linq to Sql doesn't seam to support. I came across this post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2979081&SiteID=1) that explains how to dynamically query across multiple tables. I tried this and got errors when trying to plug in the additional code. And this still does not let me dynamically add where clauses.
I tried the who Predicate thing and ran into the same problem where I can't reference the existance of values in foreign tables.
Here is my sproc. Can anyone tell me how to get this into a asp.net environment. I also do NOT want to auto-generate fields in my GridView.ALTER PROCEDURE dbo.Item_SelectByFilters @Item_Category_Id int = NULL, @ItemSpecificValueIdList varchar(100) = NULL, @debug bit = 0 AS DECLARE @sql nvarchar(4000), @paramlist nvarchar(4000) SELECT @sql = 'SELECT Item_Id, Number, Primary_Item_Category_Id, Secondary_Item_Category_Id, Engineering_Document_Id, CategoryIdList FROM Item WHERE (1 = 1)' IF @Item_Category_Id IS NOT NULL SELECT @sql = @sql + ' AND Item.CategoryIdList Like ''|'' + CONVERT(varchar, (@xItem_Category_Id) + ''|''' IF @ItemSpecificValueIdList IS NOT NULL BEGIN WHILE @ItemSpecificValueIdList <> '' BEGIN DECLARE @StartLocation int SELECT @StartLocation = CHARINDEX('-', @ItemSpecificValueIdList, 1) IF @StartLocation <> 0 BEGIN DECLARE @EndLocation int SELECT @EndLocation = CHARINDEX('|', @ItemSpecificValueIdList, @StartLocation + 1) IF @EndLocation <> 0 BEGIN DECLARE @ValueLength int SELECT @ValueLength = @EndLocation - @StartLocation - 1 DECLARE @ValueBetween varchar(10) SELECT @ValueBetween = SUBSTRING(@ItemSpecificValueIdList, @StartLocation + 1, @ValueLength) IF @ValueBetween IS NOT NULL SELECT @sql = @sql + ' AND EXISTS(SELECT Item_Specific_Value_Id FROM Item_Specific_Value WHERE Item_Id = Item.Item_Id AND Item_Specific_Value.Item_Specific_Value_Id = ' + @ValueBetween + ')' END END SET @ItemSpecificValueIdList = SUBSTRING(@ItemSpecificValueIdList, @EndLocation + 1, len(@ItemSpecificValueIdList) - @EndLocation) END END SELECT @sql = @sql + ' ORDER BY Item.Number' IF @debug = 1 PRINT @sql SELECT @paramlist = '@xItem_Category_Id int'
EXEC sp_executesql @sql, @paramlist, @Item_Category_Id
View 20 Replies
View Related
May 27, 2008
Here is another project that seams to be the same as my previous post but a little simpler. http://forums.asp.net/t/1263330.aspx
My Item_Category table is self referencing. I have a list of category Ids in a particular order representing the tree of categories from the current category to the top. For example: 1 - Fasteners, 4 - Screws, 12 - Sheet Metal Screws or the reverse.
I need to translate this so I can bind a cookie crumb type control to it. ie. Fasteners > Screws > Sheet Metal ScrewsI originally accomplished this like so;
Dim q = From ic In itemdc.Item_Category _Where valueId.Contains(ic.Item_Category_Id) _ Select Id = ic.Item_Category_Id, ic.Name But found that this does not order them properly. Remember that the list of Ids is in a particular order. I need a query to result to the below. I was using a horizontal DataList control as the cookie crumb control. Again, I am trying to do this with Linq to Sql without using Stored Procedures even though I am the DBA as well. Or is there some other better way to do this?
Id
Name
Sort
1
Fastener
1
4
Screws
2
12
Sheet Metal Screws
3
Finally, as I mentioned in my previous post, I accomplished this concept with a stored procedure but then the asp.net controls could not recognize the schema, hence I could not bind the controls to the data source control.
View 2 Replies
View Related
Oct 28, 2014
I have an existing SPROC which works the way it should do and was difficult to construct. But now I need to add 1 more condition and hopefully that should be it however I am struggling how to do this. Here is a snip of the existing SPROC.
quote:
IF @columnName = 'Rating - Fire' OR @columnName = 'Rating - PPE' OR @columnName = 'Rating - Reactivity' OR @columnName = 'Rating - Health'
BEGIN
SELECT @totalRecords = (SELECT COUNT(p.[SID]) FROM S_Summary p INNER JOIN S_Detail detail ON detail.SID = p.SID
WHERE
CASE @columnName
[code]...
so this works fine but now I need to add 1 more thing..if a new parameter is supplied (lets call it @stringBranch), then I want to join another table and also match the param value to a field in that table along with any existing WHERE conditions being applied to this:
quote:
WHERE (@columnName IS NULL AND @columnValue IS NULL)
OR
CASE @columnName
WHEN 'Rating - Fire' THEN detail.F
WHEN 'Rating - PPE' THEN detail.P
[code]...
View 9 Replies
View Related
Feb 23, 2006
I'm trying to use multiple where clauses but its not working. I want it to look something like this:
INSERT INTO [USCondex_Development].[dbo].[miamiheraldExceptions]([InvalidEmails], [InvalidAdPrintID], [InvalidPropertyStreetAddress], [InvalidPropertyPrice])
SELECT [AdvertiserEmail],[AdPrintId],[AdvertiserAddress], [PropertyPrice]
FROM [Development].[dbo].[table2]
WHERE advertiseremail is NULL
and WHERE adPrintID is NULL
and WHERE firstinsertdate is NOT NULL
and WHERE propertystreetaddress is NOT NULL
and WHERE propertyprice < 100
But it's not working.
View 7 Replies
View Related
Nov 28, 2007
I am trying to wrap my WHERE clause with an IF or a CASE but cannot seem to get it to work. This is what I am trying:
WHERE
CASE
WHEN EVENT_TYPE='d' THEN
(link_inc.incident_id = 10000005) AND (B.incident_id <> 10000005) AND link_rsn.link_rsn_sc = 'CHANGE' AND B.incident_id > 10000000
ELSE
(link_inc.incident_id = 10000005) AND (B.incident_id <> 10000005) AND link_rsn.link_rsn_sc = 'CHANGE' AND B.incident_id > 10000000 AND act_type.act_type_sc <> 'CLOSURE'
END
ORDER BY B.incident_id DESC, act_reg.act_reg_id DESC
Basically I want to run a different WHERE clause based on a value (EVENT_TYPE). The error message I am getting is:
Incorrect syntax near '='.
View 5 Replies
View Related
Jul 6, 2014
I am trying to add the results of both of these queries together:
The purpose of the first query is to find the number of nulls in the TimeZone column.
Query 1:
SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename
The purpose of the second query is to find results in the AAST, AST, etc timezones.
Query 2:
SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')
Note: both queries produce a whole number with no decimals. Ran individually both queries produce accurate results. However, what I would like is one query which produced a single INT by adding both results together. For example, if Query 1 results to 5 and query 2 results to 10, I would like to see a single result of 15 as the output.
What I came up with (from research) is:
SELECT ((SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename) + (SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST'))
I get a msq 102, level 15, state 1 error.
I also tried
SELECT ((SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename) + (SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')) as IVR_HI_n_AK_results
but I still get an error. For the exact details see:
[URL]
NOTE: the table in query 1 and query 2 are the same table. I am using T-SQL in SQL Server Management Studio 2008.
View 6 Replies
View Related
Apr 11, 2007
I have a table, basically consisting of products and their prices. I want to select some products, then sort them by price in ascending order BUT putting prices of zero at the bottom. (e.g. 5.99, 8.99, 10.99, 0.00, 0.00)I thought I'd be able to do something like:ORDER BY (price != 0), pricethinking that it would sort rows according to whether the condition was true or not, and then by price, but MSSQL doesn't seem to allow this. should this work, or is there another way around this? One solution would be to load the values into a table object and sort them using that, but I'd rather do all of this in SQL if possible, for speed.any suggestions?thanks!
View 1 Replies
View Related
Dec 4, 2007
If I have a table called "content_hits_tbl" and want to pull information, can't i write something like this:
SELECT COUNT(visitor_id) AS HITS, COUNT(DISTINCT visitor_id) AS VISITORS, COUNT(DISTINCT visitor_id) WHERE visit_type = 0 AS NEW, COUNT(DISTINCT visitor_id) WHERE visit_type = 1 AS RETURNING
FROM content_hits_tbl
Can't you have multiple WHERE clauses in the SELECT statement?
Any suggestions would be great. I have been wrestling with it and SQL queries arent my strong area ...
I'm using VS05 connecting to a SQL database.
Sincerely,
Tommy
View 3 Replies
View Related
Sep 18, 2014
Below is a script that count the amount of Void properties we had in a specific month.
SELECT COUNT(C.[Place Referance]) AS [April Total Voids]
FROM
(
SELECT DISTINCTTOP 100 PERCENT
HIST.[PLACE-REF] AS 'Place Referance'
[Code] ....
It tells me I have 53 total voids.
What I also want is a column next to this to say how many of those voids back in April are STILL Void.
So basically the WHERE clause would still be the same -
WHERE [Void Start Date YEAR] = '2014'
AND [Void Start Date MONTH] = '4'
but with the added -
AND HIST.[END-DATE] IS NULL
So ideally I'm after two columns with figures in them and then going forward I can then calculate other months as well.
View 5 Replies
View Related
May 2, 2008
hai,
i'm using a table in sql.the name of register. the table datas are
FirstName LastName UserName Pwd dob
krishna murthy ckm0006 asdfg 1985-04-01 00:00:00.000
krishna dfgd ckm0006 cxbcv 1985-05-01 00:00:00.000
raja fdd ddd ddd 1985-01-01 00:00:00.000
raja hgff fgrgf fgf 1985-02-01 00:00:00.000
i want the result as:[group by or distinct by FirstName && order by dob]
FirstName LastName UserName Pwd dob
krishna dfgd ckm0006 cxbcv 1985-05-01 00:00:00.000
raja hgff fgrgf fgf 1985-02-01 00:00:00.000
i tried many queries i'm getting error Like this
Column 'register.LastName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
can anyone resolve my problem...
View 5 Replies
View Related
Jan 14, 2008
Howdy folks, first time poster.
In a UDF, how is the best way to extend a query with additional clauses based on expressions? The user input here is used to refine the basic query by introducing additional clauses. Is there something like the following?
-- The basic query
SELECT column
FROM table
WHERE clause
-- Additional clause, only appended to query
-- if expression evaluates to true
IF @parameter <> default_value
BEGIN
AND additional_clause
END
Right now I'm using CASE like the following, but it necessarily makes the query longer. Is there a more efficient way?
-- The basic query
SELECT column
FROM table
WHERE clause
-- Additional clause, should only effect result
-- set when the parameter is not default_value
AND table.column =
CASE @parameter
WHEN default_value THEN
-- identity, table.column=table.column,
-- should have no effect other than just a long query
table.column
ELSE
@parameter
END
View 8 Replies
View Related
Jan 12, 2008
Hi
I'm not sure if I have stated my subject line correctly for what I want to achieve, but I will attempt to explain it below.
In addition to what I have in my script below, I also need to include the following clauses:
1. where TransPerPaySequence.FinancialYTDCode like '2007', and e.EmployeeStatusCode like 'CASUAL' and p.PositionGroupCode like 'AC', then instead of using the divisor of 72, it needs to be 35; and
2. where TransPerPaySequence.FinancialYTDCode like '2008', and e.EmployeeStatusCode like 'CASUAL' and p.PositionGroupCode like 'AC', then instead of using the divisor of 72 or 35, it needs to be 31.
I would really appreciate any assistance that can be provided.
Thanks
SELECT DISTINCT
pc.PositionClassificationCode, pc.Description AS positionclass, pg.PositionGroupCode, pg.Description AS positiongroup, p.Description AS position,
e.PreferredName + ' ' + e.LastName AS employeename, SUM(ha.Quantity)
/ ((CASE p2.PositionGroupCode WHEN 'AC' THEN 72 WHEN 'AL' THEN 75 WHEN 'EX' THEN 80 WHEN 'MG' THEN 80 WHEN 'SM' THEN 80 END) *
(SELECT COUNT(DISTINCT PaySequence) AS Expr1
FROM TransPerPaySequence
WHERE (PayPeriodCode LIKE 'EIT') AND (Closed = '1') AND (Description LIKE 'St%'))) AS FTE,
(SELECT COUNT(DISTINCT PaySequence) AS Expr1
FROM TransPerPaySequence AS TransPerPaySequence_1
WHERE (PayPeriodCode LIKE 'EIT') AND (Closed = '1') AND (Description LIKE 'St%')) AS payseq
FROM HistoricalAllowance AS ha LEFT OUTER JOIN
Position AS p ON ha.PositionCode = p.PositionCode LEFT OUTER JOIN
PositionGroup AS pg ON p.PositionGroupCode = pg.PositionGroupCode LEFT OUTER JOIN
PositionClassification AS pc ON p.PositionClassificationCode = pc.PositionClassificationCode LEFT OUTER JOIN
WAP ON ha.WAPCode = WAP.WAPCode LEFT OUTER JOIN
Employee AS e ON ha.EmployeeCode = e.EmployeeCode LEFT OUTER JOIN
Position AS p2 ON e.PositionCode = p2.PositionCode LEFT OUTER JOIN
TransPerPaySequence AS tpps ON ha.PaySequence = tpps.PaySequence
WHERE (e.EmployeeCode IN ('83', '739')) AND (ha.AllowanceCode IN ('005', '201', '203', '101')) AND (tpps.FinancialYTDCode LIKE '2007%')
GROUP BY pc.PositionClassificationCode, pg.PositionGroupCode, pc.Description, pg.Description, p.Description, e.PreferredName, e.LastName,
p2.PositionGroupCode
View 8 Replies
View Related
May 8, 2008
hi, i'm using Access 2007 and i'm trying to join two selects and create two new columns[complete and not complete] where 'x' denotes a hit was made. i will use this later for grouping. here is my code so far. thanks.
SELECT tblOutlookTask.TaskSubject, tblOutlookTask.PercentComplete, tblOutlookTask.ID
FROM tblOutlookTask
WHERE (((tblOutlookTask.PercentComplete)=100))
SELECT tblOutlookTask.TaskSubject, tblOutlookTask.PercentComplete, tblOutlookTask.IDFROM tblOutlookTask
WHERE (((tblOutlookTask.PercentComplete)<>100))
View 3 Replies
View Related
Feb 17, 2008
Hi, I hope some one can help me. I have a stored procedure (Microsoft SQL 2005 Express Edition) that I want users to be able to dynamically set the, group by, order by (@orderby) and where clause (@where). I have managed to get the group by to work but can't seem to get the where and order by to work. Here's my stored procedure. Any idea how this can be done? ALTER PROCEDURE [dbo].[sp_aggregate] -- Add the parameters for the stored procedure here @finfileid int, @phaseid int, @supplierid int, @measurementid int, @roleid int, @groupby int, @orderby int, @where int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT MAX(ProjectFinFileMonthItems.ProjFinFileMonthItemsMonthId) AS ProjFinFileMonthItemsMonthId, SUM(ProjectFinFileMonthItems.ProjFinFileMonthItemsValue * ProjectFinFileMonthItems.ProjFinFileMonthItemsRate * ProjectFinFileMonthItems.ProjFinFileMonthItemsAvail / 100) AS total, MAX(ProjectFinFileItems.ProjPhaseId) AS phaseid, MAX(ProjectFinFileMonthItems.ProjDeliveId) AS deliveid, MAX(ProjectFinFileMonthItems.SupplierId) AS supplierid, MAX(ProjectFinFileMonthItems.ProjFinFileItemsId) AS ProjFinFileItemsId, MAX(ProjectFinFileMonthItems.ProjFinFileMonthItemsId) AS ProjFinFileMonthItemsId, MAX(ProjectFinFileMonthItems.ProjDeliveId) AS ProjDeliveId, MAX(ProjectPhases.ProjectPhaseName) AS ProjectPhaseName, MAX(Suppliers.SupplierName) AS SupplierName, MAX(ProjectFinFileMonthItems.RoleId) AS RoleId, MAX(Measurements.MeasurementName) AS MeasurementName, MAX(ProjectFinFileMonthItems.MeasurementId) AS MeasurementId, MAX(ProjectFinFileMonthItems.FinDataTypeId) AS FinDataTypeId, MAX(FinDataTypes.FinDataTypeName) AS FinDataTypeName, max(ProjectFinFileItems.FinFileId) as finfileid FROM ProjectFinFileItems INNER JOIN ProjectFinFileMonthItems ON ProjectFinFileItems.ProjFinFileItemsId = ProjectFinFileMonthItems.ProjFinFileItemsId LEFT OUTER JOIN FinDataTypes ON ProjectFinFileMonthItems.FinDataTypeId = FinDataTypes.FinDataTypeId LEFT OUTER JOIN Measurements ON ProjectFinFileMonthItems.MeasurementId = Measurements.MeasurementId LEFT OUTER JOIN Roles ON ProjectFinFileMonthItems.RoleId = Roles.RoleId LEFT OUTER JOIN ProjectPhases ON ProjectFinFileItems.ProjPhaseId = ProjectPhases.ProjectPhaseId LEFT OUTER JOIN Suppliers ON ProjectFinFileMonthItems.SupplierId = Suppliers.SupplierId /*dynamic where clause needs to go here */ /*dynamic group by clause */ GROUP BY CASE when @groupby=1 then ProjectFinFileItems.projphaseid --phaseid when @groupby=2 then ProjectFinFileMonthItems.supplierid -- supplierid when @groupby=3 then ProjectFinFileMonthItems.measurementid -- measurment when @groupby=4 then ProjectFinFileMonthItems.roleid --role else ProjectFinFileMonthItems.ProjFinFileMonthItemsId END /*dynamic order clause needs to go here */ END cheers Mark :)
View 2 Replies
View Related
Jun 20, 2001
I have a web page which passes back parameters to a stored procedure.
From the web page the user selects different clauses for the 'where' criteria.
Based upon the number of clause items in the parameters sent back, a select statement is built and executed.
In the stored procedure I have many if statements to chose the correct sql statement.
As the no of clauses in the where statement can vary, it can become messy script.
Has anyone dealt with this scenario. What is the best strategy ?
A simple illustation of this is as follows
A statement with two clauses :-
Select * from Sales where
user = 'John' and country = 'England'
A statement with three clauses :-
Select * from Sales where
user = 'John' and country = 'England' and County = 'Staffordshire'
The stored procedure would except three parameters and would build a string based on the number of actual where clases sent back
View 2 Replies
View Related
Feb 26, 2004
Hi,
I have a complex SQL query against multiple tables that ideally would be best used as a stored procedure.
I know of and have read about creating named parameters and setting the parameters' values in ASP just before issuing an execute command via the Command object. What I'd like to do is assign the text of the filter and sort clauses to the parameters. Not the values, but the entire string, such as
"WHERE lastname LIKE 'A%' "
"ORDER BY lastname "
I need to assign the entire clause because the query may or may not use a particular clause.
I have experimented. However SQL treats the parameter as a literal string rather than part of the SQL query itself. Is there another technique that I may use to accomplish my goal?
Thanks in advance for your opinion, suggestion, criticism, etc.
--Tom.
View 2 Replies
View Related
Jun 18, 2014
I came across this structure today and haven't seen it before:
SELECT blablabla
FROM T1
FULL OUTER JOIN T2 ON
T1.Col1 = T2.Col1
AND T1.Col2 = T2.Col2 ON
T3.Col1 = T1.Col1
AND T3.Col2 = T1.Col2 ON
T4.Col1 = T1.Col1
AND T4.Col2 = T1.Col2
I can follow along until the second "ON".
View 9 Replies
View Related
Nov 28, 2007
Hello,
i've written the following query:
SELECT dbo.KALENDER.KALENDER_ID, dbo.KALENDER.JAHR_BEZ, dbo.KALENDER.JAHR_WERT, dbo.KALENDER.HALBJAHR_WERT,
dbo.KALENDER.HALBJAHR_BEZ1, dbo.KALENDER.HALBJAHR_BEZ2, dbo.KALENDER.QUARTAL_WERT, dbo.KALENDER.QUARTAL_BEZ1,
dbo.KALENDER.QUARTAL_BEZ2, dbo.KALENDER.MONAT_BEZ, dbo.KALENDER.MONAT_WERT, dbo.KALENDER.TAGE_IM_MONAT,
dbo.TAG.KALENDERWOCHE, dbo.TAG.WOCHENTAG, dbo.TAG.TAG, s.STUNDE_ID, s.DATUM_ZEIT
FROM dbo.KALENDER INNER JOIN
dbo.TAG ON dbo.KALENDER.KALENDER_ID = dbo.TAG.KALENDER_ID INNER JOIN
dbo.STUNDE AS s ON dbo.TAG.TAG_ID = s.TAG_ID
WHERE (SELECT MONTH(s.datum_zeit)) = ((SELECT MONTH(GETDATE()))-2)and
(SELECT year(s.datum_zeit)) = (SELECT year(GETDATE()))
order by s.stunde_id
when copying that query to the view editor and executing it, it trys to fix it somehow to:
SELECT TOP (100) PERCENT dbo.KALENDER.KALENDER_ID, dbo.KALENDER.JAHR_BEZ, dbo.KALENDER.JAHR_WERT, dbo.KALENDER.HALBJAHR_WERT,
dbo.KALENDER.HALBJAHR_BEZ1, dbo.KALENDER.HALBJAHR_BEZ2, dbo.KALENDER.QUARTAL_WERT, dbo.KALENDER.QUARTAL_BEZ1,
dbo.KALENDER.QUARTAL_BEZ2, dbo.KALENDER.MONAT_BEZ, dbo.KALENDER.MONAT_WERT, dbo.KALENDER.TAGE_IM_MONAT,
dbo.TAG.KALENDERWOCHE, dbo.TAG.WOCHENTAG, dbo.TAG.TAG, s.STUNDE_ID, s.DATUM_ZEIT
FROM dbo.KALENDER INNER JOIN
dbo.TAG ON dbo.KALENDER.KALENDER_ID = dbo.TAG.KALENDER_ID INNER JOIN
dbo.STUNDE AS s ON dbo.TAG.TAG_ID = s.TAG_ID
WHERE ((SELECT MONTH(s.datum_zeit) AS Expr1
FROM ) =
(SELECT MONTH(GETDATE()) AS Expr1) - 2) AND
((SELECT YEAR(s.datum_zeit) AS Expr1
FROM ) =
(SELECT YEAR(GETDATE()) AS Expr1))
ORDER BY s.STUNDE_ID
... but this causes syntax-errors. I don't understand why this query works fine in the query editor but then gets automatically "destroyed" by the view editor. Do i have to use more statements to get the working query to run inside a view?
Thanks alot for reading.
View 1 Replies
View Related
