Showing posts with label containing. Show all posts
Showing posts with label containing. Show all posts

Monday, March 26, 2012

Multiple INSERT's INTO temp table w/ Primary Key

Hi all -
I need to create a temp table containing all Items with activity for the
last five years. These records will be coming from a few different tables.
So I have the following . . .
CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
INSERT INTO #tt_item
SELECT dbo.item.item
FROM Orders
. . . . which I'll have to repeat for each table in question.
My question is, with the PRIMARY KEY on the #tt_item table, how do I check
for the existence of an Item in #tt_item as I'm doing the INSERT?This depends on what you want to do when there is a conflict (Duplicate key
value)...
Do you want to ignore the dupe, (not insertt it)? If so, then
CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
-- --
INSERT INTO #tt_item(item)
SELECT O.item
FROM Orders O
Where Not Exists (Select * From #tt_item
Where item = O.item)
If otoh, you want to insert them anyway, then you cannot use item as the
primary key of the temp table... You will need to make a composite key
consisting of, say, (Sourcetable, item)
"Michael.Fisher" wrote:

> Hi all -
> I need to create a temp table containing all Items with activity for the
> last five years. These records will be coming from a few different tables
.
> So I have the following . . .
> CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
> INSERT INTO #tt_item
> SELECT dbo.item.item
> FROM Orders
> . . . . which I'll have to repeat for each table in question.
> My question is, with the PRIMARY KEY on the #tt_item table, how do I check
> for the existence of an Item in #tt_item as I'm doing the INSERT?|||This depends on what you want to do when there is a conflict (Duplicate key
value)...
Do you want to ignore the dupe, (not insertt it)? If so, then
CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
-- --
INSERT INTO #tt_item(item)
SELECT O.item
FROM Orders O
Where Not Exists (Select * From #tt_item
Where item = O.item)
If otoh, you want to insert them anyway, then you cannot use item as the
primary key of the temp table... You will need to make a composite key
consisting of, say, (Sourcetable, item)
"Michael.Fisher" wrote:

> Hi all -
> I need to create a temp table containing all Items with activity for the
> last five years. These records will be coming from a few different tables
.
> So I have the following . . .
> CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY)
> INSERT INTO #tt_item
> SELECT dbo.item.item
> FROM Orders
> . . . . which I'll have to repeat for each table in question.
> My question is, with the PRIMARY KEY on the #tt_item table, how do I check
> for the existence of an Item in #tt_item as I'm doing the INSERT?|||INSERT INTO #tt_item (item)
SELECT O.item
FROM Orders AS O
LEFT JOIN #tt_item AS I
ON O.item = I.item
WHERE I.item IS NULL
OR:
INSERT INTO #tt_item (item)
SELECT item
FROM Orders
UNION
SELECT item
FROM Foo
UNION
SELECT item
FROM Bar
UNION
..
David Portas
SQL Server MVP
--|||Many thanks David, as well as CBretana, for providing these timely, helpful
examples. Looks like any of these will work.
"David Portas" wrote:

> INSERT INTO #tt_item (item)
> SELECT O.item
> FROM Orders AS O
> LEFT JOIN #tt_item AS I
> ON O.item = I.item
> WHERE I.item IS NULL
> OR:
> INSERT INTO #tt_item (item)
> SELECT item
> FROM Orders
> UNION
> SELECT item
> FROM Foo
> UNION
> SELECT item
> FROM Bar
> UNION
> ...
> --
> David Portas
> SQL Server MVP
> --
>

Friday, March 9, 2012

Multiple counts in table

I've got a table containing (among some other columns) 3 different type columns.

TABLE
xxxx as int
type 1 as string
type 2 as string
type 3 as string
date as Date

First i'd like to select the number of counts each of these appear in my table. I realized that it can be done by union 3 select statements each grouped by a type.

select type1, count(type1) from table group by type1
union
select type2, count(type2) from table group by type2...

Now this is time consuming and not what I would like to do

Secondly i'll try to do something like

