Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

stored procedure troubles Expand / Collapse
Author
Message
Posted Thursday, May 9, 2013 9:25 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:13 AM
Points: 2,857, Visits: 5,129
I don't understand much of French, but I can tell you that what you are trying to do is 99% possible to achieve in a single insert statement without any loop.
Error clearly tells you, that one of the string value you generating cannot be converted to datetime.
If you could provide requirements of what you are trying to do in your proc, we might be able to help you better.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1451190
Posted Friday, May 10, 2013 2:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 5, 2013 4:11 AM
Points: 25, Visits: 105
before i expose whats the requirement of this script to thank you Eugene for your help
Post #1451468
Posted Friday, May 10, 2013 3:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 5, 2013 4:11 AM
Points: 25, Visits: 105
so the first month of the year in this enterprise is October(10) and finish September (9)
( that's called CAMPAGNE its mean Crop year if i can trust Google translate :D )

@D_DEBUT = start_date
@D_FIN = end_date
date_complete : its a complete date ( DAY-MONTH-YEAR) = DATEID
Annee : year = ANNEE
trimestre : quarter of year = TRIMESTRE
mois : number of the month in the year calendar = MOIS
mois_complet : name of the month = LIB_MOIS
semaine_annee : number of the week in the year calendar = SEMAINE
jour : day = JOUR
jour_semaine : day of the week ( i think its the number of the day in a week ) = JOUR_SEMAINE
quinzaine : fifteen day in month ( month divided by 2 ) = QUINZAINE
campagne : campaign or crop year begin Oct and finish Sept = CAMPAGNE
annee2 : the year of the crop year ( in this format 12/13 yearbegin/yearfinish ) = ANNEE_CAMP
ordre_mois : the order of the months in the crop year 10 - 11 - 12 -1 ... = ORDRE_MOIS

in the end of the script hey put a concatenation with date complete with D1 D2 D3 every D is 10 day in the order

my internship supervisor is on vacation and i tried to expose the requirement so i hope its a apparent now for you
Post #1451491
Posted Friday, May 10, 2013 5:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:13 AM
Points: 2,857, Visits: 5,129
Ok, please see how the same thing can be done wihtou using any loop:

CREATE PROCEDURE [dbo].[PS_DIM_TEMPS] /*(@Date_debut varchar(10), @Date_fin varchar(10))*/
AS
truncate table DWProd.dbo.DIM_TEMPS
declare @D_DEBUT datetime
declare @D_FIN datetime

BEGIN


/*set @D_DEBUT = @Date_debut
set @D_FIN = @Date_fin*/

SET @D_DEBUT = cast('2009-10-01' as datetime)
SET @D_FIN = cast('2013-09-30' as datetime)

-- the following CTE will produce required rtange of dates!
-- please note: it's based on your exiosting logic which excludes D_FIN from range!
;WITH E1(N) AS ( SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) V(N)) -- 10E+1 or 10 rows
,E2(N) AS (SELECT 1 FROM E1 a, E1 b) --10E+2 or 100 rows
,E4(N) AS (SELECT 1 FROM E2 a, E2 b) --10E+4 or 10,000 rows max (covers around 27 years)
,cteDays(D) AS (SELECT TOP (DATEDIFF(d, @D_DEBUT, @D_FIN)) DATEADD(d,(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))-1, @D_DEBUT) FROM E4)


INSERT INTO DIM_TEMPS(DATEID,ANNEE,TRIMESTRE, MOIS, LIB_MOIS, SEMAINE, JOUR,JOUR_SEMAINE
,QUINZAINE,CAMPAGNE,ANNEE_CAMP,ORDRE_MOIS)
SELECT D AS Date_Complete
,DATENAME(YEAR, D) AS Annee
,DATEPART(QUARTER, D) AS trimestre
,DATEPART(MONTH, D) AS mois
,DATENAME(MONTH, D) AS mois_complete
,DATEPART(WEEK, D) AS semaine_annee
,DATEPART(DAY, D) AS jour
,DATEPART(WEEKDAY, D) AS jour_semaine
,CASE WHEN DATEPART(DAY, D) < 16 THEN 1 ELSE 2 END AS quinzaine

,CASE WHEN DATEPART(MONTH, D) >9
THEN RIGHT(CAST(YEAR(D) AS CHAR(4)),2) + '/'
+ RIGHT(CAST(YEAR(D) + 1 AS CHAR(4)),2)
ELSE RIGHT(CAST(YEAR(D) - 1 AS CHAR(4)),2) + '/'
+ RIGHT(CAST(YEAR(D) AS CHAR(4)),2)
END AS campagne
,CASE WHEN DATEPART(MONTH, D) > 9
THEN YEAR(D) + 1
ELSE YEAR(D)
END AS annee2
,DATEPART(MONTH, D) + 3 - ((DATEPART(MONTH, D)+2)/12) * 12 AS ordre_mois
FROM cteDays

Update DIM_TEMPS set DECADE=case When day(DATEID) between 1 and 10 then convert(varchar(2),month(DATEID))+'-D1'
When day(DATEID) between 11 and 20 then convert(varchar(2),month(DATEID))+'-D2'
When day(DATEID) between 21 and 31 then convert(varchar(2),month(DATEID))+'-D3' END

END

GO

The above code will be way faster then loop based one.
Now about datatypes (possible conversion errors). You did not supplied DDL of DIM_TEMPS table, so I could only guess required datatype where explicit conversion was maid.
Now please check with your language requirements for month names (mois_complete)
If they need to be in French, you will need to make sure that your database default language is French, or use "SET LANGUAGE French" before INSERT as I did show to you before.





_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1451521
Posted Friday, May 10, 2013 5:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 5, 2013 4:11 AM
Points: 25, Visits: 105
yes i set the language i did it but still having the same message error
its supposed work fine ( of course with setting the language in conversion datetime as you show me ) because they are imported from the production environement like they works there
the 4 SP's who doesnt work yesterday had just a problem with language in conversion datatime type no more .

im supposed to change nothing on the script , my objective is to test the new features SS2012 and make a comparative study between BI tools MS SQL server 2012 and IBM Cognos 10.1
and the Microsoft Dynamics is on the is in the process of replacing another ERP so they would see if the SSQL 2012 can be more productive then others BI tools ( COGNOS ) associated with Microsoft Dynamics ( Already validated )
Post #1451529
Posted Friday, May 10, 2013 5:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:13 AM
Points: 2,857, Visits: 5,129
El_Mou (5/10/2013)
yes i set the language i did it but still having the same message error
its supposed work fine ( of course with setting the language in conversion datetime as you show me ) because they are imported from the production environement like they works there
the 4 SP's who doesnt work yesterday had just a problem with language in conversion datatime type no more .

im supposed to change nothing on the script , my objective is to test the new features SS2012 and make a comparative study between BI tools MS SQL server 2012 and IBM Cognos 10.1
and the Microsoft Dynamics is on the is in the process of replacing another ERP so they would see if the SSQL 2012 can be more productive then others BI tools ( COGNOS ) associated with Microsoft Dynamics ( Already validated )


If you don't want to change anything, you will need to find which particular value causing you a problem. So, you will need add some sort of validation of values before they used in INSERT.




_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1451532
Posted Friday, May 10, 2013 5:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:13 AM
Points: 2,857, Visits: 5,129
Check DDL of your DIM_TEMPS table. Some varchar values you are inserting fail to convert into datetime, so it will be related to datetime columns of this table.



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1451533
Posted Friday, May 10, 2013 7:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 5, 2013 4:11 AM
Points: 25, Visits: 105
what i said about SP script's is valable for the table destination they came from the production on the same DW
but effectively your right i think i see something wrong in the Time_table
im gonna see that brb
Post #1451598
Posted Friday, May 10, 2013 9:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 5, 2013 4:11 AM
Points: 25, Visits: 105
i verify and i cant find the values :s im really lost !

