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


ADVERTISEMENT

Query Between Dates But Group By Month

May 22, 2008

hello, how can I query dates using "between" function but grouped by months? for example:

QUERY:
FROM: 15/DIC/2007 TO: 15/FEB/2008

RESULT:
DECEMBER-2007 --- $49,535
JANUARY-2008 --- $45,352
FEBRUARY-2008 --- $52.345

Thanks in advance-!

View 4 Replies View Related

Modifying Repeating Group Headers So That They Are Different On Subsequent Pages

Jan 7, 2008

It is using a table to display a very large amount of data (a basic list of information) with groups. Each of the groups can span multiple pages, so the user may not realize where the grouping actually began (I am repeating the group headers). Therefore, I would like to modify the group header when it is repeated across multiple pages by simply adding the text, "(cont.)" to the header so the reader can easily see that the group started on a prior page. There does not appear to be any standard test function for this condition. Using Previous() for this case does not work. This cannot be a new issue and must have been solved; any help with this would be greatly appreciated. Sorry if this has been asked before, I did search the forum but could not find an answer to this question...

View 3 Replies View Related

Group By Month

Aug 2, 2007



If I have a date column and want to return a columns that sum by each month of the year, what is the best way to do that?

Example

Date Amount

1/3/2007 10
1/7/2007 15
3/4/2007 8
3/21/2007 19
5/33/2007 12
9/6/2007 5
12/8/20007 4
12/12/2007 10

Return:


Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Amount 25 0 27 0 12 0 0 0 5 0 0 14




View 5 Replies View Related

Select Employees By Start Day For 8 Month Working

Jul 15, 2013

I have this column Sdate in my Employees table. This value represent the start date working.

I need to get all of the employees that between 8 and 10 month of work from today. How can I do it?

View 3 Replies View Related

SQL Server 2014 :: How To Find 5th Working Day In A Month

Oct 29, 2013

I would like to know how to find fifth workingday* of a given month and year.

workingday - working day is just any day apart from saturday and sunday. no need to consider any other holidays.

ex: I would just give month number and year as input and i would like to know date and day of the fifth working day.

Input : 10/2013
Output : 7th October 2013, Monday.

View 4 Replies View Related

Calculating Number Of Working Days In A Month

Sep 4, 2007

Hi 2 all,
I need to calculate the number of working days in a month. In clear, i need to ignore saturday.sunday and holiday in a month.

can anyone say the solution?

thanks.

View 2 Replies View Related

Scheduling Report Delivery For The First WORKING Day In The Month?

Sep 16, 2007

Has anyone figured out a good way of doing this?

The only suggestions I have seen along these lines involve scheduling the job every day and then manually editing the SQL Agent job that is created.

However the problem with this is that it completely removes the ability for non programmers to set the schedule themselves using the web interface.

Any ideas?

View 1 Replies View Related

Integration Services :: Run DTS Package On Nth Working Day Of Every Month

Aug 7, 2015

I have a  dts package where user suggested me to run it on nth working day of every month.

View 7 Replies View Related

Group By Hour, Month Etc

Apr 7, 2007

Hi All,
I have an orders table which has a filed called OrderTime which is the exact time when the order was received ? I need to write the following queries
1. Get number of orders per year
2. Get number of orders per month (obviously if a month (for example, november) is in a different year, its to be in a different row)
3. Get number of orders per hour 
Note: If there are no orders in a year etc, it should return a row with 0.
 

View 5 Replies View Related

Group By Month,day,year..

Jan 9, 2006

hi
I tried below but it doesnt work

select date_field from tbl group by DAY(date_field)

View 1 Replies View Related

Group Data By Month

Feb 20, 2008

Hi,
how can i group data using specified date like for example per month.
here is my sample data.

fielddate | fieldnumber
january 1, 2007 | 1
january 5, 2007 | 2
February 2, 2007| 3
March 4, 2007 | 3
March 5, 2007 | 4

i want to sum all fieldnumber by month
result will be:

resultdate | resultnumber
january 2007 | 3
February 2007 | 3
March 2007 | 7

thnx for the help in advance.

View 3 Replies View Related

Group Dates By Month

Jul 20, 2005

I have a table that i want to query and group the records by themonth, what SQL stanment would i use?Somthing like this? apart from i know that the "Group BY (MM/YYYY)"wont work !:)Select Date,NameFROM TableGroup by (MM/YYYY)Does this make sense?Willa

View 1 Replies View Related

Help With Modifying Query

May 29, 2008

Hi,

I have a query that I am working on that involves 2 tables. The query below is working correctly and bringing back the desired results, except I want to add 1 more column of data, and I'm not exactly sure how to write it.

What I want to add is the following data.

For each row that is brought back we want to have the COUNT(*) of users who joined the website (tbluserdetails) where their tbluserdteails.date is > the tblreferemails.referDate

Effectively we are attempting to track how well the "tell a friend" via email feature works, and converts to other joined members.


Any assistance is much appreciated!!

thanks once again
mike123


SELECT CONVERT(varchar(10),referDate,112) AS referDate,

SUM ( CASE WHEN emailSendCount = 0 THEN 1 ELSE 0 END ) as '0',
SUM ( CASE WHEN emailSendCount = 1 THEN 1 ELSE 0 END ) as '1',
SUM ( CASE WHEN emailSendCount = 2 THEN 1 ELSE 0 END ) as '2',
SUM ( CASE WHEN emailSendCount = 3 THEN 1 ELSE 0 END ) as '3',
SUM ( CASE WHEN emailSendCount > 3 THEN 1 ELSE 0 END ) as '> 3',
SUM ( CASE WHEN emailSendCount > 0 THEN 1 ELSE 0 END ) as 'totalSent',

count(*) as totalRefers,
count(distinct(referUserID)) as totalUsers,

SUM ( CASE WHEN emailAddress like '%hotmail%' THEN 1 ELSE 0 END ) as 'hotmail',
SUM ( CASE WHEN emailAddress like '%hotmail.co.uk%' THEN 1 ELSE 0 END ) as 'hotmail.co.uk',
SUM ( CASE WHEN emailAddress like '%yahoo.ca%' THEN 1 ELSE 0 END ) as 'yahoo.ca',
SUM ( CASE WHEN emailAddress like '%yahoo.co.uk%' THEN 1 ELSE 0 END ) as 'yahoo.co.uk',
SUM ( CASE WHEN emailAddress like '%gmail%' THEN 1 ELSE 0 END ) as 'gmail',
SUM ( CASE WHEN emailAddress like '%aol%' THEN 1 ELSE 0 END ) as 'aol',
SUM ( CASE WHEN emailAddress like '%yahoo%' THEN 1 ELSE 0 END ) as 'yahoo',

SUM ( CASE WHEN referalTypeID = 1 THEN 1 ELSE 0 END ) as 'manual',
SUM ( CASE WHEN referalTypeID = 2 THEN 1 ELSE 0 END ) as 'auto'



FROM tblreferemails R

WHERE DateDiff(dd, referDate, GetDate()) < 5

GROUP BY CONVERT(varchar(10),referDate,112)

ORDER BY referDate DESC









CREATE TABLE [dbo].[tblUserDetails]
(
[UserID] [int] IDENTITY(1,1) NOT NULL,
[NameOnline] [varchar](15) NULL,
[EmailAddress] [varchar](50) NULL,
[Date] [datetime] NULL,
[Active] [tinyint] NULL
)




CREATE TABLE [dbo].[tblReferEmails](
[emailID] [int] IDENTITY(1,1) NOT NULL,
[referUserID] [int] NOT NULL,
[destinationName] [varchar](50) NULL,
[emailaddress] [varchar](50) NOT NULL,
[referDate] [datetime] NOT NULL,
[referalTypeID] [int] NULL,
[deleted] [int] NULL,
[emailSendCount] [int] NULL,
[lastDateSent] [smalldatetime] NULL
) ON [PRIMARY]

GO

View 3 Replies View Related

Help In Modifying Query

Nov 19, 2007

Greetings

I have 3 tables. The tables are populated in the following order: One row for CallDetail, One for Call and one for Request and so on

I have to generate a UniqueNo - Per empid, Per StateNo, Per CityNo, Per CallType grouped by the CallDetailID and ordered by the date created
SCRIPTS

DECLARE @Request TABLE(RequestID INT, CustomerName VARCHAR(30),
StateNo NVARCHAR(5), CityNo INT, CallID INT, UniqueNo INT)

INSERT @Request
SELECT '324234', 'Jack', 'SA023', 12, 111, Null UNION ALL
SELECT '223452', 'Tom', 'SA023', 12, 112, Null UNION ALL
SELECT '456456', 'Bobby', 'SA024', 12, 114, Null UNION ALL
SELECT '22322362', 'Guck', 'SA024', 44, 123, Null UNION ALL
SELECT '22654392', 'Luck', 'SA023', 12, 134, Null UNION ALL
SELECT '225652', 'Jim', 'SA055', 67, 143, Null UNION ALL
SELECT '126756', 'Jasm', 'SA055', 67, 145, Null UNION ALL
SELECT '786234', 'Chuck', 'SA055', 67, 154, Null UNION ALL
SELECT '66234', 'Mutuk', 'SA059', 72, 185, Null UNION ALL
SELECT '2232362', 'Buck', 'SA055', 67, 195, Null

DECLARE @Call TABLE(CallID INT, CallType INT, CallDetailID INT)

INSERT @Call
SELECT 111, 1, 12123 UNION ALL
SELECT 112, 1, 12123 UNION ALL
SELECT 114, 2, 12123 UNION ALL
SELECT 123, 2, 12123 UNION ALL
SELECT 134, 3, 12123 UNION ALL
SELECT 143, 1, 6532 UNION ALL
SELECT 145, 1, 6532 UNION ALL
SELECT 154, 2, 6532 UNION ALL
SELECT 185, 2, 6532 UNION ALL
SELECT 195, 3, 6532

DECLARE @CallDetail TABLE(CallDetailID INT, EmpID INT, EntryDt DateTime)

INSERT @CallDetail
SELECT 12123, 1, '11/5/2007 10:41:34 AM' UNION ALL
SELECT 6532, 1, '11/5/2007 12:12:34 PM'
--
--select * from @Request
Query written to achieve the requirement
UPDATE r
SET r.UniqueNo = p.RecID
FROM @Request AS r
INNER JOIN (
SELECT r.RequestID,
ROW_NUMBER() OVER (PARTITION BY cd.EmpID, r.StateNo, r.CityNo, c.CallDetailID, c.CallType ORDER BY cd.EntryDt) AS RecID
FROM @Request AS r
INNER JOIN @Call AS c ON c.CallID = r.CallID
INNER JOIN @CallDetail AS cd ON cd.CallDetailID = c.CallDetailID
) AS p ON p.RequestID = r.RequestID
WHERE r.UniqueNo IS NULL

select * from @Request
OUTPUT :
324234 Jack SA023 12 111 1
223452 Tom SA023 12 112 2
456456 Bobby SA024 12 114 1
22322362 Guck SA024 44 123 1
22654392 Luck SA023 12 134 1
225652 Jim SA055 67 143 1
126756 Jasm SA055 67 145 2
786234 Chuck SA055 67 154 1
66234 Mutuk SA059 72 185 1
2232362 Buck SA055 67 195 1

EXPECTED OUTPUT: (See the last column for unique nos).
324234 Jack SA023 12 111 1
223452 Tom SA023 12 112 1
456456 Bobby SA024 12 114 1
22322362 Guck SA024 44 123 1
22654392 Luck SA023 12 134 1
225652 Jim SA055 67 143 2
126756 Jasm SA055 67 145 2
786234 Chuck SA055 67 154 2
66234 Mutuk SA059 72 185 2
2232362 Buck SA055 67 195 2

How can I modify the query to achieve the expected output?

Thanks:

View 9 Replies View Related

Is There A Way To Change The Index A Query Uses Without Modifying The Query?

May 9, 2006

MS SQL Server 2000 SP3

I'm not the most knowledgable DBA, I've had to learn almost completely on my own, AND on a production server, because it's the only MS SQL Server I have access to.

