sql query with complex logic

Question

sql query with complex logic

abhijit 71888

Newbie

Points: 9
More actions
March 1, 2015 at 7:40 am

#295586

Hi,
This is my first post in this forum...and I need your support badly as I am struggling with this for last couple of days...
Here is the following Data Structure of tbltestnew1
^{idtimestamp Bid ChangeAsk Change aveg}
5520090501 00:00:00.833 NULL NULL1.28696500
5620090501 01:01:01.582 0.0000700.0000701.28703500
5720090501 02:01:01.582 0.0000000.0000701.28707000
5820090501 03:01:01.582 0.0000600.0000001.28710000
5920090501 15:01:01.582 0.0001200.0000601.28719000
6020090501 16:01:01.582 -0.0000800.0001201.28721000
6120090501 20:59:01.582 -0.000060-0.0000801.28714000
6220090503 21:05:00.833 0.000050-0.0000601.28713500
6320090503 22:01:01.582 -0.000130 0.0000501.28709500
6420090503 23:01:01.582 0.000040 -0.0001301.28705000
6520090504 00:00:00.833 -0.000120 0.0000401.28701000
6620090504 01:01:01.582-0.000040-0.0001201.28693000
6720090504 02:01:01.5820.000060-0.0000401.28694000
6820090504 03:01:01.5820.0000400.0000601.28699000
6920090504 15:01:01.582-0.0000400.0000401.28699000
7020090504 16:01:01.5820.000000-0.0000401.28697000
7120090504 23:01:01.582-0.0001400.0000001.28690000
7220090505 00:00:00.833-0.000110-0.0001401.28677500
7320090505 01:01:01.582-0.000040-0.0001101.28670000
7420090505 02:01:01.5820.000110-0.0000401.28673500
7520090505 03:01:01.5820.0000500.0001101.28681500
7620090505 15:01:01.5820.0000000.0000501.28684000
7720090505 16:01:01.582-0.0000800.0000001.28680000
7820090505 23:01:01.5820.000000-0.0000801.28676000
7920090506 00:00:00.8330.0000300.0000001.28677500
8020090506 01:01:01.582-0.0000900.0000301.28674500
8120090506 02:01:01.582-0.0000700.0000001.28671000
8220090506 03:01:01.582-0.000070-0.0001601.28659500
8320090506 15:01:01.5820.000440-0.0000701.28678000
8420090506 16:01:01.5820.0000000.0004401.28700000
Here timestamp is varchar now I need the result in following format...
TIMESTAMP Sum of changes (Bid)Sum of changes (Ask)
20090501 15:01:01.582 0.0002500 0.0002000
20090504 15:01:01.582 -0.0002800 -0.0001200
20090505 15:01:01.582 -0.0001300 -0.0001700
20090506 15:01:01.582 0.0001600 -0.0002800
Now to get this result I have designed the follwoing SQL QUERY
_{select date=case when substring(timestamp,10,2) <= 15}
then substring(timestamp,1,8) else DATEADD("dd",1,substring(timestamp,1,8)) end,
SUM(isnull([Bid Change],0)), SUM([Ask Change]), MAX(aveg),MIN(aveg) from tbltestnew1
group by (case when substring(timestamp,10,2) <= 15
then substring(timestamp,1,8) else DATEADD("dd",1,substring(timestamp,1,8)) end),
CURR
But this query is not working for me as in timestamp the dates are not in serial order there is some gap there...please help me with this issue...since am new so this post might not be well formatted so am apologizing in advance...

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

abhijit 71888 (3/1/2015)
Hi,
This is my first post in this forum...and I need your support badly as I am struggling with this for last couple of days...
Here is the following Data Structure of tbltestnew1
^{idtimestamp Bid ChangeAsk Change aveg}
5520090501 00:00:00.833 NULL NULL1.28696500
5620090501 01:01:01.582 0.0000700.0000701.28703500
5720090501 02:01:01.582 0.0000000.0000701.28707000
5820090501 03:01:01.582 0.0000600.0000001.28710000
5920090501 15:01:01.582 0.0001200.0000601.28719000
6020090501 16:01:01.582 -0.0000800.0001201.28721000
6120090501 20:59:01.582 -0.000060-0.0000801.28714000
6220090503 21:05:00.833 0.000050-0.0000601.28713500
6320090503 22:01:01.582 -0.000130 0.0000501.28709500
6420090503 23:01:01.582 0.000040 -0.0001301.28705000
6520090504 00:00:00.833 -0.000120 0.0000401.28701000
6620090504 01:01:01.582-0.000040-0.0001201.28693000
6720090504 02:01:01.5820.000060-0.0000401.28694000
6820090504 03:01:01.5820.0000400.0000601.28699000
6920090504 15:01:01.582-0.0000400.0000401.28699000
7020090504 16:01:01.5820.000000-0.0000401.28697000
7120090504 23:01:01.582-0.0001400.0000001.28690000
7220090505 00:00:00.833-0.000110-0.0001401.28677500
7320090505 01:01:01.582-0.000040-0.0001101.28670000
7420090505 02:01:01.5820.000110-0.0000401.28673500
7520090505 03:01:01.5820.0000500.0001101.28681500
7620090505 15:01:01.5820.0000000.0000501.28684000
7720090505 16:01:01.582-0.0000800.0000001.28680000
7820090505 23:01:01.5820.000000-0.0000801.28676000
7920090506 00:00:00.8330.0000300.0000001.28677500
8020090506 01:01:01.582-0.0000900.0000301.28674500
8120090506 02:01:01.582-0.0000700.0000001.28671000
8220090506 03:01:01.582-0.000070-0.0001601.28659500
8320090506 15:01:01.5820.000440-0.0000701.28678000
8420090506 16:01:01.5820.0000000.0004401.28700000
Here timestamp is varchar now I need the result in following format...
TIMESTAMP Sum of changes (Bid)Sum of changes (Ask)
20090501 15:01:01.582 0.0002500 0.0002000
20090504 15:01:01.582 -0.0002800 -0.0001200
20090505 15:01:01.582 -0.0001300 -0.0001700
20090506 15:01:01.582 0.0001600 -0.0002800
Now to get this result I have designed the follwoing SQL QUERY
_{select date=case when substring(timestamp,10,2) <= 15}
then substring(timestamp,1,8) else DATEADD("dd",1,substring(timestamp,1,8)) end,
SUM(isnull([Bid Change],0)), SUM([Ask Change]), MAX(aveg),MIN(aveg) from tbltestnew1
group by (case when substring(timestamp,10,2) <= 15
then substring(timestamp,1,8) else DATEADD("dd",1,substring(timestamp,1,8)) end),
CURR
But this query is not working for me as in timestamp the dates are not in serial order there is some gap there...please help me with this issue...since am new so this post might not be well formatted so am apologizing in advance...

Hi and welcome aboard. Please see the article at the first link in my signature line below under "helpful links". You'll get help that way much more quickly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 2

Hi. You need to provide test data so people can easily test your problem... like this:

--Here is the following Data Structure of tbltestnew1

use [tempdb]

go

if object_id('tbltestnew1') is not null drop table tbltestnew1;

create table tbltestnew1

(

id int

,[timestamp] varchar(25)

,[Bid Change] [decimal](7,6)

,[Ask Change] [decimal](7,6)

,[aveg] [decimal](9,8)

)

INSERT tbltestnew1

(id, [timestamp],[Bid Change],[Ask Change],[aveg])

VALUES

(55, '20090501 00:00:00.833', NULL, NULL, 1.28696500),

(56, '20090501 01:01:01.582', 0.000070, 0.000070, 1.28703500),

(57, '20090501 02:01:01.582', 0.000000, 0.000070, 1.28707000),

(58, '20090501 03:01:01.582', 0.000060, 0.000000, 1.28710000),

(59, '20090501 15:01:01.582', 0.000120, 0.000060, 1.28719000),

(60, '20090501 16:01:01.582', -0.000080, 0.000120, 1.28721000),

(61, '20090501 20:59:01.582', -0.000060, -0.000080, 1.28714000),

