How To Change The List Order In My Stord Prosege Backward

Jan 23, 2008

need help how to
change the shift order in my stord prosege backward
on the field "shifttype"
not like this
shifttype
---------------------------------------------------------
111111 2008-02-24 Sunday 1
111111 2008-02-23 Saturday 2
111111 2008-02-22 Friday 3
111111 2008-02-21 Thursday 4
111111 2008-02-20 Wednesday 5
111111 2008-02-19 Tuesday 6
111111 2008-02-18 Monday 7
111111 2008-02-17 Sunday 8
111111 2008-02-16 Saturday 1
111111 2008-02-15 Friday 2
111111 2008-02-14 Thursday 3
111111 2008-02-13 Wednesday 4
111111 2008-02-12 Tuesday 5
111111 2008-02-11 Monday 6
111111 2008-02-10 Sunday 7
---------------------------------------------------------------------------------------
i need it like this
shifttype
------------------------------------------------------
111111 2008-02-24 Sunday 8
111111 2008-02-23 Saturday 7
111111 2008-02-22 Friday 6
111111 2008-02-21 Thursday 5
111111 2008-02-20 Wednesday 4
111111 2008-02-19 Tuesday 3
111111 2008-02-18 Monday 2
111111 2008-02-17 Sunday 1
111111 2008-02-16 Saturday 8
111111 2008-02-15 Friday 7
111111 2008-02-14 Thursday 6
111111 2008-02-13 Wednesday 5
111111 2008-02-12 Tuesday 4
111111 2008-02-11 Monday 3
111111 2008-02-10 Sunday 2




Code Snippet
if object_ID('tempdb..#emplist','U')<>0
Drop Table #emplist
if object_ID('tempdb..#empshifts','U')<>0
Drop Table #empshifts
go
declare @g datetime
select @g=getdate()
CREATE table #empList (
[empID] int NOT NULL,
[ShiftType] int NULL,
[StartDate] datetime NOT NULL,
[EndDate] datetime NOT NULL
)
INSERT INTO #empList ([empID], [ShiftType],[StartDate],[EndDate])
SELECT 111111,1,CONVERT(DATETIME, '01/01/2008', 103), CONVERT(DATETIME, '27/02/2009', 103) UNION ALL
SELECT 222222,2,CONVERT(DATETIME, '01/01/2008', 103),CONVERT(DATETIME, '27/02/2009', 103)UNION ALL
SELECT 333333,3,CONVERT(DATETIME, '01/01/2008', 103), CONVERT(DATETIME, '27/02/2009', 103)UNION ALL
SELECT 444444,4,CONVERT(DATETIME, '01/01/2008', 103), CONVERT(DATETIME, '27/02/2009', 103)UNION ALL
SELECT 555555,5,CONVERT(DATETIME, '01/01/2008', 103),CONVERT(DATETIME, '27/02/2009', 103)
-- create shifts table
CREATE table #empShifts (
[empID] numeric(18, 0) NOT NULL,
[ShiftDate] datetime NOT NULL,
[ShiftType] int NULL ,
[startingShiftType] int not null
)
create unique clustered index uc_empshifts on #empshifts(empid,shiftdate DESC)
declare @curr_employee int
declare @shift_id int
declare @dummyShift int
declare @dummyEmp int
--start by populating the dates into the @empshifts table
insert #empshifts (
empid,
shiftdate,
[startingShiftType]
)
select
empid,
dateadd(day,-1*spt.number,Enddate),
shifttype
from #empList cross join
master..spt_values spt
where
spt.type='P'
and spt.number<=datediff(day, startdate,enddate)

--now set up the shifts as the cursor solution did
select @shift_id=0, @curr_employee=0
update e
set
@shift_ID=shiftType=(case when @curr_employee=empid then @shift_ID else startingShiftType end -1 +
CASE WHEN @shift_id in ( 1,2,3) and DATENAME (dw,ShiftDate )='Friday' then 0
WHEN @shift_id= 8 and DATENAME (dw,ShiftDate )='Saturday' then 0
else 1 end)%8+1,
@dummyshift=@shift_ID,
@curr_employee =empid,
@dummyemp=@curr_employee
from #empshifts e WITH (index(uc_empshifts),TABLOCK) OPTION (MAXDOP 1)
--show the results
select empid,shiftdate, DATENAME (dw,ShiftDate ),shifttype from #empshifts
--select datediff(ms,@g,getdate())






