|
Colin Angus Mackay wrote: Reporting databases tend to be denormalised because what is efficient for reporting is the complete opposite to what is efficient for OLTP.
thanks. I will give it a try.I am getting use to the gray aeras in my code.
|
|
|
|
|
I want to produce a table of counts, by month, for each part type I have in inventory. Here is my table defintion:
tbl_MainReport.ReportID - primary key
tbl_MainReport.Author - text
tbl_MainReport.POI_Time - purchased date
tbl_MainReport.PartType - 1..5 (1=wrench,2=hammer,3=driver,4=plier,5=knife)
tbl_MainReport.PartSubType - 1..8
tbl_MainReport.Description - memo
I have created a SQL statement that comes close, but groups by the "raw" date (MM/DD/YYYY HH:mm:ss) and not just by months:
TRANSFORM Count(*) AS TotalByPartType
SELECT tbl_MainReportInfo.POI_Time
FROM tbl_MainReportInfo
GROUP BY tbl_MainReportInfo.POI_Time
ORDER BY tbl_MainReportInfo.POI_Time
PIVOT tbl_MainReportInfo.PartType;
This creates a listing like this:
POI_Time | 1 | 2 | 3 | 4 | 5
m/d/yyyy h:mm:ss #
m/d/yyyy h:mm:ss #
m/d/yyyy h:mm:ss #
...
But I want something like this:
POI_Time | 1 | 2 | 3 | 4 | 5
JAN ## ##
FEB ## ##
...
Can you help? Should I just revert to coding up a "Recordset"?
Thanks,
Johnny
|
|
|
|
|
john john mackey wrote: Can you help? Should I just revert to coding up a "Recordset"?
No. Your SQL Statement is almost there.
You need to group by month - which you don't have. So you'll have to create it from the POI_Time which you do have.
Your GROUP BY clause needs to look something like this:
GROUP BY datepart(year, POI_Time), datepart(month, POI_Time)
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Thanks for the clue, but I'm getting an error when I do:
TRANSFORM Count(*) AS TotalByPartType
SELECT tbl_MainReportInfo.POI_Time
FROM tbl_MainReportInfo
GROUP BY DatePart("m",tbl_MainReportInfo.POI_Time)
ORDER BY tbl_MainReportInfo.POI_Time
PIVOT tbl_MainReportInfo.PartType;
Error is: You tried to execute a qry that does NOT include the specified expression 'POI_Time' as part of an aggregate function.
I have also tried removing the ORDER BY and changing the SELECT to SELECT datepart("m",tbl_MainReportInfo.POI_Time)...
but have not been successful.
Johnny
|
|
|
|
|
If got a table named Horses with column Id and Horse_Name
and a table named Riders with column Id and Rider_Name.
Now i want a dataset using the third table named Stables and column IdHorses, IdRiders and Rider_NF.
How should i create a select statement selecting the Horse_Name and Rider_Name sorted on Rider_NF
Anny suggestions and code are welcom
|
|
|
|
|
Your SQL statement will look like this:
SELECT h.Horse_Name, r.Rider_Name, s.Rider_NF
FROM Stables AS s
INNER JOIN Horses AS h ON h.Id = s.IdHorses
INNER JOIN Riders AS r on r.Id = s.IdRiders
ORDER BY s.Rider_NF
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
It gives a error on Horses and Riders as a invalid object name.
I produced something like this and it seems to work:
SELECT *, Boekelo_Riders.Rider_Given_Name + ' ' + Boekelo_Riders.Rider_Family_Name
AS Rider_Full_Name
FROM Boekelo_Horses, Boekelo_Riders, Stables
WHERE Stables.IdHorse=Boekelo_Horses.Id
AND Stables.IdRider=Boekelo_Riders.Id
ORDER BY Stables.rider_NF, Boekelo_Horses.horse_name
The only thing i am missing is to add a extra column that contains a substitute for the rider_NF that is contained in a Country table.
In that table is the fullname of the riders national federation.
It may be that my solution contains a pitfall.
|
|
|
|
|
Sorry but found the problem now.
SELECT Boekelo_Horses.Horse_Name, Boekelo_Riders.Rider_Family_Name, Stables.Rider_NF, Country.LangEng FROM Stables AS Stables
INNER JOIN Boekelo_Horses
AS Boekelo_Horses ON Boekelo_Horses.Id = Stables.IdHorse
INNER JOIN Boekelo_Riders
AS Boekelo_Riders ON Boekelo_Riders.Id = Stables.IdRider
INNER JOIN Country
AS Country ON Country.NFid = Stables.Rider_NF
ORDER BY Stables.Rider_NF, Boekelo_Horses.Horse_Name
But still one question remains, if a Rider_NF is not on the Country table it should be on the list, ?use a other type of join
|
|
|
|
|
gharry wrote: But still one question remains, if a Rider_NF is not on the Country table it should be on the list, ?use a other type of join
LEFT OUTER JOIN because you want everything from Stables regardless if it is joined with other tables. In the case where there in no join with other tables then those columns will appear as null in the result set.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
What is a named variable and why use it?
The variable @namedVariable is a "named variable."
The "@" prefix is required for SQL Server named parameters.
|
|
|
|
|
kenn_rosie wrote: What is a named variable and why use it?
It can be used like a variable in a stored procedure, or passed as a parameter to a query.
Why use a variable in any programming language?
Also, for security in passing data through from client applications. See SQL Injection Attacks and Tips on How to Prevent Them[^]
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Hi,
Whenever a user modifies a customers data I create a log with only the id’s(int) and the datemodified(datetime). Then I want to view the 10 latest customers that the user modified, sorted by datemodyfied and with unique customernames.
So I want to select Customers.customerid and Customers.customername sorted by entries in table CustomerLogs, sorted by CustomerLogs.datemodified. No doubles of customername allowed.
I’ve tried some different joins but nothing works exactly as I want.
In a WHERE clause I have author_userid=2 AND owner_companyid=1
Can somebody give some advice, please?
I use SQL Server 2000
TABLE Customers
Customerid | customername
TABLE CustomerLogs
customerlogid | customerid | author_userid | datemodified | owner_companyid
|
|
|
|
|
I think it may look something like this:
SELECT TOP 10
Customers.customerid,
Customers.customername,
Log.LastModified
FROM
Customers
INNER JOIN
(SELECT customerId, Max(dateModified) AS LastModified
FROM CustomerLogs
GROUP BY customerId) as Log
ON (Customers.customerId = Log.customerId)
ORDER BY LastModified DESC
If your log is large - you will want an index on (customerId, dateModified)
|
|
|
|
|
Thanks Michael!
You really made my day!
Your solution works excellent!
Have a nice day!
|
|
|
|
|
I need to sort a column using dynamic sort order.
I have a table name Account with three fieds. Type, Balance and Category.
Type can be Assets,Liablity,Revenue and Expenses
Balance is Debit or Credit
Category can be Current Assets,Net Property and Equipment, Non current Assets, Current Libality,
Long Term Liablities, Equity ,Sales , Cost of Sales ,
I need to sort the category column and Type column in a dynamic order. The above order and not ascending and descending.
Is there any way i can do that.
I tried the below example but gives me syntax error
SELECT Type,Balance,Category, sort_order = (CASE WHEN Category ="Current Assets" THEN 1
ELSE WHEN Category = 'Net Property and Equipment'THEN 2
ELSE WHEN Category ='Non current Assets' THEN 3
ELSE WHEN Category ='Current Libality' THEN 4
ELSE WHEN Category = Long Term Liablities'THEN 5
ELSE WHEN Category = 'Equity' THEN 6
ELSE WHEN Category = 'Sales' THEN 7
ELSE WHEN Category ='Cost of Sales' THEN 8
ELSE 0 END)
FROM Account;
smile8691
|
|
|
|
|
The sort_order column is only used for determining the order in which the rows are returned and not required otherwise.
SELECT Type,Balance,Category
FROM Account
ORDER BY (CASE WHEN Category ="Current Assets" THEN 1
ELSE WHEN Category = 'Net Property and Equipment'THEN 2
ELSE WHEN Category ='Non current Assets' THEN 3
ELSE WHEN Category ='Current Libality' THEN 4
ELSE WHEN Category = Long Term Liablities'THEN 5
ELSE WHEN Category = 'Equity' THEN 6
ELSE WHEN Category = 'Sales' THEN 7
ELSE WHEN Category ='Cost of Sales' THEN 8
ELSE 0 END);
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Thanks.
fsdfsdfsdfsdfsdfsdf
|
|
|
|
|
I have two servers which I am trying to get merge replication working with. The kicker of this issue is that the servers are running on non-standard ports. So, my usual connection string of SERVER01\SQLInstance,1234 (1234 being the non-standard port) and SERVER02\SQLInstance,1234 which works for sqlcmd and sqlwb as well as a .NET application will not work for SQL Replication.
When I include the port in my connection string (SERVER01\SQLInstance,1234), which otherwise works, for a subscription, I get the error:
SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name 'SERVER02\SQLInstance'. (Replication.Utilities)
But when I use no port (SERVER01\SQLInstance), I get this error:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server)
Any ideas on how to proceed?
|
|
|
|
|
Are both servers working on SQL2005 or just one.
There seems to be problems moving data from 2000 to 2005 viseversa
|
|
|
|
|
Hai
When i try to attach a DB that was detached from another SQL Server 2005 instance. i got this error
The database 'Content' cannot be opened because it is version 611. This server supports version 603 and earlier. A downgrade path is not supported.
Could not open new database 'Content'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 948)
How can i slove this Error, since i am using a higher version ?
Regards
-- modified at 5:24 Wednesday 11th January, 2006
|
|
|
|
|
It looks like you developed the database on SQL Server 2005, release version, and are trying to restore it to a SQL Server 2005 June CTP release. That's not going to work and there is no conversion tool to make it work either.
You'll have to script the database out to an SQL file, export the data to another file, then run the script on the older SQL Server installation and import the data.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
hi,
I am trying to bind the dataGrid to the dataset.I am getting the following error
Login failed for user 'XXXX\ASPNET'.
I am using the server explorer in the VISUAL STUDIO.NET to create the connetion and data adapter.I dragged the table from the server explorer.
The database in the server Explorer is connected using the windows authentication.
Can any one explain me why I got this error and How to resolve this error.
Thanks,
Satish.
satishrg
|
|
|
|
|
satishrg wrote: Can any one explain me why I got this error
Because your ASP.NET application runs in its own limited security context to protect your machine from mallicious attack via the web. SQL Server has not granted any permissions to this account.
satishrg wrote: How to resolve this error
You need to create a login in SQL Server to the account named and then to grant that login permission for the database you want to use and the objects (tables, views, stored procedures, functions) that are called by the application. Do NOT under any cicumstances put your ASPNET user in SQL Server in the sysadmin group because then if there is a hole in the security in the way your web application talks to the SQL Server you've just given the attacker carte blanche to do anything they like with your database and potentially with your OS.
ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
|
|
|
|
|
Hello,
I have a stored procedure that when run directly from SQL Management Studio (with SQL Server 2000) takes about 2 or 3 seconds at most to return around 40 rows.
When I try to call that SP from my code using a data adapter, it times out (and I set the timeout to 5 minutes ). It happens always. If I limit the results I want to 2 or 3 rows, calling the SP directly takes less than a second, and from my app it takes about a minute.
I tried using a data reader and it's just as slow.
I create the connection just before creating the data adapter, and I let the Fill call open it. But for the data reader attempt, I opened the connection myself. No luck, still as slow.
[EDIT: And I tried running the app locally (same machine as SQL Server) and from another machine in the network. Same results.]
Any ideas? This is driving me nuts!
Thanks in advance,
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
-- modified at 23:03 Tuesday 10th January, 2006
|
|
|
|
|
Please, point out more detail problem (such as table structure, SQL statement...) so you can reach a help
|
|
|
|
|