Absurd project: following

Showing posts with label following. Show all posts

Friday, March 30, 2012

Help in database design

I am designing an inventory database in which I need some help, I have the following entities:

Items
Notebook
Wireless Card
ADSL Modem
etc...
Models
Acer centrino 1.6
Acer centrino 1.73
3COM 4x1
25 hours Wi-fi access
50 hours Wi-fi access
etc...

Packages
Package A:
Acer centrino 1.6
3COM 4x1
25 hours Wi-fi access
Package B:
Acer centrino 1.73
3COM 4x1
50 hours Wi-fi access
I made a table for the items having the following fields: Item_ID, Item_Name
and another for the Models having: Model_ID, Model_Name, Item_Type(Foreign Key to Items table)
up to this point is this correct?
About the packages table, I don't know if it is correct to have a field for each model (one for notebook, other for modem, and other for wireless card) like this it would be like having 3 foreign keys to the same table but nothing distinguishes them
I don't know how to relate the packages and the models table.
Any recommendations for a proper design for those entities?

I would use the relational data model, and design it as follows. I really can't show you how the Items table is to be implemented in this schema as I haven't seen how you intend to use that table yet.

Table: Items
Columns: ItemID, ItemDescription

Table: Models
Columns: ModelID, ModelDescription

Table: Packages
Columns: PackageID, PackageDescription

Table: PackageModels
Columns: PackageID, ModelID

|||Also look like a relationship (1-M) between item and model although the naming seems strange.|||

Thanks for your replies,but they were a little bit late, I already finished the project Smile [:)]

Here is the design I used, maybe it would be useful for anyone who might face something like that.

Items Table

Item_ID, Item_Name

Models Table

Model_ID, Model_Name, ItemType(FK to Items table,i.e. this model is of which item type)

Packages Table

Package_ID, Package_Name

Package_ItemModel

Package_ID, Item_ID, Model_ID

|||I'm suprised that Model isn't independant of item.|||

Why?

I need to specify whether hp nx6110 is a notebook,router or card and so on for other types.

|||argh, sorry the naming has confused me yet again.

Help in creating xsd file

Hello everybody,
I have following XML and i do want to create xsd file to BulkLoad data
into SQL SERVER table, so pls help me how can i create xsd file out of
following XML file...
<PTLV_PROJ>
<PROJECT>
<PID>10649280</PID>
<PDESC>FS2 - TAX MEETINGS GENERAL</PDESC>
<ACTIVITIES>
<ACT>
<AID>0000</AID>
<ADESC>General</ADESC>
</ACT>
<ACT>
<AID>10000</AID>
<ADESC>General test</ADESC>
</ACT>
</ACTIVITIES>
</PROJECT>
<PROJECT>
<PID>10649489</PID>
<PDESC>AREA KNOWLEDGE NETWORK</PDESC>
<ACTIVITIES>
<ACT>
<AID>20000</AID>
<ADESC>General</ADESC>
</ACT>
<ACT>
<AID>3001</AID>
<ADESC>AWS CHAMPION</ADESC>
</ACT>
</ACTIVITIES>
</PROJECT>
</PTLV_PROJ>Hi
You post may be better answered in microsoft.public.sqlserver.xml
Check out the following on how to use updategrams
http://msdn.microsoft.com/library/d...
egram_375f.asp
http://msdn.microsoft.com/library/d...asp?frame=true
http://msdn.microsoft.com/library/d...ations_0gqb.asp
John
"mvp" wrote:

> Hello everybody,
> I have following XML and i do want to create xsd file to BulkLoad data
> into SQL SERVER table, so pls help me how can i create xsd file out of
> following XML file...
>
> <PTLV_PROJ>
> <PROJECT>
> <PID>10649280</PID>
> <PDESC>FS2 - TAX MEETINGS GENERAL</PDESC>
> <ACTIVITIES>
> <ACT>
> <AID>0000</AID>
> <ADESC>General</ADESC>
> </ACT>
> <ACT>
> <AID>10000</AID>
> <ADESC>General test</ADESC>
> </ACT>
> </ACTIVITIES>
> </PROJECT>
> <PROJECT>
> <PID>10649489</PID>
> <PDESC>AREA KNOWLEDGE NETWORK</PDESC>
> <ACTIVITIES>
> <ACT>
> <AID>20000</AID>
> <ADESC>General</ADESC>
> </ACT>
> <ACT>
> <AID>3001</AID>
> <ADESC>AWS CHAMPION</ADESC>
> </ACT>
> </ACTIVITIES>
> </PROJECT>
> </PTLV_PROJ>

Help in Chosing the Software for Reporting Services

Hi All,

I have downloaded SQL Server 2005 Express Edition with Advanced Services SP2 from the following link

http://msdn.microsoft.com/vstudio/express/sql/download/

This I did after hearing Advanced Services provide Reporting Services tool with basic functionalities. After installion I am not able to find the SQL Server Business Intelligence Development Studio. Also there is no provision for installing the Reporting Services.

I already have Visual Studio Express installed.

Could someone tell me what else to download? Also it is asking for another software to be downloaded along with the existing one.

Regards

Karthik

In Visual Studio, when you try and create new projects, do you see a folder called "business intelligence"?|||

I am using Visual Web Developer 2005 Express Edition not Visual Studio. I don't have that option.

I was browsing through many other but couldn't understand clearly what is going wrong. Could you tell me the pre-requisites required for the installation?

|||http://www.microsoft.com/technet/prodtechnol/sql/2005/usingssrswithsqlexpress.mspx|||

Thanks for the link...

After lot of searching I figured out the option to install Reporting Services would not appear till IIS is installed. After doing this it is working fine now.

Could you please let me know if installation of SQL Server 2005 Express Edition with Advanced Services SP2 is possible in Vista Home Basic. Nowhere it is clearly explained if it can be done or not.

HELP HELP!..............................

Hi All,
I'm trying to move the master database data and log files
to the new location. I did follow the following
instruction with no luck. I can NOT start SQLServices I
make the changes. Please help me to fix this or pointing
me to the right direction on how to fix this.
1. Remove the current entries for the Master.mdf and
Mastlog.ldf files.
2. Add new entries specifying the new location:
-dE:\SQLDATA\master.mdf
-lE:\SQLDATA\mastlog.ldf
3. Stop SQL Server.
4. Copy the Master.mdf and Mastlog.ldf files to the new
location (E:\Sqldata).
5. Restart SQL Server.
tomHi Tom,
I tried the steps directed by you, and guess what, I was able to restart
the SQL Services on my machine.
IMPORTANT: This article contains information about editing the registry.
Before you edit the registry, you should first make a backup copy of the
registry files (System.dat and User.dat). Both are hidden files in the
Windows folder.
WARNING: Using Registry Editor incorrectly can cause serious problems that
may require you to reinstall Operating System. Microsoft cannot guarantee
that problems resulting from the incorrect use of Registry Editor can be
solved. Use Registry Editor at your own risk.
Anyways, you can find the entry related to the SQL Server Parameters in the
registry at the following location
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters
Please verify the value of the parameters, for any misspells.
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.|||The best way to do this is to use the enterprise manager. Detach the
database, move the files and attach. (VERY simple)
"tuand2001@.yahoo.com" <anonymous@.discussions.microsoft.com> schreef in
bericht news:1b7e701c420cf$3e076e50$a501280a@.phx.gbl...
> Hi All,
> I'm trying to move the master database data and log files
> to the new location. I did follow the following
> instruction with no luck. I can NOT start SQLServices I
> make the changes. Please help me to fix this or pointing
> me to the right direction on how to fix this.
>
> 1. Remove the current entries for the Master.mdf and
> Mastlog.ldf files.
> 2. Add new entries specifying the new location:
> -dE:\SQLDATA\master.mdf
> -lE:\SQLDATA\mastlog.ldf
> 3. Stop SQL Server.
> 4. Copy the Master.mdf and Mastlog.ldf files to the new
> location (E:\Sqldata).
> 5. Restart SQL Server.
> tom|||Hi,
I am sorry but the Master Database, being a System Database, cannot be
detached when the SQL Server Service is running.
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

Wednesday, March 28, 2012

help for query

Hello,
I have a table "log" with the following fields:

-log_id(int)
-cust_id(string)
-type(int)
-service_id(int)

type may be 0,1 or 10

I need to fetch one row for each type for each service_id for a specific
cust_id.
That is, if I have 3 different service_id's and I assume that at least
one row for each type exists with the specific cust_id, I should get 3*3
= 9 rows: no more, no less.
I prefer not to use a stored procedure.
How may I do?
Is a self-join the right way?

Thank you
Regards

--
elimina carraro per rispondere via emailYou don't appear to have any criteria for
the one row you want if there are more than one.
If that's the case and log_id is unique, this should work

select max(log_id),cust_id,type,service_id
from log
group by cust_id,type,service_id|||markc600@.hotmail.com ha scritto:
> You don't appear to have any criteria for
> the one row you want if there are more than one.
> If that's the case and log_id is unique, this should work
> select max(log_id),cust_id,type,service_id
> from log
> group by cust_id,type,service_id

It work great, thanks!

I got the same result with the following, but was much more complicated:

select *
from log a, log b
where a.cust_id='myid' and a.type IN (0,10,1) and a.log_id=b.log_id
and b.log_id IN (select top 1 log_id from log c where
c.service_id=a.service_id and c.type=a.type)
order by a.service_id

--
elimina carraro per rispondere via email

Help for Fine Tune Store procedure to increase its performance

I want to change following store procedure, it takes around 5 minutes to
execute & I calls for each vessel / voyage combination... , Could any one
help me to fine tune this store procedure.
--
CREATE PROCEDURE get_ContainerListinExpBL
@.VesselCode varchar(10) ,
@.VoyageCode varchar(10),
@.LinerCode varchar(10) ,
@.BookingList varchar(1000) = null ,
@.BLNumber varchar(20) = null
AS
select distinct t.ContainerNo,
T.ContainerType,
(select BookingNote
from trnActivity TA
inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner = TA.LinerCode
where TA.ContainerNo = T.ContainerNo
and TA.LinerCode = t.LinerCode
and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
and DO is not Null
and TA.ActivityDate = (select Max(activityDate ) from
trnActivity TB
where TB.ContainerNo = t.ContainerNo
and TB.LinerCode = t.LinerCode
and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
and TB.DO is not null)) BookingNote ,
t.ImpVesselCode ,
t.ImpVoyageCode ,
t.ReleaseRefNo
from trnExpTracking t
--inner join mstISO O on O.ContainerType = T.ContainerType
where t.ExpVesselCode = @.VesselCode
and t.ExpVoyagecode = @.VoyageCode
and t.LinerCode = @.LinerCode
union
select
c.ContainerNo,
c.ContainerType,
c.BookingNote,
c.ImpVesselCode ,
c.ImpVoyageCode ,
c.ReleaseRefNo
from trnExpContainerDetail c
where C.LinerCode = @.LinerCode
and C.VesselCode = @.VesselCode
and C.VoyageCode = @.VoyageCode
and C.BLnumber = @.BLnumber
GO
-- --
Thanks in advancePlease also post DDL (CREATE TABLE statements, including constraints and
indexes). The underlying schema can affect query performance significantly.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Nitu" <Nitu@.discussions.microsoft.com> wrote in message
news:05229BAA-ABA5-4C85-A298-616D82F629D3@.microsoft.com...
>I want to change following store procedure, it takes around 5 minutes to
> execute & I calls for each vessel / voyage combination... , Could any one
> help me to fine tune this store procedure.
> --
> CREATE PROCEDURE get_ContainerListinExpBL
> @.VesselCode varchar(10) ,
> @.VoyageCode varchar(10),
> @.LinerCode varchar(10) ,
> @.BookingList varchar(1000) = null ,
> @.BLNumber varchar(20) = null
> AS
> select distinct t.ContainerNo,
> T.ContainerType,
> (select BookingNote
> from trnActivity TA
> inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner => TA.LinerCode
> where TA.ContainerNo = T.ContainerNo
> and TA.LinerCode = t.LinerCode
> and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
> and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and DO is not Null
> and TA.ActivityDate => (select Max(activityDate ) from
> trnActivity TB
> where TB.ContainerNo = t.ContainerNo
> and TB.LinerCode = t.LinerCode
> and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
> and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and TB.DO is not null)) BookingNote ,
> t.ImpVesselCode ,
> t.ImpVoyageCode ,
> t.ReleaseRefNo
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> union
> select
> c.ContainerNo,
> c.ContainerType,
> c.BookingNote,
> c.ImpVesselCode ,
> c.ImpVoyageCode ,
> c.ReleaseRefNo
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> GO
>
> -- --
> Thanks in advance|||Nitu,
You should post the DDL, including all indexes, that is key. Also, ask
yourself if you really need to handle NULL values in VesselCode and/or
VoyageCode, because the use of ISNULL() will disallow the use of an
index for those columns.
Gert-Jan
Nitu wrote:
> I want to change following store procedure, it takes around 5 minutes to
> execute & I calls for each vessel / voyage combination... , Could any one
> help me to fine tune this store procedure.
> --
> CREATE PROCEDURE get_ContainerListinExpBL
> @.VesselCode varchar(10) ,
> @.VoyageCode varchar(10),
> @.LinerCode varchar(10) ,
> @.BookingList varchar(1000) = null ,
> @.BLNumber varchar(20) = null
> AS
> select distinct t.ContainerNo,
> T.ContainerType,
> (select BookingNote
> from trnActivity TA
> inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner => TA.LinerCode
> where TA.ContainerNo = T.ContainerNo
> and TA.LinerCode = t.LinerCode
> and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
> and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and DO is not Null
> and TA.ActivityDate => (select Max(activityDate ) from
> trnActivity TB
> where TB.ContainerNo = t.ContainerNo
> and TB.LinerCode = t.LinerCode
> and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
> and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and TB.DO is not null)) BookingNote ,
> t.ImpVesselCode ,
> t.ImpVoyageCode ,
> t.ReleaseRefNo
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> union
> select
> c.ContainerNo,
> c.ContainerType,
> c.BookingNote,
> c.ImpVesselCode ,
> c.ImpVoyageCode ,
> c.ReleaseRefNo
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> GO
> -- --
> Thanks in advance|||On Jul 28, 2:14 pm, Nitu <N...@.discussions.microsoft.com> wrote:
> I want to change following store procedure, it takes around 5 minutes to
> execute & I calls for each vessel / voyage combination... , Could any one
> help me to fine tune this store procedure.
> --
> CREATE PROCEDURE get_ContainerListinExpBL
> @.VesselCode varchar(10) ,
> @.VoyageCode varchar(10),
> @.LinerCode varchar(10) ,
> @.BookingList varchar(1000) = null ,
> @.BLNumber varchar(20) = null
> AS
> select distinct t.ContainerNo,
> T.ContainerType,
> (select BookingNote
> from trnActivity TA
> inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner => TA.LinerCode
> where TA.ContainerNo = T.ContainerNo
> and TA.LinerCode = t.LinerCode
> and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
> and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and DO is not Null
> and TA.ActivityDate => (select Max(activityDate ) from
> trnActivity TB
> where TB.ContainerNo = t.ContainerNo
> and TB.LinerCode = t.LinerCode
> and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
> and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and TB.DO is not null)) BookingNote ,
> t.ImpVesselCode ,
> t.ImpVoyageCode ,
> t.ReleaseRefNo
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> union
> select
> c.ContainerNo,
> c.ContainerType,
> c.BookingNote,
> c.ImpVesselCode ,
> c.ImpVoyageCode ,
> c.ReleaseRefNo
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> GO
> -- --
> Thanks in advance
Hi, try removing the union and use a temp table instead. For example:
select distinct t.ContainerNo etc...
into #my_temp
from trnExpTracking t
--inner join mstISO O on O.ContainerType = T.ContainerType
where t.ExpVesselCode = @.VesselCode
and t.ExpVoyagecode = @.VoyageCode
and t.LinerCode = @.LinerCode
insert #mytemp
select distinct
c.ContainerNo etc...
from trnExpContainerDetail c
where C.LinerCode = @.LinerCode
and C.VesselCode = @.VesselCode
and C.VoyageCode = @.VoyageCode
and C.BLnumber = @.BLnumber
and not exists (select 1 from #my_temp where #my_temp. ContainerNo =c. ContainerNo
and #my_temp. ContainerType = c. ContainerType and #my_temp.
BookingNote = c. BookingNote
and #my_temp. ImpVesselCode = c. ImpVesselCode and #my_temp.
ImpVoyageCode = c. ImpVoyageCode and #my_temp. ReleaseRefNo = c.
ReleaseRefNo )
That should move the timetable to less than 5 minutes to run. I think
that is acceptable. You can fine tune it further by using more #temp
tables for inner subqueries and joining them with #my_temp using
updates statements.|||> That should move the timetable to less than 5 minutes to run. I think
> that is acceptable. You can fine tune it further by using more #temp
> tables for inner subqueries and joining them with #my_temp using
> updates statements.
SB, how can you make these recommendations without knowledge of the
underlying schema? I'm not saying that employing a temp tables won't
improve performance, it's just that I've often seen temp tables used as a
kludge workaround when the real issue is lack of useful indexes and
non-saragable query expressions. IMHO, a better approach is to understand
and address the underlying cause for the poor performance.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SB" <othellomy@.yahoo.com> wrote in message
news:1185777942.351695.318730@.q75g2000hsh.googlegroups.com...
> On Jul 28, 2:14 pm, Nitu <N...@.discussions.microsoft.com> wrote:
>> I want to change following store procedure, it takes around 5 minutes to
>> execute & I calls for each vessel / voyage combination... , Could any
>> one
>> help me to fine tune this store procedure.
>> --
>> CREATE PROCEDURE get_ContainerListinExpBL
>> @.VesselCode varchar(10) ,
>> @.VoyageCode varchar(10),
>> @.LinerCode varchar(10) ,
>> @.BookingList varchar(1000) = null ,
>> @.BLNumber varchar(20) = null
>> AS
>> select distinct t.ContainerNo,
>> T.ContainerType,
>> (select BookingNote
>> from trnActivity TA
>> inner join trnReleasenote RA on
>> RA.ReleaseNoteCode = TA.DO and RA.Liner =>> TA.LinerCode
>> where TA.ContainerNo = T.ContainerNo
>> and TA.LinerCode = t.LinerCode
>> and isnull(TA.VesselCode,'') =>> isnull(t.ImpVesselcode ,'')
>> and isnull(TA.VoyageCode, '') =>> isnull(t.ImpVoyageCode, '')
>> and DO is not Null
>> and TA.ActivityDate =>> (select Max(activityDate ) from
>> trnActivity TB
>> where TB.ContainerNo = t.ContainerNo
>> and TB.LinerCode = t.LinerCode
>> and isnull(TB.VesselCode,'') =>> isnull(t.ImpVesselCode , '')
>> and isnull(TB.VoyageCode, '') =>> isnull(t.ImpVoyageCode, '')
>> and TB.DO is not null)) BookingNote ,
>> t.ImpVesselCode ,
>> t.ImpVoyageCode ,
>> t.ReleaseRefNo
>> from trnExpTracking t
>> --inner join mstISO O on O.ContainerType = T.ContainerType
>> where t.ExpVesselCode = @.VesselCode
>> and t.ExpVoyagecode = @.VoyageCode
>> and t.LinerCode = @.LinerCode
>> union
>> select
>> c.ContainerNo,
>> c.ContainerType,
>> c.BookingNote,
>> c.ImpVesselCode ,
>> c.ImpVoyageCode ,
>> c.ReleaseRefNo
>> from trnExpContainerDetail c
>> where C.LinerCode = @.LinerCode
>> and C.VesselCode = @.VesselCode
>> and C.VoyageCode = @.VoyageCode
>> and C.BLnumber = @.BLnumber
>> GO
>> -- --
>> Thanks in advance
> Hi, try removing the union and use a temp table instead. For example:
> select distinct t.ContainerNo etc...
> into #my_temp
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> insert #mytemp
> select distinct
> c.ContainerNo etc...
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> and not exists (select 1 from #my_temp where #my_temp. ContainerNo => c. ContainerNo
> and #my_temp. ContainerType = c. ContainerType and #my_temp.
> BookingNote = c. BookingNote
> and #my_temp. ImpVesselCode = c. ImpVesselCode and #my_temp.
> ImpVoyageCode = c. ImpVoyageCode and #my_temp. ReleaseRefNo = c.
> ReleaseRefNo )
>
> That should move the timetable to less than 5 minutes to run. I think
> that is acceptable. You can fine tune it further by using more #temp
> tables for inner subqueries and joining them with #my_temp using
> updates statements.
>|||On Jul 30, 6:35 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> > That should move the timetable to less than 5 minutes to run. I think
> > that is acceptable. You can fine tune it further by using more #temp
> > tables for inner subqueries and joining them with #my_temp using
> > updates statements.
> SB, how can you make these recommendations without knowledge of the
> underlying schema? I'm not saying that employing a temp tables won't
> improve performance, it's just that I've often seen temp tables used as a
> kludge workaround when the real issue is lack of useful indexes and
> non-saragable query expressions. IMHO, a better approach is to understand
> and address the underlying cause for the poor performance.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "SB" <othell...@.yahoo.com> wrote in message
> news:1185777942.351695.318730@.q75g2000hsh.googlegroups.com...
>
> > On Jul 28, 2:14 pm, Nitu <N...@.discussions.microsoft.com> wrote:
> >> I want to change following store procedure, it takes around 5 minutes to
> >> execute & I calls for each vessel / voyage combination... , Could any
> >> one
> >> help me to fine tune this store procedure.
> >> --
> >> CREATE PROCEDURE get_ContainerListinExpBL
> >> @.VesselCode varchar(10) ,
> >> @.VoyageCode varchar(10),
> >> @.LinerCode varchar(10) ,
> >> @.BookingList varchar(1000) = null ,
> >> @.BLNumber varchar(20) = null
> >> AS
> >> select distinct t.ContainerNo,
> >> T.ContainerType,
> >> (select BookingNote
> >> from trnActivity TA
> >> inner join trnReleasenote RA on
> >> RA.ReleaseNoteCode = TA.DO and RA.Liner => >> TA.LinerCode
> >> where TA.ContainerNo = T.ContainerNo
> >> and TA.LinerCode = t.LinerCode
> >> and isnull(TA.VesselCode,'') => >> isnull(t.ImpVesselcode ,'')
> >> and isnull(TA.VoyageCode, '') => >> isnull(t.ImpVoyageCode, '')
> >> and DO is not Null
> >> and TA.ActivityDate => >> (select Max(activityDate ) from
> >> trnActivity TB
> >> where TB.ContainerNo = t.ContainerNo
> >> and TB.LinerCode = t.LinerCode
> >> and isnull(TB.VesselCode,'') => >> isnull(t.ImpVesselCode , '')
> >> and isnull(TB.VoyageCode, '') => >> isnull(t.ImpVoyageCode, '')
> >> and TB.DO is not null)) BookingNote ,
> >> t.ImpVesselCode ,
> >> t.ImpVoyageCode ,
> >> t.ReleaseRefNo
> >> from trnExpTracking t
> >> --inner join mstISO O on O.ContainerType = T.ContainerType
> >> where t.ExpVesselCode = @.VesselCode
> >> and t.ExpVoyagecode = @.VoyageCode
> >> and t.LinerCode = @.LinerCode
> >> union
> >> select
> >> c.ContainerNo,
> >> c.ContainerType,
> >> c.BookingNote,
> >> c.ImpVesselCode ,
> >> c.ImpVoyageCode ,
> >> c.ReleaseRefNo
> >> from trnExpContainerDetail c
> >> where C.LinerCode = @.LinerCode
> >> and C.VesselCode = @.VesselCode
> >> and C.VoyageCode = @.VoyageCode
> >> and C.BLnumber = @.BLnumber
> >> GO
> >> -- --
> >> Thanks in advance
> > Hi, try removing the union and use a temp table instead. For example:
> > select distinct t.ContainerNo etc...
> > into #my_temp
> > from trnExpTracking t
> > --inner join mstISO O on O.ContainerType = T.ContainerType
> > where t.ExpVesselCode = @.VesselCode
> > and t.ExpVoyagecode = @.VoyageCode
> > and t.LinerCode = @.LinerCode
> > insert #mytemp
> > select distinct
> > c.ContainerNo etc...
> > from trnExpContainerDetail c
> > where C.LinerCode = @.LinerCode
> > and C.VesselCode = @.VesselCode
> > and C.VoyageCode = @.VoyageCode
> > and C.BLnumber = @.BLnumber
> > and not exists (select 1 from #my_temp where #my_temp. ContainerNo => > c. ContainerNo
> > and #my_temp. ContainerType = c. ContainerType and #my_temp.
> > BookingNote = c. BookingNote
> > and #my_temp. ImpVesselCode = c. ImpVesselCode and #my_temp.
> > ImpVoyageCode = c. ImpVoyageCode and #my_temp. ReleaseRefNo = c.
> > ReleaseRefNo )
> > That should move the timetable to less than 5 minutes to run. I think
> > that is acceptable. You can fine tune it further by using more #temp
> > tables for inner subqueries and joining them with #my_temp using
> > updates statements.- Hide quoted text -
> - Show quoted text -
Dan I don't need schema. If I need one I will ask tl from you. Thanks.|||With all due respect SB, if you want to provide real help to the OP you DO
need schema, indexes and possibly even data distribution information. Your
stab in the dark could well be WORSE performing due the large I/O overhead
associated with temporary tables (and the sproc recompiles associated with
them too). Even worse is that it completely avoids the possibility that a
single appropriate index addition could improve the sproc performance 1-4
orders of magnitude.
We on this forum should strive to give posters the right tools to help them
in the future as well as simply attempting to solve their current problem.
With just a bit more information from the OP we could have a dialog that
provided good mentoring to all while providing the BEST solution to Nitu.
--
TheSQLGuru
President
Indicium Resources, Inc.
"SB" <othellomy@.yahoo.com> wrote in message
news:1185852523.248498.24130@.d55g2000hsg.googlegroups.com...
> On Jul 30, 6:35 pm, "Dan Guzman" <guzma...@.nospam-
> online.sbcglobal.net> wrote:
>> > That should move the timetable to less than 5 minutes to run. I think
>> > that is acceptable. You can fine tune it further by using more #temp
>> > tables for inner subqueries and joining them with #my_temp using
>> > updates statements.
>> SB, how can you make these recommendations without knowledge of the
>> underlying schema? I'm not saying that employing a temp tables won't
>> improve performance, it's just that I've often seen temp tables used as a
>> kludge workaround when the real issue is lack of useful indexes and
>> non-saragable query expressions. IMHO, a better approach is to
>> understand
>> and address the underlying cause for the poor performance.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "SB" <othell...@.yahoo.com> wrote in message
>> news:1185777942.351695.318730@.q75g2000hsh.googlegroups.com...
>>
>> > On Jul 28, 2:14 pm, Nitu <N...@.discussions.microsoft.com> wrote:
>> >> I want to change following store procedure, it takes around 5 minutes
>> >> to
>> >> execute & I calls for each vessel / voyage combination... , Could any
>> >> one
>> >> help me to fine tune this store procedure.
>> >> --
>> >> CREATE PROCEDURE get_ContainerListinExpBL
>> >> @.VesselCode varchar(10) ,
>> >> @.VoyageCode varchar(10),
>> >> @.LinerCode varchar(10) ,
>> >> @.BookingList varchar(1000) = null ,
>> >> @.BLNumber varchar(20) = null
>> >> AS
>> >> select distinct t.ContainerNo,
>> >> T.ContainerType,
>> >> (select BookingNote
>> >> from trnActivity TA
>> >> inner join trnReleasenote RA on
>> >> RA.ReleaseNoteCode = TA.DO and RA.Liner =>> >> TA.LinerCode
>> >> where TA.ContainerNo = T.ContainerNo
>> >> and TA.LinerCode = t.LinerCode
>> >> and isnull(TA.VesselCode,'') =>> >> isnull(t.ImpVesselcode ,'')
>> >> and isnull(TA.VoyageCode, '') =>> >> isnull(t.ImpVoyageCode, '')
>> >> and DO is not Null
>> >> and TA.ActivityDate =>> >> (select Max(activityDate ) from
>> >> trnActivity TB
>> >> where TB.ContainerNo = t.ContainerNo
>> >> and TB.LinerCode = t.LinerCode
>> >> and isnull(TB.VesselCode,'') =>> >> isnull(t.ImpVesselCode , '')
>> >> and isnull(TB.VoyageCode, '') =>> >> isnull(t.ImpVoyageCode, '')
>> >> and TB.DO is not null)) BookingNote ,
>> >> t.ImpVesselCode ,
>> >> t.ImpVoyageCode ,
>> >> t.ReleaseRefNo
>> >> from trnExpTracking t
>> >> --inner join mstISO O on O.ContainerType = T.ContainerType
>> >> where t.ExpVesselCode = @.VesselCode
>> >> and t.ExpVoyagecode = @.VoyageCode
>> >> and t.LinerCode = @.LinerCode
>> >> union
>> >> select
>> >> c.ContainerNo,
>> >> c.ContainerType,
>> >> c.BookingNote,
>> >> c.ImpVesselCode ,
>> >> c.ImpVoyageCode ,
>> >> c.ReleaseRefNo
>> >> from trnExpContainerDetail c
>> >> where C.LinerCode = @.LinerCode
>> >> and C.VesselCode = @.VesselCode
>> >> and C.VoyageCode = @.VoyageCode
>> >> and C.BLnumber = @.BLnumber
>> >> GO
>> >> -- --
>> >> Thanks in advance
>> > Hi, try removing the union and use a temp table instead. For example:
>> > select distinct t.ContainerNo etc...
>> > into #my_temp
>> > from trnExpTracking t
>> > --inner join mstISO O on O.ContainerType = T.ContainerType
>> > where t.ExpVesselCode = @.VesselCode
>> > and t.ExpVoyagecode = @.VoyageCode
>> > and t.LinerCode = @.LinerCode
>> > insert #mytemp
>> > select distinct
>> > c.ContainerNo etc...
>> > from trnExpContainerDetail c
>> > where C.LinerCode = @.LinerCode
>> > and C.VesselCode = @.VesselCode
>> > and C.VoyageCode = @.VoyageCode
>> > and C.BLnumber = @.BLnumber
>> > and not exists (select 1 from #my_temp where #my_temp. ContainerNo =>> > c. ContainerNo
>> > and #my_temp. ContainerType = c. ContainerType and #my_temp.
>> > BookingNote = c. BookingNote
>> > and #my_temp. ImpVesselCode = c. ImpVesselCode and #my_temp.
>> > ImpVoyageCode = c. ImpVoyageCode and #my_temp. ReleaseRefNo = c.
>> > ReleaseRefNo )
>> > That should move the timetable to less than 5 minutes to run. I think
>> > that is acceptable. You can fine tune it further by using more #temp
>> > tables for inner subqueries and joining them with #my_temp using
>> > updates statements.- Hide quoted text -
>> - Show quoted text -
> Dan I don't need schema. If I need one I will ask tl from you. Thanks.
>