View 5 Replies


ADVERTISEMENT

Reporting Services :: Change Order Of The Day Of Week Names In Parameter Drop Down List In SSRS?

Aug 26, 2015

I have a requirement to show Day of week in parameter drop down list in different order, actual order is Monday to Sunday (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday) in DayOfWeek dimension in cube.

But my requirement is to show Friday to Thursday(Friday,Saturday,Sunday,Monday,Tuesday,Wednesday,Thursday) in DayOf Week parameter drop down list and report table. How I can get this requirement done.

View 2 Replies View Related

Stord Procedure Syntax Problem

Nov 27, 2005

This is probably a very simple question but i am having problems with stored procedure syntax.
I want to insert fields with  preset values (DateCreated/OrderVerified)
and  two values (Name/ Address1) from a databletable called "Customer" and insert both of these into a datatable called "Orders"
 
I have tried the following syntax but it is not working. Can anyone tell me where i am going wrong
CREATE PROCEDURE SP_CreateOrder
(@CustomerID Varchar(50))
ASINSERT INTO Orders (DateCreated, Verified)VALUES ( GETDATE(), 0,)AND INSERT Into Orders (Name, Address)SELECT  (Customer.Name, Customer.Address) FROM Customer
WHERE CustomerID = @CustomerID
Many thanks martin 

View 1 Replies View Related

Stord Proc Including An Sp_addlinkedserver

Nov 30, 2000

I am trying to write a stored procedure that will automatically link a server and then run a stored proc on the newly linked server. At the end the stored proc will drop the server. I do not want a permanantly linked server due to the fact that this only has to be ran once a month, can you run a remote stored proc any other way then linking the servers?? Cheers to all who reply

View 1 Replies View Related

A Column Has Been Specified More Than Once In The Order By List.

Apr 18, 2007



I have a function dbo.FIELD that extracts a part of the value from a field. I want to get two pieces and order by them.



Select failed: Warning: 169(15): A column has been specified more than once in the order by list. Columns in the order by list must be unique.



How can I do this?



SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, dbo.FIELD(WORK_ID, '*', 1) ASC





These work:

SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, WORK_ID ASC

SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, dbo.FIELD('WORK_ID', '*', 1) ASC

View 1 Replies View Related

A Column Has Been Specified More Than Once In The Order By List.

Apr 18, 2007

I have a function dbo.FIELD that extracts a part of the value from a field. I want to get two pieces and order by them.



Select failed: Warning: 169(15): A column has been specified more than once in the order by list. Columns in the order by list must be unique.



How can I do this?



SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, dbo.FIELD(WORK_ID, '*', 1) ASC





These work:

SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, WORK_ID ASC

SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, dbo.FIELD('WORK_ID', '*', 1) ASC



View 1 Replies View Related

How To ORDER BY Designated List Of Key Values

Apr 20, 2006

I need to be able to order the results of a SELECT query by the order of a specific list of key IDs provided in the WHERE IN statement.

So my query looks like:

SELECT *
FROM TableName
WHERE KeyID IN (3,104,43,22,345)
ORDER BY ????

I need the results returned in the order provided in the IN list (3,104,43,22,345).

Thanks in advance!

pr0

View 5 Replies View Related

What Is The Best Way To Store A Shopping List Or Order?

Oct 17, 2006

After a customer decides to buy a shopping list, there is generally aneed to store/insert one master record and a variable number of childdetail records, preferably all wrapped in a transaction. There are lotsof ways to do this and I am wondering if anyone knows which is mostefficient.One approach is to use ADO.NET's transaction capabilities, definesingle-record insert procs for the master and detail tables, and callthe detail insert in a loop from the web page. This has N+1 trips tothe server, which is not too attractive.Another approach is to concatenate all the data into a bigstring/varchar variable and pass it to a decoder proc that would thencall the single record insert procs via a loop inside the decoder proc.This second approach would use T-SQL's transaction capability and haveonly one trip to the server, but it is more effort to code on the webpage and in the decoder proc.Surely this is a common problem. Are there any more elegant/efficientmethods that anyone can suggest? Can one pass an array to a proc? Isthis a place for a user defined data type?Any advice is much appreciated.

View 2 Replies View Related

ORDER BY Columns In SELECT List?

Jul 20, 2005

According to BOL, columns in an ORDER BY clause do not have to be in the SELECTcolumn list unless the SELECT includes DISTINCT, or the UNION operator.Is this a SQL Server thing, or SQL standard behavior? That is, if I were to writeabsolutely pure SQL-92, must columns in the ORDER BY clause be present in the SELECTlist?

View 1 Replies View Related

How Do Order The List By Date To Show For New Dates.

Aug 2, 2007

  <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NWHCConnectionString %>"
SelectCommand="SELECT [Title], [URL], [Date] FROM [Article] ORDER BY [Date] DESC"></asp:SqlDataSource>


<asp:Repeater id="myRepeaterUL" runat="server" DataSourceID="SqlDataSource1">
<HeaderTemplate>
<ul>
</HeaderTemplate>
<ItemTemplate>
<li><a href="<%# DataBinder.Eval(Container.DataItem, "URL") %>"><%#DataBinder.Eval(Container.DataItem, "Title")%></a><br /><%# Eval("Date") %></li>
</ItemTemplate>
<FooterTemplate>
</ul>
</FooterTemplate>
</asp:Repeater>
 This is my code above, I am trying to order them to show the four new list of news. Here is a picture, yeah its old and everybody loves pictures. see the box on the right, i want to show only four, not all of it.  

View 2 Replies View Related

Change Sort Order In 6.5

Aug 25, 1999

Hi,

Is there any way to change the sort order from case sensitive to case insensitive in SQL Server 6.5 without having to rebuild the master db and all users db's.

thanks in advance.

View 1 Replies View Related

Change List Sizing

Nov 30, 2007

I have defined a list with one row of data and three more optional rows of data

Row 1 is always shown - rows 2, 3, and 4 are shown if there is data available in those fields

By using the visiblity property I can have rows 2, 3, and 4 disapper when no data is available and appear when data is available

What I would like to do now is change the height of the list item based on if rows 2, 3, or 4 are visible (have data)

So if no data is available then the list item would only be one row high

If only one of rows 2, 3, or 4 contain data then the list item would be two rows high

so forth and so on

I noticed that the textbox can increase and decrease height to accomodate content. Looking for something similar in a list item.


Thanks!

View 2 Replies View Related

Change Of Sort Order After Installation

Oct 26, 2000

hi,
if I change the sort order after I install sql server, I have to rebuild the databases. the meaning of rebuilding the Databases is to create a complete database from scrach including tables, store procedures data. or what is the meaning of rebuilding the databases

Ahmed

View 2 Replies View Related

Where To Change Chara Set And Sort Order

Aug 17, 2000

I have just started with 7.0 and found that one of my enviornments is not the same as production. I am trying to change the Char. set and sort order(rebuild master). In 6.5, install the CD and just update the options. In 7.0, do I have to un-install to do this? I looked on the CD and the local directory and can not seem to find the options to update the master db?

Thanks in Advance,

View 2 Replies View Related

Can I Change The Sort Order Of A Database On Sql

Feb 5, 2007

Im new to sql server 2000 can i change the order in the database is it possible. If anybody help me how to do it that would be a great help.

Articles on various categories

View 2 Replies View Related

How To Change Column Order In Tables?

Apr 14, 2008

I'm using VBE 2008 and in the edit table schema box it doesn't allow reordering the columns. Any solutions?

View 1 Replies View Related

Dynamically Change Grouping Order