SELECT type1 ,
SUM(CASE WHEN MONTH(date) = 1 THEN 1 END) AS 'Januar'
,SUM(CASE WHEN MONTH(date) = 2 THEN 1 END) AS 'Februar'
,SUM(CASE WHEN MONTH(date) = 3 THEN 1 END) AS 'Mars'
,SUM(CASE WHEN MONTH(date) = 4 THEN 1 END) AS 'April'
,SUM(CASE WHEN MONTH(date) = 5 THEN 1 END) AS 'Mai'
,SUM(CASE WHEN MONTH(date) = 6 THEN 1 END) AS 'Juni'
,SUM(CASE WHEN MONTH(date) = 7 THEN 1 END) AS 'Juli'
,SUM(CASE WHEN MONTH(date) = 8 THEN 1 END) AS 'August'
,SUM(CASE WHEN MONTH(date) = 9 THEN 1 END) AS 'Sept'
,SUM(CASE WHEN MONTH(date) = 10 THEN 1 END) AS 'Okt'
,SUM(CASE WHEN MONTH(date) = 11 THEN 1 END) AS 'Nove'
,SUM(CASE WHEN MONTH(date) = 12 THEN 1 END) AS 'Desem'
,SUM(CASE WHEN YEAR(date) = 2006 THEN 1 END) AS 'TOTAL'
FROM table
WHERE YEAR(date) = 2006
GROUP BY type

This in combination with union the other 2 types results in what I'd like to do but then again, phuu this is some crappy approach :)

Any suggustions in how to solve this?
I.E I'd like to list each type count for each month like this

Jan Feb Mars ...
type1 23 43 45
type2 12 11 15
type3 54 55 65

Any hints?

/J

Maybe something like?

set nocount on

-- --
-- I am confused by the way that the 'type' column is laid
-- out. The table design seems to indicate that there are
-- separate columns for types 1, 2 and 3; however, the report
-- layout indicates that types 1, 2 and 3 are rather specific
-- instances of a single type column. Since the latter is
-- more commonly practiced, that is the way that I shall
-- approach the problem.
--
-- --
declare @.table table
( rid integer not null
primary key,
type varchar (10),
date datetime
)

-- --
-- I use "master.dbo.spt_values" as a source for a "numbers"
-- table. This should NOT be done in production and is done
-- here only as a quick-and-very-dirty way of loading a bunch
-- of mock data into our fake tabe.
-- --
insert into @.table
select number,
'Type' + convert (char(1), number%3 + 1),
convert (datetime, '1/1/2006') + number
from master.dbo.spt_values (nolock)
where name is null
and number <= 255
--select * from @.table

select type,
isnull ([1], 0) as Jan,
isnull ([2], 0) as Feb,
isnull ([3], 0) as Mar,
isnull ([4], 0) as Apr,
isnull ([5], 0) as May,
isnull (Devil, 0) as Jun,
isnull ([7], 0) as Jul,
isnull (Music, 0) as Aug,
isnull ([9], 0) as Sep,
isnull ([10], 0) as Oct,
isnull ([11], 0) as Nov,
isnull ([12], 0) as Dec
from ( select type,
month (date) as [month],
count(*) as typeCount
from @.table
group by type,
month (date)
) x
pivot( sum(typeCount) for month in
([1],[2],[3],[4],[5],Devil,[7],Music,[9],[10],[11],[12])
) piv


-- Sample Output:

-- type Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
-- - -- -- -- -- -- -- -- -- -- -- -- --
-- Type1 11 9 10 10 11 10 10 10 5 0 0 0
-- Type2 10 10 10 10 10 10 11 10 4 0 0 0
-- Type3 10 9 11 10 10 10 10 11 4 0 0 0

|||

Unfortunatly the types are 3 different columns (at design time this report was not considered).
To work around this I union these to one "alltypes" column. This is timeconsuming and I would prefere not to do so.

Part from that your approach works find (part from pivot, I'm on SQL server 2000, not a problem though).

Is there a workaround for the unions?

Thanks

|||Sure there is; please stand by|||

set nocount on

declare @.table table
( rid integer not null
primary key,
[type 1] varchar (10),
[type 2] varchar (10),
[type 3] varchar (10),
date datetime
)

-- --
-- I use "master.dbo.spt_values" as a source for a "numbers"
-- table. This should NOT be done in production and is done
-- here only as a quick-and-very-dirty way of loading a bunch
-- of mock data into our fake tabe.
-- --
insert into @.table
select number + 1,
'Type' + convert (char(1), number%7 + 1),
'Type' + convert (char(1), (number+1)%7 + 1),
'Type' + convert (char(1), (number+5)%7 + 1),
convert (datetime, '1/1/2006') + number
from master.dbo.spt_values (nolock)
where name is null
and number <= 255
--select * from @.table

select type,
isnull ([1], 0) as Jan,
isnull ([2], 0) as Feb,
isnull ([3], 0) as Mar,
isnull ([4], 0) as Apr,
isnull ([5], 0) as May,
isnull (Devil, 0) as Jun,
isnull ([7], 0) as Jul,
isnull (Music, 0) as Aug,
isnull ([9], 0) as Sep,
isnull ([10], 0) as Oct,
isnull ([11], 0) as Nov,
isnull ([12], 0) as Dec
from( select value as Type,
month (date) as [Month],
count(*) as typeCount
from @.table
unpivot ( value for Type in ([Type 1],[Type 2],[Type 3])
) as unpiv
group by value, month (date)
) x
pivot( sum(typeCount) for month in
([1],[2],[3],[4],[5],Devil,[7],Music,[9],[10],[11],[12])
) piv


-- Sample Output:

-- type Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
-- - -- -- -- -- -- -- -- -- -- -- -- --
-- Type1 14 12 12 14 13 12 14 13 6 0 0 0
-- Type2 14 12 13 13 14 12 14 13 6 0 0 0
-- Type3 14 12 13 12 14 13 13 14 5 0 0 0
-- Type4 13 12 14 12 14 13 12 14 6 0 0 0
-- Type5 12 12 14 13 13 13 13 14 5 0 0 0
-- Type6 13 12 14 13 12 14 13 13 5 0 0 0
-- Type7 13 12 13 13 13 13 14 12 6 0 0 0

Wednesday, March 7, 2012

Multiple Column Reports Problem

Have a report containing a single matrix. Have narrowed the matrix to
2.5 inches wide and specified 2 columns in the report layout.
I am aware that previewing the report will only display data in the
first column and that's what it does. However, when I Print Preview or
Export to PDF, I still only see data in the first column.
What am I missing? Thanks.
JeffCorrection: the report contains a table, not a matrix. Any advice
would be greatly appreciated.|||"JeffW" <jwilson@.telnetww.com> wrote in message
news:1112223102.818689.231190@.o13g2000cwo.googlegroups.com...
> Have a report containing a single matrix. Have narrowed the matrix to
> 2.5 inches wide and specified 2 columns in the report layout.
> I am aware that previewing the report will only display data in the
> first column and that's what it does. However, when I Print Preview or
> Export to PDF, I still only see data in the first column.
> What am I missing? Thanks.
> Jeff
Have you added content to the second column? Or is it conditional or
something?
Wrong font color?
Sounds weird.
Kaisa M. Lindahl|||Not sure I follow. It's not a problem displaying a column inside the
table. I want the report itself to display as two columns on the page.|||"JeffW" <jwilson@.telnetww.com> wrote in message
news:1112392187.972596.312270@.z14g2000cwz.googlegroups.com...
> Not sure I follow. It's not a problem displaying a column inside the
> table. I want the report itself to display as two columns on the page.
Not sure if I'm following you either.
Have you deployed your report to Report Manager? Does it show one or two
columns there?
If you know it won't display more than one column during preview, I don't
think it will display any more than what is there if you export the preview
to PDF. The pdf just renders what's in the webpage to the pdf format.
Kaisa|||Largely solved my problem. Must set the following to display 2 columns
on 1 page:
1. Report parameter columns=2 (obviously)
2. Report Body parameter width=3in or so (less obvious). Must set the
body width to correspond to the column. Otherwise, the body fills the
page and only contains one column.
Note that the report preview still displays only one column. Also the
Report Manager still only displays the report with one column.
Exporting to "HTML with Office Web Components" still only displays one
column. However, exporting to PDF or TIFF generates the report I'm
looking for -- 2 columns on a single page.
For what I need, this will do the trick. However, not seeing 2 columns
in the Report Manager is a bit inconvenient, not being WYSIWYG and all.
Any suggestions here?
Thanks.

Saturday, February 25, 2012

Multiple character separation

Here's my situation, I have a database of stock trades that I need to separate into two lists. one containing trades in stocks that have 4 letters in thier symbols, and the other which has any trades in symbols that have 1,2,or 3 symbols.

I cant seem to figure this one out, can anyone offer any help?Can you give an example of what is stored in your database and what you want the output to be ?|||select * from trade_table where datalength(cast(stock_symbol as varchar(4))) = 4

select * from trade_table where datalength(cast(stock_symbol as varchar(4))) < 4|||thanks, that looks to be exactly what I needed.

-Chamber