SQL Select Question - Database Discussion Boards

indian1438-Jul-10 16:51

8-Jul-10 16:51

Hi All,

I have a report in which, I have to show the trend values like
sum for the same columns I have to show yesterday, 1week ago and 2weeks ago and 1month ago.

Like for example I have 2 columns and one date column. Now I have to write a query in such a way that
the sum(col1), sum(col2) for 1 day ago, 1 week ago and 1month ago in the same row.

Can anybody please help me in writing this query. Any link or any code snippet will be very much helpfull.

Thanks in advance.

Thanks & Regards,

Md. Abdul Aleem
NIIT technologies

Re: Sum for Trend

Mycroft Holmes8-Jul-10 23:00

Mycroft Holmes

8-Jul-10 23:00

Break it into 4 queries, 1 for each time period doing the appropriate aggregation for each query, include the common key. Then join the 4 queries into 1 using the common key. I would use table vars to hold the temp data.

BTW I would not put my company name in your sig, it can be incorrectly construed (look it up).

Never underestimate the power of human stupidity
RAH

auto pagination in sql

Ebube8-Jul-10 2:34

Ebube

8-Jul-10 2:34

Hi how do i make sql statement to page the rows six per result

Re: auto pagination in sql

Eddy Vluggen8-Jul-10 3:19

Eddy Vluggen

8-Jul-10 3:19

Hi,

There's an example over here[^] Smile | :)

I are Troll Suspicious | :suss:

Complex Query

indian1437-Jul-10 12:29

indian143

7-Jul-10 12:29

Hi All,

I have to write a query in SQL Server 2000/2005/2008 in such a way that, "the sum of 1st Column 2nd row and 2nd Column 1st row is placed in 2nd column 2nd row".
Any kind of link or any suggestion will be very very helpfull.

Thanks & Regards,

Md. Abdul Aleem
NIIT technologies

Re: Complex Query

Dr.Walt Fair, PE7-Jul-10 13:03

Dr.Walt Fair, PE

7-Jul-10 13:03

In other words, it sounds like column 2 is a running total of column 1. There's not a real elegant way that I know of, but it is a common problem.

Did you try a search for SQL running totals[^]?

CQ de W5ALT

Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software

Re: Complex Query

indian1437-Jul-10 14:22

indian143

7-Jul-10 14:22

Thanks, I got it in Crystal Reports only. It is fixed and your link also helps me a lot if I need it in SQL Server.

Thanks & Regards,

Md. Abdul Aleem
NIIT technologies

SQL Select Question

Aptiva Dave7-Jul-10 8:25

Aptiva Dave

7-Jul-10 8:25

Got myself a head scratcher here. I'm needing to pull employee names and pictures from a database, which I can do with the following select:

SELECT     dbo.vwEmployee.BadgeName, dbo.Image.Photo, dbo.vwEmployee.Updated
FROM         dbo.Image INNER JOIN
                      dbo.vwEmployee ON dbo.Image.OwnerGUID = dbo.vwEmployee.OwnerGUID
WHERE     (dbo.vwEmployee.Department = 'IT')

Problem is that the application that was used to store the employee information and photos also keeps every photo taken of the employee. I want to be able to return only the row with the most recent photo taken (or the first instance of the most recent update since some have multiple records with the same updated date). I know I can do this with the application I am writing to use this query, but I would rather let the SQL Server do most of the heavy lifting and to further narrow down my results.

Re: SQL Select Question

Corporal Agarn7-Jul-10 8:56

Corporal Agarn

7-Jul-10 8:56

Does the Image table have multiple records or multiple columns for the pictures? If record then does it have a time stamp? If so use a subquery to find the max time and pass that picture.

Re: SQL Select Question

Dr.Walt Fair, PE7-Jul-10 10:22

Dr.Walt Fair, PE

7-Jul-10 10:22

If you have a date field, then use TOP 1 and ORDER BY update_date DESC.

CQ de W5ALT

Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software

Re: SQL Select Question

Jeremy Hutchinson14-Jul-10 8:01

Jeremy Hutchinson

14-Jul-10 8:01

You need to do a sub-select to get a list of the employees and the date of their most recent picture. Then you join to that on the EmployeeID (OwnerGuid) and the date.

select c.BadgeName, a.Photo, c.Updated
  from Image a
  join (select OwnerGUID, MAX(PictureDate) PictureDate from Image group by OwnerGUID) b on a.OwnerGUID = b.OwnerGUID and a.PictureDate = b.PictureDate
  join vwEmployee c ON a.OwnerGUID = c.OwnerGUID
 where c.Department = 'IT'

UniqueIdentifier Foreign Key

tek 20096-Jul-10 23:43

tek 2009

6-Jul-10 23:43

I have two tables: table1 field contains the ID type UniqueIdentifier and table2 contains ID as foreign key when inserting values is not the same values filled in the two table table1 in this ID is filled eg e210d5ad-BC41-439th-b8e8-e8ed775ee932, and in table 2 it is filled with zero
So how do I do to unify all relationships necessary is active
the two queries are:

CREATE Procedure [dbo].[Insert1](@ID uniqueidentifier, @name varchar(10),@tel varchar(10))
AS
INSERT INTO [table1] ([ID], [name], [tel])
VALUES (NEWID(), @name, @tel)

CREATE Procedure [dbo].[Insert2](@ID uniqueidentifier, @cmd1 varchar(20))
AS
INSERT INTO [Table2] ([ID], [cmd1])
VALUES (@ID, @cmd1)

table1: ID Primary key
Table2:ID foreign Key

Thank you very much for your help

Re: UniqueIdentifier Foreign Key

Ray Cassick7-Jul-10 2:25

Ray Cassick

7-Jul-10 2:25

You would need to either wrap the inserts together and share the id created between them or return the value created by the first query and pass it into the second as a parameetr. I figure that your probably not doing a 1:1 insert here (ie: Theer are goign to be more inserts intot he second table as details?) so I would just return the ID created in the first insert and then pass it into the second as an arg myself.

LinkedIn[^] | Blog[^] | Twitter[^]

Re: UniqueIdentifier Foreign Key

Bernhard Hiller7-Jul-10 3:52

Bernhard Hiller

7-Jul-10 3:52

Since a UniqueIdentifier is really unique (in contrast to a simple autoincrement integer), you can assign it to the object in your program before inserting it to the database.

Re: UniqueIdentifier Foreign Key

Mycroft Holmes7-Jul-10 13:09

Mycroft Holmes

7-Jul-10 13:09

You use NEWID() to generate the new ID key, you need to trap this and pass it back to the client so it can be put into @ID for the second insert.

Insert1
Does not need @ID passed in as it is not used.
Create a new ID @CreatedID in the proc but outside the insert statement
Insert the record using the @CreatedID
After the insert select @CreatedID as NewID will return it to the client

Never underestimate the power of human stupidity
RAH

Excel to Access

KORCARI6-Jul-10 6:04

KORCARI

6-Jul-10 6:04

Hi Is it possible to create a Ms Excel form where I can enter data which should be automatically enter in an existing Ms Access database?
Thank you in advance

Re: Excel to Access

riced6-Jul-10 6:50

riced

6-Jul-10 6:50

The answer is yes (might require some vba code). But raises the question, Why not use a form in Access?
PS I'm a bit rusty on Excel/Access programming so don't ask me how. I know it can be done.

Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
The only valid measurement of code quality: WTFs/minute.

Blatant advertising

Richard MacCutchan5-Jul-10 6:55

Richard MacCutchan

5-Jul-10 6:55

Are you receiving any revenue from Mr BB[^]?

It's time for a new signature.

Re: Blatant advertising

J4amieC5-Jul-10 21:56

J4amieC

5-Jul-10 21:56

I think you meant to post this in site bugs & suggestions... at least that way on of the CP team will actually see it.

Re: Blatant advertising

Richard MacCutchan5-Jul-10 22:02

Richard MacCutchan

5-Jul-10 22:02

I wondered where this had gone to. If I was into drugs I'd at least have an excuse for such carelessness. D'Oh! | :doh:

It's time for a new signature.

sql function

Thanusree Duth5-Jul-10 2:28

Thanusree Duth

5-Jul-10 2:28

My sql function is

