Database Discussion Boards

Bassam Saoud8-Jan-10 4:38

8-Jan-10 4:38

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.

Re: sqlite

Dimitri Witkowski8-Jan-10 4:43

Dimitri Witkowski

8-Jan-10 4:43

SQLite doesn't have DatePart; you should save dates as long in sqlite.

See my article about Windows 7 Taskbar timer here on CodeProject

Re: sqlite

jashimu8-Jan-10 5:03

jashimu

8-Jan-10 5:03

Thanks all for your help. I will try to do this at the same time I am going to read books on sql
thanks.

Re: sqlite

Bassam Saoud8-Jan-10 6:58

Bassam Saoud

8-Jan-10 6:58

My bad - missed that fact that its a SQlLite question. Nice article by the way.

I want to use @@indentity Scalar variable in Stored procedure

Muhammad Fahim Baloch8-Jan-10 3:56

Muhammad Fahim Baloch

8-Jan-10 3:56

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....

Re: I want to use @@indentity Scalar variable in Stored procedure

Bassam Saoud8-Jan-10 4:32

Bassam Saoud

8-Jan-10 4:32

if you have a column set as identity you can use SCOPE_IDENTITY()

DECLARE @MyNewId INT
SET @MyNewId = SCOPE_IDENTITY()

Re: I want to use @@indentity Scalar variable in Stored procedure

Mycroft Holmes8-Jan-10 16:49

Mycroft Holmes

8-Jan-10 16:49

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

Re: I want to use @@indentity Scalar variable in Stored procedure

Niladri_Biswas8-Jan-10 17:51

Niladri_Biswas

8-Jan-10 17:51

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

Re: I want to use @@indentity Scalar variable in Stored procedure

Mycroft Holmes9-Jan-10 13:43

Mycroft Holmes

9-Jan-10 13:43

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

Re: I want to use @@indentity Scalar variable in Stored procedure

Niladri_Biswas10-Jan-10 2:06

Niladri_Biswas

10-Jan-10 2:06

Hi Mycroft,
You are right.
It is applicable for Insert, Update & Delete.

Have a look

a) OUTPUT Clause (Transact-SQL)[^]

b) SQL SERVER – 2005 – OUTPUT Clause Example and Explanation with INSERT, UPDATE, DELETE[^]

Niladri Biswas

Accidentally posted to wrong forum. My apologies--- Trigger Help [modified]

spankyleo1238-Jan-10 3:05

spankyleo123

8-Jan-10 3:05

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

Re: Accidentally posted to wrong forum. My apologies--- Trigger Help

Bassam Saoud8-Jan-10 4:36

Bassam Saoud

8-Jan-10 4:36

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[^]

Database Design

Gjm8-Jan-10 2:14

Gjm

8-Jan-10 2:14

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.

Re: Database Design

dan!sh 8-Jan-10 3:06

dan!sh

8-Jan-10 3:06

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...!!

Re: Database Design

riced8-Jan-10 5:56

riced

8-Jan-10 5:56

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

Re: Database Design

Gjm8-Jan-10 14:43

Gjm

8-Jan-10 14:43

thanks

EXEC Return Value

dataminers7-Jan-10 7:05

dataminers

7-Jan-10 7:05

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.

Re: EXEC Return Value

Eddy Vluggen7-Jan-10 8:57

Eddy Vluggen

7-Jan-10 8:57

You could use an output-parameter, somewhat similar to this;

SQL

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 Suspicious | :suss:

Re: EXEC Return Value

Dimitri Witkowski7-Jan-10 8:59

Dimitri Witkowski

7-Jan-10 8:59

EXEC is used to execute stored procedures; in your case you need run sql statement. It's done by the stored procedure sp_executesql, an example can be found here: http://msdn.microsoft.com/en-us/library/ms188001.aspx[^]

See my article about Windows 7 Taskbar timer here on CodeProject

Problem in Finding Hours Duration Between two Dates

Vishnu Narayan Mishra7-Jan-10 3:03

Vishnu Narayan Mishra

7-Jan-10 3:03

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

Re: Problem in Finding Hours Duration Between two Dates

Vishnu Narayan Mishra7-Jan-10 3:23

Vishnu Narayan Mishra

7-Jan-10 3:23

Hi
Actually Duration is coming right in this query and the issue is Net Duration is not coming right.

Vishnu Narayan Mishra
Software Engineer

problem for build a query

Rupesh Kumar Swami7-Jan-10 2:41

Rupesh Kumar Swami

7-Jan-10 2:41

hi all,
i want to build a query but not succesful

i have two table named A and B

A table has field id(N), title(Varchar)
B table has field mid(N), id(N)

so i want to id, title from A table and count of id from B table

let me explain with data

Table A data

1 title1
2 title2
3 title3

Table B data

so i want to following result

id title   count
1  title1   4
2  title2   3
3  title3   2

Please suggest how can build this query?

rup28aug@yahoo.co.in
Rupesh Kumar Swami
Software Developer,
Integrated Solution,
Bikaner (India)

Company - ISOL, India

Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

Re: problem for build a query

Dimitri Witkowski7-Jan-10 2:50

Dimitri Witkowski

7-Jan-10 2:50

Here's the idea how to do this:

SQL

SELECT A.id, A.title, COUNT(*) as cnt 
FROM A left join B on A.id = B.id
GROUP BY A.id, A.title

See my article about Windows 7 Taskbar timer here on CodeProject

Re: problem for build a query

i.j.russell7-Jan-10 3:05

i.j.russell

7-Jan-10 3:05

SELECT A.id, A.title, S.total 
FROM A 
LEFT OUTER JOIN 
(
	SELECT id, COUNT(id) as total
	FROM B
	GROUP BY id
) S on S.id = A.id
ORDER BY A.id ASC

How to make a query stronger in SQL Server

Member 42602707-Jan-10 0:30

Member 4260270

7-Jan-10 0:30

Hi
I have a select query.The test team told me that the query is weak.So how can I make a query stronger in SQL Server.

Thanks
Denny

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.