Jan 23, 2008



Hello everyone.

You may had this problem before and know how to do that or may be can suggest me with some ideas.
I am in a process of creating a report wich has 3 groups and the data at the moment grouped by Company - Product - Customer.
I want to be able to change grouping order and respectively the data in report , something like Product - Company - Customer or Customer - Product- Company. (just example)
Is that possible to do at all?
May I add some drag and drop functionality and integrate in my report ?

Any ideas will be most welcomed

Thanks in advance

View 6 Replies View Related

Upgrade From SQL 7 To 2000(also Change Sort Order)

Jul 9, 2002

Hi,
We ae planning to upgrade SQL 7 to SQL 2000 curretly we have BInar sort order.We have to cahnge the sort order to Dictionary sort order in SQL 2000 as per the recomendation from application vendor.
What is the best way Ican do this? Can I cahnge the sort order at the time of upgrade without effecting the data?
Thanks in advance
Mohan

View 1 Replies View Related

How To Change The Order Of Rows In Datatable Randomly?

Mar 26, 2006

Hello!

I would like randomly change the order of the rows in my table. Is there any way to do that? I also have a question about random generator. Is it possible to get a repeatable sequence of random numbers between 1 and 10 in T-SQL? (for example 2,7,6,5,8,9,3,2,....each state with the same probability). But i need the same sequence every time i run my procedure. I know this is just a pseudo generator. I tried to use function rand([seed]) and change the seed value, but I got some strange results...(floor(rand([seed])*100))

Thanx for any idea,

Z.

View 4 Replies View Related

How To Create SELECT QUERRY FOR A CHANGE ORDER FORM In ASP?

Jun 6, 2007

 Hello.
Hope you are all well and having a good day.
I've got a problem i would like you to help me out with. My
company has got a client who sells meat and my senior tech lead
has customized the cart to fit their needs and have created a
despatch table with a "simple asp form" that allows them
to scan their products in with a wi-fi scanner.

The despatch table has the following fields:
1. OrderID
2. Product
3. Quantit
4. sellbydate
5. traceabilitycode and
6. Weight
Question 1: how do i create a form in asp that allow a user to
view the order no, product and quantity
celibate,traceabilitybycode and Weight after scanning the
details into the simple form created?
Question 2: How can i create an asp form that allows users to
search for the Order that populates the orderid, Product,
quantity?
Question 3: How can i create an asp for that allows users to
search for Product with orderid,traceabilitycode and quantity?
Question 4: How can i create an asp form that allows users to
Trace: Orderid, traceabilitycode, sellbydate and Quantity?

Please find below the source code of both the:
despatchLotsRowsSimpleForm.asp that allow them to scan details
into the table and the despatchLotsExec.asp:

THE FORM:
<!#include file="../includes/settings.asp">
<!#include file="../includes/getSettingKey.asp">
<!#include file="../includes/sessionFunctions.asp">
<!#include file="../includes/databaseFunctions.asp">
<!#include file="../includes/screenMessages.asp">
<!#include file="../includes/currencyFormat.asp">

<%
on error resume next

dim mySQL, connTemp, rsTemp

' get settings
pStoreFrontDemoMode =
getSettingKey("pStoreFrontDemoMode")
pCurrencySign = getSettingKey("pCurrencySign")
pDecimalSign = getSettingKey("pDecimalSign")
pCompany = getSettingKey("pCompany")

pAuctions = getSettingKey("pAuctions")
pAllowNewCustomer = getSettingKey("pAllowNewCustomer")
pNewsLetter = getSettingKey("pNewsLetter")
pStoreNews = getSettingKey("pStoreNews")
pSuppliersList = getSettingKey("pSuppliersList")
pRssFeedServer = getSettingKey("pRssFeedServer")

%>
<b><%=getMsg(10021,"despatch")%></b>
<br>
<%=getMsg(10024,"Enter despatch ...")%>


The despatchExec.asp Page
<%
' WebShop 3.0x Shopping Cart
' Developed using ASP
' August-2002
' Email(E-Mail address blocked: See forum rules) for further information
' (URL address blocked: See forum rules)
' Details: add e-mail to newsletter list
%>

<!--#include file="../includes/settings.asp"-->
<!--#include file="../includes/getSettingKey.asp"-->
<!--#include file="../includes/databaseFunctions.asp"-->
<!--#include file="../includes/stringFunctions.asp"-->
<!--#include file="../includes/miscFunctions.asp"-->
<!--#include file="../includes/screenMessages.asp"-->
<!--#include file="../includes/sendMail.asp"-->

<%

on error resume next

dim mySQL, connTemp, rsTemp, pEmail

' get settings

pStoreFrontDemoMode = getSettingKey("pStoreFrontDemoMode")
pCurrencySign = getSettingKey("pCurrencySign")
pDecimalSign = getSettingKey("pDecimalSign")
pCompany = getSettingKey("pCompany")

pEmailSender= getSettingKey("pEmailSender")
pEmailAdmin= getSettingKey("pEmailAdmin")
pSmtpServer= getSettingKey("pSmtpServer")
pEmailComponent= getSettingKey("pEmailComponent")
pDebugEmail= getSettingKey("pDebugEmail")
pGiftRandom= getSettingKey("pGiftRandom")
pPercentageToDiscount= getSettingKey("pPercentageToDiscount")
pStoreLocation = getSettingKey("pStoreLocation")

' form
pidOrder = lcase(getUserInput(request.form("idOrder"),50))
pProduct = lcase(getUserInput(request.form("product"),50))
pQuantity = lcase(getUserInput(request.form("quantity"),50))


pPriceToDiscount = 0

' insert despatch

mySQL="INSERT INTO despatch2 (idOrder, product, quantity ) VALUES ('"
&pidOrder& "', '" &pProduct& "', '" &pQuantity&
"')"

call updateDatabase(mySQL, rstemp, "despatchExec")

pBody=getMsg(10025,"Despatch confirmed...") &VBcrlf&
getMsg(311,"To opt-out click at:") & " (URL address blocked: See
forum rules)="&pEmail

response.redirect "redit_message.asp?message="&Server.Urlencode(getMsg(10025,"Despatch confirmed"))

call closeDb()
%>

Thank you for your help in advance..
Regards,
philip

View 2 Replies View Related

T-SQL (SS2K8) :: Change Column Order In Dynamic Pivot?

Sep 17, 2014

I am creating dynamic pivot and my column order is as below

[2015-02],[2015-04] [Prior] ,[2014-08],[2014-11]

but i want to display as below:

[Prior],[2014-08],[2014-11],[2015-02],[2015-04]

View 1 Replies View Related

Replication :: Merge Rep Schema Change And Processing Order

Jul 14, 2015

Question re Merge rep (pull) and processing order.  We have a group of changes associated with an app upgrade, the scripts run fine on the publisher.   Part of the change includes creation of a new table , followed by altering a view to use new table.Following the change at the publisher, when the sync is kicked off from the subscriber, it fails - the alter of the view throws an'invalid object' error with regard to the new table.  Seems as if the view alter is attempted before the dependant table has been created. 

I have tried to amend the processing order of the view using sp_changearticle, which executes (quickly) with a 0 return code.But it is to no avail , the error still occurs.   is it possible to change the processing order for a view article , which will be applied to schema changes ? Have

View 4 Replies View Related

DB Design :: Change Order Of Column In Database Tables

Jul 22, 2015

How to Change Order of Column In Database Tables

View 10 Replies View Related

Mass Table Structure Change For Column Order

Feb 26, 2008



Say you have an existing populated SQL 2005 database, with 700+ tables, and you want to just change the order of the columns inside every table. Short of manually building conversion scripts, anyone know an automated way to do this? I was thinking thru ways to do them all in one shot, and have tools like Erwin and DbGhost that could be used also. Basically moving some standard audit columns from the end of the tables to just after the PK columns.

Thanks, Bruce

View 8 Replies View Related

Transact SQL :: Get List Of Items Present In Order Based On Confidentiality Code Of Product

Sep 29, 2015

I want to get the list of items present in that order based on the confidentiality code of that product or Item and confidentiality code of the user.

I display the list of orders in first grid, by selecting the order in first grid I display the Items present in that order based on the confidentiality code of that item.

whenever order in 1st grid is selected i want to display the items that the item code should be less than or equal to the confidentiality code of the logged-in user other items should not display.

If the all the items present in the order having confidentiality code greater than Logged-in user at that time the order no# should not display in the first grid.

Table 1:Order

Order_Id Order_No Customer_Id

2401 1234567 23
2402 1246001 24
2403 1246002 25

Table 2 : OrderedItems

OrderItem_Id Order_Id Item_Id Sequence

1567 2401 1001 1
1568 2401 1003 2
1569 2402 1005 1
1570 2402 1007 2
1571 2403 1010 1

Table 3: ItemMaster

Item_Id Item_Name confidentCode

1001 Rice Null
1003 Wheet 7
1005 Badham Null
1007 Oil 6
1010 Pista 8

Out put for 1st grid 

**Note :** Logged-in user have confidentiality code 6

Order No Customer
1234567 23
1246001 24

3rd order is not displayed in the grid

After user selects the 1st order in the grid then the items present in that 1st order should be displayed as 

1001     Rice

the second item not displayed because that having confidentiality code greater than user.

After user selects the 2nd order in the grid then the items present in that order should displays

1005 Badham
1007 Oil

I need the query to display the order details in 1st grid.

View 3 Replies View Related

SQL Server 2012 :: List Of Order Numbers Based On Stock Availability - Filter Results?

Dec 23, 2014

Trying to build a list of order numbers based on stock availability.

The data looks something like this:

OrderNumber Stockcode quantityordered quantityinstock
123 code1 10 5
123 code2 5 10
124 code3 15 20
124 code4 10 10

In this case I would like to output a single result for each order, but based on stock availability order 123 is not a complete order and 124 is so the results will need to reflect this.

View 1 Replies View Related

SQL Server 2008 :: List Of Commands That Require Exclusive Access In Order For Command To Complete

Aug 10, 2015

Any list of commands that require exclusive access in order for the command to complete? I had an instance today where a DBA executed sp_changedbowner command which is the alter database command on a production database and it locked it up.

View 0 Replies View Related

SQL Server 2008 :: How To Change Query So Apple Does Not Show Up In List

Apr 2, 2015

I am trying to print Companies with less than 100 employees for all dates.Here's my table structure

Create table CompanyEmployeeArchive(
Company varchar(100) not null, Employees int, Dateinserted date)

Insert into CompanyEmployeeArchive values('Microsoft',1001,'2015-01-01')
Insert into CompanyEmployeeArchive values('Microsoft',1050,'2015-02-01')
Insert into CompanyEmployeeArchive values('Microsoft',1600,'2015-03-01')
Insert into CompanyEmployeeArchive values('IBM',10,'2015-01-01')
Insert into CompanyEmployeeArchive values('IBM',80,'2015-02-01')
Insert into CompanyEmployeeArchive values('Apple',90,'2015-01-01')
Insert into CompanyEmployeeArchive values('Apple',900,'2015-02-01')
Insert into CompanyEmployeeArchive values('Apple',1000,'2015-03-01')

I want companies that have employees less than 100 for all dates i.e. Only IBM. Apple has < 100 employees only on one month.Select Company, dateinserted, employees from CompanyEmployeeArchive group by company,dateinserted,employees having employees < 100 order by company, dateinserted this query lists Apple too. How can I change the query so Apple does not show up in the list.

View 4 Replies View Related

T-SQL (SS2K8) :: Order Change In Parent To Its Child Tables Using FK Relations?

Apr 20, 2015

I have used Aasim Abdullah's (below link) stored procedure for dynamically generate code for deletion of child tables based on parent with certain filter condition. But I am getting a output which is not proper (Query 1). I would like to have output mentioned in Query 2.

Link:

[URL]

