|
hi tim
problem is...I dont want to do anything with them apart from get the values in the same way you do in MySQL, I just want to get rid of the duplicates that get pulled based on certain criteria.
this is what I'm converting:
SELECT
'PI' as transaction_type,
supplier_codes.supplier_code,
supplier_codes.nominal_code,
COUNT(o.orderline_id) as sale_volume,
IF ( EXTRACT(MONTH FROM STR_TO_DATE(CONCAT(YEARWEEK(dispatch_date),'0'),'%x%v%w')) = MONTH(dispatch_date),
DATE_FORMAT(STR_TO_DATE(CONCAT(YEARWEEK(dispatch_date),'0'),'%x%v%w'),'%d/%m/%Y'),
DATE_FORMAT(STR_TO_DATE(CONCAT('01/',EXTRACT(MONTH FROM dispatch_date),'/',YEAR(dispatch_date)),'%d/%m/%Y'),'%d/%m/%Y')) as my_dispatch_date,
resource.name as resourcename,
resource.reference,
SUM(supplier_codes.license_fee) as royalty_amount,
tax_codes.code,
ROUND(((SUM(supplier_codes.license_fee) / 100) * tax_codes.multiplier),2) as tax_amount,
"1" as blank_field_2,
"_" as blank_field_3,
"_" as blank_field_4
FROM order_line o
LEFT JOIN resource ON o.resource_id = resource.resource_id
LEFT JOIN supplier_codes ON resource.reference = supplier_codes.reference
LEFT JOIN partners ON supplier_codes.partner_id = partners.partner_id
LEFT JOIN partner_type ON supplier_codes.partner_type_id = partner_type.partner_type_id
LEFT JOIN tax_codes ON partners.tax_code = tax_codes.id
WHERE supplier_codes.payment_start_date <= dispatch_date
AND supplier_codes.payment_end_date >= dispatch_date
AND o.status = 1
AND DATE_FORMAT(o.dispatch_date,'%Y-%m-%d') >= '$from'
AND DATE_FORMAT(o.dispatch_date,'%Y-%m-%d') < '$to'
AND o.dm !='ZX'
GROUP BY reference, supplier_code, my_dispatch_date
ORDER BY dispatch_date, reference
dont worry about the difference in some of the vars in it, its only the group by thats doing my head in
|
|
|
|
|
Ok.... in SQLServer, a group by is used if there are any aggregate fields (sum, count, etc).
In your original query (from MySQL), there is a COUNT and 2 SUM aggregate fields.
If you want the sales volumne (COUNT), royalty amount (SUM) and tax amount (SUM) in your SQLServer query, GROUP BY any non-aggregate fields:
GROUP BY supplier_codes.supplier_code, supplier_codes.nominal_code, resource.name, resource.reference, tax_codes.code
The GROUP BY clause must go after the WHERE and before the ORDER BY
If you are currently getting duplicate records, change the SELECT to SELECT DISTINCT.
Hope that helps...
Tim
|
|
|
|
|
cheers tim
that helped a lot,i've been looking at this (migration overall) for about a week now and i can't really see the trees for the woods anymore, my brains withered.
ta
tim
|
|
|
|
|
Hi,
I am looking to return all the rows where the OldReferenceNumber field is equal to the value of the parameter, currently it returns nothing, there are a couple of fields that are null:
DECLARE @OldReferenceNumber VARCHAR(50);
SET @OldReferenceNumber = NULL;
SELECT
OldReferenceNumber
FROM
Product
WHERE
OldReferenceNumber = @OldReferenceNumber;
Please can some one help me?
Thanks
Brendan
|
|
|
|
|
You cannot reference nulls with =
WHERE
coalesce(OldReferenceNumber,'') = coalesce(@OldReferenceNumber,'')
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks, this works just fine. I just need to modify it a little bit. Lets say the colum does not contain a NULL value, and maybe some text like Old Reference Number. I need this WHERE clause modified so that if the user types in old ref that it still returns the row. Normally I would use a LIKE with a '%' but I can not seem to get it to work here.
Thanks
Brendan
|
|
|
|
|
SET @OldReferenceNumber = NULL;
OldReferenceNumber = @OldReferenceNumber;
That won't work
|
|
|
|
|
SET @OldReferenceNumber = NULL;
OldReferenceNumber = @OldReferenceNumber;
That won't work, you need
OldReferenceNumber IS NULL
Or some "not exist" value
SET @OldReferenceNumber = -1;
ISNULL(OldReferenceNumber,-1) = @OldReferenceNumber;
|
|
|
|
|
Or as I said originally,
WHERE coalesce(OldReferenceNumber,'') = coalesce(@OldReferenceNumber,'')
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Yeah, but that's ugly.
|
|
|
|
|
Ugly but correct. Coalesce is the new ISNULL
Bob
Ashfield Consultants Ltd
|
|
|
|
|
|
Hi,
I have an application , which has a thread that checks the DB existance by opening a new connection.
When the open statement is executed when some dialog gets destroyed in the main thread of the application. The database open throws exception with the error "General error: invalid window handle".
What could be the reason for it?
Is there any solution to overcome it.
|
|
|
|
|
Hi,
I am 2 fields in my Product table, namely RandAmount and ForeignAmount. When they do a search, there is are 2 radio buttons given the user the option to search for rand amount or foreign amount. And then there is a texbox for the value. Here is my where clause which does not work, it must just work on 1 of the 2 fields, if @CurrencyInd is 1 then it searches on rand amount, and when it is 2 then it searches on foreign amount:
WHERE
p.RandAmount = (CASE @CurrencyInd WHEN 1 THEN @CurrenyAmount ELSE p.RandAmount END)
AND p.ForeignAmount = (CASE @CurrencyInd WHEN 2 THEN @CurrenyAmount ELSE p.ForeignAmount END)
I have also tried the following, but also does not help:
WHERE
CASE @CurrencyInd
WHEN 1 THEN (p.RandAmount = @CurrenyAmount)
WHEN 2 THEN (p.ForeignAmount = @CurrenyAmount)
END
Please can someone help?
Thanks
Brendan
<div class="ForumMod">modified on Tuesday, August 12, 2008 5:39 AM</div>
|
|
|
|
|
|
|
hey text format is not supports thats y its shows like that
|
|
|
|
|
where (@currencyInd = 1 AND p.RandAmount = @CurrenyAmount) or
(@CurrencyInd = 2 and p.foreignamount = @CurrenyAmount)
|
|
|
|
|
Try
where (@currencyInd = 1 AND p.RandAmount = @CurrenyAmount) or
(@CurrencyInd = 2 and p.foreignamount = @CurrenyAmount)
|
|
|
|
|
Create table test(Days char(3),ids int,subject char(10))
Insert into test
Select
'Mon',1,'Eng'
Union
Select 'Mon',2,'Maths'
Union
Select 'Tue',1,'Eng'
Union
Select 'Tue',2,'Maths'
Union
Select 'Wed',1,'Eng'
Union
Select 'Wed',2,'Maths'
Select * from test
Select Distinct Days from Test
Drop table days
Create table days(Hours int,mon Char(20),Tue Char(20),Wed Char(20),Thu Char(20),Fri Char(20),Sat Char(20))
Insert Into Days(Hours)
Select 1 Union Select 2
Select * from test Order by IDS,Days
Select * from Days
Declare @IDS Char(3),@Days Char(3),@Subject Char(10),@SQL Varchar(8000)
Declare Cur Cursor For Select IDS,Rtrim(Days),Rtrim(Subject) from test Order by Days,IDS
Open Cur
Fetch Cur into @IDS,@Days,@Subject
While(@@Fetch_Status=0)
Begin
Set @SQL='Update Days Set '+@Days+'= '''+@Subject+''' Where Hours ='+@IDS+'
'
Print @SQL
Exec (@SQL)
Fetch Next from Cur Into @IDS,@Days,@Subject
End
Close Cur
Deallocate Cur
--Set @SQL='Update Days Set '+ @Days +' ='+ @Subject +' Where Hours ='+ @IDS +'
|
|
|
|
|
Have you tried to work through the problem or are you just asking someone to do it for you?
|
|
|
|
|
Create table test(Days char(3),ids int,subject char(10))
Insert into test
Select
'Mon',1,'Eng'
Union
Select 'Mon',2,'Maths'
Union
Select 'Tue',1,'Eng'
Union
Select 'Tue',2,'Maths'
Union
Select 'Wed',1,'Eng'
Union
Select 'Wed',2,'Maths'
Select * from test
Select Distinct Days from Test
Drop table days
Create table days(Hours int,mon Char(20),Tue Char(20),Wed Char(20),Thu Char(20),Fri Char(20),Sat Char(20))
Insert Into Days(Hours)
Select 1 Union Select 2
Select * from test Order by IDS,Days
Select * from Days
Declare @IDS Char(3),@Days Char(3),@Subject Char(10),@SQL Varchar(8000)
Declare Cur Cursor For Select IDS,Rtrim(Days),Rtrim(Subject) from test Order by Days,IDS
Open Cur
Fetch Cur into @IDS,@Days,@Subject
While(@@Fetch_Status=0)
Begin
Set @SQL='Update Days Set '+@Days+'= '''+@Subject+''' Where Hours ='+@IDS+'
'
Print @SQL
Exec (@SQL)
Fetch Next from Cur Into @IDS,@Days,@Subject
End
Close Cur
Deallocate Cur
--Set @SQL='Update Days Set '+ @Days +' ='+ @Subject +' Where Hours ='+ @IDS +'
|
|
|
|
|
hello,
i m trying to insert data into a database file while developing a desktop application in .net, but even when the ExecuteNonQuery() return 1 the data is visible while the application is running, but when i debug the application again the inserted data is lost........
please help me out with this. i m sending the code below...........
app.config file:
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database\LeaveMgmt.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" >
code snippet:
SqlConnection mycon = new SqlConnection(Properties.Settings.Default.LeaveMgmtConnectionString);
string insCmd = "Insert into lmsLeaveInfo values ('" + empid + "','" + DateTime.Now + "','" + dtpLeaveFrom.Value +"','" + dtpLeaveTo.Value + "','" + cmbReason.Text + "',0)";
if (checkFields())
{
try
{
mycon.Open();
SqlCommand cmd_Ins = new SqlCommand(insCmd, mycon);
cmd_Ins.ExecuteNonQuery();
mycon.Close();
clearFields();
}
}
|
|
|
|
|
Hi,
Quick guess would be that you have a pending transaction which needs to be committed.
Perhaps the easiest way to see this is to run the code to any point where you think the data would be persisted and then see from SQL Server Management Studio if you have locks on the data. If you have locks (specifically X-locks), the transaction is active.
Hope this helps,
Mika
|
|
|
|
|
Hi all .
I Wount To Connection To Sql Server 2005 By NetWork , But The Sql Server Give Me The Error (Can't Connection To Sql server Error 26 ) The Error provider:SQL Network Interfaces, error:26 - Error Locating Server/Instance Specified .
And The
SQL Server Surface Area Configuration ------>
Surface Area Configuration for Services and Connections ---->
Remote Connections------------->
Local and remote connections -------> TCp/IP
But the Error is Stile .
Who I Can Connection To Sel Server 2005 by the NetWork ??
Thanks For Any Boudy Hellp Me
Thaer
modified on Tuesday, August 12, 2008 6:07 AM
|
|
|
|
|