I have been wrestling with this problem all morning with no success so
far where I have a need to bring back an excluded field.
Basically I have a list of order numbers. Each order number can have
many order types attached one of which is a ‘P’ type. Most order types
have an account number attached in its own field however when a ‘P’ type
is selected the account number is not brought back.
Is there someway I can get this brought back for each P type or do I
have to do some fancy insert in a data warehouse to get this done (i.e.
insert account numbers into all P types)?
Many thanks
Sam
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
I am trying to do a calculation with the below query and it works long as d.closegoal has values and d1.opengoal has values but the problem is when there is no count for either, I need to bring back a value of zero if there are no matches. Since I am using it in an outer select statement for a calculation it not bringing anything back because of no matches. This is my code:
select d.lwia,
cast((d.closegoal + d1.opengoal) as float)denominator
from
(
select yg.lwia,
cast(count(yg.appid)as float) closegoal
from dbo.wiayouthgoals yg
where yg.lwia = @RWB
-- Attained a goal in the timeframe timely or untimely
and ((convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) >= @BeginDte -- Parm date for beginning of time frame needed
and convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) <= @EndDte) -- Parm date for end of time frame needed
-- Goal due but not attained
or (convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) >= @BeginDte -- Parm date for beginning of time frame needed
and convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) <= @EndDte -- Parm date for end of time frame needed
and yg.youthattaingoal <> 1))
group by yg.lwia
)d,
(
-- Closure with open goal
select cast(count(yg.appid)as float) opengoal
from dbo.tbl_caseclosure cc,
dbo.wiayouthgoals yg
where yg.appid = cc.col_idnum
and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) >= @BeginDte -- Parm date for beginning of time frame needed
and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) <= @EndDte -- Parm date for end of time frame needed
I'm trying to recreate a sql database on another server and I installed sql server 6.5 on a server and then restored the master db using the Sql Setup program. It worked without any errors, but it did not create the loginthat will use the database that I have not restored yet. Aren't the Logins storied in the master database?
I have a column that has 75 values, 50 are unique/25 are duplicates. Ineed to be able to bring back a list of the duplicates, listing allrows even if more than two have the same value. I need to be able todo this using t-sql.Thanks,Tim
I'm wondering if somebody could provide insight into a problem I'm having with SQL Server 2005. Although the problem is happening wthin an SSIS ETL, I don't think this problem is SSIS related.
In the ETL I need to rename a database, so I first put the database into single-user mode by issuing the command:
ALTER DATABASE foobar SET SINGLE_USER WITH ROLLBACK 30
The database then goes into single-user mode, and after the renaming occurs, I attempt to put the same database back into multi-user mode:
ALTER DATABASE foobar SET MULTI_USER WITH ROLLBACK IMMEDIATE
However, whenever I have a query pane opened against the same database in SQL Server Management Studio, the ETL fails and I get this error message:
"Error: Changes to the state or options of database 'foobar' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it."
I'm wondering why the ALTER DATABASE command does not kill off the active connections? This is on my development box, and I'm the only one connected to the database. I've tried with ROLLBACK 30 as well, same thing. If I cut and paste the same command into Mangement Studio, the command succeeds so I don't think its a permission issue (using Windows Authentication both Management Studio and the ETL are executed by the same login). If I close the query pane the ETL succeeds at restoring multi-user mode. Is there something I am missing? Thanks in advance!
12.) Now you have two different tables - each with two columns.Table #1Single Column2 rows with a value equal to 1 and 2Table #2Single column2 rows with a value equal to 2 and 4Construct a statement returning in a single column all the valuescontained, but not the common values.This is another question that I got in an interview, I missedit.......Thanks,Tim
I have a table with 1 million records. I want to update only 400 records. The update statement is provided by a 3rd party vendor. Once i run the update statement it will update all the 400 records. Once the table is updated the users will validate the table
if the update is successful or not. What i'm looking for is:
1) Is there a way to identify what records were updated. 2) If the update done is not what the users wanted i need to undo and bring back the 400 records to their previous values.
I have the membership stuff up and running. I've added a field to the membership table called custnmbr. Once a user logs in, I want store his custnbmr in the session and use that to lookup data in another db. ie: Joe logs in and his custnumbr is 001, he goes to the login success page and sees his list of service calls which is: select top 10 * from svc00200 where custnmbr = 001 (the membership.custnmbr for the logged in user) I know how to do this in old ASP using session variables....but I have no idea where to even start with .Net. Many thanks
I'm trying to get a list of items by there last invoice date, but I keep getting errors. I have to join two table to get all the information needed to match a date with an item number.
select i.[Invoice-date], l.[Item-no] from [Invoice] as i join [invoice-line] as l on i.[Seq-no] = l.[Seq-no]  (Select MAX(I2.[Invoice-date]) as LastDate, l2.[ITEM-No]   from [invoice] AS I2   JOIN [invoice-line] AS L2   on i2.[Seq-no] = l2.[Seq-no]   GROUP BY L2.[Item-no]) ITEMNUMBER WHERE i.[invoice-date] = lastdate.[invoice-date] and l.[item-no] = itemnumber.[item-no]
However, I keep getting this..
Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'Select'. Msg 102, Level 15, State 1, Line 9 Incorrect syntax near ')'.
We have our Production server having database on which Few DTS packages execute every night. Most of them have Bulk Insert stored procedures running.
SO we have to set Recovery Model of the database to simple for that period of time, otherwise it will blow up our logs.
Is there any way we can set up log shipping between our production and standby server, but pause it for some time, set recovery model of primary db to simple, execute DTS Bulk Insert Jobs, Bring it Back to Full recovery Model AND finally bring back Log SHipping.
It it possible, if yes how can we achieve this.
If not what could be another DR solution in this scenario.
In the following query, when an es.evstrname DOES have data in os.activity in (5,7), then the query returns the expected results. However, if there was no out of service break, then the query will return results, but there will not be any data returned for the last column of the select statement. This makes sense to me but I was hoping there was some way that I could have data returned. For instance, is there a way for me to make the query read the - (os.actualdeparttime - os.actualarrivetime) as zero so the column would still consider the first part of the equation? Or is there a way to write a subquery that would work around this?
Select s.ldate, es.evstrname as 'Run', d.lastname+', '+d.firstname as 'Driver', po.actualarrivetime as 'PullOut', os.actualarrivetime as 'OOS', os.actualdeparttime as 'IS', pi.actualdeparttime as 'PullIn', os.actualdeparttime - os.actualarrivetime as 'Break' (pi.actualdeparttime - po.actualarrivetime) - (os.actualdeparttime - os.actualarrivetime) as 'PayTime'
From Schedules S
Join eventstrings es On s.schid=es.schid
Join employees d On d.employeeid=es.employeeid
Join events po on po.evstrid=es.evstrid and po.schid=es.schid and po.activity=4
Join events pi on pi.evstrid=es.evstrid and pi.schid=es.schid and pi.activity=3
Left Outer Join events OS on os.evstrid=es.evstrid and os.schid=es.schid and os.activity in (5,7)
Where es.evstrname>=? AND es.evstrname<=? AND s.ldate>=? AND s.ldate<=?
Order by s.ldate, es.evstrname, po.actualarrivetime, os.actualarrivetime, os.actualdeparttime, pi.actualdeparttime
All es.evstrname will always have an 'event activity' (pi.activity, po.activity etc) of 3 and 4. Only es.evstrname that have clocked out of service will have data with os.activity in (5,7) (this is an out of service break).
I'm not sure if this is a format that would help, but here is some sample data. Currently, it returns the following:
Date Run Pullout OOS IS PullIn Break PayTime 10-1 101 10:00 12:00 12:30 16:00 :30 5:30 10-1 102 11:00 ---- ---- 17:00 ---- -----
I would like it to return this:
Date Run PI OOS IS PullIn Break PayTime 10-1 101 700 900 930 945 30 215 10-1 102 700 --- --- 945 -- 215
Hi I have a table where I am searching for the values between certain numbers. But then I want to exclude a record to be part of the search
so how I could do that.
This is what I am trying to do.
Select * from mytable Where Value Between 10000 and 200000 --this gives me the result
But then I want to exclude a record from the select statment. For example this is what I really want to do
Select * from mytable Where Value Between 10000 and 20000 and where value != '100o'
It is not a typo '100o' and this not let me test the select statment so I want to exclude that particular record. The type was in the table and I want to exclude that record so I can get my statment running.
I have a BOM table with all finished item receipes and semi items recipes. create a query where semi item materials are also listed in finished item recipe.
I have 2 tables, table one with 772 pieces of compliant data. Table 2 has 435 pieces of data that meet another criteria (all the columns are identical it was just passes through an additional filter). I need to capture the values that are excluded from table 2.
Example Table 1 ID some value 1 x 2 x 3 x 4 x 5 x
Table 2 ID some value 2 x 3 x 5 x
I need to capture the data from ID 1 and 4 and assign a new value to it, it is extra compliant data. Thanks!
Here's my problem. I have 2 tasks defined in my Control Flow tab:
EXECUTE SQL--------->EXECUTE DTS 2000 PACKAGE
When I attempt to run it, by right-clicking the EXECUTE SQL task, and selecting "Execute Task", it only runs the EXECUTE SQL part (successfully), and does not "kick off" the EXECUTE DTS 2000 PACKAGE, after it is done running (even though it completes successfully, as shown by the green box).
Yes, they are connected by a dark green arrow, as indicated in my diagram above.
Why is this?? Am I missing something here? Need help.
Hello,Basically, I have a table with 2 fieldsId item#1 33332 33333 22224 22225 22226 33337 33338 3333I would like to only select the last identical Item# which in this case would be the id 6,7 and 8Any idea how could I do that?Thanks
Hello,I'm trying to create a simple back up in the SQL Maintenance Plan that willmake a single back up copy of all database every night at 10 pm. I'd likethe previous nights file to be overwritten, so there will be only a singleback up file for each database (tape back up runs every night, so each daysback up will be saved on tape).Every night the maintenance plan makes a back up of all the databases to anew file with a datetime stamp, meaning the previous nights file stillexists. Even when I check "Remove files older than 22 hours" the previousnights file still exists. Is there any way to create a back up file withoutthe date time stamp so it overwrites the previous nights file?Thanks!Rick
New to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.
When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on the roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?
I need to run two reports each of A5 Size to run back to page and print on single A4 paper means in 1st half Sale bill will be printed and in second half Gate Pass Will Be Printed both report will be on same page and size and shape should be maintained. How to do it.
I am having trouble finishing the last bit of a report. The report shows orders that customers have placed that contain 0 promo items, All promo items (all items in order are promo items), and a mix of promo and non promo (at least 1 promo item and 1 non-promo item). Ive simplified this a bit for ease of understanding but lets assume we have 2 tables: A Promo table that contains the items on promotion and the dates that promotion is valid, and a Sales table, that contains the order number, order date, and sku ordered.
I've already written code that finds orders that have at least 1 promo item in them, and using that, I can determine what orders have 0 promo items in them. Where I am stuck is taking the orders that have at least 1 promo item in them, and separating them into orders that have only promo items, and those that have both promo and not promo items in them. Also, there are several promos throughout the year (called "Offers") so in my code below, you can see 2 different Offers ("JF" and "MA") with their corresponding dates they are valid. They will never overlap. My results also have to be split out by Offer so management can look at the results of each offer separately. Here is some code:
Code: create table #Promos ( Offer varchar(2) null, SKU int null, StartDt date null, EndDt date null
[Code] ....
So my results should show OrderNo A1111 in the Promo and No Promo group because of SKU 5 not being promotional during the time that order was placed. OrderNo A2222 should be in the Promo Only group because both SKUs on the order were promotional at the time the order was placed.
Hello,I am hoping you can help me with the following problem; I need to process the following steps every couple of hours in order to keep our Sql 2000 database a small as possible (the transaction log is 5x bigger than the db).1.back-up the entire database2.truncate the log3.shrink the log4.back-up once again.As you may have determined, I am relatively new to managing a sql server database and while I have found multiple articles online about the topics I need to accomplish, I cannot find any actual examples that explain where I input the coded used to accomplish the above-mentioned steps. I do understand the theory behind the steps I just do not know how to accomplish them!If you know of a well-documented tutorial, please point me in the right direction.Regards.
It's been pretty quiet on here recently - no-one experiencing any corruption problems we haven't already given guidance on? Cool...
Paul Randal Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express (Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Hi I have a problem that seems to be stupid… but still I cant solve it so please if you can help ASAP I'll appreciated.
Its 2 tables "Courses" and "Facilitators" The Course table contain: Course_Id and Course_Name The Facilitators table contain: Facili_Id and Facili_Name
The two tables have (many to many) relation between them. So I split them into a 3rd table called "Trans" which contain the PK from each Table That will be : "Trans_Course_Id" and "Trans_Facili_Id"
I put some data on each table… at least 3 records and related some of them in the "Trans" table
Now im looking for an SQL command that brings me All the "Facili_ID" and "Facili_Name" for a specific course. And only those who is not already selected by the same course?
Like if I have the data in the "Courses" tableid: 1 and Name: VB Id:2 and Name: C#
And in "Facilitators" table: Id:1 and Name: Adam Id:2 And Name: George Id:3 and Name: Sam
Now in the relation table "Trans"
Course_Id:1 and Facili_Id:1 Course_Id:2 and Facili_Id:1 Course_Id:2 and Facili_Id:3
Now I want the SQL Commands that brings me the he "Facili_ID" and "Facili_Name" For Course_id "For example" and should not be selected by the same course… That would be:
Id:2 And Name: George Id:3 and Name: Sam
And the same for eash time I pass the course_id for the command Thank you.
My management ask me use logshipping in the part of Disaster Recovery plan,as per i know i have put target database in STAND BY mode in logshipping if ACTIVE MODE fail's how can i bring back the logshipped DB to active
Hi I'm still new to sql2005 and I wanted to copy over a database to another machine along with the stored procedures but when I do an Import using SSIS import/export wizard I only get the database and not the stored procedures. Anyone know how I can also get the stored procedures?
Hello there,I have a user who can bring offline a database but he cannot bring thedatabase back online.The error which pops up is:Error5011: User does not have permission to alter database dbnameALTER DATABASE statement failed.sp_dboption command failed.Any ideas?Thanks
I have the following tables on a non-SQL database that I can accessvia ODBC:UpdatedOrderHeaderOrderDetailsInvoiceHeaderInvoiceDetailsWhen an order or an invoice is added/updated a record with the ordernumber is added to the Updated table. I need to loop through theUpdated table and for each record bring the data over to thecorresponding tables on the SQL server.Does anyone have any ideas how to do this? I was attempting to do thiswith a DTS job, but I don't know how to pass the order number to eachupdate and then loop and get the next order.Any help would be appriciated. Let me know if you need any moreinformation.Thanks,Mike