Help for Fine Tune Store procedure to increase its performance

Monday, March 26, 2012

help fixing a divide by zero error

I am very new to using SQL Server 2005. I have used Microsoft Access in the
past for my queries and reports.
I have the following calculation will give me the divide by zero error
message and not show the affected records.
The calculation is used to calculate how much footage (in feet) different
materials will give me.
The formula that I use is (material thickness * material width * 12 *
density) I have the density set up as a parameter to allow me to enter the
density of the particular material I am looking up.
The problem that I have is that on some material thickness and material
width may have either a 0 or may be blank.
I want the output field for the calculation to show a 0 for the calculated
footage, or something to show me that this record is not calculated but still
shows up on the report.
Thank you in advance for any help.
--
joef1946On Nov 3, 2:42 pm, joef1946 <joef1...@.discussions.microsoft.com>
wrote:
> I am very new to using SQL Server 2005. I have used Microsoft Access in the
> past for my queries and reports.
> I have the following calculation will give me the divide by zero error
> message and not show the affected records.
> The calculation is used to calculate how much footage (in feet) different
> materials will give me.
> The formula that I use is (material thickness * material width * 12 *
> density) I have the density set up as a parameter to allow me to enter the
> density of the particular material I am looking up.
> The problem that I have is that on some material thickness and material
> width may have either a 0 or may be blank.
> I want the output field for the calculation to show a 0 for the calculated
> footage, or something to show me that this record is not calculated but still
> shows up on the report.
> Thank you in advance for any help.
> --
> joef1946
The divide by zero error usually only occurs when dividing by zero.
You should be able to avoid the error if you use a conditional
statement/expression similar to this where you are doing the
calculation (in the report field/cell).
=iif(Fields!MaterialThickness.Value is Nothing or Fields!
MaterialThickness.Value = 0 or Fields!MaterialWidth.Value is Nothing
or Fields!MaterialWidth.Value = 0 or Parameters!Density.Label is
Nothing or Parameters!Density.Label = 0, 0, (Fields!
MaterialThickness.Value * Fields!MaterialWidth.Value * 12 * Parameters!
Density.Label))
This expression basically says if any of the variables or the
parameter are zero or null/empty, set the calculation to zero,
otherwise, set the calculation to the formula with the values
provided. Of course, if you are truly dividing by zero, use a similar
expression that checks for the denominator/bottom of the fraction
variable to see if it is zero or null/empty. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultantsql

Help finding the Max Total

Hi,

I have the following code

SELECT
PR.WBS2,
SUM(CASE WHEN LedgerAR.Period = '200408' AND LedgerAR.TransType <> 'CR'
THEN Ledgerar.amount * - 1
ELSE '0' END) AS BillExt
FROM PR
LEFT JOIN Ledgerar ON PR.WBS1 = Ledgerar.WBS1 AND
PR.WBS2 = Ledgerar.WBS2 AND PR.WBS3 = Ledgerar.WBS3
WHERE PR.WBS2 <> '98' AND pr.wbs2 <> '9001'
AND pr.wbs2 <> 'zzz' AND pr.wbs3 <> 'zzz' AND
pr.wbs1 = '001-298'
GROUP BY PR.WBS2

It prints out:
Wbs2 BillExt
0141 0
0143 0
1217 20580

I want the code to return the wbs2 code '1217' because it has the highest amount in BillExt '20580'.

Can someone help me with this?

Thanks.
lauraThe quick and dirty version...

select top 1 a.WBS2, a.BillExt
from
(SELECT
PR.WBS2,
SUM(CASE WHEN LedgerAR.Period = '200408' AND LedgerAR.TransType <> 'CR'
THEN Ledgerar.amount * - 1
ELSE '0' END) AS BillExt
FROM PR
LEFT JOIN Ledgerar ON PR.WBS1 = Ledgerar.WBS1 AND
PR.WBS2 = Ledgerar.WBS2 AND PR.WBS3 = Ledgerar.WBS3
WHERE PR.WBS2 <> '98' AND pr.wbs2 <> '9001'
AND pr.wbs2 <> 'zzz' AND pr.wbs3 <> 'zzz' AND
pr.wbs1 = '001-298'
GROUP BY PR.WBS2) a
order by BillExt desc|||Nothing dirty about it...|||That would be nice if there was only 1 record that needed to be returned.
select a.WBS2, BillExt=max(a.BillExt)
from
(SELECT
PR.WBS2,
SUM(CASE WHEN LedgerAR.Period = '200408' AND LedgerAR.TransType <> 'CR'
THEN Ledgerar.amount * - 1
ELSE '0' END) AS BillExt
FROM PR
LEFT JOIN Ledgerar ON PR.WBS1 = Ledgerar.WBS1 AND
PR.WBS2 = Ledgerar.WBS2 AND PR.WBS3 = Ledgerar.WBS3
WHERE PR.WBS2 <> '98' AND pr.wbs2 <> '9001'
AND pr.wbs2 <> 'zzz' AND pr.wbs3 <> 'zzz' AND
pr.wbs1 = '001-298'
GROUP BY PR.WBS2) a
group by a.WBS2
order by BillExt desc|||Now going a little further once I find the maximum total what if I then have to insert a value in that record. Is there a way to do this without using subqueries and instead using case statements?

For instance :

Wbs2 BillExt MaxValue
0141 0 0
0143 0 0
1217 20580 1

Thanks,
Laura|||That would be nice if there was only 1 record that needed to be returned.

Which is wat she asked for...I left my mind reading hat at home...

Laura....INSERT What to Where?

Insert into that record?

I don't understand.|||I'm sorry it's hard to describe what I am asking.

If I have the following information in a table:

WBS1 Amount MAX
0141 0
1217 2
1222 200

I first want to find the maximum of amount which is 200. Next, I want to put a 1 in the MAX column where the largest sum appears in amount.

Ending result:

WBS1 Amount MAX
0141 0
1217 2
1222 200 1

Is this possible?|||Well, once you know the ID of the record with the highest value you can issue and UPDATE statement to set its MAX field value to 1. But you will also need to issue an UPDATE statement to reset all other MAX field values that might have been the largest value before.

I can't recommend marking a record as the "Maximum value" when that status can change at any time. It is better to have a function or view or stored procedure that finds the maximum record dynamically.

Why do you want to mark this record and what are you going to do with it?|||Well, I simplified this example quite a bit to generate ideas for myself. But What I am supposed to do is create a report for accounting.

Accounting bills there clients based on services provided. Each service is designated codes. What is supposed to happen in one of the reports is to print the total reimbursable amount into the service that was billed the most.

There is no insertion are update that can be done it is just for display purposes only.

One of the restrictions that I have been under is that I cannot use subqueries only case statements which makes it even more difficult.

So I may have to go an entirely different path.

Thanks for your help,
Laura|||You can't use subqueries? What kind of lunacy is that?

You could still use a stored proc that first loads the MAX value's primary key into a variable and then uses the variable in subsequent queries.

Can't use subqueries? Kindly direct the person who gave you that directive to this informative website:

http://www.hov-hov.dk/you.htm|||Well, I simplified this example quite a bit to generate ideas for myself. But What I am supposed to do is create a report for accounting.

Accounting bills there clients based on services provided. Each service is designated codes. What is supposed to happen in one of the reports is to print the total reimbursable amount into the service that was billed the most.

There is no insertion are update that can be done it is just for display purposes only.

One of the restrictions that I have been under is that I cannot use subqueries only case statements which makes it even more difficult.

So I may have to go an entirely different path.

Thanks for your help,
LauraCan you "cheat" and JOIN a virtual table? Technically that isn't a sub-query.

-PatP

Side note to Blindman, I'm going to have to bookmark that site!

Help finding group totals for year end

My SQLServer 2k database has the following tables:
Transaction
Customer
Employee
Every Transaction is assigned a customerId
Every Customer is assigned an employee id
All Transactions have a total
What I am trying to do is calculate the top 5 customers by total for each
employee
I have the following which gives me the top 5 customers, but how do I get
the top 5 for each employee?
WB
Syntax:
SELECT t.CustomerId, sum(t.total)
FROM tblTransaction t
WHERE Year(t.TransactionDate) = 2005
GROUP BY t.CustomerId
ORDER BY sum(t.total) DESCSELECT t.employeeid, sum(t.total)
FROM tblTransaction t INNER JOIN employee e
on t.customerid = e.customerid
WHERE Year(t.TransactionDate) = 2005
GROUP BY t.employeeid
ORDER BY sum(t.total) DESC
"WB" wrote:

> My SQLServer 2k database has the following tables:
> Transaction
> Customer
> Employee
> Every Transaction is assigned a customerId
> Every Customer is assigned an employee id
> All Transactions have a total
> What I am trying to do is calculate the top 5 customers by total for each
> employee
> I have the following which gives me the top 5 customers, but how do I get
> the top 5 for each employee?
>
> WB
> Syntax:
> SELECT t.CustomerId, sum(t.total)
> FROM tblTransaction t
> WHERE Year(t.TransactionDate) = 2005
> GROUP BY t.CustomerId
> ORDER BY sum(t.total) DESC
>
>|||Is there any uniqueness about the TransAction table like an IDENTITY
Column..? I've assumed there is :-
select * from tblTransAction t1 WHERE PK_TranID IN
(SELECT TOP 5 t2.PK_TranID
FROM tblTransAction as t2
WHERE t2.CustomerID = t1.CustomerID
GROUP BY t2.CustomerID, t2.TransactionID, t2.Total
ORDER BY SUM(t2.Total) DESC)
HTH. Ryan
"WB" <none> wrote in message news:eiqpObVFGHA.376@.TK2MSFTNGP12.phx.gbl...
> My SQLServer 2k database has the following tables:
> Transaction
> Customer
> Employee
> Every Transaction is assigned a customerId
> Every Customer is assigned an employee id
> All Transactions have a total
> What I am trying to do is calculate the top 5 customers by total for each
> employee
> I have the following which gives me the top 5 customers, but how do I get
> the top 5 for each employee?
>
> WB
> Syntax:
> SELECT t.CustomerId, sum(t.total)
> FROM tblTransaction t
> WHERE Year(t.TransactionDate) = 2005
> GROUP BY t.CustomerId
> ORDER BY sum(t.total) DESC
>
>

Friday, March 23, 2012

help coverting a varchar to a float

Hi,

I'm using the following code to convert a varchar to a float in a trigger.

declare @.acre varchar (6)

set @.acre_size = 0.0

select @.acre = plotsizeacre
from inserted

declare @.num int
select @.num = isnumeric (@.acre)

if @.num = 1
set @.acre_size = @.acre

This normally works fine, but I'm getting errors if the plotsizeacre field is 1,75

Casting to a float or converting to a float also gives errors.

Any ideas how to solve this problem? (The field would normally be filled in properly, eg 1.75).

Thanks in advance,

Ian

You cannot use ISNUMERIC to do strick checking. This function will return 1 for value that can be converted to any of the integer, numeric, float and money data types. The value '1,75' can be converted to money but not float. Your best option is to chnage the schema and modify the column to float. This will require modifications from the client side also to make sure that the value that user enters is typed accordingly. If you have to keep the varchar data type then you will have to perform the cleaning of the value yourself - meaning you have to check for bad formats and convert appropriately or error out gracefully.

Wednesday, March 21, 2012

Help building query

Hello,
I need help in finding the best way to build a query for the following.
I have a table let's call EQPDTL.
This table contains all equipment usage for a company.
The table contains the followin fields:
CompanyNo
EquipNo
JobNo
DateEntered
RateCode (Can be H, I, B, Z, O, W)
DayOfWeek (1-7)
HoursUsed
TypeOfCost (0, 1, 2, 3, 4, or 5)
Here is a select statement that narrows down the actual records that I
need to work with. In english the records need to belong to CompanyNo X,
JobNo X, and it has to be a RateCode of (H, I, B, Z, O, or W), the type of
cost must not equal 0, and we only want records between the dates of X and
Y.
SELECT *
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR
RateCode = 'Z' OR RateCode = 'O' OR RateCode = 'W'))
AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
So now that we have the list of records now I need to get some
summarization data and have it in the following format.
1) For each piece of equipment I need to know the hours it was available.
This is simply the number of days it was used times 8. This would be the
first column.
2) For each piece of equipment I need to know the number of HoursUsed
based on the RateCode. So if equipment X had hours for 3 different rate
codes then at a minimum there would be at least 3 records in the original
dataset to work with... there could be more. There would be 6 columns, 1
for each RateCode (H, I, B, Z, O, W)
3) There would be another colum that has the total hours for all of the
RateCodes in #2 above.
4) There would be a final column that gives the percent utilized which is
calculated from the HoursUsed of RateCode H + O divided by AvailableHours
times 100.
The following query kinda gives me a summary look at the RateCodes where
each RateCode type summary is a record.
SELECT CompanyNo, EquipNo, RateCode, SUM(HoursUsed) AS Hours
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR RateCode = 'Z' OR
RateCode = 'O' OR RateCode = 'W')) AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
GROUP BY CompanyNo, EquipNo, RateCode
ORDER BY CompanyNo, EquipNo, RateCode
In the end I would like something like this as the results grouped by the
CompanyNo, JobNo, and EquipNo:
EquipNo, AvailableHours, H Hours, I Hours, O Hours, W Hours, Z Hours, B
Hours, Total Hours, Percent Utilized.
However I am unsure on what I should to to get this into more of a column
based results for each piece of equipment as well as get the other
results.
I can definitely take the first select statement and write an external
program to build a resulting table. I am sure I can also do a series of
temp tables to get a final result.
What I would like assistance on is how you would recommend doing this...
what do you believe would be the best method. I run into this type of
need several times a year and I really would like find a solution I can
use in situations as this.
Thank you for your time and willingness to help.
Greg
On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:

