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

SQL Server 2008 Forums

Transact-SQL (2008)

query

Author

Topic

wided
Posting Yak Master

218 Posts

Posted - 2014-01-02 : 11:49:33

hello

I have an application that worked well for sql 2000
I use (= *)
here is my request :

select t0.matricule , t1.semaine , t1.rubrique
, ROW_NUMBER ( ) OVER ( PARTITION BY t0.matricule , t1.semaine ORDER BY t0.matricule , t1.semaine ASC) AS RowNumber
, Sum ( isnull ( NbrHeure , 0) ) as NbrHeure
from H53FT_IHMParamsIntegrationEnColonne ( '20130101 ', '20130115 ') t1,
H11_Employe t0, t2 H53_MvtPresence
Where
t0.Matricule = t2.matricule
and t1.rubrique * = t2.rubrique
and t1.semaine = t2.numsemaine
and t0.Matricule = '1001 '
Between DateMvt and '20130101 'and '20130115 '

Here is the result of the first query is good :

1001 1 98 1 480
1001 1 99 2 0
1001 1 100 3 1561
1001 1 107 4 1561
1001 1 108 5 0
1001 1 120 6 0
1001 1 124 7 0
1001 1 125 8 194
1001 1 500 9 0
1001 2 98 1 0
1001 2 99 2 0
1001 2 100 3 2191
1001 2 107 4 2191
1001 2 108 5 974
1001 2 120 6 0
1001 2 124 7 0
1001 2 125 8 298
1001 2 500 9 960

with the 2008 version , I use the instructions join instead of * = , only I did not find the same result

here is the new application :

select t0.matricule , t1.semaine , t1.rubrique
, ROW_NUMBER ( ) OVER ( PARTITION BY t0.matricule , t1.semaine ORDER BY t0.matricule , t1.semaine ASC) AS RowNumber
, Sum ( isnull ( NbrHeure , 0) ) as NbrHeure
from H53FT_IHMParamsIntegrationEnColonne ( '20130101 ', '20130115 ') t1
left outer join t2 H53_MvtPresence
we t1.rubrique = t2.rubrique and t1.semaine = t2.numsemaine
join H11_Employe t0.Matricule = t0 we t2.matricule

and t0.Matricule = '1001 '
Between DateMvt and '20130101 'and '20130115 '

group by t0.matricule , t1.semaine , t1.rubrique

Here is the result of the second query :

1001 1 98 1 480
1001 1 100 2 1561
1001 1 107 3 1561
1001 1 125 4 194
1001 2 100 1 2191
1001 2 107 2 2191
1001 2 108 3 974
1001 2 125 4 298
1001 2 500 5 960

I need to find all the items ( 98,99,100 ........) are in the following H53FT_IHMParamsIntegrationEnColonne function and are not in the table H53_MvtPresence

I need your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-02 : 12:56:30

try this


select t0.matricule , t1.semaine , t1.rubrique
, ROW_NUMBER ( ) OVER ( PARTITION BY t0.matricule , t1.semaine ORDER BY t0.matricule , t1.semaine ASC) AS RowNumber
, Sum ( isnull ( NbrHeure , 0) ) as NbrHeure
from H53FT_IHMParamsIntegrationEnColonne ( '20130101 ', '20130115 ') t1
left join H53_MvtPresence t2 
on t1.rubrique  = t2.rubrique
and t1.semaine = t2.numsemaine
left join H11_Employe t0
on t0.Matricule = t2.matricule 
and t0.Matricule = '1001 '
where DateMvt Between '20130101' and '20130115'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

wided
Posting Yak Master

218 Posts

Posted - 2014-01-03 : 06:45:06

thanks Visakh16

it's ok for rubrique (98,99....)

but its not ok for matricule (first colonne = null)

NULL 1 98 1 2395
NULL 1 100 2 12054
NULL 1 107 3 12054
NULL 1 108 4 2909
NULL 1 120 5 337
NULL 1 124 6 305
NULL 1 125 7 430
NULL 1 500 8 2100
NULL 2 98 1 6900
NULL 2 99 2 300
NULL 2 100 3 16933
NULL 2 107 4 17456
NULL 2 108 5 4365
NULL 2 120 6 831
NULL 2 124 7 362
NULL 2 125 8 740
NULL 2 500 9 3180
1001 1 98 1 480
1001 1 100 2 1561
1001 1 107 3 1561
1001 1 125 4 194
1001 2 100 1 2191
1001 2 107 2 2191
1001 2 108 3 974
1001 2 125 4 298
1001 2 500 5 960

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-03 : 07:36:44

Are you telling you dont need them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

wided
Posting Yak Master

218 Posts

Posted - 2014-01-06 : 02:53:20

I need Anyone becaufe in the following query, I need to display data in columns

Numsemaine rubrique98 rubrique99 Rubriqu 100 ....
1 value98 value99 value100 ...
2
3

My second request is ready and the result depends on the first query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-06 : 06:42:52

So for those NULL values whats the value you need to see instead in output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

wided
Posting Yak Master

218 Posts

Posted - 2014-01-06 : 07:58:53

employees who do not have a listing for 98 or 99 for example, so the value of last columns must be equal to 0)

for my example: 1001 employee has no value in the section 99 (column 3), weekly (column 2), he must give me this

1001 1 99 0
1001 1 100 502
1001 2 99 0
1001 2 100 636

wided
Posting Yak Master

218 Posts

Posted - 2014-01-06 : 08:02:30

section 99 are in the function "H53FT_IHMParamsIntegrationEnColonne" but not in table H53_MvtPresence

I can do without the table H11_Employe

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-01-06 : 08:21:25

1. ROW_NUMBER() does not work in SQL2000
2. Your original query has other syntax problems.

Try something like:


SELECT t0.matricule , t1.semaine , t1.rubrique
	,ROW_NUMBER() OVER (PARTITION BY t0.matricule, t1.semaine ORDER BY t0.matricule, t1.semaine) AS RowNumber
	,SUM(ISNULL(NbrHeure , 0)) AS NbrHeure
FROM H11_Employe t0
	JOIN H53_MvtPresence t2
		ON t0.Matricule = t2.matricule
	RIGHT JOIN H53FT_IHMParamsIntegrationEnColonne('20130101', '20130115') t1
		ON t2.rubrique = t1.rubrique
			AND t2.numsemaine = t1.semaine
WHERE t2.numsemaine = t1.semaine
	AND t0.Matricule = '1001'
	AND DateMvt BETWEEN '20130101' AND '20130115'

wided
Posting Yak Master

218 Posts

Posted - 2014-01-06 : 08:44:28

Ifor, this is the result

1001 1 98 1 480
1001 1 100 2 1561
1001 1 107 3 1561
1001 1 125 4 194
1001 2 100 1 2191
1001 2 107 2 2191
1001 2 108 3 974
1001 2 125 4 298
1001 2 500 5 960

section 99 does not appear
1001 1 99 2 0....

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-01-06 : 08:49:51

What table is DateMvt in?

wided
Posting Yak Master

218 Posts

Posted - 2014-01-06 : 09:35:10

from table H53_MvtPresence

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-01-06 : 10:11:27

Try:


SELECT t0.matricule , t1.semaine , t1.rubrique
	,ROW_NUMBER() OVER (PARTITION BY t0.matricule, t1.semaine ORDER BY t0.matricule, t1.semaine) AS RowNumber
	,SUM(ISNULL(NbrHeure , 0)) AS NbrHeure
FROM H11_Employe t0
	JOIN H53_MvtPresence t2
		ON t0.Matricule = t2.matricule
		        AND t0.Matricule = '1001'
		        AND t2.DateMvt BETWEEN '20130101' AND '20130115'		
	RIGHT JOIN H53FT_IHMParamsIntegrationEnColonne('20130101', '20130115') t1
		ON t2.rubrique = t1.rubrique
			AND t2.numsemaine = t1.semaine
WHERE t2.numsemaine = t1.semaine

If that does not work you will need to post some consumable test data along with the expected results.

wided
Posting Yak Master

218 Posts

Posted - 2014-01-06 : 11:04:10

I will simplify my problem

H53FT_IHMParamsIntegrationEnColonne the function return the following line :

98
99
100
107
125
500

H53_MvtPresence table contains :

Employé Week Section Number
1001 1 100 325
1001 1 107 460
1001 1 125 255

1001 2 99 414
1001 2 107 858
1001 2 125 747

I need a list of numbers grouped by week , by topic.
If the items are not included in the table H53_MvtPresence , they appear with the value 0

The result I would like to have

1001 1 98 0
1001 1 99 0
1001 1 100 325
1001 1 107 460
1001 1 125 255
1001 1 500 0

1001 2 98 0
1001 2 99 414
1001 2 100 0
1001 2 107 858
1001 2 125 747
1001 2 500 0

H11_employe the table is not necessary

the link with the week is not necessary

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-01-06 : 12:49:24

[code]
-- *** Test Data in Consumable Format ***
-- You should provide this
CREATE TABLE #H53FT_IHMParamsIntegrationEnColonne
(
Section int NOT NULL
);
INSERT INTO #H53FT_IHMParamsIntegrationEnColonne
VALUES (98),(99),(100),(107),(125),(500);

CREATE TABLE #H53_MvtPresence
(
Employé int NOT NULL
,[Week] tinyint NOT NULL
,Section int NOT NULL
,Number int NOT NULL
);
INSERT INTO #H53_MvtPresence
VALUES (1001, 1, 100, 325)
,(1001, 1, 107, 460)
,(1001, 1, 125, 255)
,(1001, 2, 99, 414)
,(1001, 2, 107, 858)
,(1001, 2, 125, 747);
-- *** End Test Data in Consumable Format ***

WITH EmployéWeek
AS
(
SELECT DISTINCT Employé, [Week]
FROM #H53_MvtPresence
)
SELECT EW.Employé, EW.[Week], C.Section
,COALESCE(SUM(P.Number), 0) AS Number
FROM EmployéWeek EW
CROSS JOIN #H53FT_IHMParamsIntegrationEnColonne C
LEFT JOIN #H53_MvtPresence P
ON EW.Employé = P.Employé
AND EW.[Week] = P.[Week]
AND C.Section = P.Section
GROUP BY EW.Employé, EW.[Week], C.Section
ORDER BY Employé, [Week], Section;
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources