Is there any way I can import a text file like

CD Current day

CW Current week

CM current month

CQ current quarter

CY current year

CFY current financial year

PD previous day

PW previous week

 

--

--

--

--

 

in the SQL command? when you intend to add parameters; it only allows to add one by one. Then in the prompt, it will display many of these instead of a pickup list.

While I can import a text file into the parameter fields of Field Explorer like the above. But when I did, the SQL command cannot 'see' them.

Please advise

JS

 

Ah! I did not realized that I can still edit the parameters in Field Explorer once I created the same parameters in SQL commad. Thanks a lot!

 

I will try then

 

John

 

 

Case '{?Date Type}' --note that you need the quotes here around the string!

  when 'CD' then LDTE = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

  when 'CW' then LDTE BETWEEN dateadd(week, datediff(week, 0, (getdate())), 0) AND  dateadd(week, datediff(week, 0, (getdate())), 6)

...<etc>

end

 

If you can't use a case statement in a where clause (some databases don't allow it) you would do something like this:

 

Where

  (('{?Date Type}' = 'CD' and LDTE = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))

OR

  ('{?Date Type}' = 'CW' and LDTE BETWEEN dateadd(week, datediff(week, 0, (getdate())), 0) AND dateadd(week, datediff(week, 0, (getdate())), 6))

OR

...<rest of date logic>

 

Not the parentheses in red - these are required in order for the logic to work correctly.

 

-Dell

 

I have tried, that the first option doesn't work for SQL server 2008 R2 where it always points "syntax error near '=' "; so I have used the second option that works. I tried to integrate the logic of second option into the original sql part of the where clause involvig dates, the following are running OK for one of the column of crosstab:

WHERE  (('{?break_type}' = 'CD' and LDTE = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
OR
  ('{?break_type}' = 'CW' and LDTE BETWEEN dateadd(week, datediff(week, 0, (getdate())), 0) AND dateadd(week, datediff(week, 0, (getdate())), 6))
OR
  ('{?break_type}' = 'CM' and LDTE BETWEEN DATEADD(month, datediff(month, 0, (getdate())), 0) AND  convert(datetime, convert(varchar(10), getdate(), 101)))
OR
 ('{?break_type}'='CQ' and LDTE BETWEEN DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) AND  dateadd(dd,-1,dateadd(qq,1,DATEADD(qq, DATEDIFF(qq,0,getdate()), 0))))
OR
('{?break_type}'='CY' and LDTE BETWEEN DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) AND  DATEADD(year, DATEDIFF(year, -1, getdate()), -1))
OR
('{?break_type}'='CFY' and LDTE BETWEEN  cast('01-Jul-' + cast( case when datepart(mm,getdate()) in (7,8,9,10,11,12) then DATEPART(yy,getdate())
             else DATEPART(yy,getdate())-1
               end as varchar) as datetime) AND                       
                              cast('30-Jun-' + cast( case when datepart(mm,getdate()) in (6,7,8,9,10,11,12) then DATEPART(yy,getdate())
                else DATEPART(yy,getdate())+1
               end as varchar) as datetime))
OR
('{?break_type}'='PD' and LDTE = DATEADD(day, DATEDIFF(day, 0, GETDATE()), -1))
OR
('{?break_type}'='PW' and LDTE between DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) and DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6))
OR
('{?break_type}'='PM' and  LDTE between DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) and
 DATEADD(DAY, -(DAY(GETDATE())), GETDATE()))
OR
('{?break_type}'='PQ' and LDTE between dateadd(quarter,datediff(quarter,0,getdate())-1,0) and DateAdd(day, -1, dateadd(qq, DateDiff(qq, 0, GETDATE()), 0)))
OR
('{?break_type}'='PFY' and LDTE  BETWEEN  cast('01-Jul-' + cast( case when datepart(mm,getdate()) in (7,8,9,10,11,12) then DATEPART(yy,getdate())
             else DATEPART(yy,getdate())-2
               end as varchar) as datetime) AND                       
                              cast('30-Jun-' + cast( case when datepart(mm,getdate()) in (6,7,8,9,10,11,12) then DATEPART(yy,getdate())
                else DATEPART(yy,getdate())-1
               end as varchar) as datetime))
OR
('{?break_type}'='PY' and LDTE between DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) and DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)))))

 

 

e.g. the another column:

 

SELECT   'Current Member' as Record_Type, 
CASE WHEN LTP1.TYP = N'LINE' THEN    
(CASE WHEN H1.HEAD IS NULL THEN N'None' ELSE H1.HEAD END)  
ELSE    
(CASE WHEN H12.HEAD IS NULL THEN N'None' ELSE H12.HEAD END)  
END,  COUNT(*)as Item_Count
FROM BDR  
INNER JOIN MAIN ON BDR.IRN = MAIN.IRN  
INNER JOIN BFS ON BDR.IRN = BFS.IRN  
LEFT OUTER JOIN BDB ON MAIN.IRN = BDB.IRN  
LEFT OUTER JOIN LOCLNK L1 ON BDB.LNK = L1.IRN  
LEFT OUTER JOIN LTP LTP1 ON BDB.LNK = LTP1.IRN  
LEFT OUTER JOIN HEADING H1 ON L1.LNK = H1.IRN  
LEFT OUTER JOIN HEADING H12 ON L1.IRN = H12.IRN  
WHERE (MAIN.CREATED_DATE <= {?end_date} AND (MAIN.DEACT_DATE IS NULL OR MAIN.DEACT_DATE > {?end_date}))
AND BFS.CODE = N'IDALBY' AND (  ( 'ALL' IN {?location}) OR( BDB.LNK IN {?location}) OR (L1.LNK IN {?location}))
GROUP BY   
CASE WHEN LTP1.TYP = N'LINE' THEN    
(CASE WHEN H1.HEAD IS NULL THEN N'None' ELSE H1.HEAD END)  
ELSE    
(CASE WHEN H12.HEAD IS NULL THEN N'None' ELSE H12.HEAD END)  
END

 

I do not use  '{?end_date}'  to pass in parameters, but using break_type, e.g. date, week, month, quarter, so CW is current week, PW is previous week, etc.

When a user pick a break type, then the SQL in my post above doing the filter...

But I think when integrating the break type into the SQL, the logic is not working, the column of 'current members' in the cross-tab report disappears while the other columns still there; however the logic of

WHERE (MAIN.CREATED_DATE <= '{?end_date}' AND (MAIN.DEACT_DATE IS NULL OR MAIN.DEACT_DATE > '{?end_date}'))  works. So I don't know where it's incorrectly coded in the SQL part of my previous post using break_type. PLease further advise