Everything was fine before I took down the production server for maintenance. Someone suggested that I re-index my tables because I was having some performance issues with a particularly large table (it didn't help that table btw), so I did re-index.

Now, Everything works wonderfully, except for the performance issue mentioned AND one other thing that is going horribly wrong.

Here is the table:

create table ABMcontactlink
(
classifier varchar(20) not null, /* Classification of contact. */
transmitter varchar(36) not null,
contact integer not null, /* Link to ABMcontact (detail) table */
primary key (classifier,transmitter,contact),
foreign key (contact) references ABMcontacts(identifier),
group_name varchar(20) null,

priority smallint null, /* Authorization level. */

type smallint null, /* Autoalarm or Manual */

last_modification_date datetime, /* Date/time record last touched */
last_modification_id varchar(40) /* Who last touched record */
)

go
create index IndexABMcontactlink on
ABMcontactlink(classifier,transmitter)
go

create index CandidateABMcontactlink on
ABMcontactlink(transmitter)
go



As you can see, I have the primary key, which creates a clustered index, PK_ABMContactlink_Some Number, and two other indexes.

Now, this is a very busy production database, and most quick short queries benefit more from CandidateABMContactlink than from the other two indexes.

Unfortunately, in this production system, and this table, seconds count ALOT, so when I have roughly 3000-4000 quereies an hour pulling information from this table, I personally beleive I need to keep CandidateABMContactlink, and I'm not willing to find out on a production server.

** Now to the Problem at Hand **

I have one query that kicks off about 7 times a day, used to take less than 1 minute before the re-index. NOW it takes 30 Minutes. And it drags the system to a crawl.

I did some looking into it, and this query is using CandidateABMContactlink, and it takes 30 minutes. If it uses PK_Abmcontactlink it finishes in under 45 seconds.

Most queries are simple, "Select Column_names from abmcontacts where identifier in (select contact from abmcontactlink where transmitter = 'XXXXXX')"

This one is:


select * from ABMcontacts where (
(last_modification_date >= '2006-04-28 04:40:03' and last_modification_date <= '2006-05-09 16:41:14')
and EXISTS(select contact from ABMcontactlink where contact = identifier
and EXISTS(select transmitter_id from ABMtransmitter where transmitter_id = transmitter and (dealer = 'XXXX'))))


or
(EXISTS(select contact from ABMcontactlink where
(last_modification_date >= '2006-04-28 04:40:03' and last_modification_date <= '2006-05-09 16:41:14')
and contact = identifier and EXISTS(select transmitter_id from ABMtransmitter where transmitter_id = transmitter and (dealer = 'XXXX'))))


I can't change the query, so how do I make it use the Index I want it to use without removing the index that it is using? (I know there are much better ways to write the above query, I'm not the culprit, if I could re-write it, I would)

View 1 Replies View Related

Group By Month And Year Transact SQL

Feb 13, 2001

I want to set up stored procedures that let me group data into months. I use the data to produce charts so I need to be able to group into month and year like '01, 2000', '02, 2000'. What is the best way to produce data grouped into month and year from a date field? Using the Month and Year functions I get data like 1,2000 and 11,2000 which don't stand up to a text sort.

View 1 Replies View Related

Trying To Group &&amp; Subtotal By Year+Month

Feb 20, 2008

Using SQL 2000 Report Designer & Visual Studio 2003 and a report for MS CRM 3.0

I need to group by Year + Month, like now would be "2008 02". Am using the FilteredOpportunity.estimatedclosedate.
I have tried using YEAR(FilteredOpportunity.estimatedclosedate)+MONTH( FilteredOpportunity.estimatedclosedate), but does not work. Also YEAR( FilteredOpportunity.estimatedclosedate.Value). I don't store "2008 02" type data in the CRM.

Any ideas?

View 3 Replies View Related

Reporting Services :: Group By Every Month

Aug 6, 2015

I would like to generate the report for finance office to pay the salary for each staff.

I have some mass data in SQL server

STAFFCODE TIME TYPE
101 31/1/1900 09:00:00 IN
101 1/2/1900 03:02:00 OUT
102 31/1/1900 09:00:00 IN
102 31/1/1900 19:23:00 OUT

I would like to calculate each staff each month working minutes.

MONTH STAFFCODE WORKING_MINS
JAN/1900 101 900
JAN/1900 102 623
FEB/1900 101 182

View 7 Replies View Related

SQL Server 2014 :: Order By Month Number In A Group By

Dec 19, 2014

Sample Data
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE CheckRegistry (
CheckNumber smallint,

[code]...

How can I get the result orderd by the month number?

View 3 Replies View Related

Trying To Group Report Results By Year And Month By Other Variables

May 28, 2008

Below is the code. It gives 2 errors.
1 @StartDate must be defined (it is a report parameter)
2 I cannot seem to get this to create a 5 character field (yy/mm) so I can group count results by this field (Along with all the other queried field)
Help will be appreciated. DECLARE @t TABLE (Owner nvarchar(5), cdate nvarchar(7), status nvarchar(10), jtype nvarchar(5))
DECLARE @sowners nvarchar(5)
DECLARE @mydate datetime
DECLARE @sstatus nvarchar(10)
DECLARE @sjtype nvarchar(5)
DECLARE @chardate nvarchar(7)
DECLARE @Jmonth Int
DECLARE @Jyear Int
DECLARE @Jcharyear nvarchar(4)
DECLARE @Jcharmonth Nvarchar(2)
DECLARE jobcur CURSOR FAST_FORWARD READ_ONLY FOR SELECT Owners, createdate, UserField2, Jobtype
FROM Requirements
WHERE (((Owners IS NOT NULL) AND (Owners <> 'Par')) AND (CreateDate >= @StartDate))
OPEN JobCur
FETCH NEXT FROM JobCur INTO @sowners, @mydate, @sstatus, @sjtype
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Jyear = (Year(@mydate))
SET @Jmonth = (Month(@mydate))
SET @Jcharyear = CAST(@Jyear,4)
SET @Jcharmonth = CAST(@Jmonth,2)
IF Len(Jcharmonth) = 1
BEGIN
SET Jcharmonth = '0' + Jcharmonth
END
IF @Jmonth >= 10
BEGIN
SET @chardate = @Jyear + '/' + @Jmonth
END
IF @Jmonth < 10
BEGIN
SET @chardate = @Jyear + '/0' + @Jmonth
END
INSERT INTO @t (Owner, cdate, status, jtype)
VALUES (@sowners,@chardate,@sstatus,@sjtype)
FETCH NEXT FROM jobcur INTO @sowners, @mydate, @sstatus, @sjtype
END
CLOSE jobcur
DEALLOCATE jobcur
BEGIN
SELECT * FROM @t
END

View 6 Replies View Related

Help With Modifying A Data Source's Query At Runtime

Sep 13, 2007

Please help figure out what is wrong with my code. The script is supposed to load a package (from file). The loaded package already has everything set up to run a query against a local server and output the results to an Excel file. The reason for the outer script is because I need to change the query based on a global variable. When the query changes, though, I think the existing dataflow Path is no longer valid, so I should remove it and re-create another one with the new input mappings. Here is my code, which runs and throws an exception at the AcquireConnections call.

The error is

Error: 0x2 at Script Task: The script threw an exception: Exception from HRESULT: 0xC020801B

I pieced together this code from the examples in the online books, but I am not sure what to do.



' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Pipeline

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper





Public Class ScriptMain


Public Sub Main()

'

Dim app As Microsoft.SqlServer.Dts.Runtime.Application = New Application()

Dim package As Microsoft.SqlServer.Dts.Runtime.Package = _

app.LoadPackage("c:systimeExcelOutExcelOutExcelOutDo.dtsx", Nothing)


Dim pkgVars As Variables = package.Variables

Dim gsVar As Variable = pkgVars("User::gsExcelFile")

Dim currVars As Variables = Dts.Variables

Console.WriteLine(Dts.Variables("User::gsExcelFile").Value)

gsVar.Value = Dts.Variables("User::gsExcelFile").Value

pkgVars("User::gsQuery").Value = Dts.Variables("User::gsQuery").Value

pkgVars("User::gsCreateTable").Value = Dts.Variables("User::gsCreateTable").Value


Dim e As Executable = package.Executables("ExcelOutTask")

Dim thMainPipe As Microsoft.SqlServer.Dts.Runtime.TaskHost = _

CType(e, Microsoft.SqlServer.Dts.Runtime.TaskHost)

Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)


' Get the source component.

Dim SourceComponent As IDTSComponentMetaData90 = _

dataFlowTask.ComponentMetaDataCollection("Local Source")

Dim srcDesignTime As CManagedComponentWrapper = SourceComponent.Instantiate()

srcDesignTime.ProvideComponentProperties()


' Reinitialize the metadata.

srcDesignTime.AcquireConnections(vbNull)

srcDesignTime.ReinitializeMetaData()

srcDesignTime.ReleaseConnections()


' Get the destination component.

Dim destination As IDTSComponentMetaData90 = _

dataFlowTask.ComponentMetaDataCollection("Excel Destination")

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate()

destDesignTime.ProvideComponentProperties()



' Create the path.

dataFlowTask.PathCollection.RemoveAll()

Dim path As IDTSPath90 = dataFlowTask.PathCollection.New()

path.AttachPathAndPropagateNotifications(SourceComponent.OutputCollection(0), _

destination.InputCollection(0))


'Console.WriteLine(dataFlowTask.PathCollection.Count)

Dim ret As DTSExecResult

ret = package.Execute()

Console.WriteLine(ret.ToString)

Dts.TaskResult = Dts.Results.Success

End Sub

End Class


View 3 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

Reporting Services :: Calculating Average Amount Of Working Days In A Month - SSRS 2005 Matrix

Jun 22, 2015

I have got this matrix and I am trying to calculate the average amount of working days in a month. At the moment, I have divided the total number of jobs by 21 for every month which is a hard coded value. However, I am not sure how to retrieve this value dynamically. Is there any formula that can find out the working days?

View 7 Replies View Related

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 View Related

SQL Server 2012 :: Obtaining A Comma Delimited List For Each Group In The Output Of A Group By Query?

Jan 10, 2014

I'd like to ask how you would get the OUTPUT below from the TABLE below:

TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B

OUTPUT:

category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6

The code would go something like:

Select category, count(*), .... as id's
from TABLE
group by category

I just need to find that .... part.

View 3 Replies View Related

Parameter Selection Of Month, Showing Selected Month And Sum Up To That Month In Another Row

Apr 5, 2008

Hello what I'd like to display the following in a matrix report:

Parameter selected: 3 (March), 2008 (Year)

Monthly TO Summed up
ArtNo March <=March
1210 20,500 50,900
1220 21,200 64,000
1230 15,400 40,300
... ... ...

So, in the rows I have the articles and in the column the selected month via parameter. In another column I need to sum up all monthly values up to the selected month, meaning in this example the sum of jan, feb and mar per article.

View 3 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

Query By Day Of The Month

Jul 9, 2004

Looking advice on how to query for just a single day of the month. Example is for billing sent on the same day of each month, need to get all accounts that started on that day of the month no matter which month. If a account started January 7th, and it is July 7th, I would like it to list all accounts that started on the 7th day of the month. The database has a start date field in short date format.

View 1 Replies View Related

Query First Day Of Each Month

Aug 8, 2013

Is my query below the most efficient way to query the first day of each month?

SELECT base_rate, doc_pt, chgdate
FROM producthistory
WHERE chgdate IN
(
SELECT MIN(chgdate)
FROM ProductHistory
WHERE prod_id =1199812
and base_rate > 0
GROUP BY MONTH(chgdate), YEAR(chgdate)
)
AND prod_id = 1199812
ORDER BY chgdate DESC

View 5 Replies View Related

Month Name From The Sql Query

Aug 16, 2007

Hi alli want result as month name from my queryi tried with the following query but it give result as month numberlike (8)select (month(getdate())) as expri want result as month name (Augest)..give me the proper query...from Sachin

View 2 Replies View Related







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