We help IT Professionals succeed at work.

Refinement in Sql Select

Mateen
Mateen asked
on
253 Views
Last Modified: 2012-06-27
This question is identical to

https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20929954.html

I got the solution but there are some concerns.

I have cut the data sort and have eliminated few dates.

select t_date,a1,serial from mateen2

t_date            a1      serial
smalldatetime      (char 2)      integer
2003-01-01 00:00:00      71      1
2003-01-02 00:00:00      01      2
2003-01-03 00:00:00      47      3
2003-01-04 00:00:00      60      4
holiday
2003-01-06 00:00:00      96      5
2003-01-07 00:00:00      66      6
2003-01-08 00:00:00      41      7
2003-01-09 00:00:00      43      8
2003-01-10 00:00:00      00      9   -- note a1=00
2003-01-11 00:00:00      43      10
holiday
2003-01-13 00:00:00      03      11  -- note 03 should not be shown as 3
2003-01-14 00:00:00      82      12
2003-01-15 00:00:00      39      13
holiday
2003-01-17 00:00:00      39      14
2003-01-18 00:00:00      87      15
2003-01-19 00:00:00      18      16
2003-01-20 00:00:00      60      17
2003-01-21 00:00:00      49      18
holiday
2003-01-23 00:00:00      60      19
2003-01-24 00:00:00      00      20     --note a1=00
2003-01-25 00:00:00      61      21
2003-01-26 00:00:00      06      22
2003-01-27 00:00:00      08      23
2003-01-28 00:00:00      96      24
2003-01-29 00:00:00      70      25
2003-01-30 00:00:00      81      26
2003-01-31 00:00:00      51      27

t_date is primary key
difference between two serial is always 1.


select
SUM(CASE WHEN datepart(dw,t_date+2) = 1 THEN a1 ELSE 0 END) as fr1,
SUM(CASE WHEN datepart(dw,t_date+2) = 2 THEN a1 ELSE 0 END) as sat,
SUM(CASE WHEN datepart(dw,t_date+2) = 3 THEN a1 ELSE 0 END) as sun,
SUM(CASE WHEN datepart(dw,t_date+2) = 4 THEN a1 ELSE 0 END) as mon,
SUM(CASE WHEN datepart(dw,t_date+2) = 5 THEN a1 ELSE 0 END) as tue,
SUM(CASE WHEN datepart(dw,t_date+2) = 6 THEN a1 ELSE 0 END) as wed,
SUM(CASE WHEN datepart(dw,t_date+2) = 7 THEN a1 ELSE 0 END) as thu
from mateen2
group by year(t_date+2),
              datepart(week,t_date+2)

This code is bringing correct result.

fri sat sun mon tue wed thu
0   0      0   0   0   71  1
47  60      0   96  66  41  43
0   43      0   3   82  39  0
39  87      18  60  49  0   60
0   61      6   8   96  70  81
51  0      0   0   0   0   0

What's wrong above:
1) first line should contain blank spaces upto 'tue'
2) first line under thu should be '01' not 1
3) seconde line under 'sun' should be blank space
4) third line 0 should be 00 undere fri
etc.

Whate I mean is that column a1 contains data between '00' to '99' and
its type is character.


The result  Should be

fri  sat  sun  mon  tue  wed  thu
                                  71   01
47   60        96   66      41   43
00   43        03   82     39    
39   87  18  60   49             60
00   61  06  08   96      70   81
51      




Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2011

Commented:
do you really need to aggregate the A1's  9   (ie sum)
can you just use either Min or Max
because your summing sql server is converting to an int and giving you a number......

if you need sum then

Select
case fri when 0 then space(2) else right('00' + convert(varchar(2),fri),2) end as fri
case sat when 0 then space(2) else right('00' + convert(varchar(2),sat),2) end as sat
case sun when 0 then space(2) else right('00' + convert(varchar(2),sun),2) end as sun
case mon when 0 then space(2) else right('00' + convert(varchar(2),mon),2) end as mon
case tue when 0 then space(2) else right('00' + convert(varchar(2),tue),2) end as tue
case wed when 0 then space(2) else right('00' + convert(varchar(2),wed),2) end as wed
case thu when 0 then space(2) else right('00' + convert(varchar(2),thu),2) end as thu
from (
select
SUM(CASE WHEN datepart(dw,t_date+2) = 1 THEN a1 ELSE 0 END) as fr1,
SUM(CASE WHEN datepart(dw,t_date+2) = 2 THEN a1 ELSE 0 END) as sat,
SUM(CASE WHEN datepart(dw,t_date+2) = 3 THEN a1 ELSE 0 END) as sun,
SUM(CASE WHEN datepart(dw,t_date+2) = 4 THEN a1 ELSE 0 END) as mon,
SUM(CASE WHEN datepart(dw,t_date+2) = 5 THEN a1 ELSE 0 END) as tue,
SUM(CASE WHEN datepart(dw,t_date+2) = 6 THEN a1 ELSE 0 END) as wed,
SUM(CASE WHEN datepart(dw,t_date+2) = 7 THEN a1 ELSE 0 END) as thu
) as X


Author

Commented:
Hi LowFatSpread:
Sum has been used as technique to line up column a1 across days. Your code will produce
wrong result as it is not catering for data '00' (see '2003-01-10' ,'2003-01-24').

What I want is to view a1 across daywise. And the day column should start from fri to thu.
such that under missing date day should be empty value otherwise it should have value of a1.

Author

Commented:
LowfatSpread
<What I want is to view a1 across daywise. And the day column should start from fri to thu>

What I want is to view a1 (WHICH HAS DATA BETWEEN '00' TO '99')
CERTIFIED EXPERT
Top Expert 2011

Commented:
Then you're not summing so
either this
Select
coalesce(right('00' + convert(varchar(2),fri),2),space(2)) end as fri,
coalesce(right('00' + convert(varchar(2),Sat),2),space(2)) end as Sat,
coalesce(right('00' + convert(varchar(2),Sun),2),space(2)) end as Sun,
coalesce(right('00' + convert(varchar(2),Mon),2),space(2)) end as Mon,
coalesce(right('00' + convert(varchar(2),Tue),2),space(2)) end as Tue,
coalesce(right('00' + convert(varchar(2),Wed),2),space(2)) end as Wed,
coalesce(right('00' + convert(varchar(2),Thu),2),space(2)) end as Thu
from (
select
SUM(CASE WHEN datepart(dw,t_date+2) = 1 THEN a1 ELSE null END) as fr1,
SUM(CASE WHEN datepart(dw,t_date+2) = 2 THEN a1 ELSE null END) as sat,
SUM(CASE WHEN datepart(dw,t_date+2) = 3 THEN a1 ELSE null END) as sun,
SUM(CASE WHEN datepart(dw,t_date+2) = 4 THEN a1 ELSE null END) as mon,
SUM(CASE WHEN datepart(dw,t_date+2) = 5 THEN a1 ELSE null END) as tue,
SUM(CASE WHEN datepart(dw,t_date+2) = 6 THEN a1 ELSE null END) as wed,
SUM(CASE WHEN datepart(dw,t_date+2) = 7 THEN a1 ELSE null END) as thu
) as X


or this


select
coalesce(MIN(CASE Dayno WHEN 1 THEN a1 ELSE null END),space(2)) as fr1,
coalesce(MIN(CASE Dayno WHEN 2 THEN a1 ELSE null END),space(2)) as sat,
coalesce(MIN(CASE Dayno WHEN 3 THEN a1 ELSE null END),space(2)) as sun,
coalesce(MIN(CASE Dayno WHEN 4 THEN a1 ELSE null END),space(2)) as mon,
coalesce(MIN(CASE Dayno WHEN 5 THEN a1 ELSE null END),space(2)) as tue,
coalesce(MIN(CASE Dayno WHEN 6 THEN a1 ELSE null END),space(2)) as wed,
coalesce(MIN(CASE Dayno WHEN 7 THEN a1 ELSE null END),space(2)) as thu
fROM (SELECT datepart(dw,t_date+2) AS DAYNO,a1 from ...) as X

Author

Commented:
Lowfat Spread:

For your easy working please copy following lines into query analizer to create table mateen2.


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mateen2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[mateen2]
GO

CREATE TABLE [dbo].[mateen2] (
      [t_date] [smalldatetime] NOT NULL ,
      [a1] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [serial] [numeric](5, 0) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[mateen2] WITH NOCHECK ADD
      CONSTRAINT [PK_mateen2] PRIMARY KEY  CLUSTERED
      (
            [t_date]
      )  ON [PRIMARY]
GO

insert mateen2
select '2003-01-01 00:00:00','71',1
union all
select '2003-01-02 00:00:00','01',2
union all
select '2003-01-03 00:00:00','47',3
union all
select '2003-01-04 00:00:00','60',4
union all
select '2003-01-06 00:00:00','96',5
union all
select '2003-01-07 00:00:00','66',6
union all
select '2003-01-08 00:00:00','41',7
union all
select '2003-01-09 00:00:00','43',8
union all
select '2003-01-10 00:00:00','00',9
union all
select '2003-01-11 00:00:00','43',10
union all
select '2003-01-13 00:00:00','03',11  
union all
select '2003-01-14 00:00:00','82',12
union all
select '2003-01-15 00:00:00','39',13
union all
select '2003-01-17 00:00:00','39',14
union all
select '2003-01-18 00:00:00','87',15
union all
select '2003-01-19 00:00:00','18',16
union all
select '2003-01-20 00:00:00','60',17
union all
select '2003-01-21 00:00:00','49',18
union all
select '2003-01-23 00:00:00','60',19
union all
select '2003-01-24 00:00:00','00',20
union all
select '2003-01-25 00:00:00','61',21
union all
select '2003-01-26 00:00:00','06',22
union all
select '2003-01-27 00:00:00','08',23
union all
select '2003-01-28 00:00:00','96',24
union all
select '2003-01-29 00:00:00','70',25
union all
select '2003-01-30 00:00:00','81',26
union all
select '2003-01-31 00:00:00','51',27

Author

Commented:
Hi everybody:
I will be available on monday.
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:

Dear Experts,

i m co worker of Mateen .. he is ill . he will be back after some days

Regard

shahid

Author

Commented:
Dear experts:

Sorry for delayed response because of my illness and not having net at home.

Mateen:
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.