Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Topic: SQL in SQL command Posted: 26 Feb 2013 at 1:41pm
Hi,
I would like to let user to choose break for current day/previous day, current week/previous week, etc, and for month, quater, year too when prompts in the report; in the SQL command I would use somehting like below:
CASE WHEN set_break_ = 'C' THEN CASE WHEN break_type = 'CD' THEN LDTE = getdate() // current date CASE WHEN break_type = 'CW' THEN LDTE BETWEEN dateadd(week, datediff(week, 0, (getdate())), 0) AND -- as beginning_of_this_week dateadd(week, datediff(week, 0, (getdate())), 6) --as ending_of_this_week
......
END
END
I got problem where I don't kown how to remove the hh:mm:mm part of query where:
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Posted: 27 Feb 2013 at 12:04pm
Hi,
As I have described before, that if user select curret month, that will pass the parameter where the datefiled - LDTE BETWEEN as below which I have not finished construction:
CASE WHEN set_break_ = 'C' THEN CASE WHEN break_type = 'CD' THEN LDTE = getdate() // current date CASE WHEN break_type = 'CW' THEN LDTE BETWEEN dateadd(week, datediff(week, 0, (getdate())), 0) AND --as beginning_of_this_week dateadd(week, datediff(week, 0, (getdate())), 6) --as ending_of_this_week CASE WHEN break_type = 'CM' THEN LDTE BETWEEN DATEADD(month, datediff(month, 0, (getdate())), 0) AND -- first day of the month convert(datetime, convert(varchar(10), getdate(), 101)) -- last day of the month CASE WHEN break_type = 'CQ' THEN LDTE BETWEEN DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) AND --first day of the quarter dateadd(dd,-1,dateadd(qq,1,DATEADD(qq, DATEDIFF(qq,0,getdate()), 0))) -- last day of the quarter CASE WHEN break_type = 'CY' THEN LDTE BETWEEN DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) AND -- first day of the year
I think I have resolved the sql about 'month' , please help verify, thanks.
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 28 Feb 2013 at 4:33am
you might think about moving this to your WHERE clause.
For clarity, you have a command that has a parameter and based on the selection of the param you want to return specific rows that meet the condition, correct? I think you could streamline your process just a little as you do not really need the date range as much as a match. Using your datediff trick here is an option. I may be missing part of your process but hoefully ithelps you sort out a solution.
WHERE
(@param= 'CD' and datediff(day,table.datefield,getdate())=0)
or
(@param= 'CW' and DATEDIFF(week, 0, GETDATE())=DATEDIFF(week, 0, table.datefield))
or
(@param= 'CQ' and DATEDIFF(qq, 0, GETDATE())=DATEDIFF(qq, 0, table.datefield))
or
(@param= 'CY' and DATEDIFF(yy, 0, GETDATE())=DATEDIFF(yy, 0, table.datefield))
or
(@param= 'PD' and datediff(day,table.datefield,getdate())=1)
or
(@param= 'PW' and DATEDIFF(week, 0, GETDATE())-1=DATEDIFF(week, 0, table.datefield))
or
(@param= 'PQ' and DATEDIFF(qq, 0, GETDATE())-1=DATEDIFF(qq, 0, table.datefield))
or
(@param= 'PY' and DATEDIFF(yy, 0, GETDATE())-1=DATEDIFF(yy, 0, table.datefield))
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Posted: 28 Feb 2013 at 12:15pm
Hi, my apology, I have left some other details about my process of this.
Dell of Admin group has helped construct the following sql in the command to do a crosstab report where all the columns from different tables.
Now I am required to put more parameters including those about break down of day, week, month, etc: although I intend to put them in the where clause, it looks very messy; or should I put in a function?
SELECT 'Issues' 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 as Head, COUNT(*) AS Item_Count FROM OND INNER JOIN BFS ON OND.IRN = BFS.IRN LEFT OUTER JOIN LOCLNK L1 ON OND.LOC = L1.IRN LEFT OUTER JOIN LTP LTP1 ON OND.LOC = LTP1.IRN LEFT OUTER JOIN HEADING H1 ON L1.LNK = H1.IRN LEFT OUTER JOIN HEADING H12 ON L1.IRN = H12.IRN WHERE LDTE BETWEEN {?start_date} AND {?end_date} AND BFS.CODE = N'IDALBY' AND (( 'ALL' IN {?location} ) OR (OND.LOC 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 UNION ALL SELECT 'Reservations' 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 RVP INNER JOIN RVC ON RVP.IRN = RVC.IRN INNER JOIN BFS ON RVP.IRN = BFS.IRN LEFT OUTER JOIN LOCLNK L1 ON RVP.PLOC = L1.IRN LEFT OUTER JOIN LTP LTP1 ON RVP.PLOC = LTP1.IRN LEFT OUTER JOIN HEADING H1 ON L1.LNK = H1.IRN LEFT OUTER JOIN HEADING H12 ON L1.IRN = H12.IRN WHERE RVP.DTE BETWEEN {?start_date} AND {?end_date} AND BFS.CODE = N'IDALBY' AND ( ( 'ALL' IN {?location} ) OR (RVP.PLOC 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 UNION ALL
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Posted: 02 Mar 2013 at 1:35am
Hi
Is there any way I can import a text file like
CD Current day
CW Current 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.
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Posted: 04 Mar 2013 at 4:56pm
Hi,
I have integrated Where clause into the SQL command where:
SELECT 'Issues' 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 as Head, COUNT(*) AS Item_Count FROM OND INNER JOIN BFS ON OND.IRN = BFS.IRN INNER JOIN ICO ON OND.ITM = ICO.IRN INNER JOIN MAIN ON ICO.COL = MAIN.IRN LEFT OUTER JOIN LOCLNK L1 ON OND.LOC = L1.IRN LEFT OUTER JOIN LTP LTP1 ON OND.LOC = LTP1.IRN LEFT OUTER JOIN HEADING H1 ON L1.LNK = H1.IRN LEFT OUTER JOIN HEADING H12 ON L1.IRN = H12.IRN WHERE (({?break_type} = 'CD' and datediff(day, LDTE, getdate())=0) or ({?break_type} = 'CW' and DATEDIFF(week, 0, GETDATE())= DATEDIFF(week, 0, LDTE)) or ({?break_type}= 'CQ' and DATEDIFF(qq, 0, GETDATE())= DATEDIFF(qq, 0, LDTE)) or ({?break_type} = 'CY' and DATEDIFF(yy, 0, GETDATE())= DATEDIFF(yy, 0, LDTE)))
AND BFS.CODE = N'IDALBY' AND NOT MAIN.XCODE IN ('EBOOK', 'MDEVICE') AND (( 'ALL' IN {?location} ) OR (OND.LOC 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
when running the report, it generates an error where;
....Description: Inocrrect syntax near '=', SQL state 42000 Native Error:102(Database Vedor code:102).
It seems that Where clause is correct, and could not figure out where was wrong. Could you help have a look?
I think the error may refer to the '=' in between ({?break_type} = 'CW' and DATEDIFF(week, 0, GETDATE()) = DATEDIFF(week, 0, LDTE))
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