--[Patient] is the Parent table, [Case] is child table and [ChartInstanceCase] is grand child

--When I am deleting a grand child table, it should be linked to child table first followed by Parent

--- query 1

DELETE Top(100000) FROM [dbo].[ChartInstanceCase]
FROM [dbo].[Patient] INNER JOIN [dbo].[Case] ON [Patient].[PatientID] = [Case].[PatientID]
INNER JOIN [dbo].[ChartInstanceCase] ON [Case].[CaseID] = [ChartInstanceCase].[CaseId]
WHERE [Patient].PracticeID = '55';

--Query 2

DELETE Top(100000) [dbo].[ChartInstanceCase]
FROM [dbo].[ChartInstanceCase] INNER JOIN [dbo].[Case] ON [ChartInstanceCase].[CaseId]=[Case].[CaseID]
INNER JOIN [dbo].[Patient] ON [Patient].[PatientID] = [Case].[PatientID]
WHERE [Patient].PracticeID = '55';

how to modify the SP 'dbo.uspCascadeDelete' to get the output as Query 2.

View 1 Replies View Related

Transact SQL :: Order Change In Parent To Its Child Tables Using FK Relations?

Apr 20, 2015

I have used Aasim Abdullah's (below link) stored procedure for dynamically generate code for deletion of child tables based on parent with certain filter condition. But I am getting a output which is not proper (Query 1). I would like to have output mentioned in Query 2.

Link: [URL]

--[Patient] is the Parent table, [Case] is child table and [ChartInstanceCase] is grand child

--When I am deleting a grand child table, it should be linked to child table first followed by Parent

--- Query 1

DELETE Top(100000) FROM [dbo].[ChartInstanceCase]
FROM [dbo].[Patient] INNER JOIN [dbo].[Case] ON [Patient].[PatientID] = [Case].[PatientID]
INNER JOIN [dbo].[ChartInstanceCase] ON [Case].[CaseID] = [ChartInstanceCase].[CaseId]
WHERE [Patient].PracticeID = '55';

--Query 2

DELETE Top(100000) [dbo].[ChartInstanceCase]
FROM  [dbo].[ChartInstanceCase] INNER JOIN [dbo].[Case] ON [ChartInstanceCase].[CaseId]=[Case].[CaseID] 
INNER JOIN
[dbo].[Patient] ON [Patient].[PatientID] = [Case].[PatientID]
WHERE [Patient].PracticeID = '55';

how to modify the SP 'dbo.uspCascadeDelete' to get the output as Query 2

View 15 Replies View Related

Need Help, How To Change Datatype In Order To Sort Some Extracted Numbers From String

Apr 3, 2006

Hello,

I am trying to extract from some strings like the following strings the number and order them by that number:

Box 1
Box 2
Box 3
Box 20
Box 21
...(and so on)


The problem I am having is that I already extracted the number using

Substring([field],[starting position],[lenght])

but the output seems to be in a string format, so the order is not in an ascending order.

Thanks for any suggestions.

View 6 Replies View Related

Transact SQL :: Unable To Change Column Order Of Existing Table In Server

Jul 23, 2015

In y sql server table has millions of records available. I don't want to drop the tables.

My requirement is I want to change the column order of an existing table. some tables I am able to saving on design window Like Below image.

Even I had generate a script and using that script trying to execute on Management Studio but unable to saving the new column orders. I am getting the Timeout expired error after couple of minutes. How can we save the orders without dropping the table !

View 13 Replies View Related

SQL Server 2008 :: Does List Of Page IDs Allocated For A Table Change During Index Rebuilds

Jul 16, 2015

We noticed a deadlock 3-4 weeks ago on a table (table1) and deadlock graph was captured.

When I am analyzing the deadlock graph, page number using DBCC PAGE, I am getting the object id for a different table (table2). But deadlock graph shows the name of the object as table1.

Is it possible that subsequent defragmentation of indexes would have caused the respective page id to got re-allocated to a different table? I checked the deadlock graph lately only after 3-4 weeks.

View 1 Replies View Related







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