(62, '20090503 21:05:00.833', 0.000050, -0.000060, 1.28713500),

(63, '20090503 22:01:01.582', -0.000130, 0.000050, 1.28709500),

(64, '20090503 23:01:01.582', 0.000040, -0.000130, 1.28705000),

(65, '20090504 00:00:00.833', -0.000120, 0.000040, 1.28701000),

(66, '20090504 01:01:01.582', -0.000040, -0.000120, 1.28693000),

(67, '20090504 02:01:01.582', 0.000060, -0.000040, 1.28694000),

(68, '20090504 03:01:01.582', 0.000040, 0.000060, 1.28699000),

(69, '20090504 15:01:01.582', -0.000040, 0.000040, 1.28699000),

(70, '20090504 16:01:01.582', 0.000000, -0.000040, 1.28697000),

(71, '20090504 23:01:01.582', -0.000140, 0.000000, 1.28690000),

(72, '20090505 00:00:00.833', -0.000110, -0.000140, 1.28677500),

(73, '20090505 01:01:01.582', -0.000040, -0.000110, 1.28670000),

(74, '20090505 02:01:01.582', 0.000110, -0.000040, 1.28673500),

(75, '20090505 03:01:01.582', 0.000050, 0.000110, 1.28681500),

(76, '20090505 15:01:01.582', 0.000000, 0.000050, 1.28684000),

(77, '20090505 16:01:01.582', -0.000080, 0.000000, 1.28680000),

(78, '20090505 23:01:01.582', 0.000000, -0.000080, 1.28676000),

(79, '20090506 00:00:00.833', 0.000030, 0.000000, 1.28677500),

(80, '20090506 01:01:01.582', -0.000090, 0.000030, 1.28674500),

(81, '20090506 02:01:01.582', -0.000070, 0.000000, 1.28671000),

(82, '20090506 03:01:01.582', -0.000070, -0.000160, 1.28659500),

(83, '20090506 15:01:01.582', 0.000440, -0.000070, 1.28678000),

(84, '20090506 16:01:01.582', 0.000000, 0.000440, 1.28700000)

--Here timestamp is varchar now I need the result in following format...

/*

TIMESTAMP Sum of changes (Bid) Sum of changes (Ask)

20090501 15:01:01.582 0.0002500 0.0002000

20090504 15:01:01.582 -0.0002800 -0.0001200

20090505 15:01:01.582 -0.0001300 -0.0001700

20090506 15:01:01.582 0.0001600 -0.0002800

*/

--Now to get this result I have designed the follwoing SQL QUERY

-- Modified: order by Date

select

[date]=case when substring(timestamp,10,2) <= 15 then substring(timestamp,1,8)

else DATEADD("dd",1,substring(timestamp,1,8)) end,

[Sum of Changes (Bid)]=SUM(isnull([Bid Change],0)),

[Sum of Changes (Ask)]=SUM([Ask Change]),

[Max Average]=MAX(aveg),

[Min Average]=MIN(aveg)

from tbltestnew1

group by (case when substring(timestamp,10,2) <= 15 then substring(timestamp,1,8)

else DATEADD("dd",1,substring(timestamp,1,8)) end)

order by [date]

--But this query is not working for me as in timestamp the dates are not in serial order there is some gap there...please help me with this issue...since am new so this post might not be well formatted so am apologizing in advance...

Results are:

2009-05-01 00:00:00.0000.0002500.0002001.287190001.28696500

2009-05-02 00:00:00.000-0.0001400.0000401.287210001.28714000

2009-05-04 00:00:00.000-0.000140-0.0001601.287135001.28693000

2009-05-05 00:00:00.000-0.000130-0.0001701.286970001.28670000

2009-05-06 00:00:00.0000.000160-0.0002801.286800001.28659500

2009-05-07 00:00:00.0000.0000000.0004401.287000001.28700000

There are a few issues here:

Using DATEADD in the Date column has forced a conversion to DateTime data type - this is not a very good way of doing this... so there are a few changes that could be made

But if I've got it right, your main problem is that 2009-05-03 is missing.

Is that right?