Query probs............., - Database Discussion Boards

Re: Help needed for an user defined Function

Exelioindia9-Jul-07 23:02

Exelioindia

9-Jul-07 23:02

Hi
thanks a lot. I need this only. Thank you very much

Know is Drop, Unknown is Ocean

Re: Help needed for an user defined Function

gauthee9-Jul-07 23:26

gauthee

9-Jul-07 23:26

you can use the count function too!

select @row_count = count([country_code]) from.....

Gautham

Right Outer Join Probs...

Member 38798819-Jul-07 20:19

Member 3879881

9-Jul-07 20:19

SELECT DISTINCT dbo.magdates.mydate, EM.empId AS EId, EM.empName AS EName,
TC.carddate, TC.shift, PM.location, TC.reportingtime AS FromTime,
TC.leavingtime AS ToTime, MHD.AppWHours AS Reg, MHD.AppOtHours AS Ot,
MHD.AppSHours AS SReg, MHD.AppSOtHours AS SOt, SD.Whrs
FROM dbo.tblTimeCardmain TC
INNER JOIN dbo.tblEmployeeMaster EM ON EM.empId = TC.empid
INNER JOIN dbo.tblsalarydetails SD ON SD.EmpID = TC.empid
INNER JOIN dbo.tblProjectMaster PM ON PM.projectID = TC.projectid
INNER JOIN dbo.tblMonthHourDetails MHD ON MHD.EmpId = TC.empid
RIGHT OUTER JOIN dbo.magdates ON TC.carddate = dbo.magdates.mydateWHERE (EM.empId = 213) AND (DATEPART(mm, TC.carddate) = 6) AND
(DATEPART(yyyy, TC.carddate) = 2007) ORDER BY TC.carddate

Hi this is my query...,
See in that magdates table contain dates between 6/1/2007 to 6/30/2007...,
But the tbltimecardmain contain dates between 6/1/2007 to 6/28/2007 only...,
So i used right outer join and selected all the dates from magdates table...,
But its giving only what are the dates contain tbltimecardmain those dates only...,
But i want show the dates 6/29/2007 and 6/30/2007 also...,
How to display magdates all the dates...,
hlp me plz,
Advance Thanks,

Regards,
Magi

Passing an array to stored procedures

M. J. Jaya Chitra9-Jul-07 18:40

M. J. Jaya Chitra

9-Jul-07 18:40

Hello,

Can you please tell me how to pass an array or list to the SQL StoredProcedure.

Best Regards,
M. J. Jaya Chitra

Re: Passing an array to stored procedures

Sathesh Sakthivel9-Jul-07 19:16

Sathesh Sakthivel

9-Jul-07 19:16

Get it[^]

Regards,

Satips. Rose | [Rose]

Don't walk in front of me, I may not follow;
Don't walk behind me, I may not lead;
Walk beside me, and just be my friend. - Albert Camus

Query Insert Date?

Vodstok9-Jul-07 16:14

Vodstok

9-Jul-07 16:14

Okay, I don't think this is humanly possible, but I have been asked to run a query on a table based on the insert time of the records. The problem? There is no "Date Created" Column. Is there a way to retrieve the creation time of a row if it is not specifically saved?

______________________
Mr Griffin, eleventy billion is not a number... WTF | :WTF:

Re: Query Insert Date?

Frank Kerrigan16-Jul-07 2:32

Frank Kerrigan

16-Jul-07 2:32

Does it have an ID column and do some gues work.

Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)

http://www.frankkerrigan.com/[^]

Query probs.............,

Member 38798819-Jul-07 3:36

Member 3879881

9-Jul-07 3:36

Hi the follwing procedure i am getting a month all dates...,
In that i am using my own query for the select statement place...,
I used left outer join to show all the dates of @tbl_date...,
But it's displaying only What are the dates i am having in tblMonthhourdetails table those dates only...,
But i want to display all the dates of that month and if the @tbl_date table dates and tbltimecardmain and tblmonthhourdetails dates are matchable those records also want to display...,
Else if there's is no matachable date in that timecardmain table and month hour details table those date place it want to show null...,
I pasted my query on here,
I tried with all joins but i didnt get answer...,
Plz hlp me how to do that...,

Stored procedure:

DECLARE @startDate datetime
declare @nextdate datetime
DECLARE @endDate datetime
declare @tbl_date table (mydate datetime)
set nocount on
SET @startDate = CAST( CAST( 2007 AS varchar(4) ) + '0101' AS datetime )
SET @startDate = DATEADD( m, 6 - 1, @startDate )
set @enddate=dateadd(m,1,@startdate)
while @startdate < @enddate
begin
--SET @nextdate = DATEADD( d, 0, @startdate )
insert into @tbl_date values (@startdate)
set @startdate=dateadd(d,1,@startdate)
end

