|
sekhar.k wrote: I got one more alternative for that...
The .NET version would be preferable over an SQL-statement; it's obvious what the code does if you read your version. It's not that obvious what the query on the MSys tables does.
Well done
I are Troll
|
|
|
|
|
Hi All, I am trying to create a report for every hour based on a selected day.
I am trying to use sql statement. For example, I have dateTimePicker and I select a dy of a month
then it should get all the data that took place from 12am to 11pm for every hour.
FYI : my database sqlite. coding in C#
thanks for any help in advance.
|
|
|
|
|
And what is the problem?
|
|
|
|
|
Thanks Dmitry, I am not an expert in sql. therefore, I don't know how to do that.
FYI: TableOne has Column Sell, quantity, emID,orderDate,shipDate.
I want to know how many sell was made on a given day. And how many sell was made per hour in a gaven day(24 hours).
|
|
|
|
|
You should create a query with condition, like this:
select count(*) from TaleOne where shipDate between <start_date> and <end_date>
You can google and find how to use queries in sqlite, there are plenty of such info.
|
|
|
|
|
SELECT COUNT(SELL)
FROM TableOne
GROUP BY CONVERT(DATETIME, orderDate, 101)
The convert is to use short date
For the hour look at DatePart[^]
It is always good to read a book or tutorials before jumping to development.
|
|
|
|
|
SQLite doesn't have DatePart ; you should save dates as long in sqlite.
|
|
|
|
|
Thanks all for your help. I will try to do this at the same time I am going to read books on sql
thanks.
|
|
|
|
|
My bad - missed that fact that its a SQlLite question. Nice article by the way.
|
|
|
|
|
Hy to All;
I want to use two insert statement in one stored procedure
I have Two Tables "Activitity" (ActivitityId as a primery key)
and "TODo" Second Table (TODOId as a primery) and (ActivitityId as a foriegn key) ....
So when a value insert into "Activitity" Table ,I want to insert its primery key into "TODO" Table as Foriegn key in a single Stored procedure...
So for this purpose I want to use @@indentity variable
how can I use @@indentity variable in my stored procedure....
|
|
|
|
|
if you have a column set as identity you can use SCOPE_IDENTITY()
DECLARE @MyNewId INT
SET @MyNewId = SCOPE_IDENTITY()
|
|
|
|
|
So store the @@Identity or Scope_Identity() in a variable directly after inserting the activity record and use it in the second insert for todo, there you have your relationship
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Even you can go ahead with OUTPUT.INSERTED
e.g.
insert into Activitity
output inserted.ActivitityId into TODo(ActivitityId)
values(1,'somerecord')
select * from TODo
Output:
TODOId ActivitityId
1 1
Niladri Biswas
|
|
|
|
|
Now that's cool, I was not aware INSERTED worked for an ordinary insert statement, I thought it was only for triggers. Now to go play, I wonder is UPDATED works as well
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Hi guys,
I have created a trigger for auditing ..
I want to keep track of changes made to each field by using a counter.
can you let me I go about achieving this task....
modified on Wednesday, January 13, 2010 5:34 AM
|
|
|
|
|
If I understand your questions correctly, why dont you create an audit table and always add a record before each update.. so you want an update triger[^]
|
|
|
|
|
I am developing an ecommerce website, aimed at selling electronic products.
So for storing the product details I use a product table in which I have the fields for storing the product specifications. My problem is how to include the specifications of different products like motherboard, mouse, Monitor in a single table (product table).
How should I design the product table to hold the specifications of different products. Suggest me a good database design for this problem.
|
|
|
|
|
You can have two tables like this:
In product table:
ProductId
ProductName
ProductType
and in ProductTypeCodes:
ProductTypeCode
ProductType
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
You need to think about the data - I'd get plenty of examples and see how they look - what attributes define a particular product for example.
A possible suggestion is two tables: one to hold products and the other to hold attributes. This is a simplified example of this approach.
Product Table
-------------
ProductID
ProductName
ProductType
Attribute Table
---------------
AttributeID
ProductID
Attribute
Value
Examples:
Products
1, LG XYZ, TFT Monitor
2, AX-999, Motherboard
Attributes
1, 1, Screen Size, 22
2, 1, Colour, Black
3, 2, CPU Type, AMD
4, 2, PCI Sockets, 3
You need to link the tables. Also this is probably too simplistic - think about the units for attributes. Some will numeric others strings.
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
|
|
|
|
|
|
DECLARE @Current_SQL varchar(500)
DECLARE @QueryResult int
....
SET @QueryResult = EXEC(@Current_SQL)
I get error: "Incorrect syntax near the keyword 'EXEC'."
My @Current_SQL query like "Select Count(*) From MyTable Where ID = 1"
Haw can I get return value from EXEC metod.
|
|
|
|
|
You could use an output-parameter, somewhat similar to this;
declare @Current_SQL nvarchar(500)
declare @result bigint
set @Current_SQL = 'select @result = count(*) from [MyTable] where [ID] = 1'
execute sp_executesql @Current_SQL, N'@result int output', @result output Good luck
I are Troll
|
|
|
|
|
|
Hi to All Forum members
I am stuck in finding duration and Net duration in the following procedure
I also adding image Url for better under standing of my requirement.
Please go to this Url ..
http://picasaweb.google.com/lh/photo/203UnHt9l8EnouLOtIKxBg?authkey=Gv1sRgCM6puf-sutfJ2QE&feat=directlink[^]
Following is my T-Sql Query...
Select F.Id as [FleetID], Convert(varchar(30), F.AssignedOn, 113) as [Date], D.DepotName as [Depot], DT.DutyID as [Duty Number],
IsNull(Convert(varchar(30), F.ActualOutSheddingTime, 113),'Not Assigned') as [Actual OutShedding Time],
IsNull(Convert(varchar(30), F.ActualInsheddingTime, 113) ,'Not Assigned') as [Actual Inshedding Time],
IsNull(CONVERT(varchar(5), DATEADD(ms, DATEDIFF(ms, F.ActualOutSheddingTime, F.ActualInsheddingTime), 0), 114) ,'00:00')as [Duration],
IsNull(DT.RestHours, 00) as [ShiftChangeoverTime],
IsNull(CONVERT(varchar(5), DATEADD(ms, DATEDIFF(ms, CONVERT(varchar(5), DATEADD(ms, DATEDIFF(ms, F.ActualOutSheddingTime, F.ActualInsheddingTime), 0), 114), Convert(Datetime, '00:'+ IsNull(Convert(varchar(30), DT.RestHours, 114), 30))), 0), 114),'00:00') as [Net Duration]
From Fleet F
Left Outer Join Duties DT on F.DutyID = DT.ID
Inner Join Depot D on DT.DepotId = D.ID
Inner Join Vehicles V on V.ID = F.VehicleID
Welcome for your valuable suggestions.
Vishnu
Vishnu Narayan Mishra
Software Engineer
|
|
|
|