Posted: 12 Aug 2013 09:26 PM PDT

Hi All,CREATE TABLE TBL_SAMPLE( Name nVarchar(5), Unit nVarchar(3), Figure Int)INSERT INTO TBL_SAMPLE VALUES('ABC','m',1)INSERT INTO TBL_SAMPLE VALUES('PQR','m',1)INSERT INTO TBL_SAMPLE VALUES('XYZ','m',1)INSERT INTO TBL_SAMPLE VALUES('ABC','ft',2)INSERT INTO TBL_SAMPLE VALUES('PQR','ft',2)INSERT INTO TBL_SAMPLE VALUES('XYZ','ft',2)SELECT * FROM TBL_SAMPLE OUTPUT: Name Unit Figure------------------------------ABC m 1PQR m 1XYZ m 1ABC ft 2PQR ft 2XYZ ft 2How to get output like below according to above records.Name m ft---------------------ABC 1 2PQR 1 2XYZ 1 2Please give the solution.Your Help will be appreciableIt helps me alot.Thanks In Advance,Venki Desai.

Posted: 13 Aug 2013 12:03 AM PDT

The system I am helping maintain must store SSNs - no choice there. What's the best way/practice to store this? I imagine I probably want to encrypt it somehow in the field properties?If it helps with suggestions, this is an internal app - the data will never be exposed to the outside world.Thanks!

to find avg between intervel

Posted: 12 Aug 2013 05:44 PM PDT

I had requirement to find avg of values between intervals like 15,30,60I had written query browsing some internet fortunately I got results right bu I want know how datedif(min,0,datetime)/60*60 works please reply ALTER procedure [dbo].[sale]@st datetime,@typ intasbegincreate table temp56 (DateAndTime datetime, kwh float, PF float,LLAVG float,LNAVG float,[avg CURRENT] float,frequency float )create table tempFIN (DateAndTime datetime, KWH float, PF float,LLAVG float,LNAVG float,AVGCURRENT float,FREQ float )begininsert into temp56select a.DateAndTime as dateandtime,a.Val as kwh,b.val as PF,c.val as LLAVG,d.val as LNAVG ,e.val AS [avg CURRENT],f.Val AS frequency from( select dateandtime ,val from dbo.FloatTable where tagindex=0 and dateandtime>=@st and dateandtime<=dateadd(dd,1,@st))as a join (select dateandtime ,val from dbo.FloatTable where tagindex=1 and dateandtime>=@st and dateandtime<=dateadd(dd,1,@st))as b on(a.DateAndTime=b.DateAndTime) join(select dateandtime ,val from dbo.FloatTable where tagindex=2 and dateandtime>=@st and dateandtime<=dateadd(dd,1,@st))as c on(b.DateAndTime=c.DateAndTime) join(select dateandtime ,val from dbo.FloatTable where tagindex=3 and dateandtime>=@st and dateandtime<=dateadd(dd,1,@st))as d on(c.DateAndTime=d.DateAndTime) join(select dateandtime ,val from dbo.FloatTable where tagindex=4 and dateandtime>=@st and dateandtime<=dateadd(dd,1,@st))as e on(d.DateAndTime=e.DateAndTime) join(select dateandtime ,val from dbo.FloatTable where tagindex=5 and dateandtime>=@st and dateandtime<=dateadd(dd,1,@st))as f on(e.DateAndTime=f.DateAndTime) endif(@typ=15)beginINSERT INTO tempFINselect dateadd(minute,datediff(minute,0,dateandtime)/15*15,0) as t,AVG(kwh) as KWH,AVG(PF)AS PF,AVG(LLAVG)AS LLAVG,AVG(LNAVG) AS LNAVG,AVG([AVG CURRENT])AS AVGCURRENT,AVG(frequency)AS FREQ from temp56GROUP BY dateadd(minute,datediff(minute,0,dateandtime)/15*15,0)order by tendelse if(@typ=30)beginINSERT INTO tempFINselect dateadd(minute,datediff(minute,0,dateandtime)/30*30,0) as t,AVG(kwh) as KWH,AVG(PF)AS PF,AVG(LLAVG)AS LLAVG,AVG(LNAVG) AS LNAVG,AVG([AVG CURRENT])AS AVGCURRENT,AVG(frequency)AS FREQ from temp56GROUP BY dateadd(minute,datediff(minute,0,dateandtime)/30*30,0)order by tendelse if(@typ=60)beginINSERT INTO tempFINselect dateadd(minute,datediff(minute,0,dateandtime)/60*60,0) as t,AVG(kwh) as KWH,AVG(PF)AS PF,AVG(LLAVG)AS LLAVG,AVG(LNAVG) AS LNAVG,AVG([AVG CURRENT])AS AVGCURRENT,AVG(frequency)AS FREQ from temp56GROUP BY dateadd(minute,datediff(minute,0,dateandtime)/60*60,0)endelsebegiNINSERT INTO tempFINselect * from temp56endselect * from tempFINorder by DateAndTimedrop table tempFINdrop table temp56end

Sql samples, sql tips

Tuesday, August 13, 2013

[SQL Server] how to convert vertical data to horizontal

[SQL Server] how to convert vertical data to horizontal

No comments:

Post a Comment

Search This Blog