SELECT EM.empId as EId, EM.empName as EName,ttd.mydate,
TC.shift,PM.location,TC.reportingtime as FromTime, TC.leavingtime as ToTime,
MHD.AppWHours as Reg,MHD.AppOtHours as Ot,MHD.AppSHours as SReg,
MHD.AppSOtHours as SOt,SD.Whrs
FROM dbo.tblEmployeeMaster EM INNER JOIN dbo.tblTimeCardmain TC ON EM.empId = TC.empid
left outer join @tbl_date ttd on ttd.mydate=TC.carddate
left outer JOIN dbo.tblMonthHourDetails MHD ON (ttd.mydate = MHD.SDate
and TC.empid = MHD.EmpId)
--order by EM.empid,ttd.mydate
INNER JOIN dbo.tblProjectMaster PM ON TC.projectid = PM.projectID
INNER JOIN dbo.tblsalarydetails SD ON MHD.EmpId = SD.EmpID
where EM.empid=213 and datepart(mm,Ttd.mydate)=6 and
datepart(yyyy,ttd.mydate)=2007
order by ttd.mydate

Regards,
Magi

Re: Outer join problem

andyharman9-Jul-07 6:49

andyharman

9-Jul-07 6:49

Hi Magi

I think what you want is:

FROM @tbl_date ttd
LEFT OUTER JOIN dbo.tblTimeCardmain TC
  ON TC.carddate = ttd.mydate
LEFT OUTER JOIN dbo.tblEmployeeMaster EM
  ON EM.empId = TC.empid
LEFT OUTER JOIN dbo.tblsalarydetails SD
  ON SD.EmpID = TC.empid
LEFT OUTER JOIN dbo.tblProjectMaster PM
  ON PM.projectID = TC.projectid
LEFT OUTER JOIN dbo.tblMonthHourDetails MHD
  ON MHD.EmpId = TC.empid
  AND MHD.SDate = TC.carddate

Your @tbl_date table is the lead-table in the query. The rest of the tables should be outer-joined as they contain the optional data.

Regards
Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Re: Outer join problem

Member 38798819-Jul-07 19:07

Member 3879881

9-Jul-07 19:07

NO andy Still i didnt get answer...,
Now also its displaying what are dates i am having in my tblmonthhourdetails those records only...,
But i need the @tbl_date all dates ,
and if that date and tblcardmain and tblmonthourdetails dates are matchable those records,
other remaining dates place it want to show null...,

Regards,
Magi

how to connect a database in sql with vb6 application

treem jone9-Jul-07 1:42

treem jone

9-Jul-07 1:42

hi all
i'm making database using sql server 2000 and want to connect this database with an application desinged using visual basic6

Re: how to connect a database in sql with vb6 application

Pete O'Hanlon9-Jul-07 1:48

Pete O'Hanlon

9-Jul-07 1:48

I'm sorry, but what's the question? You've stated an intent only. Do you mean how do I do this? If so, I would suggest that you will want to use ADO.

Please visit http://www.readytogiveup.com/ and do something special today.
Deja View - the feeling that you've seen this post before.

Re: how to connect a database in sql with vb6 application

Sven Cipido9-Jul-07 20:23

Sven Cipido

9-Jul-07 20:23

Well if you want to know what the connection string is, this is the only place for all your connectionstrings : http://www.connectionstrings.com/[^].

Otherwise, please give more information in your question as it is too common.

Sven Cipido
(http://blog.svencipido.be)

Creation of table

Ashish Vohra9-Jul-07 1:33

Ashish Vohra

9-Jul-07 1:33

hi dere....
i'm not able to create table in sql server 2005 from another table...i know this thing in sql plus in oracle....but i'm not able to construct in sql server 2000

Ashish Vohra

Re: Creation of table

SHatchard9-Jul-07 2:02

SHatchard

9-Jul-07 2:02

I am not sure exactly what you mean, could you please clarify your exact requirement?

Re: Creation of table

sam#9-Jul-07 2:05

sam#

9-Jul-07 2:05

SELECT * INTO NewTable FROM OldTable

Re: Creation of table

Krish - KP9-Jul-07 2:11

Krish - KP

9-Jul-07 2:11

SELECT * INTO <newtbl from="" <oldtbl="">
This creates a copy of <oldtbl> to <newtbl>

INSERT INTO <tablea> (<col1>, <col2>) SELECT <col1>, <col2> FROM <tableb>
Inserts rows fetched by select statement into <tablea>

Regards
KP

Backup or restore

jaics2629-Jul-07 0:46

jaics262

9-Jul-07 0:46

How to write script to take backup or restore database(when i run a prticular file it automatically backup or restore database)

Re: Backup or restore

Paul Conrad13-Jul-07 11:54

Paul Conrad

13-Jul-07 11:54

http://www.google.com[^] around for it.

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

Problem inserting data in SQL Server 2000

Mubashir Javaid8-Jul-07 22:12

Mubashir Javaid

8-Jul-07 22:12

Hi,
I have faced a problem while inserting data into the sql server 2000.
First I have made a database and related Store procedure in SQL Server 2005.
When I run a store procedure in SQL Server 2005, it works fine.
But I have generated SQL script from SQL Server 2005 and run in Server 2000 (after some changes in script).
Tables and store procedure are created in SQL Server 2000. Then I made a database user and allow it all permissions (Set this user as dbo).
When I insert data into the sql server 2000 table it runs the store procedure but no insertion will take place

Re: Problem inserting data in SQL Server 2000

Rajesh Mascon8-Jul-07 23:22

Rajesh Mascon

8-Jul-07 23:22

May I see the full script with storred procedure which you have ran on sql server 2000 db

housekeeping Blobs. [modified]

gloffa8-Jul-07 21:39

gloffa

8-Jul-07 21:39

Hi,

MSSQL 2005, I want to delete blobs on a regular basisto preserve diskspace.
The blobs has various meta-data but I want to delete them based on time of
creation (date-time) and type. My concern is to equally disperse the deletion
so no type ends up being empty, but rather delete a few of this type and some
in that type ... and so on.

Any kind of help regarding this would be appriciated.

This CTE takes 100 from every Category

WITH tempdata AS (SELECT ROW_NUMBER() OVER (PARTITION BY Category<br />
ORDER BY Age DESC) AS rownum, ID FROM Imageblob)<br />
DELETE FROM tempdata WHERE rownum > 1000;

But how do i use it with a grouping selection so I can choose
to delete from the category with the most rows.

Select count(Age)<br />
From imageblobs<br />
Group by Catebogry

that gives me each row per category but I want to use it with the first CTE
and also order them.

Any suggestions/code/help/hints are much appritiated´
My t-sql knowledge is rubbish

Cheers,

/Andreas

-- modified at 12:27 Monday 9th July, 2007

SQL Server 2000 connection problem

Deteroc8-Jul-07 21:31

Deteroc

8-Jul-07 21:31

Hi, all!
I have the following problem.
I have an application in vb 2005 and a database in SQL server 2000. The instance of SQL server is installed on one machine in the network, however the application should work on several machines within this network.
The question is do I need to have separate instances of Sql server on each of the machines with the same database attached, or I need to connect to a single SQL server, in that case what will be the connection string? The name of the computer in the network and name of the server, or only the name of the server?
Could you please suggest me the sample connection string, I should use in my case.

Thanks beforehand,
D.

Re: SQL Server 2000 connection problem

Nouman Bhatti8-Jul-07 21:57

Nouman Bhatti

8-Jul-07 21:57

You don't need to install sql server 2000 instance on each and every system.
just install it on one system in the lan or workgroup
and in your query string give the name of that system

for example i had installed the sql on "test" system so the query string will be

"Server=test;Database=your_db_name;uid=sa;"

if u have a password for sa account then also include it.

display stored procedure format...........,

Member 38798818-Jul-07 21:16

Member 3879881

8-Jul-07 21:16

Hi i am using one procedure to get a month all dates...,
But each and every dates creating separate,separate column name...,
But i want display those all dates under come in same column name...,
Means:
when i execute my procedure i am getting o/p like:

Startdate
2007-06-01

Startdate
2007-06-02

startdate
2007-06-03
.
.
.
startdate
2007-06-30

But i want following format:

Startdate
2007-06-01
2007-06-02
.
.
.
2007-06-30

This is the procedure:

DECLARE @startDate datetime
declare @nextdate datetime
DECLARE @endDate datetime

SET @startDate = CAST( CAST( 2007 AS varchar(4) ) + '0101' AS datetime )

SET @startDate = DATEADD( m, 6 - 1, @startDate )

set @enddate=dateadd(m,1,@startdate)
set @enddate=dateadd(d,-1,@enddate)

select @startdate as startdate

while @startdate < @enddate
begin

SET @nextdate = DATEADD( d, 0, @startdate )
set @startdate=dateadd(d,1,@nextdate)
select @startdate as startdate

end

Advance Thanks,

Regards,
Magi

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