It's been a while since I last posted on here, about 4 years maybe more. Anyway I'm happy to be back. I have an existing query that I needed to add two columns to. I did that but now my dilemma is to have the query NOT return NULL values in a particular column (pd.premium_amount). I tried adding WHERE pd.premium_amount IS NOT NULL and that did not do the trick. I was thinking about trying to use CASE statement but didn't really see that as being an option. Below is my code.
USE [AmLink_DW] DECLARE @Bil_Grps TABLE (bil_grp_id int, bil_grp_sname varchar(50)) INSERT INTO @Bil_Grps (bil_grp_id, bil_grp_sname) SELECT * FROM ( VALUES
Where it's select in red I want to be able to look in another table with a listing of email domains (@express.com,@express.net,@example.org), and when the are query that I don't view them. I don't know if I need some kind of varible or just lookup in another table. I have windows 2000, and same for SQL. I am trying to write it using SQL Query Analyzer.
Thank You,
Ernie
SELECT FirstName, LastName, Street, city, State, zip, Country, Providence, TypeOfAccount, RemoteComputerName, Timestamp1, PresentEmail, NewEmail, Password, Age, Account_Active FROM dbo.newusers WHERE (FirstName <> ' NULL') AND (LastName <> ' NULL') AND (PresentEmail <> ' LOOKUP in another table') AND (NewEmail <> ' NULL') AND (Password <> ' NULL') AND (Age <> ' NULL') AND (Account_Active <> 'yes') AND (Account_Active <> 'reject') AND (Account_Active <> 'spammer')
Hi guys, please help.What I did is :Step1. create a Database named [Test], it's default owner is [sa]Step2. create a User in Database [Test] named [kimliuTest], which mapsto the Login [kkhadkimliu] on this Database Server, it's a windowsdomain user.Step3. create a Schema in Database [Test] named [schTest], whose SchemaOwner is User [kimliuTest]Step4. created two tables: [dbo].[t1] and [schTest].[t2]Step5. run the scriptALTER USER kimliuTest WITH DEFAULT_SCHEMA = schTestto set the default schema.Now my problem is, when I try to runselect * from schTest.t2, everything is OK, but if I runselect * from t2the error message says:Msg 208, Level 16, State 1, Line 1Invalid object name 't2'.why? I'm loggin in with my [kkhadkimliu] windows domain id , ActiveMonitor also shows that. but the command does not work?Yeah, it's a good habit to write the full name of the Table with Schemaname as prefix, however, we did our development in SQL 2000 and tryingto upgrade to SQL2005, so trying to avoid scanning codes and insertingin thousand of places.How could we omit the Schema name when referrring to Tables? Thanks.ps. I posted in another topic days ago, but nobody is replying, when Itried to bring it forward by replying myself, it failed. please help me, thanks!
I have this report i setup as an agent job in SSMS; It's setup to run every 7 minutes and it only sends the report if data is present. I'd like to add the ability to omit any rows that were sent in the previous report.
This is what the script looks like:
if exists (select o.ord_billto, o.ord_refnum , o.ord_hdrnumber, o.mov_number, o.ord_status, o.ord_cmdvalue, o.ord_startdate from orderheader o where ord_billto in ('A','B','C','D') and DATEDIFF(minute , o.ord_datetaken, GETDATE())<=7
[Code] ....
Also, why I can't seem to use IF ( Select [...]) > 0? When i try using that instead of IF EXISTS i get this error: "Msg 116, Level 16, State 1, Line 7 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
Dear group,I am a DB-Administrator for a Company that creates and maintains asoftware solution based on SQL-Server and Crystal-Reports.The Crystal-Reports part uses the following method to present data.First a specific user (database authentification) is created with afixed password. Then Crystal Reports uses thisuser/password-information to login to the database and present or printthe data.I recently received a database from a customer of ours and i restoredit on my machine. It worked perfectly except that the Crystal Reportspart did not function at all (showing only blank reports).After dropping the specific user and creating the fixed password thething worked.So my question: does restoring a SQL-Server database on a differentmachine alter or eliminate login and user information ?Any reply will be highly appreciated.Greetings from ViennaUli
I'm using some xslt documents to transform the xml output of my Reports but have come across two curiosities where the xslt filter seems to behave unusually.
Firstly, I need the final saved file to have an xml declaration, which I believe it should do by default. Even if I put omit-xml-declaration="no" in the xsl:output tag I don't get an xml declaration. At present we have a custom job that writes these declarations back into the xml after SRS has saved it.
Secondly and more importantly, I need to have some of my output tags wrapped in CDATA sections. I've tried using the cdata-section-elements attribute, again with no luck.
my XSLT looks something like this (simplified for space)
Hello, I have to migrate an application from oracle to sqlserver but i habe query with connect by prior What is the equivalent in sqlserver 2000. Please help me.
I'm trying to find out if a certain row exists in my table, if it does exist then I do nothing if it doesn't exist I will insert a value. Only problem is i'm quite new and not sure how to get the values from the insert to correctly check if they already exist. Heres my attempt using query analyser
I can't figure this out for the life of me. Wanted to know if it's possible to select certain date conditions in a query, then later reference those conditions and to only select the max of them.
I need to do this dynamically as I do not know what the max value is. I've provided an example below:
Select var1 From table1 where ( (Date1 = '11/30/2005') OR (Date1 = '12/31/2005') ) and Date1 = (Max of previously selected values e.g. '12/31/2005')
What I can't figure out is how to dynamically retrieve the max of 11/31/2005 and 12/31/2005. Any ideas are greatly appreciated.
With this, I can build a query that subtracts the start and end dates to give me the total differences between each record... But, how do I write a query that can count the elapsed time between the current record's "start time" and the prior record's "end time" to give me the elapsed time between each record?
Hello,I have an Access DB with a column [date] holding dates from present day backto around year 1702, and I am trying to run DTS Import with this MDB, whichfails due to a conversion error.MSDN says the datetime data type only allows 1753-01-01 to 1900-01-01, thusI am under the impression having the [date] field as varchar is the only wayto store the dates.My questions are:a) Can dates prior to 1753-01-01 be stored in a date column?b) If dates are in a column of type varchar (or similar) is it possible tosort them chronologically with T-SQL ?c) 1753-01-01 seems particularly arbitrary, any reason for this?Many thanks for any help, it's driving me mad!John
I have a scheduled job that will do a database restore at given time every day. Sometimes I run into a situation where some people leave themselves logged on to the database, which prevents the job from running.
Is there a way that I can set up my job to include killing any open processes against the database that I'm restoring prior to the restore being done?
I've set up a SQL7 database with MSAccess97 as a front end. I'm trying to enter a person with a birthdate prior to 1900, get an ODBC call error, "Datetime field overflow". How to enter dates prior to year 1900? Thanks.
I have a datetime field in a databse for when a tender closes. More often then not its 4pm - for what its worth. And I want to send an email informing the manager 6hrs, 30mins prior and when the tender closes. So I need to somehow check that field either once a day and store those times somewhere or quite regularly ... like every hour.
I would be interested to hear how people would approach such a concept. I havn't got a great deal of experience in triggers or SQL mail.
I am trying to make a connection to sql server using sspi (windows authentication), however, I do not wish to use the built in tokenauthentication system. Administrators are worried about somebody using an unattended logged on computer to gain access to the database. So I am left with using sql server authentication (which I am not fond of doing, more password administration) or what I would like to do is use windows authentication but make the user enter thier windows password prior to logging into sql server. Is there a way? Thanks in Advance.
This is for SQL 2005 and I know how to right click an index and do a "script index as create to new query window"
Basically, the one of the Microsoft scripts or views will tell us that we have 100+ indexes that exist for a database but that they are not being used by SQL server.
I will probably take them "offline" for a while and then drop them later on.
Before dropping them, I would like to be able to recreate them within minutes if system performance is degraded after this happens.
I was working on a script to pull this info out of the sys. tables like below but was wondering if anyone already has a script or an easier way to do this. (...and I don't want to right click 100 indexes within 137 tables and script to a new window and then compile a big script)
I have a situation where our stored procedure inserts records from table_1 to table_2 when they don't already exist (uses the EXIST statement) on that table. If table_1 contains multiple records that are the same, it appears after the 1st record has been inserted, it does not recognize it as being there when it checks the existence when attempting to insert record 2.
Here's an example of the script:
insert into table_2 (col1,col2,col3) select col1,col2,col3 from table_1 t1 where not exists (select '1' from table_2 t2 where t1.col1 = t2.col1 and t1.col2 = t2.col2 and t1.col3 = t2.col3)
Data from Table_1 -- Assume that table_2 does not contain these records
col1 col2 col3 AA 11 A1 AA 11 A1 BB 22 B2
All 3 records would be inserted to table_2 in this example.
We were asked to fix a query to get rows from a prior year history table that did not match to rows in the current year to show a variance from one year to the next. Rows must match on [corpnbr],[plincd],[pgrpcd] and [pitmcd]. If the combination has rows in the current and prior year ([hstyr]) then everything is fine. However, if they have rows in the prior year (e.g. [hstyr]='2014') but not in the current year (e.g. [hstyr]='2015') then they do not show in the result. Below is how they designed the table and below that is the stored procedure to pull the records.
I'm facing a complicated problem and I don't think that the solution will be an easy one.
I have an SQL statement in Oracle which I need to translate it in Sql Server 2005.
select lpad(' ',5*(orderid)) || to_char(descr) as menui from <table _name> where MENU_ITEM not in ('test1','test2','test3') and item_parent not in ('test4,'test5,'test6') start with <item_parent='item_parent' > connect by prior <menu_item = item_parent and menu_name='ADR_m_adr_frame'; > ?(condition)
Somewhere I have read that SQL server does not support Hierarchical Sql statements. Is this true ? How am I going to do that ?
I am trying to set up replication between a central office and multiple sites. This will be merge repl, since either central or satellites can be modified. However, one issue I ran into is setting up subscribers. I don't always have the option of starting with a publisher's snapshot; some of the subscribers have their own data already, which may or may not overlap with central site, and which needs to be synchronized into the central site somehow.
So far, I haven't seen any solution for this kind of scenario, at least nothing out of the box. Am I doomed to having to roll out custom data transfers (I don't think I can use DTS since all sites are running Express), or is there some standard way of doing this?
My overall problem is to do some string manipulation and then DATEADD, but I still don't know why I getting what SQL is giving me.
To get the prior month, I use the following code;
SELECT ( SELECT RIGHT('00' + CAST( CASE CAST(RIGHT(MAX(Eff_Period), 2) AS int) - 1 WHEN 0 THEN 12 ELSE CAST(RIGHT(MAX(Eff_Period), 2) AS int) - 1 END AS varchar(2)), 2) )--, -- Month --CAST(CAST(LEFT(MAX(Eff_Period), 4) AS int) - 1 AS varchar(4)) -- Year FROM Current_Membership;
This correctly gives me the proper month number for the prior month, in this case, '06' since the current month is '07.' But, for debugging, I wanted to test to make sure it would properly adjust for January - '01' - by replacing MAX(Eff_Period) with '201501.'
It did. Worked fine. Then, to make really sure, I put in '201502.'
Instead of the single record of '01,' it shot out 177,209 rows of all '02's!
I put back in the MAX(Eff_Period) instead of the date string constant and, worked fine. Swapped back in the date string - boom! - 177,209 rows again.
Like I said, I can use DATEADD - Now I'm really confused. I changed the date string to '201501' just to verify before posting this, and it spit out 177,209 rows of '01's! So, to keep from altering things and to maintain the edit history, I started up another query and C&P'd everything to it. Then, on the original, I backed up to an earlier version, one that worked. That listing is the same as above, but the commented out comma in line 9 and the commented CAST in the line after it - to get the year - were uncommented.
Now, it works with both '201501' and '201502.' But, if I replace the comments, basically removing the CAST with the year, it slams back with 177,209 rows!
I have a maintenance plan set to back up my 2 primary databases, one about 1 GB, the other about 2GB. I have about 4 GB available on the back up drive. I have set it to delete old backups more than 1 minute old. Problem is, the first night it works fine, the second night it sometimes it refuses to delete the old and therefore does not have enough space to complete the new back-up. Does anyone run into this before? Does it complete both back-ups THEN erase the old, in which case I need to have about 6GB available? Sometimes it works and sometimes it doesn't. It can be going along fine for weeks then stop working. Any info or links to how this is supposed to work would help.
I'm using two different server for application(.net version 1.1) and database(sqlserver200) with win2k3 environment. I'm getting the below error message all the times. I've veryfied the communication between the two server is fine.
"The timeout period elapsed prior to completion of the operation or the server is not responding"
I have a table (represented by #Events) that holds modifications made to another table. I do have some control over the table structure and indexing. I want to pull all of the change records that were made between two dates.
The tricky part is to include the previous version of each record, which will usually be found prior to the start date in question.
The code that I have provided below works. So you can use it to easily see what should be returned. But it's very slow in production.
Any better method to pull this data together?
-- Production version of this table has 4.5 million rows (roughly 1,000 rows per day) -- Primary key is on L4Ident (clustered) -- nonclustered index on ProcessDate, LinkRL4 DROP TABLE dbo.#Events; DROP TABLE dbo.#Results; CREATE TABLE dbo.#Events ( L4Ident int IDENTITY(1,1) NOT NULL,
CREATE TABLE #TEMP ( TYPE VARCHAR(10), SEQ INT, SUB_TYPE VARCHAR(10))
[Code] ....
Now for each type the seq is very important. Effectively by order of seq the subtype stays the same until another subtype changes it. So for TYPE1 100,110 and 150 are A. 170, 200,220 are B. 230 and 250 are C and so on.
However as you can see the data isnt actually stored in the row. I need a select statement that shows this data.
I have done this:
SELECT t1.*,t3.SUB_TYPE FROM #TEMP t1 CROSS APPLY (SELECT MAX(SEQ) SEQ FROM #TEMP AS t2 WHERE t1.SEQ >= t2.seq AND t2.SUB_TYPE <>'' AND t1.TYPE = t2.TYPE GROUP BY t2.TYPE) t2 INNER JOIN #TEMP t3 ON t3.TYPE = t1.TYPE AND t2.SEQ = t3.SEQ
And it seems to work. Is this the easiest way to do it or am i missing something?
I have some my below requirment to loading some last year and currnet year records for some ID's in my table,
We have to load the ID's that are active at the end of the year for the prior year and ID's that are active as of today for the current year.Here is the scenario when the ID is currently terminated but active at the end of the prior year and the record is not in the table.so, we didn’t load the count for the prior year
Here prior year is 2015-2015 and Current year is 2015-2016
CREATE TABLE remp_year (ID INT, STATUS NVARCHAR(100) NULL, START_DATE DATE NULL, END_DATE DATE NULL, date_year nvarchar(10) NULL)INSERT INTO remp_year VALUES (10,'Active','2015-05-26','2015-12-31','2015-2016');
[Code] ...
Here ID 20 and 50 for terminated records is the prior year records so it should count for the last year and those are active in this year those will count for this year.
I would like a way to check a file before attempting to restore it. I want to be able to tell that this is a valid SQL backup file and not just some random file before I attempt a restore.
I’m using DAX to calculate the prior MTD count of a specific column. My data ends on 2/8/2013 and that day's PriorMTD is incorrectly corresponding to 1/31/2013. Whereas, the previous 7 days in February correctly match their corresponding January dates..Below is an image of my pivot table and I have outlined the values in red that are in question.Below are my DAX formulas used each column visible in my image:
Distinct Count of Events:=DISTINCTCOUNT([EventID])CurrentMTD:=CALCULATE([Distinct Count of Events], DATESMTD(Events[EventDate]), ALL (dimDate) )PriorMTD:=CALCULATE([Distinct Count of Events], DATEADD(DATESMTD(Events[EventDate]), -1, MONTH), all(dimDate)) ParallelMonth:= CALCULATE ([Distinct Count of Events], ParallelPeriod(Events[EventDate], -1, MONTH), ALL(dimDate))
