Search results for query: *

Correction. The second field should title should be 'RE Count'. Same name for 2 fields are not possible. SELECT Product, Count(CASE WHEN LEFT(a.JobNo,2) = 'DA' THEN 1 else 0 END) AS 'DA Count', Count(CASE WHEN LEFT(a.JobNo,2) = 'RE' THEN 1 else 0 END) AS 'RE Count' FROM tblLift a LEFT JOIN...

Try this code. I haven't tested it. SELECT Product, Count(CASE WHEN LEFT(a.JobNo,2) = 'DA' THEN 1 else 0 END) AS 'DA Count', Count(CASE WHEN LEFT(a.JobNo,2) = 'RE' THEN 1 else 0 END) AS 'DA Count' FROM tblLift a LEFT JOIN tblJobMethods b ON a.JobNo = b.JobNo GROUP BY a.Product George

You may want to look at INFORMATION_SCHEMA.VIEW_COLUMN_USAGE select * from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

Try the below code. Note:If 10 contineous blank cells will stop the execution. Sub Button1_Click() mRow = 1 nCnt = 0 While True If Trim(Sheet1.Cells(mRow, 1)) = "" Then Sheet1.Cells(mRow, 1).Delete nCnt = nCnt + 1 If nCnt = 10 Then 'If 10 contineous blank cells...

Larry, Why don't you try the inner query alone specified by Ryan and see the output matches your requirement. The solution provided by Ryan will returns 10 as total count and 3 as offsite count. As long as discovered_offsite_date used in the subquery, the DISTINCT operator will check that...

You can achieve this by Dynamic SQL, here is one small sample. Filter condition applied on String field declare @MyVar varchar(100) declare @newvar varchar(1500) set @MyVar = '~155A06420~,~151A06420~,~515A06607~' set @newvar = 'select * from MyTable where MyCol in(' +...

This might help you. Instead of msgbox you can assign it to a variable and use it. msgbox (DateAdd("d",15,DTPicker1.Value))

The below code might give you some idea. select * from #test Select dateadd(day,A.number, T.fDate ) From #Test As T Inner Join ( Select Number From master..spt_values Where Type = 'P' ) As A On dateadd(day,A.number, T.fDate ) Between...

I have some sql script(complex), which will take too much time because of the data. It has to run on a daily basis. I have scheduled this for every morning,at the same time some other sql scripts are also execute in the same server. So my sql script timed out mostly. It will run some days...

My mistake, I pasted wrong code. Updated code is given below. SELECT @@rowcount as TotalCount,HCID FROM ( SELECT HCID FROM prod_SPBILL_SSA_Invoice_Entry WHERE Status <> 'G' and (CancelledStatus ='N' or CancelledStatus is NULL) UNION SELECT...

If the exe is calling from a DOS BATCH file, you could achive it by giving the following line of code just before the EXE call. mode con cols=80 lines=24

I have a question on the scope of @@ROWCOUNT. I have a Select statement used in my Stored Procedure, which is given below. I would like to know whether it will return the desired results all the time or any possibility of showing wrong result on multi user environment. About my requirement...

Small correction in the code for fixing the ID field. It should be like running sequence number. DECLARE @Test TABLE (Id int, Value int) INSERT @Test VALUES (1, 2) INSERT @Test VALUES (2, 4) INSERT @Test VALUES (4, 5) SELECT count(ISNULL(Tbl1.id,0)) AS id, SUM(ISNULL(Tbl1.Value,0))...

if the field in your table is datetime , then there is no need to convert to dd/mm/yyyy format. You can directly use '20040401' in your filter condition.

More over, need to qualify ownername before the function. for example - dbo.fMyFunction

It is because you have used set QUOTED_IDENTIFIER ON statement in your code.

Select Top 1 Store, count(Item) Item, min(avgscore) avgscore from Stock group by store Order by Item Desc, avgscore Asc

update TestTable set Amount=Amount/1000

if min of ORDERNO for each JOBNO always same then better use code suggested by PatriciaObreja for performance.

One simple way is given below. Didn't checked the performance. select b.jobno, a.value from testing a inner join (select min(orderno) orderno, jobno from testing b group by jobno) b on a.orderno=b.orderno and a.jobno=b.jobno