query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query help

Author

Topic

kodumudisadha
Starting Member

33 Posts

Posted - 2009-05-09 : 10:16:36

hi all,
in this below query for one cm_number multiple PLU_CPT will be there.
for example
cm_number plu_cpt amt
--------- -------- ----
a 1 100
a 2 50
a 3 200
b 2 500
b 3 600
then below query will return 3 rows.i need to return like this
that is for every cm_number i have to cross join the plu_cpt.
here cm_number will go n number of records but plu_cpt will repeat between 1 and 10 only.
cm_number plu_cpt count amt
--------- -------- ----- ----
a 1-2 2 150
a 1-3 2 300
a 2-3 2 250
b 2-3 2 1100

select storename,CM_NUMBER,cm_date,PLU_CPT,count(distinct CM_NUMBER),sum(plu_price)
from dbo.POSSALESTRNDETAILS d,crmstore s
where d.storeid=s.storeid and
cm_date between '04-apr-07' and '04-apr-07'
group by storename,CM_NUMBER,cm_date,PLU_CPT

i need query some thing like this.i dont knou how to group this so this is retriving like like 1,2,3...how to do please...help
cm_number,(case
when PLU_CPT=1 then '1'
when PLU_CPT=2 then '2'
when PLU_CPT=3 then '3'
when PLU_CPT=4 then '4'
when PLU_CPT=5 then '5'
when PLU_CPT=6 then '6'
when PLU_CPT=7 then '7'
when PLU_CPT=8 then '8'
when PLU_CPT=9 then '9'
when PLU_CPT=10 then '10'
when PLU_CPT=1 and PLU_CPT=2 then '1-2'
when PLU_CPT=1 and PLU_CPT=2 then '1-2'
when PLU_CPT=1 and PLU_CPT=2 then '1-2'
----
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-09 : 10:32:36

[code]
SELECT t.cm_number,cast(t.plu_cpt as varchar(2))+ '-' + cast(t1.plu_cpt AS varchar(2)) AS plu_cpt,
2,t.amt+t1.amt AS amt
FROM YourTable t
CROSS APPLY (SELECT *
FROM YourTable
WHERE cm_number=t.cm_number
AND plu_cpt<> t.plu_cpt
)t1
[/code]

kodumudisadha
Starting Member

33 Posts

Posted - 2009-05-09 : 10:53:41

hi,
its showing the following error

SELECT t.cm_number,
cast(t.plu_cpt as varchar(2))+ '-' + cast(t1.plu_cpt AS varchar(2)) AS plu_cpt,
2,t.amt+t1.amt AS amt
FROM POSSALESTRNDETAILS t
CROSS APPLY (SELECT *
FROM POSSALESTRNDETAILS
WHERE cm_number=t.cm_number
AND plu_cpt<> t.plu_cpt
)t1

Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'APPLY'.
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 't1'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-09 : 11:42:05

Cross apply is a feature of SQL Server 2005.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-09 : 11:43:57

quote:

Originally posted by visakh16

CROSS APPLY (SELECT *
             FROM YourTable
             WHERE cm_number=t.cm_number
             AND plu_cpt <> t.plu_cpt
             )t1

AND plu_cpt > t.plu_cpt

E 12°55'05.63"
N 56°04'39.26"

kodumudisadha
Starting Member

33 Posts

Posted - 2009-05-09 : 11:59:27

hi friends,
i am using 2000 version ,in this how to achieve this scenario ,
please help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-09 : 12:31:15

[code]
SELECT t.cm_number,cast(t.plu_cpt as varchar(2))+ '-' + cast(t1.plu_cpt AS varchar(2)) AS plu_cpt,
2,t.amt+t1.amt AS amt
FROM YourTable t
JOIN YourTable t1
ON t1.cm_number=t.cm_number
AND t1.plu_cpt> t.plu_cpt
[/code]

kodumudisadha
Starting Member

33 Posts

Posted - 2009-05-09 : 13:12:18

hi thanks for all,
i need another help ,i want to join all plu_cpt for one cm_number that is one row for one cm_number like this how to do pls help
a 1-2-3 350
b 2-3 1100

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-09 : 13:25:49

for that first create a UDF like this


CREATE FUNCTION ConcatValues
(
@cm_number char(1)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @Result varchar(8000)

SELECT @Result=COALESCE(@Result,'') + CAST(plu_cpt as varchar(5)) + '-'
FROM YourTable
WHERE cm_number=@cm_number

RETURN @Result
END


then use it like below

SELECT cm_number,dbo.ConcatValues(cm_number) AS plu_cpt,
amt
FROM
(
SELECT cm_number,SUM(amt) AS amt
FROM YourTable
GROUP BY cm_number
)t

kodumudisadha
Starting Member

33 Posts

Posted - 2009-05-09 : 23:11:48

Dear visak,
thank you very much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-10 : 02:12:21

you're welcome

kodumudisadha
Starting Member

33 Posts

Posted - 2009-05-11 : 08:23:39

hi, i need last one help
in function ConcatValues i want to get which plu_cpt is more than 1,i want to ignore single plu_cpt for one cm_number

i tried
SELECT @Result= COALESCE(@Result,'') + CAST( plu_cpt as varchar(5)) + '-'
FROM (select distinct plu_cpt from POSSALESTRNDETAILS
WHERE cm_number=@cm_number and cm_date=@cm_date and storeid=@storeid)t1
group by plu_cpt
having count(plu_cpt)>1

but its showing plu_cpt null for all records

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-11 : 10:32:34

do you mean this? make concat values like below and check if this is what you want.


CREATE FUNCTION ConcatValues
(
@cm_number char(1)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @Result varchar(8000)

SELECT @Result=COALESCE(@Result,'') + CAST(t.plu_cpt as varchar(5)) + '-'
FROM YourTable t
INNER JOIN (SELECT plu_cpt
            FROM YourTable
            GROUP BY plu_cpt
            HAVING COUNT(*) >1) t1
ON t1.plu_cpt=t.plu_cpt
WHERE t.cm_number=@cm_number
RETURN @Result
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-11 : 10:38:16

and just in case, what you want is to take plu_cnt having more than 1 occurance per cm_number value, then make it like below


CREATE FUNCTION ConcatValues
(
@cm_number char(1)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @Result varchar(8000)

SELECT @Result=COALESCE(@Result,'') + CAST(t.plu_cpt as varchar(5)) + '-'
FROM YourTable t
INNER JOIN (SELECT plu_cpt,cm_number
            FROM YourTable
            GROUP BY plu_cpt,cm_number
            HAVING COUNT(*) >1) t1
ON t1.plu_cpt=t.plu_cpt
AND t1.cm_number=t.cm_number
WHERE t.cm_number=@cm_number

RETURN @Result
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources