?
Solved

Refinement in Sql Select

Posted on 2004-04-03
10
Medium Priority
?
241 Views
Last Modified: 2012-06-27
This question is identical to

http://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      




0
Comment
Question by:Mateen
  • 6
  • 3
10 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10746891
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


0
 

Author Comment

by:Mateen
ID: 10746921
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.

0
 

Author Comment

by:Mateen
ID: 10746931
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')
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10747024
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

0
 

Author Comment

by:Mateen
ID: 10747164
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

0
 

Author Comment

by:Mateen
ID: 10747427
Hi everybody:
I will be available on monday.
0
 
LVL 7

Assisted Solution

by:ChrisFretwell
ChrisFretwell earned 400 total points
ID: 10748930
If you add

group by year(t_date+2),datepart(week,t_date+2)

to Lowfatspreads code, I believe it gives you exactly what you want...I his/her code with the group by added and it returned

fr1  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                              

so the end code would be


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
group by year(t_date+2),datepart(week,t_date+2)
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 1600 total points
ID: 10751454
ok


try 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(week,t_date+2) as w,year(t_date+2) as y, datepart(dw,t_date+2) AS DAYNO,a1 from mateen2) as x
group by y,w

the other example was wrong and would need to be

Select
coalesce(right('00' + convert(varchar(2),fri),2),space(2)) as fri,
coalesce(right('00' + convert(varchar(2),Sat),2),space(2))  as Sat,
coalesce(right('00' + convert(varchar(2),Sun),2),space(2)) as Sun,
coalesce(right('00' + convert(varchar(2),Mon),2),space(2))  as Mon,
coalesce(right('00' + convert(varchar(2),Tue),2),space(2))  as Tue,
coalesce(right('00' + convert(varchar(2),Wed),2),space(2))  as Wed,
coalesce(right('00' + convert(varchar(2),Thu),2),space(2))  as Thu
from (
select
min(CASE WHEN datepart(dw,t_date+2) = 1 THEN a1 ELSE null END) as fri,
min(CASE WHEN datepart(dw,t_date+2) = 2 THEN a1 ELSE null END) as sat,
etc..


hth
0
 

Author Comment

by:Mateen
ID: 10795832

Dear Experts,

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

Regard

shahid
0
 

Author Comment

by:Mateen
ID: 10803959
Dear experts:

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

Mateen:
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question