this is the table DIM_TEMPS :
CREATE TABLE [dbo].[DIM_TEMPS](
[DATEID] [datetime] NOT NULL,
[JOUR] [int] NULL,
[JOUR_SEMAINE] [int] NULL,
[SEMAINE] [int] NULL,
[QUINZAINE] [int] NULL,
[MOIS] [int] NULL,
[ORDRE_MOIS] [int] NULL,
[LIB_MOIS] [varchar](12) NULL,
[TRIMESTRE] [int] NULL,
[ANNEE] [varchar](50) NULL,
[CAMPAGNE] [varchar](8) NULL,
[ANNEE_CAMP] [int] NULL,
[DECADE] [varchar](40) NULL,
CONSTRAINT [PK_DIM_TEMPS] PRIMARY KEY CLUSTERED
(
[DATEID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


and this is the PS_DIM_TEMPS :

ALTER PROCEDURE [dbo].[PS_DIM_TEMPS] /*(@Date_debut varchar(10), @Date_fin varchar(10))*/
AS
truncate table DWHProdVeg.dbo.DIM_TEMPS
declare @D_DEBUT datetime
declare @D_FIN datetime

declare @Date_Complete datetime
declare @Annee VARCHAR(4)
declare @Annee2 int
declare @campagne VARCHAR(5)
declare @trimestre VARCHAR(6)
declare @quinzaine varchar(1)
declare @mois VARCHAR(6)
declare @ordre_mois VARCHAR(6)
declare @mois_complet VARCHAR(12)
declare @semaine_annee VARCHAR(7)
declare @jour VARCHAR(2)
declare @jour_semaine VARCHAR(9)
declare @anneeS VARCHAR(4)

BEGIN

SET @D_DEBUT = cast('01-10-2009' as datetime)
SET @D_FIN = cast('30-09-2013' as datetime)

/*set @D_DEBUT = @Date_debut
set @D_FIN = @Date_fin*/

WHILE @D_DEBUT<@D_FIN BEGIN

set @Date_Complete= @D_DEBUT
set @Annee =datename(YY,@D_DEBUT)
set @trimestre =datepart(Q,@D_DEBUT)
set @mois = datepart(MM,@D_DEBUT)
set @ordre_mois = case @mois
when '10' then 1
when '11' then 2
when '12' then 3
when '1' then 4
when '2' then 5
when '3' then 6
when '4' then 7
when '5' then 8
when '6' then 9
when '7' then 10
when '8' then 11
when '9' then 12
end
set @mois_complet =datename(MONTH,@D_DEBUT)
set @semaine_annee = datepart(WW,@D_DEBUT)
set @jour = datepart(DD,@D_DEBUT)
set @jour_semaine = datepart(DW,@D_DEBUT)
if (@jour<16)
set @quinzaine =1
else
set @quinzaine =2
set @anneeS=@annee+1

if (@jour>0 and @mois>9) set @campagne=substring(@annee,3,2)+'/'+substring(@anneeS,3,2)
if (@jour>0 and @mois>9) set @annee2=@anneeS


SET LANGUAGE FRENCH
INSERT INTO DIM_TEMPS(DATEID,ANNEE,TRIMESTRE,
MOIS,LIB_MOIS,SEMAINE,
JOUR,JOUR_SEMAINE,
QUINZAINE,CAMPAGNE,ANNEE_CAMP,ORDRE_MOIS)
VALUES(@Date_Complete,@Annee,CONVERT(int,@trimestre),
CONVERT(int,@mois),@mois_complet,CONVERT(int,@semaine_annee),
CONVERT(int,@jour),CONVERT(int,@jour_semaine),
CONVERT(int,@quinzaine),@campagne,@annee2,CONVERT(int,@ordre_mois))

set @D_DEBUT =dateadd(d,1,@D_DEBUT)
END

Update DIM_TEMPS set DECADE=case When day(DATEID) between 1 and 10 then convert(varchar(2),month(DATEID))+'-D1'
When day(DATEID) between 11 and 20 then convert(varchar(2),month(DATEID))+'-D2'
When day(DATEID) between 21 and 31 then convert(varchar(2),month(DATEID))+'-D3' END


END



i spent a week on this , how im gonna spend on SSDT and all new features and i should give a presentation next week about my progress :s :s :s :s :s :s

Post #1451640
Posted Friday, May 10, 2013 9:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 5, 2013 4:11 AM
Points: 25, Visits: 105
ITS DONE NOW ! thanks for your help EUGENE
Post #1451643
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse