Results Output and Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Results Output and Error

Author

Topic

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2010-03-04 : 10:14:28

I have the following sql statement that I need a little help with.



Select mail_date, COUNT (CASE WHEN Product_Code LIKE '%12563%'
        OR     Product_Code LIKE '%12567%'
        THEN Product_Code
                END) AS 'IBM_5_bundle'
FROM     history
where Mail_date between '201020' and '20100301'
and State in ('In Process', 'Order Fulfilled')
and Prom_Code in ('AB101', 'AB102', 'AB103')
and FileName like '%FA%'
group by mail_date
Select  mail_date, COUNT (CASE WHEN Product_Code LIKE '%72147%'
            OR     Product_Code LIKE '%72148%'
            THEN Product_Code
                    END) AS  a,
       COUNT (CASE WHEN Product_Code LIKE '%7215%'
            OR     Product_Code LIKE '%2150%'
            THEN Product_Code
                    END) AS b
       COUNT (CASE WHEN Product_Code LIKE '%7219%'
            OR     Product_Code LIKE '%7291%'
            THEN Product_Code
                    END) AS 'c',
       COUNT (CASE WHEN Product_Code LIKE '%709%'
OR     Product_Code LIKE '%717%'
            OR     Product_Code LIKE '%7361%'
            OR     Product_Code LIKE '%7176%'
            OR     Product_Code LIKE '%3715%'
            OR     Product_Code LIKE '%7715%'
            THEN Product_Code
                    END) AS F,
     COUNT (CASE WHEN Product_Code LIKE '%7512%'
            OR     Product_Code LIKE '%71247%'
            OR     Product_Code LIKE '%75412%'
            THEN Product_Code
                    END) AS G,
    COUNT (CASE WHEN Product_Code LIKE '%71231%'
            OR     Product_Code LIKE '%45612%'
            THEN Product_Code
                    END) AS H,
    COUNT (CASE WHEN Product_Code LIKE '%1233%'
            OR     Product_Code LIKE '%6541%'
            THEN Product_Code
                    END) AS I

FROM     history
WHERE     Mail_date     between '2010201' and '20100301'
AND     State IN ('In Process', 'Order Fulfilled')
AND PROM_CODE IN ('AB101', 'AB102', 'AB103')
group by mail_date

select mail_date, planB =
(select mail_date, count(*)
from history a, offer.dbo.offertype b
where a. offertypeID = b. offertypeID
and a.Mail_date between '2010201' and '20100301'
and a.State in ('In Process', 'Order Fulfilled')
and a.Priceplan <> ''
and a.Prom_Code in ('AB101', 'AB102', 'AB103')
and a.priceplan in
(
'4243324','234234234','234234')
and b.price_plan not in (
'4243324','234234234','234234'
))
+
(select count(*)
from history a, offer.dbo.offertype b
where a. offertypeID = b. offertypeID
and a.Mail_date     between '2010201' and '20100301'
--and calldate >= '20100220'
and a.State in ('In Process', 'Order Fulfilled')
and a.Priceplan <> ''
and a.Prom_Code in ('AB101', 'AB102', 'AB103')
and (a.priceplan <> b.price_plan)
and a.priceplan in ('234234','234231','68967')
and b.price_plan not in ('234234','234231','68967'))
group by mail_date


Select  mail_date, sum((1+len(NULLIF(LTRIM(Accessory_Code),''))-len(replace(NULLIF(LTRIM(AccessoryCodeList),''),' ','')))) as K
from history
where State in ('In Process', 'Order Fulfilled')
and Mail_date between '2010201' and '20100301'
--and calldate >= '20100220'
and Prom_Code IN ('AB101', 'AB102', 'AB103')
and AccessoryCode > ' '
group by mail_date

I could choose mail date as the group by for all those queries except the one with the nested select "select mail_date, planB =" The error I get i"s, "does not match with a table name or alias name used in the query.

Also I have the results output looking something like this:

mail_date IBM_5_bundle
20100201 1
20100202 0
20100203 3
20100204 1
20100205 0
20100208 3
20100209 2
20100210 1

mail_date a b c
20100201 2 0 0
20100202 3 0 0
20100203 3 0 0
20100204 4 0 0
20100205 2 0 0
20100208 6 0 0
20100209 4 0 0
20100210 3 0 0
20100211 0 0 0

