Date: 08/15/08     Keywords: software

Hi. I am currently using a stripped-down version of CR 8.0 integrated into a software platform called SysPro.

I'm trying to pull out some timecard information for multiple people, organized by employee, then date, then job#. I've put it in and all of the names and times are pulling correctly.

HOWEVER. I also need to calculate the Regular Pay Time and Overtime from these numbers. Currently, I've got a Summary of my Total Time (which is calculated from four different time fields in each record) in my Group #1 Footer, which gives the correct amount of total hours.

What I'd like to do is create two formula fields that calculate out RT and OT (regular time<=40 hours).

What I imagine for these fields is something like the following:
Regular time: if {Sum of @Total Time} > 40 then "40"
Overtime: If {Sum of @Total Time} >40 then {@Total Time} - 40

Of course, the problem here is that I cannot insert a Summary field into this equation the way I would a calculation field.

Anyone have any ideas? Help would be greatly appreciated.

Source: http://community.livejournal.com/sqlserver/66825.html

Date: 04/10/08     Keywords: database, sql

Hi,

I'm new to this community. I joined last week. I hope posting a Crystal Reports question is OK to do here.

I'm using a SQL server to store a database that contains calibration data. I have been using Crystal Reports to create calibration certificates and so far everything has been working fine. Recently I started working on a way to create calibration labels using Crystal Reports. I have everything laid out fine and all the correct data is being displayed. The problem I am having is trying to make the report start printing from a specific location.

I'm trying to print to a sheet of labels. There are 30 labels on the sheet. 10 rows and 3 columns. There isn't a problem if I'm trying to print on a brand new sheet of labels. The problem I'm running into is trying to start printing on a specific row/column.

For example, I had a brand new label sheet and printed 13 calibration labels. So I have used up the first 4 rows and the first column on the fifth row. Now the next time I want to print calibration labels I want to start on the second column of the fifth row. Does anyone know a way to do this?

I'm currently using Crystal Reports 8.0 but I'm planning on getting the newest version in a few weeks.

Source: http://community.livejournal.com/sqlserver/66378.html

Date: 03/03/08     Keywords: sql

Hi,

Our daily processing is failling on various stored procedures. Because these stored procedures are launched via a vbscript we are unable to get a meaningful SQL error message being returned by the stored procedure when it fails. What can you recommend to trace an errors. It is related to SQL Server 2000.

Thank you.

Source: http://community.livejournal.com/sqlserver/66253.html

Date: 01/04/08     Keywords: database, web

Okay, now that the compatibility-level problem has gone the way of the great auk (until someone at the client decides to reset it again, I guess), I can now get to my underlying problem-- PIVOT, which I can't seem to understand from the overly-simplistic exampes I've found on the web.

Here's a small, heavily-edited version of the current crosstab query:

--create the pivot
INSERT INTO @m_tblItemDataPivot (brand, upc, item_desc, category, class, subclass, week_ending, week_1_dol, 
week_2_dol, week_3_dol, week_4_dol, week_5_dol, week_6_dol, week_7_dol, week_8_dol, ... 
SELECT DISTINCT i.brand, i.upc, u.item_desc, i.category, i.class, i.subclass, i.week_ending,
SUM(CASE p.period_sysno WHEN @m_intThisWeekSysno - 51 THEN phg.total_dollars ELSE 0 END) AS week_1_dol,
SUM(CASE p.period_sysno WHEN @m_intThisWeekSysno - 50 THEN phg.total_dollars ELSE 0 END) AS week_2_dol,
SUM(CASE p.period_sysno WHEN @m_intThisWeekSysno - 49 THEN phg.total_dollars ELSE 0 END) AS week_3_dol,
SUM(CASE p.period_sysno WHEN @m_intThisWeekSysno - 48 THEN phg.total_dollars ELSE 0 END) AS week_4_dol,
SUM(CASE p.period_sysno WHEN @m_intThisWeekSysno - 47 THEN phg.total_dollars ELSE 0 END) AS week_5_dol,
SUM(CASE p.period_sysno WHEN @m_intThisWeekSysno - 46 THEN phg.total_dollars ELSE 0 END) AS week_6_dol,
SUM(CASE p.period_sysno WHEN @m_intThisWeekSysno - 45 THEN phg.total_dollars ELSE 0 END) AS week_7_dol,
SUM(CASE p.period_sysno WHEN @m_intThisWeekSysno - 44 THEN phg.total_dollars ELSE 0 END) AS week_8_dol,...
FROM   ia_rep.dbo.pos_history phg (NOLOCK)			
INNER JOIN @m_tblSchickUPC u  ON phg.upc_sysno = u.upc_sysno
INNER JOIN @m_tblItemData i ON u.upc_code = i.upc
INNER JOIN ia_rep.dbo.periods p (NOLOCK) ON phg.period_sysno = p.period_sysno
    AND p.period_sysno BETWEEN @m_intThisWeekSysno-52 AND @m_intThisWeekSysno
	GROUP BY i.brand, i.upc, u.item_desc, i.category, i.class, 
            i.subclass, i.week_ending

Source: http://community.livejournal.com/sqlserver/65797.html

Date: 01/03/08     Keywords: database, sql

Is there any way to simply check what the compatibility level is on a database without changing it in SQL Server 2005?

Everything I've found points to sp_dbcmptlevel, which will not simply return the level the database is currently at, it requires you to enter a value to change it to. I need to return the current compatibility level, not write over it.

Source: http://community.livejournal.com/sqlserver/65674.html

Date: 12/14/07     Keywords: no keywords

I'm trying to write something in a stored procedure (I say "something" because I don't care if it's a SELECT statement, a CREATE TABLE statement, whatever) that will allow me to dynamically declare column names. I've got variables set to 52 week ending dates (@m_week1, @m_week2, etc.), and I need to turn them into 104 column names by adding suffixes ([week1]_dollars, [week1]_units, [week2]_dollars, etc.). Doesn't seem to matter how I do this, however, I get a syntax error.

Can it be done? If so, how?

Thanks.

Source: http://community.livejournal.com/sqlserver/65345.html

Date: 12/03/07     Keywords: database, sql

I'd like input on what is considered SQL Server developer tasks vs. SQL Server DBA tasks. I know there's a lot of overlap and would like to identify which task truly falls into which bucket and which tasks truly apply to both.



I'm a bit "stuck between a rock and a hard place," SQL Server-wise. I've done a lot of database design, creation, administering, writing complex ANSI SQL and T-SQL and the like in Access since 1994 (when you didn't get fluffy cute wizards to help you and had to learn to program and normalize data and plan db's the hard way!) :-p

I now have a little over 3 yrs experience in SQL Server (v.2000 and 2005). Unfortunately, both places where I did SQL work did not allow for me to get into the fun developer tasks (barely had the chance to work with or edit stored procedures or do any real performance tuning or query optimization).

I did get to do a lot of complex T-SQL coding, including detailed CASE statements. I did various tasks that fall under ETL (importing flat files, spreadsheets, cleaning the data, pulling random samples) as well as Decision Support (pulling a variety of counts and other aggregate data post-project for the Project Managers and Exec. Mgmt).

My goal is to get the new 2005 version of the MCDBA certification and be a 2005 SQL Developer. It's a bit hard to get in anywhere except as a SQL Server Analyst at the moment. Any advice in that area would be appreciated.

Source: http://community.livejournal.com/sqlserver/65221.html

Date: 11/28/07     Keywords: sql, microsoft

Anyone ever work with Microsoft's Live Communication Server (LCS) from the SQL side?  I'm interested in the scalability of it and I'm having trouble digging up very much information from the vendor... 

Source: http://community.livejournal.com/sqlserver/64773.html

Date: 11/06/07     Keywords: database, sql

I need to produce a report with cummulative stats each each month & year in the database.

The data is stored as follows:

11/05/2007 Dave FXR1200
11/05/2007 Frank FXR1200
10/31/2007 Tom Fatboy
10/30/2007 Dave Fatboy
10/30/2007 Paul TZ250

I need to produce a report that shows:

November 2007 2
October 2007 3

My SQL is poor (hence my post). The only query I can come up with is the one below that produces yearly stats.

SELECT year(logdate) as LogMonth, COUNT(id) AS Prospects
FROM calllogs
GROUP BY year(logdate)
order by year(logdate) asc

Can anyone suggest the approach I need to produce the correct stats ?

Source: http://community.livejournal.com/sqlserver/64541.html

Date: 11/05/07     Keywords: sql

I joined the group this morning, but sorta feel stupid in that I've no idea how it works and you guys seem well, out of my league. I just got a job offer last Friday to go and help migrate/admin stuff from MS Access over to SQL Server 2005. I know Access, I know SQL, but damned if I can figure out how to do anything on the server thing. The only advice I got from my friend was to just hook it up to some VB thing and do it that way. Are there any tutorial sites that you'd recommend or just fetch myself SQL Server for Dummies?
I already downloaded Server 2005 Express and the configuration manager stuff.

Source: http://community.livejournal.com/sqlserver/64489.html

Date: 11/02/07     Keywords: database, sql

Does anyone know a decent freeware for data modeling from SQL Server database? Like ERWin Data Modeler but free. I don't need one that's on steroids, just something that will allow me to pull data models from existing tables in SQL Server 2000/2005 and Oracle.

If I'm asking too much for a freebie, can anyone suggest a good one as alternative to ERWin? I'm curious to see what people are using. We're looking for one that's cheaper in cost than ERWin.

Thanks!

Source: http://community.livejournal.com/sqlserver/64005.html

Date: 10/10/07     Keywords: no keywords

Okay, not quite understanding what I'm doing wrong. According to the help file, this query:

-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable

should produce this:

Cost_Sorted_By_Production_Days 0 1 2 3 4
AverageCost 5.0885 223.88 359.1082 NULL 949.4105

I translate it to work with my own data set.

select division_sysno, division_desc, upc_sysno, upc_code, item_sysno, item_code, item_desc,
revlon_product, company_category_sysno, [286]...[389]
from @m_SkeletonData
pivot
(
sum(total_units)
for period_sysno
in ([286]...[389])
) as p

(101 numbers removed for brevity from each bit)

I should get one row for each product, yes? Ah, no! Here's a heavily-edited sample of my result set:

1 WEST COAST 1376418 03300000013 147303 0409076 ALMAY 1 COAT L/S FLUTTER Almay 189049 13 NULL NULL NULL
1 WEST COAST 1376418 03300000013 147303 0409076 ALMAY 1 COAT L/S FLUTTER Almay 189049 NULL 4 NULL NULL
1 WEST COAST 1376418 03300000013 147303 0409076 ALMAY 1 COAT L/S FLUTTER Almay 189049 NULL NULL 10 NULL
1 WEST COAST 1376418 03300000013 147303 0409076 ALMAY 1 COAT L/S FLUTTER Almay 189049 NULL NULL NULL 10

Obviously, it's not aggregating the way I expect it to. Given that the rest of the information on each line is identical, I can't quite understand why. I thought it might be the extra non-aggregated fields, but I reduced it to a single field of that type, and the problem persists. Anyone have any ideas? Thanks.

Source: http://community.livejournal.com/sqlserver/63976.html

Date: 09/27/07     Keywords: sql

Here's a T-SQL (SQL2005) statement for you.

UPDATE @m_tblAllSalesStaging SET UnitsPercentChange4 = (TYUnits_4Wks/LYUnits_4Wks)*100 WHERE UnitsPercentChange4 IS NULL

Here's some of my result set (the columns are TYUnits_4Wks, LYUnits_4Wks, and UnitsPercentChange4):
5126 6240 0.00
24 156 0.00
64 141 0.00
10 36 0.00
2180 2541 0.00
0 2 0.00
2 48 0.00
0 12 0.00

More interesting, this is the same logic I'm using for the dollars columns that come just before, and the division is working swimmingly.

I have no clue why it's not picking anything up. Anyone have any ideas? Thanks.

Source: http://community.livejournal.com/sqlserver/63508.html

Date: 09/24/07     Keywords: database

I'm a tad confused here. I thought when you used the syntax...

CREATE TABLE ##m_tblOpenReceipts(

...that the table was destroyed when the stored procedure ended. So why, when I try to run it a second, time, do I get the error...

Msg 2714, Level 16, State 6, Line 43
There is already an object named '##m_tblOpenReceipts' in the database.

...?

(This is also the case with a single pound.)

If I'm wrong about this, as I seem to be, how does one create a true temp table?

Source: http://community.livejournal.com/sqlserver/63487.html

Date: 08/28/07     Keywords: sql, web

I'm 98% sure this can be done, but I'm not finding anything on the web that explicitly says yes or no, and I want to rule out the possibility that I'm just banging my head against a wall. T-SQL, SQL Server 2005.

Can SET IDENTITY_INSERT be used on an in-memory table? e.g.,

DECLARE @m_tblOpenReceipts TABLE (
ID INT IDENTITY (1,1), (etc.)
}

...

SET IDENTITY_INSERT @m_tblOpenReceipts ON

Thanks.

Source: http://community.livejournal.com/sqlserver/63141.html

Date: 08/24/07     Keywords: web

I'm trying to create a temporary staging table in SSIS, and I assume I'm going about it the wrong way. I'm importing from a flat file into what I want to be a temp table I can then access from the next step in the package, but none of the destinations seem to have that functionality; the only temporary destinations I can find are DataReader and Recordset, and I can't figure out, once I set them as a destination in step A, how to then access them in step B. Not finding any relevant info on the web, maybe I'm using the wrong search terms.

Anyone know how to do this? Thanks.

Source: http://community.livejournal.com/sqlserver/62788.html

Date: 08/13/07     Keywords: database

Dear developers! If you have time and desire to me to help, help to do the design for site. The Details shall write on E-mail Also ask you to help to form the database and code of the site. In principle, they there is, but wanted that all were made professional. Also write on E-mail.

The Site has two languages. Shall very pleased if you will help to avoid some mistakes in translation and literate,but the main, understandable write the text.

Source: http://community.livejournal.com/sqlserver/62393.html

Date: 08/04/07     Keywords: web

I am making a project intranet page and I need to pull up a section of tasks that have been completed in the last week and currently have it only pulling the last 10 items on the webpage with the following code, any more efficient suggestions?

SELECT *
FROM tasks
WHERE complete = 'y' AND
comp_date < current_timestamp

Source: http://community.livejournal.com/sqlserver/62020.html

Date: 07/24/07     Keywords: asp

My previous post was easily solved but face a new one now. I am getting a String or binary data would be truncated error with the same AddUser stored procedure.

I double-checked that the sizes for the varChar and longVarChar fields were correct in both my code and the SP, and they are.

I am still learning a lot of the intricacies of using SP's in ASP/VB so I'm still highly uncertain on many things here.

Sample output. The length of the field is in parenthesis:
usrCategory: 2 (1)
usrCity: Manyunk (7)
usrZipcode: 19127 (5)
templateid: (0)
subaction: create (6)
ID: 0 (1)
usrEmail: testuser321@aol.com (19)
usrIsActive: 0 (1)
submit: Submit (6)
validate: 1 (1)
usrUserId: test1123 (8)
usrPassword: asdfsdf (7)
usrAddressID: (0)
usrContactMeansID: (0)
usrAddress1: 1900 West Ln. (13)
usrAddress2: Suite 4000 (10)
usrStatus: 7 (1)
usrFirstName: Bob (3)
usrPhone: 555-7878 (8)
usrPhoneExt: 351 (3)
usrMiddleInitial: A (1)
usrNonUsState: (0)
usrLastName: Smith (5)
usrCompany: Big Company (11)
usrFax: 555-9663 (8)
usrInactiveReason: just a test (11)
usrTitle: Big Chief (9)
usrCountry: 225 (3)
usrStateID: 38 (2)
usrComments: (0)

And turning off ANSI_WARNINGS is not an option.

Source: http://community.livejournal.com/sqlserver/61758.html

Date: 07/24/07     Keywords: asp, sql

I'm running classic ASP against SQL Server 8, and trying to execute a stored procedure. It's the 4th in a sequence of them so I know that my code, so far, is accurate. Here's the top part of the SP:

CREATE PROCEDURE AddUser (
  @statusCodeValueId int,
  @userCategoryCodeValueId int,
  @AddressId int,
  @ContactMeansId int,
  @firstName varchar(25),
  @middleInitial char(1),
  @lastName varchar(25),
  @username varchar(25),
  @password varchar(100),
  @orgnaizationUserBelongsTo varchar(100),
  @title varchar(20),
  @Comments varchar(2000),
  @inactivationReason varchar(1000),
  @isActive bit,
  @Userid int
 )

Source: http://community.livejournal.com/sqlserver/61512.html