ALTER FUNCTION[dbo].[FN_ATTENDANCE_REPORT_DATES] ( @EMPID varchar(50) ,@FROMDATE datetime,@TODATE

datetime)RETURNS TABLE
AS RETURN(
SELECT DR_EmployeeID AS EMPID,EM_FirstName+EM_MiddleName+EM_LastName AS EMPNAME,ED_Department AS DEPARTMENT,
ISNULL(CONVERT(VARCHAR,DR_TimeIn,103),'Not checked')AS DATEOFWORK,
SUBSTRING(CONVERT(VARCHAR,ES_TimeIn,9),14,15)+'-'+SUBSTRING(CONVERT(VARCHAR,ES_TimeOut,9),14,15)AS

SHIFTTIME,
ISNULL(SUBSTRING(CONVERT(NVARCHAR,MIN(DR_TimeIn), 22), 10, 11),'Not checked')
AS FIRSTTIMEIN,
ISNULL(SUBSTRING(CONVERT(NVARCHAR,MAX(DR_TimeOut), 22), 10, 11),'Not checked')
AS LASTTIMEOUT,
ISNULL(CONVERT(VARCHAR(5),DATEDIFF(SS,DR_TimeIn,DR_TimeOut)/3600)
+':'+
CONVERT(VARCHAR(5),DATEDIFF(SS,DR_TimeIn,DR_TimeOut )%3600/60)
+':'+
CONVERT(VARCHAR(5),(DATEDIFF(SS,DR_TimeIn,DR_TimeOut)%60)),'0 Blush | :O

')AS TOTALHOURS
FROM
SG_Daily_Register INNER JOIN SG_Emp_Shift ON SG_Daily_Register.DR_ShiftID=SG_Emp_Shift.ES_ShiftID
INNER JOIN SG_Emp_Master ON SG_Daily_Register.DR_EmployeeID=SG_Emp_Master.EM_EmployeeID
INNER JOIN SG_Emp_Department ON SG_Emp_Master.EM_DeptID=SG_Emp_Department.ED_DeptID
WHERE DR_EmployeeID=@EMPID AND CONVERT(VARCHAR,DR_TimeIn,101) BETWEEN @FROMDATE AND @TODATE
GROUP BY

DR_EmployeeID,EM_FirstName+EM_MiddleName+EM_LastName,ED_Department,ES_TimeIn,ES_TimeOut,DR_TimeIn,DR_TimeOut
)

I got output as
FIRSTTIMEIN LASTTIMEOUT
2833020 PreethyS Development 02/12/2010 7:00:00:000PM-3:00:00:000AM 7:10:09 PM 9:40:45 PM 2:30:36
2833020 PreethyS Development 02/12/2010 7:00:00:000PM-3:00:00:000AM 10:20:32 PM 12:15:27 AM 1:54:55
2833020 PreethyS Development 03/12/2010 7:00:00:000PM-3:00:00:000AM 12:23:25 AM 1:24:23 AM 1 Blush | :O

:58
2833020 PreethyS Development 03/12/2010 7:00:00:000PM-3:00:00:000AM 1:32:43 AM 3:45:54 AM 2:13:11

But I have to get output as
2833020 PreethyS Development 02/12/2010 7:00:00:000PM-3:00:00:000AM 7:10:09 PM 12:15:27 AM 4:26:26
2833020 PreethyS Development 03/12/2010 7:00:00:000PM-3:00:00:000AM 12:23:25 AM 3:45:54 AM 3:14:09
pls help

Re: sql function

Simon_Whale5-Jul-10 2:52

Simon_Whale

5-Jul-10 2:52

could do with knowing the following

1. what version of SQL?
2. Sample Data
3. Tables (DDL)

As barmey as a sack of badgers

Re: sql function

J4amieC5-Jul-10 3:24

J4amieC

5-Jul-10 3:24

This is the 3rd question on the same topic. You should stick to 1 thread for 1 topic. Ive also pointed out the forum guidlines which ask you to use descriptive subject lines . "sql function" does not qualify as descriptive in a sql forum. The forum guidlines should also include the request to put code within formatted code blocks for ease of reading (if it doesnt it should).

Furthermore, you have not given any response to the answers youve been given below, so we have no idea if were even on the right track.

Lastly, Ive already answered this question below.

sql

Thanusree Duth4-Jul-10 23:02

Thanusree Duth

4-Jul-10 23:02

I required only time in select query from datetime field in format like:
2:20:55 AM.

Pls help me

Re: sql

J4amieC4-Jul-10 23:58

J4amieC

4-Jul-10 23:58

That is presentation logic, use your presentation layer to format a datetime instance appropriately.

This should start you off:

var now = DateTime.Now;
Console.WriteLine(now.ToString("h:mm:ss"));

More info: Date / Time format strings[^]

edit: read the forum guidlines for posting messages; pay specific attention to the subject line requirements.

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