Transact SQL :: Sum And / Or Group Not Working Properly

Oct 21, 2015

I have a query taken from a Crystal Report, and I've been working to modify it for a slightly different purpose.  The initial report was designed to take an input of an end date, go back to the last day of the previous month, get Actuals (sales totals), then add up all sales, costs, and purchases up to the end date, then display assorted data.

The new query needs to take a begin date and and end date, go back to the last day of the previous month for actuals, add all sales, costs, and purchases to those actuals until it gets to the begin date.  That is now the new Actual, and we need to sum up all transactions from then to the end date.

My problem lies in the fact that I can't get the query to add up the numbers.

The query is here:

SELECT DailyReport.Store, DailyReport.Report_Date, sum(dailyreport_Detail.sales) as Sales, sum(dailyreport_detail.actual) as Actual, sum(dailyreport_detail.cost) as Cost, CompanyGroups.Category, DailyReport_Detail.Product, CompanyGroups.Retail_Inv
FROM (`DailyReport` `DailyReport`
INNER JOIN `DailyReport_Detail` `DailyReport_Detail` ON

[Code] ...

The return data looks like this:

3212,2015-07-31,126.35,1781.20,0.00,Fountain,Hot Dsp Bev,0
3212,2015-07-31,149.17,1311.94,0.00,Fountain,Cold Dsp Bev,0
3212,2015-07-31,666.63,4930.02,0.00,Food Service,My Deli,0
3212,2015-08-01,88.67,0.00,0.00,Fountain,Hot Dsp Bev,0
3212,2015-08-01,109.62,0.00,0.00,Fountain,Cold Dsp Bev,0

[Code] ....

I want it to look like this:

3212,2015-08-04,595.53,1311.94,400.76,Fountain,Cold Dsp Bev,0
3212,2015-08-04,485.85,1781.20,165.66,Fountain,Hot Dsp Bev,0
3212,2015-08-04,2762.05,4930.02,1388.00,Food Service,My Deli,0

View 8 Replies


ADVERTISEMENT

Order By Not Working Properly

Jan 17, 2007

Hello, i have something like this, i want the annoucements (status = 0) to be on top, then topics with (status = 1) below, then the rest of the topics.
So i tried:
SELECT forum_topics.id, forum_topics.status, forum_topics.username AS starter, forum_topics.subject, forum_topics.closed, forum_topics.answerpostid, forum_topics.views, forum_topics.answers, forum_topics.lastanswer, forum_topics.lastanswerid, forum_topics.created AS started, forum_answer.username, forum_answer.answer, forum_answer.created FROM forum_topics LEFT OUTER JOIN forum_answer ON forum_answer.id = forum_topics.lastanswerid WHERE (boardid = @ID OR boardid = 0) ORDER BY (status) ASC, (created) ASC
Problem is that they are not sorted diffrently, when i change the (created) ASC to (created) DESC i get the same result and the rows are not sorted, they only get sorted by status so i have status=0 at the top, then status=1 then the rest. How do i get them to be sorted first by status ASC then by created ASC/DESC?
Patrick

View 1 Replies View Related

SQL Server Is Not Working Properly

Apr 21, 2008

Hello friends, I have developed an ASP.NET 2.0 application. The problem is that the IIS server returns the next error:

SQL Server does not allow remote connections

Sometimes returns:

SQL Server does not exists.

My question is if there is any known bug with ASP.NET and SQL Server. My sql server have activated the Remote Connections (its working properly on other web page).

Its a problem of the number of connections??
Its a problem ofbad configuration??
Its a IIS problem??

Please bring me some help.

View 8 Replies View Related

Ragged Right Not Working Properly

Dec 25, 2007



Hi,
I am exporting a table to a fixed width flat file using "Ragged Right" format. Though i specify carriage return for the last column, the text file is not taking it up.

I am doing the following for connection
cmFlatFile.properties("Format").SetValue(cmFlatFile,"RaggedRight")
cmFlatFile.properties("RowDelimiter").SetValue(cmFlatFile,vbCrLf)

and while adding the column i am setting up the width as follows

vColumns = flatFileDestination.InputCollection(0).GetVirutalInput().VirtualInputColumnCollection

For cols = 0 to vColumns.Count -1
Dim col As IDTSConnectionManagerFlatFileColumn90 = ff.Columns.Add()
if cols = vColumns.Count - 1 Then
col.ColumnDelimiter = vbCrLf
end if
cols.ColumnType = "FixedWidth"
cols.Columnwidth = 20
......
Next cols

Still the rows are exported next to each other just like when we use FixedWidth.

View 4 Replies View Related

Logging Isn't Working Properly

Oct 2, 2007

Hi,

I have several packages where I set up a SQL Server log provider. The packages are all called from jobs.

However, for some reason, only ONE of the packages is logging to the sysdtslog90 table in my specified database.

All of packages have the identical set up for the logging. Here are my settings under logging:

PROVIDERS AND LOGS:
Name: SSIS Log Provider for SQL Server (box is checked)
Configuration: ServerName.DBName (I use this same connection for all the packages)

DETAILS:
OnError is checked

CONTAINERS:
All items are checked

So what's going on here? I have successfully logged to this table using test cases in BIDS, and as I mentioned, ONE of the packages is actually logging to the table when it's called from the job.

Thanks



View 5 Replies View Related

Connection TimeOut Seems Not Working Properly

Oct 11, 2007

Hi all,
I use SqlHelper to connect to database. I need to set a timeout to execute some sp because I don´t want to wait for this sp if it make me wait a lot. So I set the Connection TimeOut every time I execute a sp. But this seems not working properly. I set this value to 5 and I execute a sp than runs in 15 but It waits for it.
Any idea
 
Best regards
www.ITCubo.net

View 1 Replies View Related

UPDATE Trigger Not Working Properly

Oct 4, 2004

We've altered an UPDATE trigger - now it doesn't work properly. When updating a record (via a web application) by clearing the value for a particlar column, we get error:

UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.

Code = 800a0c93
Source = ADODB.Recordset
Description = Operation is not allowed in this context.

What the trigger does is prevent a zero-length string from being inserted into the database, which violates a check constraint, and instead inserts NULL. The web app - whose code we cannot modify - apparently does this. This was working fine, until we altered the trigger to add an additional column for update. Our SQL developer person has left, so us less knowledgable (about SQL) folks are trying to pick up the slack.

trigger code:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO




ALTER TRIGGER trigViaNetCardholderUpdateEmptyStrings
ON viewViaNetCardholder
INSTEAD OF UPDATE AS
UPDATE tblIDCross
SET
tblIDCross.chUSCId = CASE WHEN ins.chUSCId = '' THEN NULL ELSE ins.chUSCId END,
tblIDCross.chNineDigit = CASE WHEN ins.chNineDigit = '' THEN NULL ELSE ins.chNineDigit END,
tblIDCross.chPID = CASE WHEN ins.chPID = '' THEN NULL ELSE ins.chPID END,
tblIDCross.chEmployeeId = CASE WHEN ins.chEmployeeId = '' THEN NULL ELSE ins.chEmployeeId END,
tblIDCross.chAIMSNumber = CASE WHEN ins.chAIMSNumber = '' THEN NULL ELSE ins.chAIMSNumber END,
tblIDCross.intCustomerType = ins.intCustomerType
FROM INSERTED ins
WHERE tblIDCross.intUSCardId = ins.intUSCardId




GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Please help...

View 1 Replies View Related

Bulk Insert Not Working Properly

Mar 28, 2008

Hi guys, I am importing a file using the Bulk Insert command, but the fieldterminator is not working for me.
My data:
“lname�, “fname�, “addr�, “phone�, “lang�

My command:
BULK INSERT dbo.zGE_RCF_POS_IMPORT FROM 'E:operationsdatafilesGEFTPINGE_RCF_POS_EXPORT.txt'
WITH (FIELDTERMINATOR = '","')
The quote are removed from every field except the first quote in front of lname and the trailing quote behind lang. I tried changing the command to:
BULK INSERT dbo.zGE_RCF_POS_IMPORT FROM 'E:operationsdatafilesGEFTPINGE_RCF_POS_EXPORT.txt'
WITH (FIELDTERMINATOR = '"')
But ran into field conversion problems, I also tried defining two fieldterminators got a syntax error. How can I get the desired results of removing all dbl quotes? Or do I have to write something to follow the Bulk Insert to remove the leading and trailing quotes?

View 4 Replies View Related

SP And ADO Page Property Not Working Properly

Dec 3, 2005

Hi all...I have a stored proc that works fine except I can't get thepaging property to work in ASP. It seems that because I'm building theSQL withing the SP is causing the problem. If I don't build the SQL asa string paging works. In my ASP page, the pagecount property returns-1 for some reason.Here's the proc:SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOALTER Procedure LS_DisplayAllCategoriesBoolean@aSections varchar(255),@field varchar(255)Asdeclare @sql varchar(1000)SET NOCOUNT ONset @sql = 'SELECT DISTINCT ls_product_catalog.id,ls_product_catalog.ItemNumber, ls_product_catalog.Name,ls_product_catalog.[Descriptor], ls_product_catalog.Price,ls_product_catalog.Shade_Name, ls_product_catalog.Sale_Price,ls_product_catalog.Sale_DescFROM dbo.ls_brand INNER JOINdbo.ls_product_catalog ON dbo.ls_brand.nID =dbo.ls_product_catalog.CategoryID INNER JOINdbo.ls_product_category ON dbo.ls_product_catalog.id =dbo.ls_product_category.nProductIDWHERE dbo.ls_brand.bVisible = 1 anddbo.ls_product_category.nCategoryID in (Select IntValuefrom dbo.CsvToInt(''' + @aSections + ''') ) 'if @field = 'bNew'beginset @sql = @sql + ' AND bNew = 1 AND(dbo.ls_product_catalog.bGiftOnly is null ordbo.ls_product_catalog.bGiftOnly = 0)'set @sql = @sql + ' order byls_product_catalog.[Descriptor]'endif @field = 'bGift'beginset @sql = @sql + ' AND bGift = 1 or bGiftOnly =1'set @sql = @sql + ' order byls_product_catalog.[Descriptor]'endif @field = 'bGiftOnly'beginset @sql = @sql + ' AND bGiftOnly= 1'set @sql = @sql + ' order byls_product_catalog.[Descriptor]'endif @field = 'sale_price'beginset @sql = @sql + ' AND (sale_price is not null ORsale_desc is notnull)'set @sql = @sql + ' order by ls_product_catalog.Name'endexec(@sql)GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ON

View 1 Replies View Related

TRY...CATCH Block Not Working Properly

May 25, 2008

Can someone please help me figure out why this trigger causes a "Timeout expired" error after the first try? What I'm trying to do is create an incrementing default value for the projectid field. I have a unique index on the field and for some reason, the INSERT statement won't run inside the TRY block, even though the WHILE loop creates a unique id. It's acting like the loop isn't incrementing, but the PRINT statements prove that the loop does work, but the INSERT statement doesn't work after a succesful first try (i.e. NEWPROJ-1 is inserted correctlly). Anyways, any help would be appreciated.





Code Snippet
GO
/****** Object: Trigger [Projects].[CreateID] Script Date: 05/25/2008 14:16:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [Projects].[CreateID]
ON [Projects].[tblProjects]
INSTEAD OF INSERT
AS
DECLARE @projectid varchar(25)
DECLARE @projectname varchar(100)
DECLARE @projectepskey int
DECLARE @maxcount int
SELECT @maxcount = 1

BEGIN
SET NOCOUNT ON;
SELECT @projectname = projectname
FROM inserted
SELECT @projectepskey = projectepskey
FROM inserted

SELECT projectid
FROM Projects.tblProjects
WHERE (projectid = 'NEWPROJ')

IF (@@RowCount = 0)
BEGIN
SELECT @projectid = 'NEWPROJ'
INSERT INTO Projects.tblProjects(projectepskey, projectid, projectname)
VALUES
(@projectepskey, @projectid, @projectname)
END
ELSE
BEGIN
WHILE @maxcount >= 1
BEGIN

BEGIN TRY
SELECT @projectid = 'NEWPROJ-' + CONVERT(varchar, @maxcount)
PRINT @projectid
INSERT INTO Projects.tblProjects(projectepskey, projectid, projectname)
VALUES
(@projectepskey, @projectid, @projectname)
BREAK
END TRY
BEGIN CATCH
SELECT @maxcount = @maxcount + 1
PRINT CONVERT(varchar, @maxcount)
CONTINUE
END CATCH;
END
END
END









View 4 Replies View Related

WMI Event Watcher Is Not Working Properly

May 30, 2006

Hi,

I have an SSIS Package to get the data from an Excel Source. For that I am using WMI Event Watcher to find when the file drops into a specific folder. It is working fine when I check any folders in the local machine.

We have a separate WebServer and Database Server for our application. The application is deployed in the Web server and the SSIS package is deployed in the Database server.

Since the files are dropped through application, it is dropped in the Webserver. I have mapped that drive in my Database Server and I am trying to link that folder in the SSIS package, but the event is not firing.

Please provide me some solution for this and revert for any clarifications.

Thanks & Regards,
Prakash Srinivasan

View 13 Replies View Related

System::ContainerStartTime May Nt Be Working Properly

Jun 12, 2005

Hi,

View 7 Replies View Related

Query In Reference To Dates Not Working Properly

Sep 14, 2006

Here is the query:WHERE DATEPART(month, " + tableName + ".timestamp)>='" + startTextBox.Text + "' AND DATEPART(month, " + tableName + ".timestamp)<='" + endTextBox.Text + "'This is in a program using C# which is why it's in quotes and all that good stuff. The query itself works properly when startTextBox.Text = 8 and endTextBox.Text = 9. Itreturns results for both months 8 and 9. But when I want a result from a single month, say just 9... I put 9 in both text boxes and it ends up returning no results.Logic would tell me that say that both logics should come back TRUE but for some reason it's failing. Any ideas/suggestions? Thanks in advance! 

View 2 Replies View Related

SqlDataSource Insert Query Is Not Working Properly

Jan 7, 2008

 Hello,
I have a SqlDataSource that is not doing my inserts properly. even if the user is logged in (UserName!=""), it always inserts a null in the UserName field.
SqlDataSource:
         <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:imLLConnectionString %>"            DeleteCommand="DELETE FROM [tblDiaryEntries] WHERE [DiaryEntryID] = @DiaryEntryID"            InsertCommand="INSERT INTO [tblDiaryEntries] ([DiaryEntry], [Subject], [EntryDate], [UserName]) VALUES (@DiaryEntry, @Subject, @EntryDate, @UserName)"            SelectCommand="SELECT [DiaryEntry], [Subject], [EntryDate], [DiaryEntryID], [UserName] FROM [tblDiaryEntries] WHERE [UserName]=@UserName"            UpdateCommand="UPDATE [tblDiaryEntries] SET [DiaryEntry] = @DiaryEntry, [Subject] = @Subject, [EntryDate] = @EntryDate WHERE [DiaryEntryID] = @DiaryEntryID">            <DeleteParameters>                <asp:Parameter Name="DiaryEntryID" Type="Int32" />            </DeleteParameters>            <UpdateParameters>                <asp:Parameter Name="DiaryEntry" Type="String" />                <asp:Parameter Name="Subject" Type="String" />                <asp:Parameter Name="EntryDate" Type="String" />                <asp:Parameter Name="UserName" Type="String"/>                <asp:Parameter Name="DiaryEntryID" Type="Int32" />            </UpdateParameters>            <InsertParameters>                <asp:Parameter Name="DiaryEntry" Type="String" />                <asp:Parameter Name="Subject" Type="String" />                <asp:Parameter Name="EntryDate" Type="String" />                <asp:Parameter Name="UserName"  Type="String"/>            </InsertParameters>        </asp:SqlDataSource>
 
and from code behind, i do:
    protected void Page_Load(object sender, EventArgs e)    {        if (!this.IsPostBack)        {            SqlDataSource1.SelectParameters.Add("UserName", this.User.Identity.Name);            SqlDataSource1.InsertParameters.Add("UserName", this.User.Identity.Name);                        }    }
 
Any ideas/suggestions? Thanks! 

View 9 Replies View Related

Standard Input Variable Not Working Properly

Oct 20, 2007

Hi,

I have an Execute Process Task that calls a batch file.

I need to pass in a date value to the batch file.

If I enter the value 20071018 in the "Arguments" section, it works fine.

However, I use the "Standard Input Variable", User::MyDate (which gets assigned in a previous Execute SQL Task), it bombs. The query I am using returns the date back in the format yyyymmdd. It gets assigned to a string variable, called MyDate.

e.g.)
SELECT TOP 1 CONVERT(VARCHAR, MyDate, 112) AS MyDate
FROM myTable

which result is mapped to User::MyDate

Is there any obvious reason why this doesn't work?

Thanks

View 3 Replies View Related

Report Parameter Not Working Properly After Deploy

Feb 21, 2008

Hi All

I have a parameter @User givning a dropdown list from a dataset:

SELECT FullName FROM tvf_GetListOfUsers ('LDAP://OU=Business
Users,OU=Users,OU=Production,DC=MRP,DC=NET,DC=NZ','Subtree')
ORDER BY FullName


The parameter is passed to a second dataset when the User is chosen:

SELECT GroupName FROM tvf_GetUserMembership('LDAP://cn=' + @User +
',ou=business users,ou=users,ou=production,DC=MRP,DC=NET,DC=NZ')
ORDER BY GroupName

This works fine in VS but when I deploy it only works the first time.

That is, I enter the username and run the report - it brings back the
desired results. But when I enter a different username it just redisplays the
old data from the first username I entered.

Any ideas?

Thanks
Pete

PS The funcions above called in the datasets are for querying Active
Directory but I think this is not the cause of the problem.

View 1 Replies View Related

For Each Loop And Script Task Not Working Properly

Oct 5, 2007

Hi,

I have a FOR EACH LOOP that loops through a bunch of folders in search of particular filename (including path). That part works fine.

Then, INSIDE the FOR EACH LOOP, I have a script task that evaluates the filename and grabs the date portion of the filename. That part works fine too.

Now, here's my dilemma. If the date portion of the file name IS GREATER THAN the the value of another date in a variable, I want it to load the file into a table using a data flow task.

So, this is what my package looks like:

[ FOR EACH LOOP (Script task --- evaluate the date using an expression (see below) ---> Data flow task) ]

My expression looks like this:

@[User::fileDate] > @[User::maxLoadDate]

You think that this would work, right??

However, after the VERY FIRST iteration of the loop, the script task turns green, then it goes right to the data flow task, which then says that the file name is "", and can't be loaded. (I'm not sure why this is occuring)

My question is, is my logic correct? Can I use an expression inside the for each loop and have it work?





View 1 Replies View Related

Export Column Tarnsformation Is Not Working Properly

Sep 28, 2007



hi,
i have a table having two column say columnA and columnB.columnA(text datatype) contains the data that is to written in a file and columnB(nvarchar datatype) contains the filepath.

but while configuring the export column tranformation, in the export column field i am getting the list of both the colums. here i am choosing columnA but in the file path name column i am not getiing the name of the column which contains the file name.

can anyone help me regarding how to configure the file path name column in export column transformation. from where we ll get the file path name.

in control flow, i have used only dataflow component and data flow tab i have used oledb source-->export column transformation-->oledb destination.

please help in configuring the export column transformation.
thanks a lot !!!!!!!!!!!!!!!!!!!!!!!

View 2 Replies View Related

Stored Procedure Where Clause Not Working Properly

May 24, 2008

I am having a problem with this stored procedure. I'm using SQL Server 2005 Developer's edition and if I execute the procedure in a query window, I get no errors. Also, when the script runs from a website call there are no errors. The problem is that it doesn't return the information that is in the database. It is supposed to return the orders from Washington state between such and such dates. The orders are there in the database, so I think the where clause must be wrong.

Thanks for the help.

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CommerceLibOrdersGetWashingtonState]') AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

CREATE PROCEDURE [dbo].[CommerceLibOrdersGetWashingtonState]

(@ShippingStateProvince VARCHAR(50),

@ShippingCountry VARCHAR(50),

@StartDate smalldatetime,

@EndDate smalldatetime)

AS

SELECT OrderID,


DateCreated,

DateShipped,

Comments,

Status,

CustomerID,

AuthCode,

Reference,
ShippingCounty,

ShippingStateProvince,

ShippingCountry,

ShippingID,

TaxID,

ShippingAmount,

TaxAmount

FROM Orders

WHERE (DateCreated BETWEEN @StartDate AND @EndDate)

AND (ShippingStateProvince = @ShippingStateProvince)

AND (ShippingCountry = @ShippingCountry)

ORDER BY DateCreated DESC'

END

View 4 Replies View Related

Group By Doesn't Work Properly

Apr 3, 2008



hi guyz i am trying to trap the duplicate records . cannot trap the below dupes any idea. i also used ltrim and rtrim it still thinking it is a duplicate but it is not. what is the best way doing it.


select count(*) , muzenbr from muzealbums_1 where muzenbr = '1000082' group by MuzeNbr, PNOTES, CNOTES, CAT3, Performer having count(*) > 1


MuzeNbr nvarchar no 14 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
PNOTES nvarchar no -1 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
CNOTES nvarchar no -1 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
CAT3 nvarchar no -1 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
Performer nvarchar no 510 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS


1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL
1000082 NULL NULL Classical NULL

View 6 Replies View Related

Attached DB Not Working Properly In Vista Ultimate Enviroment

Aug 8, 2007

hi,

i have installed SQLEXPRESS (SQL Server 9.0.3042) on my windows ultimate laptop. of late am facing this issue of database. this database is from SQl 7.0 Desktop edition(the one that i use in my office machine with Xp SP2). As such it works fine in SQL 2000 server after attachment (for test).


i have run SQLEXPRESS as administrator to make/attach new database. i can attach the database easily and can even view tables and other stuffs inside database in administrator mode. the issue pops up when i run SQLEXPRESS in normal mode. when i try to open and view the same DB's content, error pops up stating


"The database XXX is not accessible. (Micorosoft.sqlserver.express.objectExplorer)"


i cannot access this DB via VB 6 form as it dosnt show up at all in database list when tried to connect via ADODC.

i want to make this DB accessibility as normal operation, not previliged only to administrator as application run on normal mode only.


the issue dosnt arise when i locally make a new database as it can be accessed when run in normal mode also. why does this happen to database that is attached and not to ones locally made.


pls guide me as i usually do my works at home and making whole database again will surely take lots of pain. why not it just attach then start working, not asking for preveliged mode.


thanks in adv
Nyaken

View 3 Replies View Related

Date Parameters In Query Aren't Working Properly

Dec 11, 2007

I'm a SQL newbie, and I'm trying to write a report that returns records based on a beginning and end date that the user supplies. But when I run the query and supply the dates (begin 11/29/2007 / end 11/30/2007, for example), I'm only getting back one record, when there should be at least 3. It appears that my query is ignoring the =, and only returning those records that have a date > or <, not <= or >=.

I was told that it's possible that the time needs to be converted to UTC as well, but I'm not sure how to do that ( I tried, but I really don't know what I'm doing so I commented it out)... Can someone offer some guidance on how to get all the records to show up? I've pasted my query below, thanks in advance!



SELECT FilteredContact.fullname, FilteredContact.new_memberleadsourcename, FilteredContact.new_prospectforclubname, FilteredContact.owneridname,

FilteredContact.new_referred_by, convert(varchar(10),FilteredContact.createdon,101) as Date--, SELECT GETDATE(FilteredContact.createdon) AS CurrentTime, GETUTCDATE(FilteredContact.createdon) AS UTCTime

FROM FilteredContact JOIN filteredOpportunity on

FilteredOpportunity.contactid = Filteredcontact.contactid

where filteredopportunity.new_opportunitytype = 1 and (FilteredContact.createdon >= '11/29/2007') and (FilteredContact.createdon <= '11/30/2007')

View 3 Replies View Related

Reports Containing Drill Through Interactive Bar Charts Not Working Properly When Deployed

Feb 9, 2006

Hi all,

Drill through bar charts work great in the report designer preview, but as soon as I deploy the report, and view the report in a webpage, drill through does not work smoothly.

I have to move the mouse around to be able to find a place on the bar to click through to the next report. Sometimes, I am able to click on the bar to go to next report, but then sometimes I have to click on the label, and other times somewhere in the vicinity of the label.

Any help will be much appreciated.

Thanks,

Kanwal

View 1 Replies View Related

Integration Services :: Send Mail Task Not Working Properly When Configured In Precedence Constraint (Failure)

Jul 29, 2015

Send Mail task not working properly when it configured in Precedence Constraint (Failure)...

Scenario:

I'm using multiple containers in my ssis package. Also I have configured mail task for capturing error.

I'm getting mail only when error occurs in foreach loop container. If error occurs in other containers it doesn't works.

Actually I would like to receive mail notification on error when it occurs in any of the containers or DFT.

View 9 Replies View Related

GROUP BY Not Working?

Mar 8, 2004

I'm having a really odd problem.. this is basic SQL, so it should be easy for most of you. For some strange reason, the quantity is not being summed up. Any idea why this would be happening? Everything else is grouped. The SQL is below, and some of the results are below that (you can see that it SHOULD be grouping them properly).

SELECT TOP 100 PERCENT dbo.cp_elements.campaign_id, dbo.cp_campaigns.name AS campaign_name, dbo.cp_elements.item_no, dbo.cp_elements.name,
dbo.cp_orderables.est_qty, dbo.cp_orderables.qty_increment, SUM(dbo.cp_order_detail.qty) as qty, dbo.cp_attribs.price
FROM dbo.cp_orderables INNER JOIN
dbo.cp_elements ON dbo.cp_orderables.element_id = dbo.cp_elements.element_id INNER JOIN
dbo.cp_attribs ON dbo.cp_orderables.orderable_id = dbo.cp_attribs.orderable_id INNER JOIN
dbo.cp_order_detail ON dbo.cp_attribs.attrib_id = dbo.cp_order_detail.attrib_id INNER JOIN
dbo.cp_selected_shiptos ON dbo.cp_order_detail.shipto_id = dbo.cp_selected_shiptos.shipto_id INNER JOIN
dbo.cp_campaigns ON dbo.cp_elements.campaign_id = dbo.cp_campaigns.campaign_id
GROUP BY dbo.cp_elements.campaign_id, dbo.cp_campaigns.name, dbo.cp_elements.item_no, dbo.cp_elements.name, dbo.cp_selected_shiptos.state,
dbo.cp_orderables.qty_increment, dbo.cp_attribs.price, dbo.cp_orderables.est_qty
ORDER BY dbo.cp_elements.item_no


RESULTS:
16,Project Z,10S,Ten Spot,10,1,0,9.9900
16,Project Z,10S,Ten Spot,10,1,15,9.9900
16,Project Z,10S,Ten Spot,10,1,0,9.9900

That 2nd from last column is the qty_increment.. it's spitting out 3 rows when it should just show one, with 15 as the qty_increment.

Thanks for any help/clues!

View 2 Replies View Related

GROUP BY Not Working

Apr 17, 2008

This query gives me Customer Sales data. SQL Server 2005. This is grouped by Region and Location. A customer such as 'ABC' should only show up once per Location, but it is pulling in some companies more than once. Can anyone help me figure out how to group this so this gets fixed?



SELECTRegion,
Location,
WarehouseCode,
CASE
WHEN CustRank <= 19 THEN CustomerNumber
ELSE 'AllOthers'
END AS CustomerNumber,
CustomerName,
MonthLessEleven,
MonthLessTen,
MonthLessNine,
MonthLessEight,
MonthLessSeven,
MonthLessSix,
MonthLessFive,
MonthLessFour,
MonthLessThree,
MonthLessTwo,
MonthLessOne,
CurrentMonth,
CurrentYearTotal,
LastYearYTD,
LastYearTotal,
CASE
WHEN CustRank <= 19 THEN CustRank
ELSE 20
END AS CustRank
FROM(
SELECTgla.Region,
gla.Location,
ihh.WarehouseCode,
ihh.CustomerNumber,
cm.CustomerName,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 11 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessEleven,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 10 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessTen,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 9 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessNine,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 8 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessEight,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 7 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessSeven,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 6 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessSix,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 5 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessFive,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 4 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessFour,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 3 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessThree,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 2 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessTwo,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 1 THEN ihd.SOExtChargeAmount ELSE 0 END) AS MonthLessOne,
SUM(CASE WHEN DATEDIFF(MONTH, ihh.SOTransDate, GETDATE()) = 0 THEN ihd.SOExtChargeAmount ELSE 0 END) AS CurrentMonth,
SUM(CASE WHEN DATEDIFF(YEAR, ihh.SOTransDate, GETDATE()) = 0 THEN ihd.SOExtChargeAmount ELSE 0 END) AS CurrentYearTotal,
SUM(CASE WHEN DATEADD(YEAR, - 1, GETDATE()) > ihh.SOTransDate AND DATEDIFF(YEAR, ihh.SOTransDate, GETDATE()) = 1 THEN ihd.SOExtChargeAmount ELSE 0 END) AS LastYearYTD,
SUM(CASE WHEN DATEDIFF(YEAR, ihh.SOTransDate, GETDATE()) = 1 THEN ihd.SOExtChargeAmount ELSE 0 END) LastYearTotal,
ROW_NUMBER() OVER (PARTITION BY gla.Region ORDER BY gla.Region, SUM(CASE WHEN DATEDIFF(YEAR, ihh.SOTransDate, GETDATE()) = 0 THEN ihd.SOExtChargeAmount ELSE 0 END) DESC) AS CustRank
FROMMAS_BIF_AR1_CustomerMaster AS cm
INNER JOINMAS_BIF_ARN_InvHistoryHeader AS ihh ON ihh.CustomerNumber = cm.CustomerNumber
INNER JOINMAS_BIF_ARO_InvHistoryDetail AS ihd ON ihd.InvoiceNumber = ihh.InvoiceNumber
INNER JOIN


(
SELECTAccountKey,
Account,
CASE SUBSTRING(Account, 5, 3)
WHEN '936' THEN 'North Region'
WHEN '908' THEN 'East Region'
ELSE 'Unknown'
END AS Region,
CASE SUBSTRING(Account, 5, 3)
WHEN '900' THEN 'ALE'
WHEN '902' THEN 'ATO'
WHEN '904' THEN 'BOW'
WHEN '906' THEN 'BRY'
WHEN '908' THEN 'BPT'
WHEN '910' THEN 'BYD'
WHEN '912' THEN 'BUF'
WHEN '914' THEN 'CLE'
WHEN '916' THEN 'GRN'
WHEN '920' THEN 'DXN'
WHEN '924' THEN 'CTH'
WHEN '926' THEN 'ELC'
WHEN '928' THEN 'FTL'
WHEN '930' THEN 'FTW'
WHEN '932' THEN 'I35'
WHEN '936' THEN 'GAI'
WHEN '000' THEN 'GAI'
WHEN '939' THEN 'STW'
WHEN '940' THEN 'GRE'
WHEN '942' THEN 'HEN'
WHEN '944' THEN 'FTS'
WHEN '948' THEN 'JAC'
WHEN '952' THEN 'JEN'
WHEN '956' THEN 'KIL'
WHEN '957' THEN 'MCA'
WHEN '958' THEN 'MIN'
WHEN '960' THEN 'NOC'
WHEN '962' THEN 'ODE'
WHEN '964' THEN 'BTP'
WHEN '966' THEN 'RA'
WHEN '968' THEN 'RIF'
WHEN '970' THEN 'SWD'
WHEN '971' THEN '3PS'
WHEN '972' THEN 'ROC'
WHEN '976' THEN 'SJO'
WHEN '978' THEN 'SMB'
WHEN '980' THEN 'STO'
WHEN '982' THEN 'TOL'
WHEN '984' THEN 'VEL'
WHEN '985' THEN 'CFP'
WHEN '986' THEN 'CLM'
WHEN '988' THEN 'WHI'
WHEN '992' THEN 'WRA'
WHEN '995' THEN 'ADM'
ELSE 'Unknown'
END AS Location
FROMMAS_BIF_GL_Account
) AS gla ON gla.AccountKey = ihd.SOGLSalesAcct
GROUP BYSUBSTRING(Account, 5, 3),gla.Region, gla.Location, ihh.CustomerNumber,
cm.CustomerName,
gla.Account,
ihh.WarehouseCode
) AS x

View 2 Replies View Related

Help With Modifying Working Query (group By Month)

May 28, 2008

Hi,

I have the following query which is working fine, but its returning a row for each day.

I'd like to modify it somehow so that we had the same data returned, but 1 row for each month. Can anyone offer some insight ?

Any help is much appreciated !!

thanks once again,
mike123






CREATE PROCEDURE [dbo].[select_Stats_LoginHistory]

(
@numDays int
)

AS SET NOCOUNT ON


SELECT CONVERT(varchar(10),LL.loginDate,112) as loginDate,
COUNT(LL.userID) AS TotalLogins,
COUNT(DISTINCT LL.userID) AS TotalLogins_Unique

FROM tblLogins_Log LL WITH (NOLOCK)
WHERE DateDiff(dd, LL.loginDate, GetDate()) < @numDays
GROUP BY CONVERT(varchar(10),LL.loginDate,112)
ORDER BY loginDate DESC


table structure below


CREATE TABLE [dbo].[tblLogins_Log](
[loginID] [int] IDENTITY(1,1) NOT NULL,
[userID] [int] NULL,
[IP] [varchar](15) NOT NULL,
[loginDate] [datetime] NOT NULL
) ON [PRIMARY]

GO

View 1 Replies View Related

SQL 2012 :: Availability Group Failover Stops Working After First Failure

Apr 21, 2014

I've setup a two node Cluster Server (non-shared storage) with a file sharing witness. I'm testing some of the different failover scenarios to see that everything is working properly. Everything works fine until I try testing the failure of the SQL Server service. When I stop the SQL Service on the primary server, it fails over to the secondary server as expected. I then start the service on the (now) secondary server and it comes back online as the secondary server. I then try to test that the service will fail back over when I stop the service on the new primary server.

However, when I stop the service, the secondary server now shows "resolving" and never comes back online. When I bring the service back up on the primary server, the secondary now shows as secondary instead of resolving. So to see if it's something about failing over from one server to another, I do a manual failover making the original primary server the primary again and everything is as it was originally.

I then stop the service on the primary server, but the secondary server now says resolving and the AG will not become available again until I start the service on the primary server.

It seems that when I first configured the quorum it worked fine the first failover scenario, then stopped working. I then added the file sharing witness, and failover worked the first time again, but not after that. For some reason after the initial failover it won't automatically failover again after that.

Config:

Servers: Windows Server 2012 Standard
SQL : SQL Server 2012 Enterprise SP1

View 5 Replies View Related

Cross Database Ownership Chaining Not Working With Windows Group

Mar 18, 2008

We are having a problem with cross database ownership chaining. Below is a description of the problem:

I have a domain group named DOM1GROUP1
I have a domain user DOM2USER1 who is a member of DOM1GROUP1 (note that they are in different domains)
I have a database DB1 which contains a stored procedure (st_insertdata) that does an insert in a table (tb_data) on DB2
DOM1GROUP1 has been granted login rights on the SQL Server
DOM1GROUP1 is a user in both DB1 and DB2
DOM1GROUP1 has execute rights on procedure st_insertdata and insert rights on table tb_data.
All objects are owned by the dbo schema.
The database owner for DB1 and DB2 is sa

When DOM1USER1 executes st_insertdata an error is returned:
The server principal "DOM1USER1" is not able to access the database "DB2" under the current security context.

I've played around with the options "trustworthy" and "db chaining" but these do not make any difference. The only thing that fixes this problem is if I create a login for DOM2USER1 and grant it access to DB2 (with no other rights other than membership of the public role).

It seems that SQL Server does not recognize that DOM2USER1 is a user in DB2 by virtue of its membership of the domain group DOM1GROUP1. Is there a way to get this to work without granting explicit rights to DOM2USER1?

View 4 Replies View Related

Transact SQL :: Working Of Aggregate In Sub-query Using Exists?

Aug 4, 2015

When i am running  below snippet execution plan is showing constant scan instead of referring subquery table.

I want to know  how this query working. and why in execution plan there is no scan /seek which will basically indicate that particular table is getting referred.

select count(*) from A  where exists (select count(1) from B where A.a=B.a)

execution plan has to show  scan or seek for subquery. Surprisingly, output is coming as expected.

View 8 Replies View Related

Transact SQL :: Insert On Merge Statement Not Working

Oct 7, 2015

In a t-sql 2012 merge statement that is listed below, the insert statement on the merge statement listed below is not working. The update statement works though.

Merge test.dbo.LockCombination AS LKC1
USING
(select LKC.lockID,LKC.seq,A.lockCombo1,A.schoolnumber
from
[Inputtb] A
JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number
 
[code]...

Thus would you tell me what I need to do to make the insert statement work on the merge statement listed above?

View 10 Replies View Related

Transact SQL :: Flag Yes Or No Within ID Group

May 20, 2015

I have a table with 4 columns,let say ID, WorkId , HireDate ,TerminateDate

My source date

ID   WorkId   HireDate    TerminateDate
1     123    2006-01-05   2013-01-13
1     456    2006-02-23   2013-04-13
1     789    2005-12-12   2013-06-13
2     913    2004-12-15   2012-01-13
2     406    2006-04-13   2014-04-13
3     113    2009-11-15   2011-01-13
3     106    2010-04-13  
4     513    2002-11-15  
4     168    2011-10-23  
5     342    2007-01-05   2009-01-13      
5     416    2008-02-23                   
5     159    2008-12-12   2010-06-13   
6     542    2005-01-03   2007-01-13      

Rule -1:  set flag = yes

-Whenever terminatedate is not null
-Also with in the ID group if one record terminatedate is null then set flag=yes

Rule-2: Set flag=No
- with in the ID group Whenever terminatedate is null

My output should look like this..

ID   WorkId   HireDate    TerminateDate     Flag
1     123    2006-01-05   2013-01-13        yes ( Rule-1: set flag = yes within the group for ID =1 because all the 3 records terminatedate is not null)
1     456    2006-02-23   2013-04-13        yes
1     789    2005-12-12   2013-06-13        yes
 2     913    2004-12-15   2012-01-13       yes (Rule-1: set flag = yes within the group for ID =2 because all the 2 records terminatedate is not null)

[Code] .....

View 3 Replies View Related

Transact SQL :: Get Max Occurring Value Using GROUP BY

May 14, 2015

I have 3 columns of data CustomerNum, Newsletter, and NumSent.

I need to return the only the CustomerNumber and Newsletter combinations having the Max(NumSent) So it should be a unique customernumber with the newletter having the most numsent.

For the data below, my result set will be:

0000000000000000101 Healthcare
0000000000000000102 Construction-Environ Svcs

How can I do this easily with GROUP BY , Max or subselect?

Sample Data:
0000000000000000101 Healthcare                       19
0000000000000000101 Construction-Environ Svcs 11
0000000000000000101 Manufacturing                   8               

0000000000000000101 Homecare                        5
0000000000000000101 Daycare                          4
0000000000000000102 Healthcare                       9
0000000000000000102 Construction-Environ Svcs 21
0000000000000000102 Manufacturing                   5              

0000000000000000102 Homecare                        11
0000000000000000102 Daycare                          1

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved