September 24, 2013 at 11:10 pm
hi ,
pls check this query
select cardno,min(datetime1),max(datetime1),action
from view1
group by cardno,datetime1,action
September 25, 2013 at 12:10 am
Why obfuscate such simple logic with a view?
SELECT CARDNO, [Date]=LEFT(b.strDate, 10)
,InTime=SUBSTRING(b.strDate, 12, 5)
,OutTime=SUBSTRING(c.strDate, 12, 5)
FROM table2 a
CROSS APPLY
(
SELECT CONVERT(VARCHAR(19), a.DateTime1, 20)
) b (strDate)
CROSS APPLY
(
SELECT TOP 1 CONVERT(VARCHAR(19), d.DateTime1, 20)
FROM table1 d
WHERE a.CARDNO = d.CARDNO AND d.CHANNEL_NO = 2
ORDER BY Datetime1 DESC
) c (StrDate)
Note: I didn't notice the second page of posts when I posted this, so I'm not sure this solution is still valid. Give it a try and let me know.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 11, 2014 at 4:51 am
Below is my query which is giving MIN(InTime) & MAX(OutTime) for one day of one employee. Now I want to show total effective hours of employee by calculating all the In & Out timings difference of employee for one day.
Any help on this will be appreciated.
WITH cte_tables (zcardno, zdate, zs_datetime, zchannel_no) AS
( SELECT cardno, DATEADD(dd, DATEDIFF(dd, 0, s_datetime), 0), s_datetime, [channel no] as action
FROM transactions where s_datetime >= '2013-03-01'
UNION ALL SELECT cardno, DATEADD(dd, DATEDIFF(dd, 0, s_datetime), 0), s_datetime, Action
FROM custom_transactions where s_datetime >= '2013-03-01'),
cte_minmax (acardno, adate, aMinS_datetime, aMaxS_datetime) AS
(SELECT zcardno, zdate, MIN(zs_datetime), NULL FROM cte_tables
WHERE zchannel_no = 2 GROUP BY zcardno, zdate UNION ALL SELECT zcardno, zdate, NULL, MAX(zs_datetime)
FROM cte_tables WHERE zchannel_no = 1 GROUP BY zcardno, zdate)
SELECT m.name, c.acardno AS cardno, REPLACE(CONVERT(VarChar(50), c.adate, 103),'/','-') AS [date],
LEFT(CONVERT(varchar,MAX(c.aMinS_datetime),108),5) AS InTime,
LEFT(CONVERT(varchar,MAX(c.aMaxS_datetime),108),5) AS OutTime FROM cte_minmax c
inner join master m on c.acardno = m.cardno where acardno in ('14109393') and adate between '2013-06-01' and '2013-06-08'
GROUP BY m.name, c.acardno, c.adate order by c.acardno
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply