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 ««12

Need urgent help with query Expand / Collapse
Author
Message
Posted Monday, July 29, 2013 7:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:14 PM
Points: 13,126, Visits: 11,964
kanchan 58240 (7/29/2013)
Hi,

Thanks for all your replies. Here is the query worked as required.

select  t.cardno ,dateadd(dd, -datediff(dd, t.datetime1, 1), 1) AS date,

(select SUBSTRING(CONVERT(varchar, min(st.datetime1), 100), 13, 2) + ':' + SUBSTRING(CONVERT(varchar,min(st.datetime1), 100), 16, 2) + ' ' + SUBSTRING(CONVERT(varchar, min(st.datetime1), 100), 18, 2) from view1 st
where t.cardno=st.cardno and dateadd(dd, -datediff(dd, t.datetime1, 1), 1)=dateadd(dd, -datediff(dd, st.datetime1, 1), 1) and st.action='1'
)as InTime,
(select SUBSTRING(CONVERT(varchar, max(st.datetime1), 100), 13, 2) + ':' + SUBSTRING(CONVERT(varchar,max(st.datetime1), 100), 16, 2) + ' ' + SUBSTRING(CONVERT(varchar, max(st.datetime1), 100), 18, 2) from view1 st
where t.cardno=st.cardno and dateadd(dd, -datediff(dd, t.datetime1, 1), 1)=dateadd(dd, -datediff(dd, st.datetime1, 1), 1) and st.action='2'
)as OutTime
from view1 t where t.cardno in ('111111','222222') and group by t.cardno, dateadd(dd, -datediff(dd, t.datetime1, 1), 1)



Thanks for posting your solution. However this is not correct. It can't possibly work because you have
AND GROUP BY


Perhaps if you post your actual code for your solution we can offer a better alternative than multiple subselects each with lots of string manipulation.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1478576
Posted Monday, July 29, 2013 10:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 6, 2013 12:03 AM
Points: 6, Visits: 10
Hi Thanks for the reply.

Yes. from the query we need to remove 'and'.

The column datetime1 in both the table is of type datetime.
The values which are present are like '2013-02-04 10:45:00.000', '2013-02-10 18:45:00.000', '2013-04-01 09:20:00.000', etc. From this value, I want to retrieve in the form of hours and minute.

The end result values will be
10:45, 18:45, 09:20, etc.

Could you please help me out to get the result in the above form?

Thanks in advance.
Post #1478815
Posted Tuesday, July 30, 2013 7:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:14 PM
Points: 13,126, Visits: 11,964
kanchan 58240 (7/29/2013)
Hi Thanks for the reply.

Yes. from the query we need to remove 'and'.

The column datetime1 in both the table is of type datetime.
The values which are present are like '2013-02-04 10:45:00.000', '2013-02-10 18:45:00.000', '2013-04-01 09:20:00.000', etc. From this value, I want to retrieve in the form of hours and minute.

The end result values will be
10:45, 18:45, 09:20, etc.

Could you please help me out to get the result in the above form?

Thanks in advance.


I formatted this a bit so it is more legible. I also removed all of the date formatting. Honestly, your date formatting should be left to the front end instead of doing it in sql.

select  t.cardno ,dateadd(dd, -datediff(dd, t.datetime1, 1), 1) AS date,
(
select min(st.datetime1)
from view1 st
where t.cardno = st.cardno
and dateadd(dd, -datediff(dd, t.datetime1, 1), 1) = dateadd(dd, -datediff(dd, st.datetime1, 1), 1)
and st.action = '1'
)as InTime,
(
select max(st.datetime1)
from view1 st
where t.cardno = st.cardno
and dateadd(dd, -datediff(dd, t.datetime1, 1), 1) = dateadd(dd, -datediff(dd, st.datetime1, 1), 1)
and st.action = '2'
)as OutTime
from view1 t
--where t.cardno in ('111111','222222')
group by t.cardno, dateadd(dd, -datediff(dd, t.datetime1, 1), 1)



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1479007
Posted Tuesday, July 30, 2013 8:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:39 AM
Points: 101, Visits: 317
Use CTEs to eliminate subselects and make your code more readable (the view is also eliminated by the first cte):

WITH cte_tables (zCardNo, zDate, zDatetime1, zChannel_no)
AS ( SELECT cardno, CONVERT(DATE, datetime1), datetime1, channel_no
FROM TABLE1
UNION ALL
SELECT cardno, CONVERT(DATE, datetime1), datetime1, Action
FROM TABLE2
)
, cte_minmax (aCardNo, aDate, aMinDatetime1, aMaxDatetime1)
AS (
SELECT zCardNo, zDate, MIN(zDatetime1), NULL
FROM cte_tables
WHERE zChannel_no = 1
GROUP BY zCardNo, zDate

UNION ALL
SELECT zCardNo, zDate, NULL, MAX(zDatetime1)
FROM cte_tables
WHERE zChannel_no = 2
GROUP BY zCardNo, zDate
)
SELECT aCardNo AS cardno
, REPLACE(CONVERT(VarChar(50), aDate, 103),'/','-') AS [date]
, LEFT(CONVERT(varchar,MAX(aMinDatetime1),108),5) AS intime
, LEFT(CONVERT(varchar,MAX(aMaxDatetime1),108),5) AS outtime
FROM cte_minmax
GROUP BY aCardNo, aDate
Post #1479021
Posted Tuesday, August 6, 2013 12:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 6, 2013 12:03 AM
Points: 6, Visits: 10
Hi

Thanks a lot R. Brush.

You are simply great. The query worked as required.
Post #1481163
Posted Tuesday, September 24, 2013 11:10 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 23, 2014 4:13 AM
Points: 73, Visits: 333
hi ,
pls check this query


select cardno,min(datetime1),max(datetime1),action
from view1
group by cardno,datetime1,action
Post #1498123
Posted Wednesday, September 25, 2013 12:10 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:07 PM
Points: 3,617, Visits: 5,236
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1498131
Posted Tuesday, February 11, 2014 4:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 10:53 PM
Points: 1, Visits: 3
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
Post #1540136
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse