need help with Invoice summary query

Question

Post reply

need help with Invoice summary query

acmedeepak

Old Hand

Points: 385
More actions
May 27, 2017 at 5:41 am

#343023

Hi,
I am working on an Invoice summary query. I have multiple queries which need to be merged into one to get the accurate result. Even some column names come from data tables. Please help me merge these queries into one to get the desired output below:
My queries are:
select ItemNumber, SellingPrice from PJ_Item_M where Variation = 'Std' order by ItemNumber
select ItemPrice from PJ_Daily_DbnMatrix_T where ItemNumber = '" & RSItmInfo(0) & "' and Distbn_Date = '" & bShpDate & "' and ItemPrice is Not NULL
select Load_Priority,Store_Number,Location from PJ_Location_M order by Load_Priority,Store_Number
(select ItemNumber, SUM(CAST([" & ResRep(1) & "] as Integer)) from PJ_Matrix_RowDstbn_T where Distbn_Date = '" & bShpDate & "' and ItemNumber = '" & ItemId(j) & "' group by ItemNumber) Union (select ItemNumber,SUM(CAST(Qty as Integer)) from PJ_Special_Order_T where Store_Number = '" & ResRep(1) & "' and SplOrd_Date = '" & bShpDate & "' and ItemNumber = '" & ItemId(j) & "' Group by ItemNumber)
--Total Special order value select sum(CAST(Price as decimal(10,2)) * Qty) from PJ_Special_Order_T where SplOrd_Date = '" & bShpDate & "'
I am new to SQL server. Please help me put these queries into one to get the desired output.
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

Can you please provide the DDL (create table) scripts, sample data as an insert statement and the expected output?
😎

acmedeepak Old Hand Points: 385 More actions · Answer 2

Thanks for the reply. Expected input will be
Please find the tables:
USE [BSJ_APPLN] GO /****** Object: Table [dbo].[PJ_Special_Order_T] Script Date: 27-05-2017 19:07:46 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[PJ_Special_Order_T]( [SplOrd_Num] [numeric](18, 0) NULL, [SplOrd_Date] [datetime] NULL, [SplOrd_Notes] [varchar](200) NULL, [Store_Number] [varchar](10) NULL, [ItemNumber] [varchar](30) NULL, [Variation] [varchar](50) NULL, [ItemName] [varchar](50) NULL, [Qty] [numeric](10, 0) NULL, [Price] [varchar](20) NULL ) ON [PRIMARY] GO

USE [BSJ_APPLN] GO

/****** Object: Table [dbo].[PJ_Matrix_RowDstbn_T] Script Date: 27-05-2017 19:11:36 ******/ SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE TABLE [dbo].[PJ_Matrix_RowDstbn_T]( [Distbn_Date] [datetime] NOT NULL, [ItemNumber] [varchar](30) NOT NULL, [Variation] [varchar](50) NOT NULL, [ItemPrice] [varchar](15) NULL, [206] [varchar](15) NOT NULL, [221] [varchar](15) NOT NULL, [226] [varchar](15) NOT NULL, [230] [varchar](15) NOT NULL, [231] [varchar](15) NOT NULL, [237] [varchar](15) NOT NULL, [240] [varchar](15) NOT NULL, [241] [varchar](15) NOT NULL, [243] [varchar](15) NOT NULL, [244] [varchar](15) NOT NULL, [301] [varchar](15) NOT NULL, [302] [varchar](15) NOT NULL, [303] [varchar](15) NOT NULL, [304] [varchar](15) NOT NULL, [305] [varchar](15) NOT NULL, [306] [varchar](15) NOT NULL, [307] [varchar](15) NOT NULL, [308] [varchar](15) NOT NULL, [310] [varchar](15) NOT NULL, [312] [varchar](15) NOT NULL, [313] [varchar](15) NOT NULL, [314] [varchar](15) NOT NULL, [315] [varchar](15) NOT NULL, [316] [varchar](15) NOT NULL, [318] [varchar](15) NOT NULL, [319] [varchar](15) NOT NULL, [320] [varchar](15) NOT NULL, [321] [varchar](15) NOT NULL, [322] [varchar](15) NOT NULL, [323] [varchar](15) NOT NULL, [324] [varchar](15) NOT NULL, [326] [varchar](15) NOT NULL, [333] [varchar](15) NOT NULL, [780] [varchar](15) NOT NULL, [1062] [varchar](15) NOT NULL, [1070] [varchar](15) NOT NULL, [1093] [varchar](15) NOT NULL, [1166] [varchar](15) NULL, [785] [varchar](50) NULL, [1174] [varchar](15) NULL, [1176] [varchar](15) NULL, [1177] [varchar](15) NULL, [1195] [varchar](15) NULL, [2000] [varchar](15) NULL, [2001] [varchar](15) NULL, [2002] [varchar](15) NULL, [@Store_Number] [varchar](15) NULL, [2003] [varchar](15) NULL, [2004] [varchar](15) NULL, [2005] [varchar](15) NULL, [2006] [varchar](15) NULL ) ON [PRIMARY] GO

USE [BSJ_APPLN] GO

/****** Object: Table [dbo].[PJ_Location_M] Script Date: 27-05-2017 19:12:35 ******/ SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE TABLE [dbo].[PJ_Location_M]( [Store_Number] [varchar](10) NOT NULL, [Location] [varchar](50) NULL, [Address1] [varchar](50) NULL, [Address2] [varchar](50) NULL, [City] [varchar](50) NULL, [State] [varchar](50) NULL, [Zip] [varchar](15) NULL, [Phone] [varchar](15) NULL, [Fax] [varchar](15) NULL, [DMM_Name] [varchar](50) NULL, [Route_Name] [varchar](50) NULL, [Load_Priority] [varchar](10) NULL, [SiteId] [varchar](10) NULL, CONSTRAINT [PK_PJ_Location_M] PRIMARY KEY CLUSTERED ( [Store_Number] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] GO

USE [BSJ_APPLN] GO

/****** Object: Table [dbo].[PJ_Item_M] Script Date: 27-05-2017 19:13:11 ******/ SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE TABLE [dbo].[PJ_Item_M]( [ItemNumber] [varchar](30) NOT NULL, [Variation] [varchar](50) NOT NULL, [ItemName] [varchar](50) NULL, [QtyPerCase] [varchar](10) NULL, [CasePerWagon] [varchar](10) NULL, [QtyPerWagon] [varchar](15) NULL, [CapacityValue] [varchar](15) NULL, [SellingPrice] [varchar](15) NULL, [Mon_Perc] [varchar](10) NULL, [Tue_Perc] [varchar](10) NULL, [Wed_Perc] [varchar](10) NULL, [Thu_perc] [varchar](10) NULL, [Fri_Perc] [varchar](10) NULL, [Sat_Perc] [varchar](10) NULL, [Sun_perc] [varchar](10) NULL, [RetailPrice] [varchar](15) NULL, [MatrixHide] [varchar](10) NULL ) ON [PRIMARY] GO

USE [BSJ_APPLN] GO

/****** Object: Table [dbo].[PJ_Daily_DbnMatrix_T] Script Date: 27-05-2017 19:14:00 ******/ SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE TABLE [dbo].[PJ_Daily_DbnMatrix_T]( [FrDate] [datetime] NULL, [Store_Number] [varchar](10) NULL, [ItemNumber] [varchar](30) NULL, [Variation] [varchar](50) NULL, [Tot_PlanToShip] [varchar](20) NULL, [Actual_PlanToShip] [numeric](18, 0) NULL, [Hot_Item] [varchar](10) NULL, [ItemPrice] [varchar](15) NULL, [Distbn_Date] [datetime] NULL ) ON [PRIMARY] GO

USE [BSJ_APPLN] GO

INSERT INTO [dbo].[PJ_Daily_DbnMatrix_T] ([FrDate] ,[Store_Number] ,[ItemNumber] ,[Variation] ,[Tot_PlanToShip] ,[Actual_PlanToShip] ,[Hot_Item] ,[ItemPrice] ,[Distbn_Date]) VALUES ('2016' ,'206' ,'C123' ,'Flowers' ,'20' ,'25' ,'C123' ,'2.5' ,'2016-02-03') GO

USE [BSJ_APPLN] GO

INSERT INTO [dbo].[PJ_Item_M] ([ItemNumber] ,[Variation] ,[ItemName] ,[QtyPerCase] ,[CasePerWagon] ,[QtyPerWagon] ,[CapacityValue] ,[SellingPrice] ,[Mon_Perc] ,[Tue_Perc] ,[Wed_Perc] ,[Thu_perc] ,[Fri_Perc] ,[Sat_Perc] ,[Sun_perc] ,[RetailPrice] ,[MatrixHide]) VALUES (<ItemNumber, varchar(30),> ,<Variation, varchar(50),> ,<ItemName, varchar(50),> ,<QtyPerCase, varchar(10),> ,<CasePerWagon, varchar(10),> ,<QtyPerWagon, varchar(15),> ,<CapacityValue, varchar(15),> ,<SellingPrice, varchar(15),> ,<Mon_Perc, varchar(10),> ,<Tue_Perc, varchar(10),> ,<Wed_Perc, varchar(10),> ,<Thu_perc, varchar(10),> ,<Fri_Perc, varchar(10),> ,<Sat_Perc, varchar(10),> ,<Sun_perc, varchar(10),> ,<RetailPrice, varchar(15),> ,<MatrixHide, varchar(10),>) GO

Please imagine any values. I have given the table scripts to create. The output pattern is on the image in my first post. Please help me

acmedeepak Old Hand Points: 385 More actions · Answer 3

Hi Eirikur Eiriksson,
Can you please help me with the query. I need to work on it and publish soon.

Thanks,
Deepak

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

acmedeepak - Sunday, May 28, 2017 11:59 PM
Hi Eirikur Eiriksson,
Can you please help me with the query. I need to work on it and publish soon.

Keep in mind that people posting here are volunteers, doing in their free time. If you need guaranteed delivery and deadlines, hire a contractor/consultant.
Today's a public holiday in the UK, so Eirkur's probably out enjoying a day off.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 5

acmedeepak - Sunday, May 28, 2017 11:59 PM
Hi Eirikur Eiriksson,
Can you please help me with the query. I need to work on it and publish soon.
Thanks,
Deepak

You will have to do better than this, looking at the sample data set, it only has one row in one table and no expected result set.
😎

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 6

GilaMonster - Monday, May 29, 2017 12:15 AM
acmedeepak - Sunday, May 28, 2017 11:59 PM
Hi Eirikur Eiriksson,
Can you please help me with the query. I need to work on it and publish soon.
Keep in mind that people posting here are volunteers, doing in their free time. If you need guaranteed delivery and deadlines, hire a contractor/consultant.
Today's a public holiday in the UK, so Eirkur's probably out enjoying a day off.

You got that one right, nice weather, new pizza oven and spoiling me little girls rotten 😀
😎

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Eirikur Eiriksson - Monday, May 29, 2017 4:01 AM
GilaMonster - Monday, May 29, 2017 12:15 AM
acmedeepak - Sunday, May 28, 2017 11:59 PM
Hi Eirikur Eiriksson,
Can you please help me with the query. I need to work on it and publish soon.
Keep in mind that people posting here are volunteers, doing in their free time. If you need guaranteed delivery and deadlines, hire a contractor/consultant.
Today's a public holiday in the UK, so Eirkur's probably out enjoying a day off.
You got that one right, nice weather, new pizza oven and spoiling me little girls rotten 😀
😎

Lucky you. Cold front rolled in over the weekend. It's freezing (for Joburg definitions of the word), cloudy, windy and downright unpleasant.
Not pizza weather. Maybe nice lamb stew weather.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 8

GilaMonster - Monday, May 29, 2017 4:05 AM
Eirikur Eiriksson - Monday, May 29, 2017 4:01 AM
GilaMonster - Monday, May 29, 2017 12:15 AM
acmedeepak - Sunday, May 28, 2017 11:59 PM
Hi Eirikur Eiriksson,
Can you please help me with the query. I need to work on it and publish soon.
Keep in mind that people posting here are volunteers, doing in their free time. If you need guaranteed delivery and deadlines, hire a contractor/consultant.
Today's a public holiday in the UK, so Eirkur's probably out enjoying a day off.
You got that one right, nice weather, new pizza oven and spoiling me little girls rotten 😀
😎
Lucky you. Cold front rolled in over the weekend. It's freezing (for Joburg definitions of the word), cloudy, windy and downright unpleasant.
Not pizza weather. Maybe nice lamb stew weather.

Blistering (London style) 22Â° C,rain in the afternoon but the pizza oven is sheltered.
😎
Maybe some spicy chili in the stew to counter the chill?

acmedeepak Old Hand Points: 385 More actions · Answer 9

acmedeepak

Old Hand

Points: 385

May 29, 2017 at 5:50 am

#1944482

Nice going both of you...

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 10

acmedeepak - Monday, May 29, 2017 5:50 AM
Nice going both of you...

Are you going to update the sample data and the expected result set?
😎