|
Try this
declare @tblAccountList table(TwinPack int)
insert into @tblAccountList
select 111 union all select 222 union all
select 333 union all select 444 union all
select 555 union all select 666 union all
select 777 union all select 888 union all select 1100
declare @tblBudget table(TwinPack int,Mnth varchar(20))
insert into @tblBudget
select 111,'Jan' union all select 222,'Feb' union all
select 333,'Mar' union all select 444,'Apr' union all
select 555,'May' union all select 666, 'Jun' union all
select 777,'Jul' union all select 888 ,'Aug' union all
select 999,'Sep' union all select 1000 ,'Oct' union all
select 1100,'Nov' union all select 1200 ,'Dec'
SELECT a.TwinPack
FROM @tblAccountList a
except
SELECT b.TwinPack
FROM @tblBudget b
WHERE b.Mnth = CONVERT(varchar(3),DATENAME(mm,GETDATE()))
Basically I am finding out the TwinPack from @tblAccountList where the budget is not in November. I am using Except here
Note- I have done the schema by myself which I hope is as per ur requirement.Else you please correct and I will again give a shot.
Hope this helps
Niladri Biswas
|
|
|
|
|
|
Try this
SELECT [Account List].[Twin Pack]
FROM [Account List]
WHERE NOT EXISTS (SELECT 1 from Budget
WHERE Budget.[Twin Pack] = [Account List].[Twin Pack]
and Budget.Month=DateValue([Date])
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
So I have a table Invoices with ID type Guid. But the user wants to have a unique, human-friendly code, to go along w/ each row. Any good suggestions on how to implement this?
Should I have the client app check->generate->update, or should I have some sort of scalar function generate this (auto-compute column value). Maybe a trigger? Thoughts appreciated.
(running mssql'08)
|
|
|
|
|
Keep you guid as the primary key (I hate guids) and add a CustomerCode field, populate the initial set of data according to the clients needs and let the operator manage the code from then on.
NEVER and I mean not once do you use this field for anything but human UI searches, continue to use the guid as the key constraint for you data.
|
|
|
|
|
|
hii ,
i have question and i wont ask this question in form of the example.
if we have table named CUSTOMER:
CUSTOMER TABLE:
CustomerID : Integer (Not a primary Key)
CustomerName: TEXT
And This is the table's data:
CustomerId | CustomerName
12 | Jone
25 | Mark
25 | Marc
10 | AJ we note that CustomerId number 25 has deffrint spiling of the CustomerName for the same Customer!
i need to return the unique CustomerId with the first CustomerName.
i write this querey :
select DISTINCT CustomerID , CustomerName
From Customer
but it got me the four records !! the 25 is dublicated twies becuse the spiling of the name are deffrint for the same Customer
how to write querey return the unique CustomerId with the first CustomerName?
Many Thanks
|
|
|
|
|
Maybe it ins't the best solution but you can use a group by (CustomerId), then you have to choose wich CustomerName is the choosen (min(CustomerName), max(CustomerName...). Sql:
>select CustomerId, max(CustomerName) from Customer group by CustomerId.
Visit my blog at http://dotnetforeveryone.blogspot.com
|
|
|
|
|
 Ohhh...Yes it is work !! many thanks
|
|
|
|
|
SELECT
@customerId as customerId,
(SELECT TOP 1 customerName FROM Customer WHERE customerId=@customerID ORDER BY customerName) AS customerName
Whenever your data looks this bad, its a sure-fire signal to clean your data, and ensure your apps only insert clean data.
|
|
|
|
|
hii ,sorry what do you mean by sure-fire signal ? 
yes i agree with you that the data is look bad , but i work on a huge data that which is extracted by the Customers them self so i can not edit any thing. 
many thanks
|
|
|
|
|
Member 3018046 wrote: hii ,sorry what do you mean by sure-fire signal
It means that it is time to examine why you have 2 customer records with the same Id, but different names.
|
|
|
|
|
You can even try this
declare @tblcustomer table(customerid int,customername varchar(50))
insert into @tblcustomer
select 12,'Jone' union all select 25,'Mark' union all
select 25,'Marc' union all select 10,'AJ'
select x.customerid
,x.customername
from
(
select
ROW_NUMBER() over (partition by customerid order by customerid) as rn
,customerid
,customername
from @tblcustomer)X(rn,customerid,customername)
where rn = 1
Output:
customerid customername
10 AJ
12 Jone
25 Mark
Niladri Biswas
|
|
|
|
|
How do you know which is the first CustomerName? Is the 'first' name always the correct one?
|
|
|
|
|
I have a SQL database .mdf file.I want to detach this database mdf file and then copy the file into a new directory using VisualBasic code. How to do this?
|
|
|
|
|
I recommend that you BACKUP the database instead of copying the MDF.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I was trying to backup the database file by copying it from one folder to another, but it is throwing error that file is in use. So I thought of detach it first and then copy.
So How to do this?
|
|
|
|
|
The file is in use because the (SQL) server is using it - always. There are some excellent examples of how to take a backup correctly[^] right here on CP
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Use this :
BACKUP DATABASE [DataBaseName]
TO DISK = N'D:\DataBases\BackupDB.bak' WITH
NOFORMAT,
NOINIT,
NAME = N'DataBaseBackup-Name',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
Create an object of SqlCommand and pass this as CommandText.
Run this in master Database (I mean the connection object should point to master database)
Use ExecuteNonQuery to take backup to Disk location mentioned.
![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
Hi
I am trying to create a database using T-SQL and i am getting the following error message.
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\saledat.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
I am using SQL server express 2005 which I recently installed on a new desktop.
The script I am using to create the database is as follows.
USE master;
GO
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\saledat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB ) ;
GO
I don't know what could be causing this error, and I would appreciate any help granted.
Thanks in advance
|
|
|
|
|
I'm not certain, but doesn't
twsted f8 wrote: MSSQL10.MSSQLSERVER
refer to a SQL Server 2008 instance? (Does on my machine)
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
If you are creating this on a server then make sure that path exists ON THE SERVER.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
On my system the path for SQL Express 2005 is:
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL1\MSSQL\DATA\saledat.mdf',
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
I ran the script and it created a Sql Server 2008 database in the specified folders. The path the op is using is definitely for 2008, and if he was using a 2005 instance then the default path should have been what you posted - notice no comments from the op for over 4 hours.
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
Still no response after 18hrs. At this rate you'll be on your Hols before we know if the problem was resolved.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
