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)
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
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.
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.
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)
I am using stored procedure to load gridview but problem is that i am not getting all rows from first table[ Subject] on applying conditions on second table[ Faculty_Subject table] ,as you can see below if i apply condition :-
Faculty_Subject.Class_Id=@Class_Id
Then i don't get all subjects from subject table, how this can be achieved.
Sql Code:-
GO
ALTER Proc [dbo].[SP_Get_Subjects_Faculty_Details]
@Class_Id int
AS BEGIN
[code] ....
create function quantprice
( @itemnmbr varchar(50), @startdate datetime, @enddate datetime)
RETURNS TABLE
AS
RETURN
(Select distinct t.itemnmbr,t.totalprice as totalcost
from
(
select distinct
vs.itemnmbr, sum(vs.totalsumprce) as totalprice
from vwquantityprice vs
Where vs.itemnmbr = @itemnmbr and (vs.docdate between @startdate and @enddate)
group by vs.itemnmbr
) as t
)
select * from quantityprice('06-5840','4/1/2007','4/1/2008')
well, guys, i have this function and obviously i will get one row from this in output...with that particular itemnumber but i want multi-itemnmbrs some times in output...some times 2..soemtimes 3...
example,
select * from quantityprice('06-5840,ab-4581,0a-1458,45-0945','4/1/2007','4/1/2008')
can you tell me what condition it will come to get this output..
like in where itemnmbr in('06-5840,ab-4581,0a-1458,45-0945')
and in starting input v ariable @itemnmbr..
create function quantprice
( @itemnmbr varchar(50)..
don't know what condition it will come and where i have to put condition in where clause or in input variable..
any help would be appreciated..thanks a lot!! guys plz reply.
I have a SELECT statement with multiple JOINs. I want to number the rows returned by this SELECT statement. What's the easiest way to do this?
My current SELECT statement returns this:
ProjectId -- TaskId -- TaskName
123 - 111 -- Do something
123 - 222 -- Do something else
123 - 333 -- Do one more thing
I want to return this:
ProjectId -- TaskId -- TaskName -- Sequence
123 - 111 -- Do something -- 1
123 - 222 -- Do something else -- 2
123 - 333 -- Do one more thing -- 3
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] .....
I want to write an Query for a report that has 7 parameters. I want to allow Null for these parameters but at the same time in the Query i want to put an AND condition for all the parameters. I cannot put an OR condition.
For Example;
If i have a report with 2 parameters then the script will have 2* 2 If conditions
something like:-
if( @parameter1 is not Null and @parameter is not Null)
select A, B, C
From Table 1
Where parameter1 = @parameter1 and parameter2 = @prameter2
if( @parameter1 is not Null AND @parameter2 is Null)
select A, B, C
From Table 1
Where parameter1 = @parameter1
If( @parameter1 is Null AND @parameter2 is Not Null)
select A, B, C
From Table 1
Where parameter2 = @parameter2
If( @parameter1 is Null AND @parameter2 is Null)
select A, B, C
From Table 1
For 7 parameter there will be way too many If conditions. Is there an better approch to write a Query for such a situation?
I am using SQL 2014 RTM (may be it's time to upgrade).
I have the following view:
create view [dbo].[SiriusV_Max4SaleList]
as
select m.id as Max4SaleId,
mt.[Description] as [TypeDescription],
CAST(m.[type] as tinyint) as [Type],
m.start_time as [StartTime],
m.end_time as [EndTime],
[Code] ....
I am thinking I may want to remove CAST for department, category, item later on as I don't really care if these columns would be defined as key for my EF model, but I do want to search by these columns. Anyway, this is my current view.
I executed the following select statement once
select * FROM dbo.siriusv_max4saleList where department like 's%' or category like 's%' or item like 's%'
And I believe I got 29 rows initially. However, when I execute this statement now I'm getting just 13 rows. If I execute just the department like 's%' I am getting 0 rows although I can see in the first result a row where department has s in in.
I guess I keep it here since I've created the message already but now I figured out why I am not getting the expected result. I used the condition like 's%' and not like '%s%' which application is doing.
I am using SQL SERVER 2008R2, not Denali, so I cannot use OFFSET FETCH Clause.
In my stored procedure, I am doing a SELECT INTO #tblTemp FROM... Working fine. This resultset is going to be used in an SSIS package which will generate a pipe-delimited .txt file... Working fine.
For recoverability sake, I am trying to throttle back on the commit chunks to 1000 rows per commit until there are no more rows. I am trying to avoid large rollbacks.
Q: Am I supposed to handle the transactions (begin/commit/rollback/end trans) when the records are being inserted into the temp table? Or when they are being selected form the temp table?
Q: Or can I handle this in my SSIS package for a flat file destination? I don't see option for a flat file destination like I do for an OLE DB Destination (like Rows per batch, Maximum insert commit size).
Below. I have also pasted the current result of this query and the desired result.
Query can be updated to get the desired result as given below?
Query:
Select c.OTH_PAYER_ID, c.PAID_DATE, f.GROUP_CODE, f.REASON_CODE, f.ADJUSTMENT_AMOUNT
From MMIT_CLAIM_ITEM b, mmit_tpl c , mmit_attachment_link d, MMIT_TPL_GROUP_RSN_ADJ f
where b.CLAIM_ICN_NU = d.CLAIM_ICN and b.CLAIM_ITEM_LINE_NU = d.CLAIM_LINE_NUM and c.TPL_TS = d.TPL_TS and f.TPL_TS = c.TPL_TS and b.CLAIM_ICN_NU = '123456788444'
Current Result which I am getting with this query
OTH_PAYER_ID PAID_DATE GROUP_CODE REASON_CODE ADJUSTMENT_AMOUNT
5501 07/13/2015 CO 11 23.87
5501 07/13/2015 PR 12 3.76
5501 07/13/2015 OT 32 33.45
2032 07/14/2015 CO 12 23.87
2032 07/14/2015 OT 14 43.01
Desired/Expected Result for which I need updated query
OTH_PAYER_ID PAID_DATE GROUP_CODE_1 REASON_CODE_1 ADJUSTMENT_AMOUNT_1 GROUP_CODE_2
REASON_CODE_2 ADJUSTMENT_AMOUNT_2 GROUP_CODE_3 REASON_CODE_3 ADJUSTMENT_AMOUNT_3
5501 07/13/2015 CO 11 23.87 PR 12 3.76 OT 32 33.45 2032 07/14/2015 CO 12 23.87 OT 14 43.01
Using DB2.
I need to select specific values from all rows where the value of a specific column is "Active"
This part works: SELECT LastName, FirstName, MiddleInit, ClientId FROM dbo.Client
But I want to add: WHERE StatusType = (Active) and how to do this.
So my data column [EODPosting].[MatchDate] is defined as a DATE column. I am trying to SELECT from my table where [EODPosting].[MatchDate] is today's date.
SELECT*
FROM[dbo].[EODPosting]
WHERE[EODPosting].[MatchDate]=GETDATE()
Is this not working because GETDATE() is like a timestamp format? How can I get this to work to return those rows where [EODPosting].[MatchDate] is equal to today's date?
I have a scenario where in I need to use a comma delimited string as input. And search the tables with each and every string in the comma delimited string.
Example:
DECLARE @StrInput NVARCHAR(2000) = '.NET,Java, Python'
SELECT * FROM TABLE WHERE titleName = '.NET' AND titleName='java' AND titleName = 'Python'
As shown in the example above I need to take the comma delimited string as input and search each individual string like in the select statement.
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.
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 RelatedI 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 RelatedI am using the following query to retrieve the top five customers from a MySQL table where 'accmanid' 1:
SELECT accmanid, custid, SUM(billone + billtwo) AS
total FROM customers WHERE accmanid = 1 ORDER BY total DESC LIMIT 5
This outputs five rows ordered by the total bill where accman = 1.
Here is my question:
How can I write the query to output five rows for EACH accmanid; Im not sure how to do such without the WHERE clause.
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'
Not sure how to do this but here is example of what I have
Table A
ID data1 data2 data3 data4
1 535 452 213 554
2 325 651 321 554
3 654 846 096 355
4 765 658 321 422
I want to have a select that will pull the following information out with count = the number of rows that have matching data in data4
ID data1 count
1 535 2
2 325 2
3 654 1
4 765 1
Right now I am using a VB script to loop thru get the current data4 value then using SELECT COUNT(data1) AS count FROM tbl_toolerrors WHERE data4 = {data4 value currently looking at}
Of course this take a bunch of trips to database and I think there should be a way to do it. I was thinking of a nested SQL querry like
Select data1, data2, ID, data3, (select count ...) Order by data1
can anyone help?
Hi,
I have two tables : Students and StuHistory. The structure of the Student table is as follows :
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Student]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Student](
[RID] [int] NOT NULL,
[Class] [int] NULL,
[Section] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SubSection] [int] NULL,
[RollNo] [int] NULL,
[DesiredRoll] [int] NULL,
[TrackingNo] [int] NULL,
[Original_rollno] [int] NULL,
[StudentStatus] [int] NULL
)
END
GO
A section has subsections where students are allocated rollno's. Every student has a unique roll no in that subsection. However he is also given a choice to enter his desired roll no. If more than one student choose the same desired roll no in that subsection/section, there is a [TrackingNo] field that then starts keeping a count. For the first unique desired roll no in that subsection/section the tracking no is always 0.
[StudentStatus] represents the following : (-1 for deleted, 0 for edited, 1 for newly inserted).
After every fortnight, i have to run a batchquery that does the following:
1. all students marked with -1 are moved to a table called StuHistory which has the same structure as that of Student.
2. Now oncethe -1 status students are moved, there will be a gap in the roll no. I want to reallocate the rollnos now, where rollnos = desired roll no taking into consideration the trackingno
So if 4 students have chosen the desired roll no as 5 and their current roll no is scattered in a subsection lets say 7, 10, 14,16, then while rearranging they will be together(grouped by subsection/section) and will be allocated roll no's 5,6,7,8. The other students will be moved down based on their desired roll nos. Over here i have to also fill the gaps caused because of the students who were deleted.
How do i write query for this? I have been struggling.
I thought of posting this as a new post as it was mixed in the previous post.
Script :
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (1, 1, N'A', 1, 1, 1, 0, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (2, 1, N'A', 1, 2, 2, 0, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (3, 1, N'A', 1, 3, 1, 1,0,1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (4, 1, N'A', 12, 1, 1, 0,-1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (5, 1, N'A', 12, 2, 1, 1, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (6, 1, N'A', 12, 3, 2, 0, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (7, 1, N'B', 5, 1, 3, 0, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (8, 1, N'B', 5, 2, 3, 1, 0 ,1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (9, 1, N'B', 5, 3, 3, 2, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (10, 1, N'B', 5, 4, 2, 0, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (11, 1, N'B', 5, 5, 2, 1, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (12, 1, N'B', 10, 1, 1, 0, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (13, 1, N'B', 10, 2, 1, 1, 0, 1 )
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (14, 1, N'B', 10, 3, 1, 2, 0, -1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (15, 1, N'B', 10, 4, 2, 0, 0, 1)
Thanks.
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.
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] .....
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 RelatedI 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] ....
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 )
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.
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?
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?
Create table #table (id int identity , from_country varchar(20) ,
to_country varchar(20),noofdays int, datetravel datetime )
insert into #table(from_country,to_country,noofdays,datetravel)
values
('Malaysia','India',2,getdate()-99),
('India','Singapore',4,getdate()-88),
('Singapore','China',5,getdate()-77),
('China','Japan',6,getdate()-66),
('Japan','USA',7,getdate()-55)
select * from #table
I want to insert data to another table based on "noofdays" columns. If "noofdays" is 4 then 4 rows should inserted to new table with 1 day increment in "datetravel" column.
Ex :
#table
1MalaysiaIndia22015-02-09 02:04:09.247
2IndiaSingapore42015-02-20 02:04:09.247
[code]...
In #table , 1st row noofdays is 2 , so in new table #table_new first 2 rows should inserted with 1 day increment in "datetravel" column.
I need to find the rows that exist in one table but not in the otherwith this condition:(prod_name exist in table1 and not in table2.prod_name ) AND(prod_name exist in table1 and not in table2.'S'+prod_name )explanation:i want to know if the product not exit and if the combination of thecharachter "S" with the product Name also not exist at the othertableB.Ryuvi
View 2 Replies View Related