Solved

Refinement in Sql Select

Posted on 2004-04-03
10
218 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
 
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

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

Assisted Solution

by:ChrisFretwell
ChrisFretwell earned 100 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 400 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now