>Hello,
>I need help in finding the best way to build a query for the following.
(very big snip)
>What I would like assistance on is how you would recommend doing this...
>what do you believe would be the best method. I run into this type of
>need several times a year and I really would like find a solution I can
>use in situations as this.
>Thank you for your time and willingness to help.
Hi Greg,
This kind of problem is typically known as crosstab or pivot. The usual
recommendation is to do this in the client, as it's basically just
another formatting issue. However, there are times when that's not
practical; in that case, you can use a variation of this simple example:
USE pubs
go
SELECT pub_id,
SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
business_sales,
SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
psychology_sales,
SUM(CASE WHEN type NOT IN ('business','psychology') THEN
ytd_sales END) AS other_sales,
SUM(ytd_sales) AS total_sales
FROM titles
GROUP BY pub_id
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||In article <1q14i1porio6ug54pglonpop1k6emrec6s@.4ax.com>,
hugo@.pe_NO_rFact.in_SPAM_fo says...
> On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
> (very big snip)
> Hi Greg,
> This kind of problem is typically known as crosstab or pivot. The usual
> recommendation is to do this in the client, as it's basically just
> another formatting issue. However, there are times when that's not
> practical; in that case, you can use a variation of this simple example:
> USE pubs
> go
> SELECT pub_id,
> SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
> business_sales,
> SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
> psychology_sales,
> SUM(CASE WHEN type NOT IN ('business','psychology') THEN
> ytd_sales END) AS other_sales,
> SUM(ytd_sales) AS total_sales
> FROM titles
> GROUP BY pub_id
> go
>
> Best, Hugo
>
Hugo,
Thanks for the reply... I ended up going down the client route. I
appreciate your example... I started down that route as well and then just
decided I like the client solution for my needs.
Greg

Help building query

Hello,
I need help in finding the best way to build a query for the following.
I have a table let's call EQPDTL.
This table contains all equipment usage for a company.
The table contains the followin fields:
CompanyNo
EquipNo
JobNo
DateEntered
RateCode (Can be H, I, B, Z, O, W)
DayOfWeek (1-7)
HoursUsed
TypeOfCost (0, 1, 2, 3, 4, or 5)
Here is a select statement that narrows down the actual records that I
need to work with. In english the records need to belong to CompanyNo X,
JobNo X, and it has to be a RateCode of (H, I, B, Z, O, or W), the type of
cost must not equal 0, and we only want records between the dates of X and
Y.
SELECT *
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR
RateCode = 'Z' OR RateCode = 'O' OR RateCode = 'W'))
AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
So now that we have the list of records now I need to get some
summarization data and have it in the following format.
1) For each piece of equipment I need to know the hours it was available.
This is simply the number of days it was used times 8. This would be the
first column.
2) For each piece of equipment I need to know the number of HoursUsed
based on the RateCode. So if equipment X had hours for 3 different rate
codes then at a minimum there would be at least 3 records in the original
dataset to work with... there could be more. There would be 6 columns, 1
for each RateCode (H, I, B, Z, O, W)
3) There would be another colum that has the total hours for all of the
RateCodes in #2 above.
4) There would be a final column that gives the percent utilized which is
calculated from the HoursUsed of RateCode H + O divided by AvailableHours
times 100.
The following query kinda gives me a summary look at the RateCodes where
each RateCode type summary is a record.
SELECT CompanyNo, EquipNo, RateCode, SUM(HoursUsed) AS Hours
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR RateCode = 'Z' OR
RateCode = 'O' OR RateCode = 'W')) AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
GROUP BY CompanyNo, EquipNo, RateCode
ORDER BY CompanyNo, EquipNo, RateCode
In the end I would like something like this as the results grouped by the
CompanyNo, JobNo, and EquipNo:
EquipNo, AvailableHours, H Hours, I Hours, O Hours, W Hours, Z Hours, B
Hours, Total Hours, Percent Utilized.
However I am unsure on what I should to to get this into more of a column
based results for each piece of equipment as well as get the other
results.
I can definitely take the first select statement and write an external
program to build a resulting table. I am sure I can also do a series of
temp tables to get a final result.
What I would like assistance on is how you would recommend doing this...
what do you believe would be the best method. I run into this type of
need several times a year and I really would like find a solution I can
use in situations as this.
Thank you for your time and willingness to help.
GregOn Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:

>Hello,
>I need help in finding the best way to build a query for the following.
(very big snip)
>What I would like assistance on is how you would recommend doing this...
>what do you believe would be the best method. I run into this type of
>need several times a year and I really would like find a solution I can
>use in situations as this.
>Thank you for your time and willingness to help.
Hi Greg,
This kind of problem is typically known as crosstab or pivot. The usual
recommendation is to do this in the client, as it's basically just
another formatting issue. However, there are times when that's not
practical; in that case, you can use a variation of this simple example:
USE pubs
go
SELECT pub_id,
SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
business_sales,
SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
psychology_sales,
SUM(CASE WHEN type NOT IN ('business','psychology') THEN
ytd_sales END) AS other_sales,
SUM(ytd_sales) AS total_sales
FROM titles
GROUP BY pub_id
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||In article <1q14i1porio6ug54pglonpop1k6emrec6s@.4ax.com>,
hugo@.pe_NO_rFact.in_SPAM_fo says...
> On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
>
> (very big snip)
> Hi Greg,
> This kind of problem is typically known as crosstab or pivot. The usual
> recommendation is to do this in the client, as it's basically just
> another formatting issue. However, there are times when that's not
> practical; in that case, you can use a variation of this simple example:
> USE pubs
> go
> SELECT pub_id,
> SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
> business_sales,
> SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
> psychology_sales,
> SUM(CASE WHEN type NOT IN ('business','psychology') THEN
> ytd_sales END) AS other_sales,
> SUM(ytd_sales) AS total_sales
> FROM titles
> GROUP BY pub_id
> go
>
> Best, Hugo
>
Hugo,
Thanks for the reply... I ended up going down the client route. I
appreciate your example... I started down that route as well and then just
decided I like the client solution for my needs.
Gregsql

Help building query

Hello,
I need help in finding the best way to build a query for the following.
I have a table let's call EQPDTL.
This table contains all equipment usage for a company.
The table contains the followin fields:
CompanyNo
EquipNo
JobNo
DateEntered
RateCode (Can be H, I, B, Z, O, W)
DayOfWeek (1-7)
HoursUsed
TypeOfCost (0, 1, 2, 3, 4, or 5)
Here is a select statement that narrows down the actual records that I
need to work with. In english the records need to belong to CompanyNo X,
JobNo X, and it has to be a RateCode of (H, I, B, Z, O, or W), the type of
cost must not equal 0, and we only want records between the dates of X and
Y.
SELECT *
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR
RateCode = 'Z' OR RateCode = 'O' OR RateCode = 'W'))
AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
So now that we have the list of records now I need to get some
summarization data and have it in the following format.
1) For each piece of equipment I need to know the hours it was available.
This is simply the number of days it was used times 8. This would be the
first column.
2) For each piece of equipment I need to know the number of HoursUsed
based on the RateCode. So if equipment X had hours for 3 different rate
codes then at a minimum there would be at least 3 records in the original
dataset to work with... there could be more. There would be 6 columns, 1
for each RateCode (H, I, B, Z, O, W)
3) There would be another colum that has the total hours for all of the
RateCodes in #2 above.
4) There would be a final column that gives the percent utilized which is
calculated from the HoursUsed of RateCode H + O divided by AvailableHours
times 100.
The following query kinda gives me a summary look at the RateCodes where
each RateCode type summary is a record.
SELECT CompanyNo, EquipNo, RateCode, SUM(HoursUsed) AS Hours
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR RateCode = 'Z' OR
RateCode = 'O' OR RateCode = 'W')) AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
GROUP BY CompanyNo, EquipNo, RateCode
ORDER BY CompanyNo, EquipNo, RateCode
In the end I would like something like this as the results grouped by the
CompanyNo, JobNo, and EquipNo:
EquipNo, AvailableHours, H Hours, I Hours, O Hours, W Hours, Z Hours, B
Hours, Total Hours, Percent Utilized.
However I am unsure on what I should to to get this into more of a column
based results for each piece of equipment as well as get the other
results.
I can definitely take the first select statement and write an external
program to build a resulting table. I am sure I can also do a series of
temp tables to get a final result.
What I would like assistance on is how you would recommend doing this...
what do you believe would be the best method. I run into this type of
need several times a year and I really would like find a solution I can
use in situations as this.
Thank you for your time and willingness to help.
GregOn Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
>Hello,
>I need help in finding the best way to build a query for the following.
(very big snip)
>What I would like assistance on is how you would recommend doing this...
>what do you believe would be the best method. I run into this type of
>need several times a year and I really would like find a solution I can
>use in situations as this.
>Thank you for your time and willingness to help.
Hi Greg,
This kind of problem is typically known as crosstab or pivot. The usual
recommendation is to do this in the client, as it's basically just
another formatting issue. However, there are times when that's not
practical; in that case, you can use a variation of this simple example:
USE pubs
go
SELECT pub_id,
SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
business_sales,
SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
psychology_sales,
SUM(CASE WHEN type NOT IN ('business','psychology') THEN
ytd_sales END) AS other_sales,
SUM(ytd_sales) AS total_sales
FROM titles
GROUP BY pub_id
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||In article <1q14i1porio6ug54pglonpop1k6emrec6s@.4ax.com>,
hugo@.pe_NO_rFact.in_SPAM_fo says...
> On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
> >Hello,
> >
> >I need help in finding the best way to build a query for the following.
> (very big snip)
> >What I would like assistance on is how you would recommend doing this...
> >what do you believe would be the best method. I run into this type of
> >need several times a year and I really would like find a solution I can
> >use in situations as this.
> >
> >Thank you for your time and willingness to help.
> Hi Greg,
> This kind of problem is typically known as crosstab or pivot. The usual
> recommendation is to do this in the client, as it's basically just
> another formatting issue. However, there are times when that's not
> practical; in that case, you can use a variation of this simple example:
> USE pubs
> go
> SELECT pub_id,
> SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
> business_sales,
> SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
> psychology_sales,
> SUM(CASE WHEN type NOT IN ('business','psychology') THEN
> ytd_sales END) AS other_sales,
> SUM(ytd_sales) AS total_sales
> FROM titles
> GROUP BY pub_id
> go
>
> Best, Hugo
>
Hugo,
Thanks for the reply... I ended up going down the client route. I
appreciate your example... I started down that route as well and then just
decided I like the client solution for my needs.
Greg

Help building a #temp table

I have the following query:

--build query for #temp table
select 'L' as RECTYPE, identity(int, 1, 1) as JELINENO, max(i.jeno) + 1 as JENO, 'AJE' as JECODE,
--build the fiscal year, when month = October, November or December add 1 to actual year
FY = case
when datepart(month, GetDate()) = '10' then datepart(year, GetDate()) + 1
when datepart(month, GetDate()) = '11' then datepart(year, GetDate()) + 1
when datepart(month, GetDate()) = '12' then datepart(year, GetDate()) + 1
else datepart(year, GetDate())
end,
-- The PD starts in April and increases by 1 each month
PD = case
when datepart(month, GetDate()) = '1' then '10'
when datepart(month, GetDate()) = '2' then '11'
when datepart(month, GetDate()) = '3' then '12'
when datepart(month, GetDate()) = '4' then '1'
when datepart(month, GetDate()) = '5' then '2'
when datepart(month, GetDate()) = '6' then '3'
when datepart(month, GetDate()) = '7' then '4'
when datepart(month, GetDate()) = '8' then '5'
when datepart(month, GetDate()) = '9' then '6'
when datepart(month, GetDate()) = '10' then '7'
when datepart(month, GetDate()) = '11' then '8'
when datepart(month, GetDate()) = '12' then '9'
else 'error'
end,
'1' as SUBPD, 'N' as REVFLAG, 'Journal Entry from MAXIMO' as HEADDESC,
--If the issuetype is a return the AMOUNT is a negative value, multiply by -1
AMOUNT = case
when m.linecost < 0 then m.linecost * -1
else m.linecost
end
into #temp
from matusetrans m, issuesreturns i
go
select * from #temp
drop table #temp

When I run this query, I get the following error:

Server: Msg 8118, Level 16, State 1, Line 2
Column 'm.linecost' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 2
Column 'm.linecost' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 2
Column 'm.linecost' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#temp'.

I can find no reason for this error. If I run the query without the AMOUNT = case statement, it works fine. Can anyone help?

Thanks,

Dave

The mistake only arises in the AMOUNT=CASE statement since it's the only one (except from the MAX(i.jeno) column) that's accesing a real column in the database. The rest of the columns are calculated.

So, as you already have an aggregate function (the MAX) the rest of the columns from real tables must either have an aggregate function or the SELECT clause must contain a GROUP BY clause.

You can't form a SELECT with only one MAX column and the rest of them normal columns without telling SQL how it should group the results from where to choose the MAX value from.

If you really want to achieve that you have to calculate the MAX column either before in a variable or in the same SELECT with a sub-SELECT query.

IE: SELECT var1, var2, var3, (SELECT MAX(othervar) FROM table)

Hope i was clear.
Good luck,
Alan.

|||

First of all I would compress the code and rewrite it as following.

Please post DDLs as it's not clear what do you want to do. the problem is clear as you have a reference to the column m.linecost that is not in a group by list. Just to get rid of the error you can add a group by abs(m.linecost), but it doesn't look like what you want to do.

select
'L' as RECTYPE,
identity(int, 1, 1) as JELINENO,
max(i.jeno) + 1 as JENO,
'AJE' as JECODE,

--build the fiscal year, when month = October, November or December add 1 to actual year

FY = datepart(year, GetDate()) + (datepart(month, GetDate()) + 2) / 12,

-- The PD starts in April and increases by 1 each month

PD = (datepart(month, GetDate()) + 8) % 12 + 1,

'1' as SUBPD,

'N' as REVFLAG,

'Journal Entry from MAXIMO' as HEADDESC,

--If the issuetype is a return the AMOUNT is a negative value, multiply by -1

AMOUNT = abs(m.linecost)

into #temp

from matusetrans m, issuesreturns i

|||Dave -

You can't use an Aggregate Function on only one column without using a Group By for the other columns

So you either need to group by m.linecost OR put an Agrregate around it - like max() or min().

To simpify your query

select
max(i.jeno) + 1 as JENO, -- YOU USED MAX HERE
AMOUNT = case
when m.linecost < 0 then m.linecost * -1
-- SO YOU EITHER NEED TO USE AN AGRREGRATE HERE OR
else m.linecost
end
into #temp
from matusetrans m, issuesreturns i
-- OR GROUP BY m.linecost HERE

Hope that helps

AWAL

Friday, March 30, 2012

Help in database design

Help in creating xsd file

Help in Chosing the Software for Reporting Services

HELP HELP!..............................

Wednesday, March 28, 2012

help for query

Help for Fine Tune Store procedure to increase its performance

Help for Fine Tune Store procedure to increase its performance

Help for Fine Tune Store procedure to increase its performance

Monday, March 26, 2012

help fixing a divide by zero error

Help finding the Max Total

Help finding group totals for year end

Friday, March 23, 2012

help coverting a varchar to a float

Wednesday, March 21, 2012

Help building query

Help building query

Help building query

Help building a #temp table

Absurd project

Blog Archive

About Me