Transact SQL :: Passing Value In Where Condition Loop Wise

Jun 1, 2015

I want to calculate year to month data for month wise . Ihave start range and end range table like below

Start range and end range table

Start dateEnd date
01-04-201401-11-2014
01-04-201401-12-2014
01-04-201401-01-2015
01-04-201401-02-2015

[Code] ....

Then I want to pass this value one by one in below table for get value month wise

Main table

Branchamountperiod
Branch110201103
Branch22201104
Branch33201401
Branch44201402
Branch58201403
Branch610201404

[Code] ....

The below query is for november month: (Apr to Nov)

select * from maintable
where period between '01-04-2014' and '01-11-2014'
then for December month : (Apr to Dec)

So I want to pass second row

select * from maintable
where period between '01-04-2014' and '01-12-2014'
.....
....
select * from maintable
where period between '01-04-2015' and '01-12-2015'

Like wise I want to get month wise data of YTM data.

My expected output is

201411
201412
201501
201502
201503
201504
201505

[Code] ....

View 10 Replies


ADVERTISEMENT

Transact SQL :: Split Date Range Into Monthly Wise And Loop Through Each Month To Perform Some Operation

Oct 20, 2015

Let's say if the date is 01/01/2015 till 01/01/2016

I want split these dates monthly format wise and then use them in variable in cursors to loop

For an example Monthly date should be 01/01/2015 and 01/31/2015 and use these two values in 2 variables and make a loop until it never ends whole date range which is 01/01/2016

View 2 Replies View Related

Transact SQL :: While Loop Does Not End When Condition Is No Longer True

Dec 1, 2015

I have a Stored Procedure, wherein I need to use a while loop. essentially the loop looks like this.

Set @Kount = 1
--TestScript
Select 'TempReqTable', * from @TempReq
WHILE Exists(Select * from @TempReq WHERE TempID = @Kount)
BEGIN
--Do stuff with values from table
Set @Kount += 1
END

The test script confirms that there is only one row in the @TempReq table.  However the loop never stops running and @Kount keeps being incremented and incremented until I manually stop the execution of the SP.

Is there some rule that I am not aware of that does not allow the use of an Exists statement in the condition of a While loop?

View 12 Replies View Related

Transact SQL :: How To Add Condition In Where Clause According To Another Condition

Oct 17, 2015

I write a query to get some data as the following. but i need when a user check specified condition a query parameter change to specified condition :

create proc proc_ReservationDetails
(
@status nvarchar(50) = null
)
as
begin
select reservationId, reservationStatus, reservationDesc

[Code] .....

View 3 Replies View Related

Transact SQL :: Time Column With Order Wise

Nov 17, 2015

I have a table like below

CREATE table #TempTable (ID integer,CTime time(7),CType Varchar(1))
insert into #TempTable VALUES (1001,'16:50:05.0000000','I')
insert into #TempTable VALUES (1001,'13:27:49.0000000','O')
insert into #TempTable VALUES (1001,'20:44:00.0000000','O')
insert into #TempTable VALUES (1001,'21:12:00.0000000','O')

I need result like below screen shot 

here 'I' stands for In,and 'O' for Out, in this example first In time not available

View 7 Replies View Related

Transact SQL :: Count Of Records By Month Wise

Oct 7, 2015

I am trying to get count of  records by month wise when they select year .It was showing  the out put correctly but its showing months arer in numbers,but I want to display Jan,Feb ...

SELECT DISTINCT Standard, COUNT(Standard) AS Total,month(ReportDate) Month
FROM CPTable where
year(ReportDate) = '2015'
GROUP BY Standard, Standard ,
month(ReportDate)

Output

Standard Total Month
NULL 0 1 //Jan
NULL 0 2 //Feb
NULL 0 3
NULL 0 4
NULL 0 5
OSHA 18001, 1 5
NULL 0 6
NULL 07
NULL 08
OSHA 18001,158
TL 9000,18
NULL 09
OSHA 18001,139

View 4 Replies View Related

Transact SQL :: Cumulative Values Quarter And Half Yearly Wise

Nov 23, 2015

Having table like below. Here want to cumulative the values quarter and half yearly wise...

declare @table table 
(month varchar(10),
value int)
insert into @table values('apr' ,100 )
insert into @table values('may' ,200 )
insert into @table values('jun' ,300 )

[Code] ....

Like wise the data should added...

View 3 Replies View Related

Transact SQL :: Query For Month Wise Running Totals Of Sales Amount?

Nov 28, 2012

I have a sales tables which looks as below.

DEPARTMENT
Barnd_Name
Item_Group
     S_DATE
          S_AMOUNT
Administration
IBM

[code]....

Now i need Month Wise Running Totals.but i should check the following group as show below i that order

1) DEPARTMENT
1) Brand
3) Item Group
4) Month

View 12 Replies View Related

Transact SQL :: How To Display Data Party Name And Time Interval Wise In Server

Sep 9, 2015

i want to show data Party Name and Time interval wise. here is my table from where i will fetch data. so pasting table data here.

Call start Call duration Ring duration Direction Is_Internal Continuation Party1Name Park_Time
------------------------- ---------------- ------------- --------- ----------- ------------ --------------- -----------
2015/06/08 08:06:08 00:02:28 2 I 0 0 Emily 0
2015/06/08 08:16:38 00:00:21 0 I 0 1 Line 2.0 0
2015/06/08 08:16:38 00:04:13 5 I 0 0 Jen 0

[code]...

now i am not being able to cross join this CTE with my table to get data party name wise and time interval wise. say for if no data exist for a specific time interval then it will show 0 but each party name should repeat for time interval 9:00:00 - 9:30:00 upto 17:30:00. i like to add what filter need to apply to get data for incoming, outgoing, call transfer and miss call.

For Incoming data calculation
where direction='I' and
Is_Internal=0 and continuation=0 and
RIGHT(convert(varchar,[call duration]),8)<> '00:00:00'
For outgoing data calculation

[code]...

View 3 Replies View Related

For Loop Container Does Not Check Condition First Time

Feb 28, 2007

Hi,
It seems like For Loop Container works like do-while loop in C++. I have set its EvalExpresstion to @[User::SyncStats]==0 , I have an Execute SQL Task before this container that sets the variable to 1 but Container still executes atleast one time.


Anybody else been through this ?

View 5 Replies View Related

Loop By Passing Variable Into Query

Nov 2, 2007



Hi all,

I'm have created a data flow that uses an OLEDB source with a SQL Query. In the WHERE statement of this query is a condition for the storecode. I want to figure out how to create a loop that will cycle through a list of storecodes using a variable which is passed to the dataflow in turn to the OLEDB source's query and runs through the process for each store.

The reason i'm using a loop is because there are about 15 million records that are merge joined with 15 million others which is causing a huge performance problem. I'm hoping that by looping the process with one store at a time it should be faster. Any ideas would be greatly appreciated.

View 3 Replies View Related

Reporting Services :: Using Exists Condition In WHERE Clause While Passing Values From SSRS Parameter

Sep 10, 2015

Table : incident
----------------
incident_id usr_id item_id Inc_Date
10059926 191 61006 8-22-2015
10054444 222 3232 6-7-2015

Table: act_reg
--------------
act_reg_id act_type_id incident_id usr_id act_type_sc
454244 1 10059926 191 ASSIGN
471938 115 10059926 191 TRAVEL TIME
473379 40 10059926 191 FOLLOW UP
477652 115 10059926 191 TRAVEL TIME
489091 504 10059926 191 ADD_ATTCHMNTS
477653 504 10054444 222 ADD_ATTCHMNTSParameter: @attach (value=1, Label=Yes & Value=0, Label=No)

Result (While I am selecting 'Yes' in dropdown)
----------------------------------------------
incident_id usr_id item_id
10059926 191 61006
10054444 222 3232

SELECT incident.incident_id,incident.usr_id,incident.item_id
FROM incident
where exists (How i can write query here to check the act_type_sc=ADD_ATTCHMNTS is exists)

View 7 Replies View Related

Is There A Way To Loop Over Report Parameters, And Format Them Before Passing Them Onto The Query

Nov 13, 2007

Good morning all,


I have a report which is getting its parameters from an ASP.net page. My ASP developer wants to send in simple values, such as the list 1,2,3,4 for a parameter. However my report needs that list to look like [CD RSRC].[RSRC].&[1], [CD RSRC].[RSRC].&[2], [CD RSRC].[RSRC].&[3], [CD RSRC].[RSRC].&[4].


Is there any way, on the report services side, to capture an incoming report parameter, parse it, loop over the parsed values and format them?


I don't think there is, but I wanted to check before I go back to the developer and tell him he has to send in tuple lists.


Thanks,
Kathryn

View 1 Replies View Related

Transact SQL :: How To Delete Row With Condition

Aug 6, 2015

create table target
(Taid int, studenid int, std int,div int,year int ,ActivityId int)
insert into target values(1,1,1,2015,1),
(1,1,1,2015,2),(1,1,1,2015,3),(1,1,2,2015,1)
,(1,1,2,2015,4) ,(1,1,2,2015,3);

create table activity(ActivityId int,Activity nvarchar)
insert into activity values (1,'sport')
,(2,'swim')
,(3,'read')
,(4,'dace');

I want to edit(update,delete,insert) target table activity from front end for that I took grid view and that grid all activity come of single student studenid(unique key for each student) that you see design below like this 

that activity present in target table that come as check in check and that activity not select student from activity activity table that come unchecked

I want to update above information means  unchecked alredy check data should delete and new check data add , already check data update

I have use merge statement but problem is that at single time only one row execute that is check because  data come from data gridview.

View 5 Replies View Related

Transact SQL :: Using Isnull In Where Condition

Jul 10, 2015

I am in assumption that we cannot do NULL value comparison in where clause directly (results will not be proper) we have to use ISNULL. Is my understanding correct? Is using ISNULL efficient when we have to this operation for large volume?

View 5 Replies View Related

Transact SQL :: How To Apply Condition

Jul 24, 2015

I have an order with the time 08/03/2015 7:30 08/03/2015 10:30 ..I have availability with the time 08/03/2015 07:00:00 to 08/03/2015 19:30:00...Here the availability falls for 3 hours only..My requirement is that For order if availability< 4hours i don't want to show employee.How to apply the condition.

View 7 Replies View Related

Transact SQL :: Select Column On Condition

Jul 4, 2015

I have a table called names  (firstname, lastname, number) then i have a DECLARED variable called  @displaynum...If @displaynum id true   my select query should select the number column.  if the variable is false , only the first two columns are selected.

e.g   @displaynum = true                                          
@displaynum = false
Firstname  | Lastname | number                               Firstname  | Lastnam

blah           |   blah        |   12345                             
blah           |  blah

i am not sure if this is possible as a case still selects the column but just changes the values.

View 12 Replies View Related

Transact SQL :: Updating Only One Row Based On Where Condition

Sep 16, 2008

How can I update only one row doing an update query?
 
For example:
 
update from table1
set category = 'C'
where country = 'Brazil'
 
One want to update only one row with country = 'brazil'

View 5 Replies View Related

Transact SQL :: Where Condition - If No Input Value Select All Rows

Apr 27, 2015

I  have table with filed

EmpProject -Table
Empno, Name, Project NO, cost center

I need to restrict the results by project No by user input. In case if user did not provide any value for the projectNO, then need to fetch all rows.

Select empno, name, projecno, cost_center from empproject where projectno=nvl(:pno, :deptno)

View 6 Replies View Related

Transact SQL :: Get Records Based On Condition In Server

Nov 22, 2015

I have a question about SQL Server.

Table patient:

create table patient (pn int,code int,date date,doctorcode int)
insert into patient (pn,code,date,doctorcode)
values
(1,10,'2015-02-19','100),
(1,10,'2015-02-19','101),
(1,10,'2015-02-19','102),

[Code] ...

Table Patientref:

create table patientref
(pn int,code int, sdate date,edate date,status int)
insert into patientref(pn,code,sdate,edate,status)
values
(1,10,'2015-02-13','2015-02-19',1),
(1,10,'2015-02-19','2015-03-24',2),

[Code] ...

Here we need consider patient dates that fall between sdate and edate of the patientrefs table, and then we need to consider the highest status values in order (for example, the highest values in order - 2 is first highest, 4 is second highest, 3 is third highest, and 1 is fourth highest value)

If the date falls between multiple different sdate and edate with the same status values, then we need to consider the latest sdate value and from that entire record we need to extract that value.

Examples: patient

pn |  code  |  date      |   doctorcode
2  |  10    |2015-02-12  |   101
2  |  10    |2015-02-13  |   102
2  |  10    |2015-02-14  |   103

Table : Patientref:

pn |  code  |  sdate      |   edate      | Status
2  |  10    |2015-02-08   |   2015-02-19 |  4
2  |  10    |2015-02-09   |   2015-02-19 |  2
2  |  10    |2015-02-10   |   2015-02-19 |  2
2  |  10    |2015-02-11   |   2015-02-18 |  1

Here, pn=2 values have dates which fall between sdate and edate of patientref table. Then we give highest values status is 2, and status 2 values have two records, then we go for max sdate(latest sdate). Then this pn=2 latest sdates is 2015-02-10 and we need to retrieve the corresponding edate and status values.

pn = 4donot have sdate and edate and status values dut not fall conditon 

Based on this, the desired output is below:

pn |  code  |  date      |   doctorcode | sdate     |edate      |status
1  |  10    |2015-02-19  |   100        |2015-02-19 |2015-03-24 | 2
1  |  10    |2015-02-19  |   101        |2015-02-19 |2015-03-24 | 2
1  |  10    |2015-02-19  |   102        |2015-02-19 |2015-03-24 | 2
2  |  10    |2015-02-12  |   101        |2015-02-10 |2015-02-19 | 2

[Code] ...

I tried it like this:

select p.pn,p.code,p.[date],p.doctorcode,pr.sdate,pr.edate,pr.[status] from patient p
 outer apply (select top 1 pr.pn,pr.code,pr.sdate,pr.edate,pr.[status] from patientref pr 
where pr.pn=p.pn and pr.code=p.code and p.date between pr.sdate and pr.edate
 order by case when pr.status=2 
then 1 when pr.status=4 then 2
 when pr.status=3 then 3 
when pr.status=1 then 4 end ,pr.sdate 
 )pr

but this query not given expected result.here when dos not fall between sdate and edate  that records not given in the above query. I required that records also.if not fall b/w condition then we need retrive that records empty values for that records.

View 7 Replies View Related

Transact SQL :: Include A Condition In Store Proc

Sep 29, 2015

I have an existing store proc with insert and update statements, I want to add a condition at the top of the proc with conditions like;

declare @dayofweek int
set datefirst 1
select @dayofweek = datepart(dw,getdate())
--select @dayofweek
if @dayofweek = 1

[Code] ...

If the condition meets with either of the above 2 conditions, than have to run the actual store proc.

My actual Storeproc in which I need to incorp the above conditions is:

CREATE PROCEDURE [dbo].[Load_Product]
@FileDate date = NULL
AS
BEGIN
DECLARE @EventText varchar(500),
@Rows int = 0,
@RowsTotal int = 0,

[Code] .....

View 12 Replies View Related

Transact SQL :: Condition Failed Rows Count

Jul 3, 2015

I am using SQL Server 2008.Each stock item will have default 4 document type (1, 5, 6, 7) and each will have 3 zone's (1, 2, 3) to qualify. Each zone will be updated to 1 for that document type if the item successfully pass through it. If all zone are NULL means no transaction. How to retrieve only the failed rows which means not all zone are 1 or NULL.In the image GJ-00064 has one row failed. So how to get the count of failed rows for each item

Expected result:

Uniid <-> Stockcode <-> FailedRows
1670 <-> GJ-00064 <-> 1

View 8 Replies View Related

Transact SQL :: How To GROUP BY And Get Aggregate Where A Certain Condition Is True

Jul 16, 2015

I am using SQL 2005.  I have some data from an old application that did not follow the rules for normalization.  The table is for Invoices, and the table allows for 13 purchase items per record.  So in each row of my table I have a non-unique integer field itemID, itemID1, itemID2 ... itemID12.  For each itemID I also have "lbs_total" and "line_total" (which is price * lbs_total) - so itemID, lbs_total, line_total ... itemID1, lbs_total1, line_total1 ... etc.  It's a mess, I know.Each row has a unique Customer Number ("cno") and an Invoice Date ("inv_date").  My proc needs to allow for params for the item number, and a start date and end date for BETWEEN on the inv_date.I also need to get the aggregate for the lbs_total and the line_total.

View 15 Replies View Related

Transact SQL :: How To Use Case Statement In Select Condition

Nov 6, 2015

below is my original query

select Value = count(*) from dbo.test 

I have 20 rows in dbo.test so i will get 20 as my output, now i need to write a case statement here such that when count(*) = 0 then it should display text filed 'NO Data' else it should display the count.

View 5 Replies View Related

Transact SQL :: Get Only Those Distinct Records Which Satisfied Condition

Jun 3, 2015

I need to write a query on a table..

table defination .. table Client_Master
file_id int identity(1,1) primary key 
, client_id int
,LOAN_SANCTION_DATE datetime
,is_reject bit

has Data ..

FILE_ID CLIENT_ID
LOAN_SANCTION_DATE IS_REJECT
21139 22784
2014-02-03 00:00:00.000 0
21141 22784
2014-02-03 00:00:00.000 NULL
20869 22784
2014-02-03 00:00:00.000 1
20870 22784
2014-02-03 00:00:00.000 0
21571 22784
2014-02-03 00:00:00.000 1
66056 22784
2014-02-03 00:00:00.000 1

Above has 6 files entries for client id 22784 and LOAN_SANCTION_DATE 2014-02-03  out of which 3 are rejected ..

Now , i want to write a query to select those distinct client_id , LOAN_SANCTION_DATE  from Client_Master where all files has been rejected ..

means by grouping client ID and LOAN_SANCTION_DATE all the files are rejected ..

I have wrote as below .. got the result but not satisfy with the query

SELECT DISTINCT CLIENT_ID,LOAN_SANCTION_DATE,COUNT(FILE_ID) AS No_Of_Files
,COUNT(DISTINCT CASE WHEN IS_REJECT=1 THEN FILE_ID END )AS No_Of_Rejected
FROM  dbo.FILE_MASTER 
GROUP BY CLIENT_ID ,LOAN_SANCTION_DATE
HAVING
COUNT(FILE_ID)=COUNT(DISTINCT CASE WHEN IS_REJECT=1 THEN FILE_ID END )

View 4 Replies View Related

Transact SQL :: Selecting First Row After Date Matching Condition?

Sep 22, 2015

I've two audit tables, AUDIT_ORDERS and AUDIT_ORDER_LINES.

The AUDIT_ORDERS has these columns: AUDIT_ID, ORDER_ID, AUDIT_DATE and other ones.

The AUDIT_ORDER_LINES has these columns: AUDIT_ID, ORDER_ID, ORDER_LINE_ID, AUDIT_DATE and other ones.

I need to join these two tables in order to select for each order line row the first order having the related audit date lower than or equal to the audit date of the related order line.

I don't want to use the TOP 1 clause or a subquery. I think to complete a such statement:

SELECT OL.Order_Line_ID, O.Order_ID, OL.Audit_Date, O.Audit_Date
FROM AUDIT_ORDER_LINES as OL INNER JOIN AUDIT_ORDERS as O
on OL.Order_ID = O.Order_ID and O.Audit_Date <= OL.Audit_Date ...

I'd like to get the first row of the Audit_Orders with audit_date <= of the audit_date of the Audit_Order_Lines table by using the join clause.

View 9 Replies View Related

Transact SQL :: How To Use Multiple Values In Where Condition In Query

Jun 17, 2015

I have a query where i would calculate the counts of three Servers 

declare @Managed float
declare @Active float
DECLARE @Values varchar(1000)
SET @Values = 'WE,EE,CO'
select @Managed = count(Name0) from v_R_System where Operating_System_Name_and0 like '%Workstation%' 

[Code] ...

Here i need output like below

Workgroup Managed Active
'WE' 255 ,400
'EE' 300 ,450
'CO' 155, 600

So how to use these three values in the where condition when i use the where clause i have put in condition it will give me the subquery returns more than one value,so how should i use this scenario to accomplish this output?

View 3 Replies View Related

Integration Services :: SSIS Foreach Loop Container Passing Variables To Store Procedure

Sep 8, 2015

I am having a challenge to pass the filename variable from SSIS Foreach Loop Container to SQL store procedure.  I have an "Execute SQL Task" inside "Foreach Loop Container" which will receive the input filename parameter passed by the Foreach Loop Container.  The store procedure command line is defined as "EXEC sp_mySQLStoreProcedure  ?" within the "Execute SQL Task".  The Foreach loop container will gather the filenames from File System then put it in the filename variable one by one.  I would like to pass the filename variable as the input parameter to the sp_mySQLStoreProcedure in the "Execute SQL Task".  How can I connect the variable and the store procedure so that it will process all the input files gathered by the Foreach Loop Container?

View 4 Replies View Related

Transact SQL :: Insert Missing Record Based On A Condition

Sep 29, 2015

How do I get the below scenario:

EmpID DepID
12 2
17 3
17 2
13 3

Every Employee should be in Department 2 and 3 (as example EmpID = 17 has DepID 2 and 3 from above table). But when any of the employees either exists only in any one department (as EmpID = 12 has only DepID = 2), then a new row should be added to the table for that employee with that missing DepID.

Desired Output:

EmpID DepID
12 2
17 3
17 2
13 3
12 3
13 2

View 5 Replies View Related

Transact SQL :: Exclude A Distinct Records From Select When One Condition Is True?

May 28, 2015

I have customers named Alex (Cid=1), Bob (Cid=2), and Carrie (Cid=3) in a table customer.

Cid
First_Name
1
Alex
2
Bob
3
Carrie

I have products name Gin (Pid=1), Scotch (Pid=2) and Vodka (Pid=3) in a table products.

Pid
Product_Name
1
Gin
2
Scotch
3
Vodka

And I have a table that holds purchase called Customer_Purchases that contain the following records:

Cid
Pid
1
1
1
2
2
1
2
3
3
2

I would like to make a marketing list for all customers that purchased Gin or Scotch but exclude customers that purchased Vodka. The result I am looking for would return only 2 records: Cid’s 1 (Alex) and 3 (Carrie) but not 2 (because Bob bought Vodka).

I know how to make a SELECT DISTINCT statement but as soon as I include Pid=2 This clearly doesn’t work :

SELECT DISTINCT Pid, Cid
FROM           
Customer_Purchases
WHERE        (Cid = 1) OR
(Cid = 3) OR
(Cid <> 2)

View 3 Replies View Related

PL-SQL LOOP Equivilant In TRANSACT-SQL

Jul 20, 2005

Hi,I need to convert this anonymous pl-sql block into transact-sql to runin Microsoft SQL Server Query Analyser.Does anyone know what the conversion syntax would be?I know that to declare variables to use the @.PRINT for printing the output.No idea for the LOOP.Any suggestions greatly appreciated.Many thanks.Thiko!____________________________________________SET SERVEROUTPUT ONDECLARErecordcount NUMBER;BEGINDBMS_OUTPUT.ENABLE(5000000); -- Sets buffer size.recordcount := 0;LOOPINSERT INTO RecoverTest VALUES (SYSDATE - (recordcount / 4));COMMIT; -- frees up rollback segment;recordcount := recordcount + 1;DBMS_OUTPUT.PUT_LINE('INSERT: ' || recordcount || ' - 45000 ');EXIT WHEN recordcount > 45000;END LOOP;END;/

View 1 Replies View Related

Transact SQL :: Limit A Query Results When All Of Line Items Under Group Meet Certain Condition

Oct 1, 2015

I have a query that returns the data about test cases.  Each test case can have multiple bugs associated to it.  I would like a query that only returns the test cases that have all their associated bugs status = closed.For instance here is a sample of my data

TestCaseID TestCaseDescription  BugID BugStatus
1                TestCase1                       1      Closed
2                TestCase1                       2      Open
3                TestCase2                      11     Closed
4                TestCase2                      12     Closed
5                TestCase2                      13     Closed

How can I limit this to only return TestCase2 data since all of that test case's bugs have a status of closed.

View 3 Replies View Related

Transact SQL :: Pass Filenames To A Loop

Aug 27, 2015

I’m working on building a file list from a directory I have and wish to loop through the filenames. I’ve worked out I can get the list using

EXEC
xp_dirtree @path, 10, 1

I’m just not sure out I pass this to my loop 1 element at a time.

E.g. Let’s say there are 10 files in my folder, I wish to loop through each name using my @Filename parameter

DECLARE @Path
varchar(50), @FileName
varchar(20)
SET @Path
= 'C:myFiles'
EXEC
xp_dirtree @path, 10, 1

[Code] ....

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved