Database Discussion Boards

First Prev Next

Re: SQL 2008 to SQL2005

Eddy Vluggen23-Apr-12 0:20

Eddy Vluggen

23-Apr-12 0:20

jojoba2011 wrote:
and why doesn't the SQL create correct scripts

It did. You generated them on Sql2008, and thus it will use all the language-features available for 2008. If you wanted to be compatible with Sql2005, you'd generate them from there.

Put your database in compatibility mode (Google knows how) and try to script again.

Bastard Programmer from Hell Suspicious | :suss:

Re: SQL 2008 to SQL2005

jojoba201122-Apr-12 23:10

jojoba2011

22-Apr-12 23:10

Thanks!<br />
but this is not good trick!<br />
and no use in here cause i have different parameters;<br />
this was example @Param.

SQL Get Max for each group

Richard.Berry10020-Apr-12 11:52

Richard.Berry100

20-Apr-12 11:52

Hi

I posted a similar question to this here [^]

however after looking at the data in more detail, the query has to be changed a lot. Also in the previous post, I could not get it to work.

The select statement below is what i need except, this returns all the rows for each product, and all I need is the MAX date_despatched for EACH product

SELECT scheme_opdetm.warehouse, scheme_opdetm.product, scheme_opdetm.net_price, scheme_opdetm.despatched_qty, scheme_opheadm.date_despatched
FROM scheme_opdetm INNER JOIN scheme_opheadm ON scheme_opdetm.order_no = scheme_opheadm.order_no
WHERE (((scheme_opheadm.status)="8") AND ((scheme_opdetm.warehouse)="06") AND ((scheme_opdetm.net_price)>0) AND ((scheme_opdetm.despatched_qty)>0))
ORDER BY scheme_opdetm.product, scheme_opheadm.date_despatched DESC;

Basically the Sales Order Header (scheme_opheadm) contains the Date Despatched, Sales Order, ans Status (which must be 8 for despatched orders).

The Sales Order Detail (scheme_opdetm) has the Warehouse Product Net Price (There are also comments and other lines that I need to ignore in the Detail Table, hence the filters for Net Price > 0, Qty Despatched > 0, Warehouse = 06 (finished goods).

How can I get only the details of the LAST despatch for EACH item?

Re: SQL Get Max for each group

Mycroft Holmes20-Apr-12 13:40

Mycroft Holmes

20-Apr-12 13:40

Create a sub query that uses Row_Number() and Partition based in the id field in scheme_opdetm, selecting data from your transaction table and numbered/ordered by the date field. Join that to your main query and filter it on row_number = 1.

Never underestimate the power of human stupidity
RAH

Re: SQL Get Max for each group

Richard.Berry10020-Apr-12 20:51

Richard.Berry100

20-Apr-12 20:51

Thanks Mycroft, but I have no idea how to do this.

First, I'm not sure how to put in the PARTITION clause,
Second, the Despatch date is in the header table (opheadm), with a Sales Order Num, but I want to Partition by Product not by Sales Order Num, and the product is in the Details Table (opdetm).

This what I have tried so far, but I get an error "The column 'order_no' was specified multiple times for ordlist

SELECT opdetm.warehouse, opdetm.product, opdetm.net_price, opdetm.despatched_qty, opheadm.date_despatched 
FROM vektron.scheme.opdetm INNER JOIN vektron.scheme.opheadm ON opdetm.order_no = opheadm.order_no ,
(SELECT ROW_NUMBER() OVER (PARTITION BY opheadm.order_num ORDER BY date_entered DESC) , *  FROM vektron.scheme.opheadm inner join vektron.scheme.opdetm on opdetm.order_no = opheadm.order_no) as ordlist
WHERE(((opheadm.status) = '8') And ((opdetm.warehouse) = '06') And ((opdetm.net_price) > 0) And ((opdetm.despatched_qty) > 0)) and ordlist.order_no = 1 and opdetm.product = ordlist.product
ORDER BY opdetm.product, opheadm.date_despatched DESC

Re: SQL Get Max for each group

Mycroft Holmes20-Apr-12 21:23

Mycroft Holmes

20-Apr-12 21:23

Without the DB I'm not going to be able to build the query, but here is how I would go about it.

Create a query that returns you the minimum bits you need to do the partition job. That should be the ID, the date and the product fields. The ID to link back to your result set, the data to order by and the product to partition over.

Once you have that right you put it aside and create another query that had the ID field and all the bits you want in your end result.

Now join the 2 queries!

Never underestimate the power of human stupidity
RAH

Re: SQL Get Max for each group

Jörgen Andersson21-Apr-12 1:18

Jörgen Andersson

21-Apr-12 1:18

Try something like this:

SQL

WITH original_query AS (
    SELECT scheme_opdetm.warehouse, scheme_opdetm.product, scheme_opdetm.net_price, scheme_opdetm.despatched_qty, scheme_opheadm.date_despatched
    FROM scheme_opdetm INNER JOIN scheme_opheadm ON scheme_opdetm.order_no = scheme_opheadm.order_no
    WHERE (((scheme_opheadm.status)="8") AND ((scheme_opdetm.warehouse)="06") AND ((scheme_opdetm.net_price)>0) AND ((scheme_opdetm.despatched_qty)>0))
    ORDER BY scheme_opdetm.product, scheme_opheadm.date_despatched DESC;
    )
,last_despatched AS (
    SELECT  product,Max(date_despatched) AS date_despatched
    FROM    original_query
    GROUP BY product
    )
SELECT  o.warehouse, o.product, o.net_price, o.despatched_qty, o.date_despatched
FROM    original_query o,last_despatched l
WHERE   o.product = l.product
AND     o.date_despatched = l.date_despatched

I love CTEs.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

Re: SQL Get Max for each group

Richard.Berry10021-Apr-12 2:41

Richard.Berry100

21-Apr-12 2:41

Thanks so much Jorgen!

That worked brilliantly.

I did have to remove:

ORDER BY scheme_opdetm.product, scheme_opheadm.date_despatched DESC;

becaue it gave an error (MSSQL 2008 R2): "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

Re: SQL Get Max for each group

Jörgen Andersson21-Apr-12 6:44

Jörgen Andersson

21-Apr-12 6:44

Glad to help.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

Re: SQL Get Max for each group

vvashishta23-Apr-12 19:16

vvashishta

23-Apr-12 19:16

SELECT
b.RowNumber,
iSrno,
vUserCode,
dLoggedDate
FROM (
SELECT
iSrno,
vUserCode,
dLoggedDate,
ROW_NUMBER() OVER ( PARTITION BY vUserCode ORDER BY vUserCode DESC ) 'RowNumber'
FROM HOUserLog) b
WHERE RowNumber = 1

/*
Here HOUserLog is the table from where the Group By MAX needs to be pick.
ROw_Number() OVER ( PARTITION BY .... ) will generate the RowNumber according to the Group By
Column you provided in ORDER BY ..... DESC Clause.
Now pick the values for every RowNumber Column = 1 will get you the solution.

MARK AS ANSWER IF WORKS

- Happy Coding -
Vishal Vashishta

SSRS 2005 question

SQL Ed20-Apr-12 10:13

SQL Ed

20-Apr-12 10:13

After running a report in SSRS 2005 I am trying to export it into excel and I am getting an error
"Index is out of range. Must be non-negative and less than the size of the collection. Parameter name:index"

When I remove the "Page break at start" or the "Page break at end" grouping property, it fixes the problem, but I need this property on because that is how the report gets seperated onto seperate Excel worksheets by the groups I have set up. Any suggestions??? D'Oh! | :doh:

mysql to ms sql server

Ramkumar_S19-Apr-12 21:27

Ramkumar_S

19-Apr-12 21:27

Hi All,

I have same table structure in Mysql and sqlserver.
If there is change in Mysql table, then sqlserver table should get updated.

Please suggest proper way to do.

We dont have SSIS we only use ssms2005 express.

Ramkumar
("When you build bridges you can keep crossing them. ")

http://ramkumarishere.blogspot.com

Re: mysql to ms sql server

Eddy Vluggen20-Apr-12 0:48

Eddy Vluggen

20-Apr-12 0:48

Ramkumar_S wrote:
Please suggest proper way to do.

Sql Express doesn't offer this functionality out of the box. You'd have several options;

Add the MySql server as a linked server and sync using triggers
Write an app to do the synchronization, using the Sync Framework
Upgrade Sql Server

Bastard Programmer from Hell Suspicious | :suss:

Re: mysql to ms sql server

jschell20-Apr-12 10:27

jschell

20-Apr-12 10:27

I suspect the solution would require that you first analyze what happens if there is an error.

For example you update the MySQL table and at that moment the SQL Server database is down so of course the corresponding table cannot be updated then - so what do you expect to happen then?

Additionally how soon does the data need to be in the SQL Server database?

Getting unique combination of rows in SQL??

Lima318-Apr-12 21:08

Lima3

18-Apr-12 21:08

I have a requirement as in below

SQL

DECLARE @TEST TABLE (X VARCHAR(10),Y VARCHAR(10))

    insert into @TEST values ('A','B'),('C','D'),('E','F'),('B','A'),('D','C'),('F','E')

SELECT A.* FROM @TEST A, @TEST B WHERE A.X = B.y

gives
X Y

A B
C D
E F
B A
D C
F E

But I have to get only the combination (AB or BA) ,(CD or DC),(EF or FE) in my select statement. In my sql we have some thing called DistinctRow.. What is it in SQL ..Pelase help ..urgent..

Re: Getting unique combination of rows in SQL??

AHSAN11118-Apr-12 21:31

AHSAN111

18-Apr-12 21:31

Try using the Distinct Keyword

SQL

SELECT Distinct(A.x), A.Y FROM @TEST A, @TEST B WHERE A.X = B.y

Re: Getting unique combination of rows in SQL??

Blue_Boy19-Apr-12 0:57

Blue_Boy

19-Apr-12 0:57

here it is

SQL

DECLARE @TEST TABLE (X VARCHAR(10),Y VARCHAR(10))
 
insert into @TEST values ('A','B')
insert into @TEST values ('C','D')
insert into @TEST values ('E','F')
insert into @TEST values ('B','A')
insert into @TEST values ('D','C')
insert into @TEST values ('F','E')


SELECT A.X
,(
select  top 1 b.y from @test as b where b.y > a.x  and b.x<a.y
) as subY

 FROM @TEST A
where 
(
select  top 1 b.y from @test as b where b.y > a.x  and b.x<a.y
) is not null
order by a.x

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

Re: Getting unique combination of rows in SQL??

Lima319-Apr-12 1:53

Lima3

19-Apr-12 1:53

Excellent .. thanks for the reply..

Re: Getting unique combination of rows in SQL??

Lima319-Apr-12 1:56

Lima3

19-Apr-12 1:56

I was looking for some key word like distinctrow in MYSQL.. dont we have any thing like that in SQL.

Re: Getting unique combination of rows in SQL??

Lima319-Apr-12 2:38

Lima3

19-Apr-12 2:38

Finally SELECT X,Y FROM @TEST WHERE X

Re: Getting unique combination of rows in SQL??

Blue_Boy19-Apr-12 2:45

Blue_Boy

19-Apr-12 2:45

There exists DISTINCT option to retrieve non-repeated values, but your case was different from using DISTINCT.

Glad to help you.

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

Traking Offence

Billa218-Apr-12 20:06

Billa2

18-Apr-12 20:06

I have a table called tbloffence where we can see list of offence which workers have done in worksite.so now i want to display the sumtotal of their offence in .aspx page based on workes userid,but the condition is like this:
a) offence since last login:
b)offence in last 1 month.

i want to write a sql query based on this condition...how can i track"offence since last login"in sql server 2005

Re: Traking Offence

Mohibur Rashid18-Apr-12 20:45

Mohibur Rashid

18-Apr-12 20:45

This is not an specific question. To get better answer, I would suggest you to generalize the question and provide more information.

Anyway
This is a very simple problem if I did understand properly.

All you need is to record log in time. that is everytime user log in the user.

And you would have to work in two different Query or may be in Procedeure, where you would send the user_id and procedure will return the result.

Re: Traking Offence

Billa218-Apr-12 22:09

Billa2

18-Apr-12 22:09

Actually i want to workin 3 tables

1)tbloffence : where we will get the sum(offence) here i track maincontractorID and sum(offence) query will be like this :
"SELECT sum(o.OffenceID) as OffenceNumber,u.UserID

from tblOffence o
inner join tblSubContractor ts on
o.MainContractorID=ts.SubContractorID"
from here i will chk the tblUSER with these subcontractorID :
"inner join tblUser u on
u.SubContractorID=ts.SubContractorID" .

and my doubt is that upto here i will get the total sum of offence and appropriate userid.,for eg
UserID : 520 TotalOffence:6000
but there is another table called tblAuditTrailSession where we will get logondate and userid which is tracking here..so i want to display offence based on userlogin(userid) ..since last login. i want a help in query format..Hope this is a generalized format question.

modified 19-Apr-12 4:26am.

complicated SQL: How to filter duplicate data about technical support and compute rank

bbaaking16-Apr-12 16:54

bbaaking

16-Apr-12 16:54

I’ve got a big problem. Could anyone give me a hand?

My first aim(not the final goal) is:fliter the duplicate data with query statment( select not delete), that is, the various entries of EndTime and Qos for a same ServiceID due to the repeated feedback. The rule is to only keep the records with the lowest Qos for a same ServiceID. If there are several records having the lowest Qos, then only keep whatever one of these records. The client accepts and prefers to this scheme. In this example, for the ID=6,7, and 8, just keep 6 or 7. And I have got answer of this aim here (thanks for TenmanS14 Thumbs Up | :thumbsup:

SQL

SELECT MIN(Qos), serviceid, id, TCID, EndTime from service
GROUP BY serviceid

aim2: Qos=1 represents the Satisfied Service. The monthly total Satisfied Services achieved by each technical support engineer is referred to as “Personal Total Satisfied Services this Month”. Those technical support engineers whose “Personal Total Satisfied Service this Month” ranking among the top 2 in that month are referred to as the “Top 2 since this Month”. If they are outstanding enough to be entitled to the “Top 2 since this Month” every month, then they can be referred to as “Outstanding of Class 1”. All in all, this step is to compute the “Outstanding of Class 1”. In this example, “Top 2 since this Month” for January is Andrew and Jacob, and that for February are Andrew, Dlyan, and Jacob. Therefore, the honor title of “Outstanding of Class 1” is awarded to Andrew and Jacob.

please help me with aim2 (finally I should reach the aim 4, the final goal), result of aim 2 should be

SQL

TCID
Andrew
Jacob

explain:There are some technical service data (ServiceID, TCID, EndTime, and QoS) in a whole year, and the field ID is a unique primary key because there are some duplicates.

some data:

ServiceID   ID  TCID    EndTime Qos
2000    2   Jacob   2011/1/1    2
2000    3   Jacob   2011/1/1    2
2001    4   Jacob   2011/1/1    2
2002    5   Jacob   2011/2/3    1
2003    6   Tyler   2011/1/4    1

Data Structure:
ID: Unique primary key of record
ServiceID: ID of a certain service
TCID: ID of a technical support engineer
EndTime: Ending Time of aservice
Qos：Quality of service (1 Satisfied; 2 Average; 3Unsatisfactory/Dissatisfied).

DDL and insert SQL(mysql5):

SQL

CREATE TABLE `service` (
  `ServiceID` INTEGER(11) NOT NULL,
  `ID` INTEGER(11) NOT NULL ,
  `TCID` VARCHAR(40)  NOT NULL,
  `EndTime` DATE NOT NULL,
  `Qos` CHAR(1)  NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`)
);
COMMIT;
INSERT INTO `service` (`ServiceID`, `ID`, `TCID`, `EndTime`, `Qos`) VALUES
  (2004, 9, 'Jacob', '2011-02-04', '1'),
  (2000, 2, 'Jacob', '2011-01-01', '2'),
  (2000, 3, 'Jacob', '2011-01-01', '2'),
  (2001, 4, 'Jacob', '2011-01-01', '2'),
  (2002, 5, 'Jacob', '2011-02-03', '1'),
  (2003, 6, 'Tyler', '2011-01-04', '1'),
  (2003, 7, 'Tyler', '2011-01-04', '1'),
  (2003, 8, 'Tyler', '2011-01-03', '2'),
  (2005, 10, 'Jacob', '2011-02-05', '1'),
  (2006, 11, 'Jacob', '2011-02-04', '2'),
  (2007, 12, 'Jacob', '2011-01-08', '1'),
  (2008, 13, 'Tyler', '2011-02-06', '1'),
  (2009, 14, 'Dylan', '2011-02-08', '1'),
  (2010, 15, 'Dylan', '2011-02-09', '1'),
  (2014, 16, 'Andrew', '2011-01-01', '1'),
  (2013, 17, 'Andrew', '2011-01-01', '1'),
  (2012, 18, 'Andrew', '2011-02-19', '1'),
  (2011, 19, 'Andrew', '2011-02-02', '1'),
  (2015, 20, 'Andrew', '2011-02-01', '1'),
  (2016, 21, 'Andrew', '2011-01-19', '1'),
  (2017, 22, 'Jacob', '2011-01-01', '1'),
  (2018, 23, 'Dylan', '2011-02-03', '1'),
  (2019, 24, 'Dylan', '2011-01-09', '1'),
  (2020, 25, 'Dylan', '2011-01-01', '1'),
  (2021, 26, 'Andrew', '2011-01-03', '1'),
  (2021, 27, 'Dylan', '2011-01-11', '1'),
  (2022, 28, 'Jacob', '2011-01-09', '1'),
  (2023, 29, 'Tyler', '2011-01-19', '1'),
  (2024, 30, 'Andrew', '2011-02-01', '1'),
  (2025, 31, 'Dylan', '2011-02-03', '1'),
  (2026, 32, 'Jacob', '2011-02-04', '1'),
  (2027, 33, 'Tyler', '2011-02-09', '1'),
  (2028, 34, 'Daniel', '2011-01-06', '1'),
  (2029, 35, 'Daniel', '2011-02-01', '1');
 COMMIT;

Almost forget to say that, just a reminder, the client only accept the SQL instead of stored procedure in database to implement it. And I only need query statement because customer did not allow us to write database.

this is my first 2 aims, there are 2 more, I am intended to complete all these steps one after another. Could anyone help achieve the first goal? I know it is quite complicated and many thanks to you in advance.

aim3:Then, to compute the “Outstanding of Class 2” (the engineers of “Top 2 since this Month” are not included) equals to compute those ranking the third and the forth places. In this example, the “Outstanding of Class 2” is the Tyler.

aim4:The final goal is to combine the “Outstanding of Class 1” with “Outstanding of Class 2”. The result will be ultimately transferred to report for rendering. My dataSet is just like:

TCID           level
Andrew         top2
Jacob          top2
Tyler          top4

modified 18-Apr-12 5:05am.

Last Visit: 31-Dec-99 18:00 Last Update: 12-Oct-24 2:54

Refresh

ᐊ Prev 1...372 373 374 375376377 378 379 380 381 Next ᐅ

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