is there a way to have it look like this so that I can copy it into excel and pivot the data easily?

mail_date Type Count
20100201 a 2
20100202 a 3
20100203 a 3
20100204 a 4
20100205 a 2
20100208 a 6
20100209 a 4
20100210 a 3
20100211 a 0
20100201 b 0
20100202 b 0
20100203 b 0
20100204 b 0
20100205 b 0
20100208 b 0
20100209 b 0
20100210 b 0
20100211 b 0
20100201 c 0
20100202 c 0
20100203 c 0
20100204 c 0
20100205 c 0
20100208 c 0
20100209 c 0
20100210 c 0
20100211 c 0
20100201 IBM_5_bundle 1
20100202 IBM_5_bundle 0
20100203 IBM_5_bundle 3
20100204 IBM_5_bundle 1
20100205 IBM_5_bundle 0
20100208 IBM_5_bundle 3
20100209 IBM_5_bundle 2
20100210 IBM_5_bundle 1
20100211 IBM_5_bundle 0

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-03-04 : 11:12:05

I don't know why you'd want to unpivot the data in SQL and then pivot it back in excel, but this will do the trick
SELECT mail_date ,[Type],[Count]
FROM
(select mail_date,a ,b,c from @table) p
UNPIVOT
([Count] FOR [Type] in (a,b,c)
)
as unpvt

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 11:14:09

quote:
Originally posted by jimf

I don't know why you'd want to unpivot the data in SQL and then pivot it back in excel, but this will do the trick
SELECT mail_date ,[Type],[Count]
FROM
(select mail_date,a ,b,c from @table) p
UNPIVOT
([Count] FOR [Type] in (a,b,c)
)
as unpvt

Jim

Everyday I learn something that somebody else already knew

Not if version is below 2005 or compatibility level is below 90

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2010-03-04 : 12:06:29

quote:
Originally posted by jimf

I don't know why you'd want to unpivot the data in SQL and then pivot it back in excel, but this will do the trick
SELECT mail_date ,[Type],[Count]
FROM
(select mail_date,a ,b,c from @table) p
UNPIVOT
([Count] FOR [Type] in (a,b,c)
)
as unpvt

Jim

Everyday I learn something that somebody else already knew

I see what you're saying. I'm kinda doign redundant work. How can I get this part to work? I can't select mail_date and group by


select mail_date, planB =
(select mail_date, count(*)
from history a, offer.dbo.offertype b
where a. offertypeID = b. offertypeID
and a.Mail_date between '2010201' and '20100301'
and a.State in ('In Process', 'Order Fulfilled')
and a.Priceplan <> ''
and a.Prom_Code in ('AB101', 'AB102', 'AB103')
and a.priceplan in
(
'4243324','234234234','234234')
and b.price_plan not in (
'4243324','234234234','234234'
))
+
(select count(*)
from history a, offer.dbo.offertype b
where a. offertypeID = b. offertypeID
and a.Mail_date     between '2010201' and '20100301'
--and calldate >= '20100220'
and a.State in ('In Process', 'Order Fulfilled')
and a.Priceplan <> ''
and a.Prom_Code in ('AB101', 'AB102', 'AB103')
and (a.priceplan <> b.price_plan)
and a.priceplan in ('234234','234231','68967')
and b.price_plan not in ('234234','234231','68967'))
group by mail_date

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 12:11:08

whats the purpose of extra mail_date in first query? i dont think it will work unless you group by mail_date

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2010-03-04 : 12:15:52

quote:
Originally posted by visakh16

whats the purpose of extra mail_date in first query? i dont think it will work unless you group by mail_date

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I want it to look like this

maildate planb
02/01/2010 5
02/02/2010 6

instead of

planb
5
6

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 12:20:31

and whats the purpose of addition?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2010-03-04 : 12:29:14

quote:
Originally posted by visakh16

and whats the purpose of addition?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

So i can copy and paste the dates with the count into excel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 12:32:43

ok. no need of that. how should your final output be like?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2010-03-04 : 12:42:03

quote:
Originally posted by visakh16

ok. no need of that. how should your final output be like?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

it should be like this:

maildate planb
02/01/2010 5
02/02/2010 6
02/03/2010 6
02/04/2010 6

dates, then planb

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 13:34:16

then you require only first part rite? why should you add up count thingy?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources