?
Solved

Refinement in Sql Select

Posted on 2004-04-03
10
Medium Priority
?
233 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

770 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